MySQL8.0 Replication REQUIRE_TABLE_PRIMARY_KEY_CHECK

MySQL 8.0 부터 replication 관련 재밌는 기능들이 많이 생겼는데
그 중 하나가 오늘 소개해드릴 REQUIRE_TABLE_PRIMARY_KEY_CHECK 기능입니다.
replication 구성 환경에서 Table 생성 시 Primary key를 항상 강제시킬 수 있는 기능인데요
이전부터 CUBRID 라는 DB에서는 PK가 없는 테이블은 replication이 안되는데
CUBRID가 떠오르는 기능이네요

REQUIRE_TABLE_PRIMARY_KEY_CHECK 란?

  • performance_schema.replication_applier_configuration (8.0.20 이상 지원)
mysql> desc performance_schema.replication_applier_configuration;
+---------------------------------+---------------------------+------+-----+---------+-------+
| Field                           | Type                      | Null | Key | Default | Extra |
+---------------------------------+---------------------------+------+-----+---------+-------+
| CHANNEL_NAME                    | char(64)                  | NO   | PRI | NULL    |       |
| DESIRED_DELAY                   | int                       | NO   |     | NULL    |       |
| PRIVILEGE_CHECKS_USER           | text                      | YES  |     | NULL    |       |
| REQUIRE_ROW_FORMAT              | enum('YES','NO')          | NO   |     | NULL    |       |
| REQUIRE_TABLE_PRIMARY_KEY_CHECK | enum('STREAM','ON','OFF') | NO   |     | NULL    |       |
+---------------------------------+---------------------------+------+-----+---------+-------+

=> replication 구성에서 Slave가 Master의 트랜잭션을 반영할 때
영향을 주는 설정을 확인할 수 있는 테이블

  • CHANNEL_NAME : replication 채널 이름
  • DESIRED_DELAY : slave 가 replication을 의도적으로 지연하는 시간(초)
  • REQUIRE_ROW_FORMAT : replication을 위한 binary log의 format을 ROW로 강제할 것인지 (default off)
  • REQUIRE_TABLE_PRIMARY_KEY_CHECK : Table을 생성할 때 항상 Primary Key를 강제할 것인지 (default stream)

테스트

  • replication 설정 (slave)
change master to
master_host='172.17.0.6',
master_port=3306,
master_user='repl',
master_password='qhdks123',
master_log_file='binlog.000002',
master_log_pos=823
for channel 'repl1';
  • 특정 replication channel에 옵선 적용
stop slave sql_thread for channel 'repl1';
CHANGE MASTER TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON FOR CHANNEL 'repl1';
start slave sql_thread for channel 'repl1';
  • 옵션 적용 확인
mysql> select * from performance_schema.replication_applier_configuration;
+--------------+---------------+-----------------------+-------------------+---------------------------------+
| CHANNEL_NAME | DESIRED_DELAY | PRIVILEGE_CHECKS_USER | REQUIRE_ROW_FORMAT |REQUIRE_TABLE_PRIMARY_KEY_CHECK |
+--------------+---------------+-----------------------+-------------------+---------------------------------+
| repl1        |             0 | NULL                  | NO                 |ON                              |
| repl2        |             0 | NULL                  | NO                 |STREAM                          |
+--------------+---------------+-----------------------+-------------------+---------------------------------+
2 rows in set (0.00 sec)
  • REQUIRE_TABLE_PRIMARY_KEY_CHECK ON 일 때
### Master

mysql> use repl1_test;
Database changed

mysql> create table repl1 (a int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into repl1 values(1);
Query OK, 1 row affected (0.00 sec)
    

### Slave

show slave status\G;

Last_SQL_Error: Error 'Unable to create or change a table without a primary key, when the systemvariable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variableto avoid this message. Note that tables without a primary key can cause performance problems inrow-based replication, so please consult your DBA before changing this setting.' on query.Default database: 'repl1_test'. Query: 'create table repl1 (a int)'
    
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 23ab24f6-8ea1-11ea-8084-0242ac110006
Channel_Name: repl1

=> sql_require_primary_key 설정에 의해 PK 없는 테이블은 복제하지 않음과 동시에 replication stop

  • REQUIRE_TABLE_PRIMARY_KEY_CHECK OFF 일 때
### Master

mysql> use repl2_test;
Database changed

mysql> create table repl2 (a int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into repl2 values(1);
Query OK, 1 row affected (0.00 sec)
    

### Slave

mysql> select * from repl2_test.repl2;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

=> PK 없어도 상관없음