서로 다른 MySQL 테이블엔진 간 복제(MyISAM <-> InnoDB)

호밀빵 굽는 쿼카·2026년 5월 25일
InnoDBMyISAMmysql

MySQL`s LAB

목록 보기
7/10
post-thumbnail

2024/02 에 작성한 글을 옮깁니다.

운영 중 버전 업그레이드가 필요한 서비스가 있었습니다. 첫번째로 MyISAM + MySQL5.7 에서 InnoDB + MySQL 8.0 로 업그레이드가 필요했으며, 두번째로는 utf8mb3 로 쓰고있는 charset 도 utf8mb4 로 변환해야했습니다. 업그레이드 시, 무중단 배포가 필요하여 영향도를 확인하기 위해 복제 테스트를 진행했습니다.

필요한 테스트가 무엇인가요?

총 2가지의 케이스를 테스트했습니다.

  • MyISAM + MySQL5.7 <-> InnoDB + MySQL 8.0 간의 복제 가능여부
  • utf8mb3 와 utf8mb4 간의 복제 가능여부

이론상 가능한가요?

1. MyISAM 과 InnoDB 간의 복제가 가능한가?

MySQL의 복제 기능이 데이터베이스 엔진과 무관하게 동작합니다. 마스터 서버의 이벤트를 바이너리 로그에서 읽어들여 SQL 스레드를 통해 실행하기 때문입니다.

2. utf8mb3 와 utf8mb4 간의 복제가 가능한가?

utf8mb4는 utf8의 확장 버전이며, utf8mb4 문자 세트는 utf8 문자 세트의 상위 집합입니다. 이는 utf8mb4가 utf8에 비해 더 많은 문자를 지원한다는 뜻입니다. 복제 가능합니다.

3. 테스트를 진행합니다

1) utfmb3 <-> utfmb4 복제 가능여부

결론은 가능합니다.
먼저, Master <-> Slave 구성의 테스트 서버2대에 utf8 의 database 생성합니다.

[2024-2-20 10:25:32][dev-dbatst-t0101][(none)]> CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8*/;
Query OK, 1 row affected (0.00 sec)
[2024-2-20 10:25:34][dev-dbatst-t0101][(none)]> show create database testdb;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| testdb   | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

slave 테스트 서버의 database charset를 변경합니다.

-- binlog 전파 막고 진행
[2024-2-21 17:13:46][dev-dbatst-t0102][testdb]> set session sql_log_bin = off;
Query OK, 0 rows affected (0.00 sec)

-- 복제 중단
[2024-2-21 19:39:12][dev-dbatst-t0102][testdb]> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

[2024-2-21 16:56:03][dev-dbatst-t0102][(none)]> SELECT concat("ALTER DATABASE `",table_schema,
    ->             "` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ;") as _sql
    -> FROM information_schema.`TABLES`
    -> WHERE table_schema in ( 'testdb')
    -> GROUP BY table_schema;
+--------------------------------------------------------------------------------+
| _sql                                                                           |
+--------------------------------------------------------------------------------+
| ALTER DATABASE `testdb` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ; |
+--------------------------------------------------------------------------------+

-- database charset 변경
[2024-2-21 19:39:17][dev-dbatst-t0102][testdb]> ALTER DATABASE `testdb` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ;
Query OK, 1 row affected (0.09 sec)
[2024-2-21 19:39:44][dev-dbatst-t0102][testdb]> show create database testdb\G;
*************************** 1. row ***************************
       Database: testdb
Create Database: CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)
[2024-2-21 19:39:54][dev-dbatst-t0102][testdb]> SELECT @@hostname, SCHEMA_NAME,
    ->        DEFAULT_COLLATION_NAME,
    ->        TABLE_COLLATION,
    ->        count(*),
    ->        CASE
    ->            WHEN DEFAULT_COLLATION_NAME = TABLE_COLLATION THEN 'True'
    ->            ELSE 'False'
    ->            END AS Collation_Match
    -> FROM information_schema.SCHEMATA AS s
    -> JOIN information_schema.tables AS t ON (s.SCHEMA_NAME = t.table_schema)
    -> WHERE table_schema IN ('testdb')
    -> AND table_type='BASE TABLE'
    -> GROUP BY SCHEMA_NAME, DEFAULT_COLLATION_NAME, TABLE_COLLATION
    -> ORDER BY 2, 3, 4;
+------------------+-------------+------------------------+--------------------+----------+-----------------+
| @@hostname       | SCHEMA_NAME | DEFAULT_COLLATION_NAME | TABLE_COLLATION    | count(*) | Collation_Match |
+------------------+-------------+------------------------+--------------------+----------+-----------------+
| dev-dbatst-t0102 | testdb      | utf8mb4_general_ci     | utf8mb3_general_ci |       1  | False           |
+------------------+-------------+------------------------+--------------------+----------+-----------------+
1 row in set (0.00 sec)

slave 테스트 서버의 table charset를 변경합니다.

[2024-2-21 17:06:34][dev-dbatst-t0102][testdb]> show table status from testdb;
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------+
| tbl_l | MyISAM |      10 | Dynamic    |    3681 |            132 |      488712 | 281474976710655 |       271360 |         0 |              1 | 2024-02-20 10:28:22 | 2024-02-20 10:29:08 | 2024-02-20 10:29:08 | utf8mb3_general_ci |     NULL |                |         |
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------+
1 rows in set (0.00 sec)

[2024-2-21 16:59:54][dev-dbatst-t0102][testdb]> SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,
    ->               "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;") as _sql
    ->   FROM information_schema.`TABLES`
    ->  WHERE table_schema in ( 'testdb')
    ->  GROUP BY table_schema, table_name;
+----------------------------------------------------------------------------------------------------------+
| _sql                                                                                                     |
+----------------------------------------------------------------------------------------------------------+
| ALTER TABLE `testdb`.`tbl_l` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;               |    --> this one
+----------------------------------------------------------------------------------------------------------+
1 rows in set (0.01 sec)

[2024-2-21 19:40:46][dev-dbatst-t0102][testdb]> ALTER TABLE `testdb`.`tbl_l` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;
Query OK, 3681 rows affected (0.54 sec)

복제 상태 정상으로 확인할 수 있습니다.

-- 복제 재개
[2024-2-21 19:42:16][dev-dbatst-t0102][testdb]> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

[2024-2-21 19:42:40][dev-dbatst-t0102][testdb]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.20.10.11
                  Master_User: repl
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000020
          Read_Master_Log_Pos: 329623869
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 323
        Relay_Master_Log_File: mysql-bin.000020
             Slave_IO_Running: Yes    --> 정상
            Slave_SQL_Running: Yes    --> 정상

[2024-2-21 19:42:45][dev-dbatst-t0102][testdb]> SELECT @@hostname, SCHEMA_NAME,
    ->        DEFAULT_COLLATION_NAME,
    ->        TABLE_COLLATION,
    ->        count(*),
    ->        CASE
    ->            WHEN DEFAULT_COLLATION_NAME = TABLE_COLLATION THEN 'True'
    ->            ELSE 'False'
    ->            END AS Collation_Match
    -> FROM information_schema.SCHEMATA AS s
    -> JOIN information_schema.tables AS t ON (s.SCHEMA_NAME = t.table_schema)
    -> WHERE table_schema IN ('testdb')
    -> AND table_type='BASE TABLE'
    -> GROUP BY SCHEMA_NAME, DEFAULT_COLLATION_NAME, TABLE_COLLATION
    -> ORDER BY 2, 3, 4;
+------------------+-------------+------------------------+--------------------+----------+-----------------+
| @@hostname       | SCHEMA_NAME | DEFAULT_COLLATION_NAME | TABLE_COLLATION    | count(*) | Collation_Match |
+------------------+-------------+------------------------+--------------------+----------+-----------------+
| dev-dbatst-t0102 | testdb      | utf8mb4_general_ci     | utf8mb4_general_ci |        1 | True            |
+------------------+-------------+------------------------+--------------------+----------+-----------------+
1 rows in set (0.01 sec)
신규 데이터를 insert 했을때도 정상적으로 복제되는 것을 확인할 수 있습니다.

[2024-2-21 19:45:51][dev-dbatst-t0101][testdb]> INSERT INTO tbl_l (kid, emp_name, emp_no, proxy_empno, dept, email, outemail, proxy_email, mng_no, mng_email, noti, idms_rate, status, create_user_id, create_datetime, update_user_id, update_datetime)
    -> VALUES ('testid01', '홍길동', 'TL00001', NULL, '테스트서비스팀', 'tester01@test.com', 'tester01@gmail.com', NULL, 'TE00001', 'mgr01@test.com', 1, NULL, 1, NULL, NULL, 'BATCH', '2024-02-19 09:04:35');
Query OK, 1 row affected (0.01 sec)
[2024-2-21 19:45:43][dev-dbatst-t0102][testdb]>  select * from tbl_l where kid='testid01';
+----------+-----------+---------+-------------+--------------------+-------------------+
| kid      | emp_name  | emp_no  | proxy_empno | dept               | email             | 
+----------+-----------+---------+-------------+--------------------+-------------------+
| testid01 | 홍길동      | TL00001 | NULL        | 테스트서비스팀         | tester01@test.com |
+----------+-----------+---------+-------------+--------------------+-------------------+
1 row in set (0.00 sec)

[2024-2-21 19:46:25][dev-dbatst-t0102][testdb]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.20.10.11
                  Master_User: repl
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000020
          Read_Master_Log_Pos: 329624590
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 1044
        Relay_Master_Log_File: mysql-bin.000020
             Slave_IO_Running: Yes  --> 정상
            Slave_SQL_Running: Yes  --> 정상


-- binlog 에도 정상 기록
-- 참고로 binlog_format=mixed
#240221 19:46:08 server id 78380100  end_log_pos 329624506 CRC32 0x25e6eab9  Query thread_id=43402386 exec_time=0 error_code=0
SET TIMESTAMP=1708512368/*!*/;
INSERT INTO tbl_l (kid, emp_name, emp_no, proxy_empno, dept, email)
VALUES ('testid01', '홍길동', 'TL00001', NULL, '테스트서비스팀', 'tester01@test.com')
/*!*/;
# at 329624506

2) MyISAM <-> InnoDB 복제 가능여부

결론은 가능합니다. 바로 변경해볼까요?

[2024-2-21 19:42:09][dev-dbatst-t0102][testdb]> ALTER TABLE `testdb`.`tbl_l` ENGINE=InnoDB;
Query OK, 3681 rows affected (0.33 sec)
Records: 3681  Duplicates: 0  Warnings: 0

[2024-2-21 19:47:30][dev-dbatst-t0102][(none)]> show table status from testdb\G;
*************************** 12. row ***************************
           Name: tbl_l
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3653
 Avg_row_length: 435
    Data_length: 1589248
Max_data_length: 0
   Index_length: 688128
      Data_free: 3145728
 Auto_increment: 1
    Create_time: 2024-02-21 19:42:13
    Update_time: 2024-02-21 19:46:08
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:

[2024-2-21 19:42:40][dev-dbatst-t0102][testdb]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.20.10.11
                  Master_User: repl
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000020
          Read_Master_Log_Pos: 329623869
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 323
        Relay_Master_Log_File: mysql-bin.000020
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

# InnoDB 변경 테이블에서도 복제 가능
[2024-2-21 19:55:26][dev-dbatst-t0102][testdb]> INSERT INTO tbl_l (kid, emp_name, emp_no, proxy_empno, dept, email)
    ->  VALUES ('testid02', '홍길동', 'TL00001', NULL, '테스트서비스팀', 'tester01@test.com');
Query OK, 1 row affected (0.00 sec)

[2024-2-21 19:46:22][dev-dbatst-t0101][testdb]> select * from tbl_l where kid='testid02';
+----------+-----------+---------+-------------+--------------------+-------------------+
| kid      | emp_name  | emp_no  | proxy_empno | dept               | email             |
+----------+-----------+---------+-------------+--------------------+-------------------+
| testid02 | 홍길동      | TL00001 | NULL        | 테스트서비스팀         | tester01@test.com | 
+----------+-----------+---------+-------------+--------------------+-------------------+
1 row in set (0.00 sec)

정상적으로 복제되는것을 확인할 수 있습니다.

4. 결론

MyISAM + MySQL5.7 <-> InnoDB + MySQL 8.0 간의 복제 가능
utf8mb3 와 utf8mb4 간의 복제 가능

profile
호밀빵 굽는 쿼카
열심히 굽고 있어요🍞
이전 포스트

waiting for table metadata lock 테스트

다음 포스트

MySQL 트랜잭션 흐름과 복제 메커니즘 깊게 이해하기

0개의 댓글