MySQL 복제 환경에서 마스터 서버에서 실행한 특정 SQL 문이 슬레이브(복제 서버)로 전파되는 것을 일시적으로 막고 싶은 경우가 있습니다. 예를 들어, 데이터 정리 작업이나 임시 데이터 수정을 마스터에만 적용하고 싶을 때 유용합니다.
이를 구현하려면 sql_log_bin 시스템 변수를 0으로 설정하면 됩니다. 이 변수가 0이 되면 해당 세션에서 실행되는 SQL 문이 바이너리 로그(binary log)에 기록되지 않으므로, 바이너리 로그를 기반으로 동작하는 복제에도 반영되지 않습니다.
테스트용 테이블 생성하기
개념을 이해하기 위해 먼저 테스트용 테이블을 생성해 보겠습니다. 테이블 생성 쿼리는 다음과 같습니다.
mysql> create table SQLStatementsDemo
-> (
-> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> UserName varchar(20)
-> );
Query OK, 0 rows affected (0.79 sec)
샘플 데이터 삽입하기
INSERT 명령을 사용하여 테이블에 레코드를 몇 개 삽입합니다.
mysql> insert into SQLStatementsDemo(UserName) values('John');
Query OK, 1 row affected (0.18 sec)
mysql> insert into SQLStatementsDemo(UserName) values('Carol');
Query OK, 1 row affected (0.14 sec)
mysql> insert into SQLStatementsDemo(UserName) values('Bob');
Query OK, 1 row affected (0.14 sec)
mysql> insert into SQLStatementsDemo(UserName) values('Mike');
Query OK, 1 row affected (0.14 sec)
mysql> insert into SQLStatementsDemo(UserName) values('Sam');
Query OK, 1 row affected (0.14 sec)
mysql> insert into SQLStatementsDemo(UserName) values('David');
Query OK, 1 row affected (0.14 sec)SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select *from SQLStatementsDemo;
실행 결과는 다음과 같습니다.
+--------+----------+
| UserId | UserName |
+--------+----------+
| 1 | John |
| 2 | Carol |
| 3 | Bob |
| 4 | Mike |
| 5 | Sam |
| 6 | David |
+--------+----------+
6 rows in set (0.00 sec)
sql_log_bin을 활용한 복제 제외 실습
이제 MySQL 복제에서 특정 SQL 문을 제외하는 핵심 쿼리들을 살펴보겠습니다. 먼저 SET sql_log_bin=0;을 실행하여 바이너리 로깅을 비활성화한 후, UPDATE, INSERT, DELETE 문을 실행합니다.
mysql> SET sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update SQLStatementsDemo set UserName='Maxwell' where UserId=6;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from SQLStatementsDemo;
+--------+----------+
| UserId | UserName |
+--------+----------+
| 1 | John |
| 2 | Carol |
| 3 | Bob |
| 4 | Mike |
| 5 | Sam |
| 6 | Maxwell |
+--------+----------+
6 rows in set (0.00 sec)
mysql> insert into SQLStatementsDemo(UserName) values('Chris');
Query OK, 1 row affected (0.16 sec)
mysql> select *from SQLStatementsDemo;
+--------+----------+
| UserId | UserName |
+--------+----------+
| 1 | John |
| 2 | Carol |
| 3 | Bob |
| 4 | Mike |
| 5 | Sam |
| 6 | Maxwell |
| 7 | Chris |
+--------+----------+
7 rows in set (0.00 sec)
mysql> delete from SQLStatementsDemo where UserId=7;
Query OK, 1 row affected (0.10 sec)
mysql> SET sql_log_bin=1 ;
Query OK, 0 rows affected (0.00 sec)
동작 원리 및 주의사항
위 예제에서 SET sql_log_bin=0; 실행 이후 수행된 UPDATE(UserId 6번을 'Maxwell'로 변경), INSERT('Chris' 추가), DELETE(UserId 7번 삭제) 문은 모두 마스터에는 적용되지만 바이너리 로그에 기록되지 않기 때문에 슬레이브로는 복제되지 않습니다.
작업이 끝난 후에는 반드시 SET sql_log_bin=1;을 실행하여 바이너리 로깅을 다시 활성화해야 합니다. 그렇지 않으면 이후 실행되는 모든 SQL 문이 계속 복제에서 누락되어 마스터와 슬레이브 간 데이터 불일치가 발생할 수 있습니다.
또한 다음 사항을 유의해야 합니다.
세션 단위 설정: sql_log_bin은 세션(session) 범위의 변수이므로, 해당 설정은 현재 접속 세션에만 영향을 미칩니다. 다른 연결에는 적용되지 않습니다.
SUPER 권한 필요: 이 변수를 변경하려면 SUPER 권한(또는 MySQL 8.0 이상에서는 SESSION_VARIABLES_ADMIN 권한)이 필요합니다.
GTID 사용 시 주의: GTID 기반 복제 환경에서 sql_log_bin=0 상태로 실행한 트랜잭션은 GTID가 부여되지 않으므로, 나중에 해당 데이터를 슬레이브에 수동으로 동기화해야 할 수 있습니다.
데이터 불일치 가능성: 의도적으로 복제를 제외한 작업이므로, 작업 내용과 범위를 신중하게 계획하고 마스터-슬레이브 간 데이터 차이를 인지하고 있어야 합니다.