MySQL 복제 문제 발생시 Skip - Replication Error Skip

Last Updated on 1월 20, 2024 by Jade(정현호)

MySQL 의 복제(Replication, 동기화) 를 사용하는 과정에서 여러 이유로 Slave 에서 복제가 중단될 수 있습니다. 문제 발생시 Slave(Replica) 노드에서 show slave status\G 를 하였을 때 아래와 같은 에러 메세지가 확인됩니다

에러 메세지는 다양하게 나타나며 아래는 예시입니다.

Last_SQL_Error: Error Duplicate entry 2 for key 1 on query. Default database: TESTDB. Query: INSERT INTO ....

발생된 에러 내역에 따라 조치 방안은 달라질 수 있으나 PK 키가 중복된 상태이거나 이미 테이블이 생성이 되었다는 에러나 이미 유저가 생성이 되어있다는 에러의 유형과 같이 skip(해당 트랜잭션을 drop) 이 필요하거나 해도 무방할 경우 skip 을 고려할 수 있습니다.
           

sql_slave_skip_counter 사용

sql_slave_skip_counter 를 사용하여 에러가 난 만큼을 skip 을 할 수 있으며 이 방법은 gtid 를 사용하지 않는 master binlog 와 pos 정보를 통하여 replication 하는 환경에서 사용하면 됩니다.

1개의 에러만큼 skip을 하려면 아래의 진행 순서대로 하면 됩니다.

mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;

mysql> start slave;
mysql> show slave status\G

skip 하였으나 다시 replication 에러가 발생할 경우 위의 과정을 재차 반복합니다.
            

GTID_NEXT 사용

gtid 즉 Global Transaction IDentifier 를 사용하여 Replication 을 사용하는 환경에서는 gtid_next 를 통해서 skip 을 진행할 수 있습니다.

Last_SQL_Error: Error 'Operation CREATE USER failed for 'test'@'%'' on query. Default database: ''

Retrieved_Gtid_Set: 1f3b100f-8275-11eb-b444-08002761d7ed:1-1405
Executed_Gtid_Set: 1f3b100f-8275-11eb-b444-08002761d7ed:1-1402,

위와 같이 에러가 발생시 1402 까지는 진행된 상태이고 1403 에서 에러가 발생된 상태임으로 1403 에 대해서 처리를 진행하면 됩니다.

또는 performance_schema.replication_applier_status_by_worker 를 조회하면 아래와 같이 에러 내역을 더 자세히 확인할 수 있습니다.

mysql> selct * 
from performance_schema.replication_applier_status_by_worker\G

******************** 1. row *********************
CHANNEL_NAME: REPL_CH1
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION : 1f3b100f-8275-11eb-b444-08002761d7ed:1-1403 
LAST_ERROR_NUMBER: 1133
LAST_ERROR_MESSAGE: Worker 1 failed executing .... 1f3b100f-8275-11eb-b444-08002761d7ed:1-1403
LAST_ERROR_TIMESTAMP: 2021-03-12 ...

[참고] 5.7 버전 기준의 정보이며, 8.0 버전에서는 더 많은 컬럼 정보가 있습니다.

위의 정보에서도 1403 를 처리하던 중 문제가 발생되었음을 알 수 있습니다.


SET GTID_NEXT 입력 시 아래와 같이 -(대시) 앞에는 제외하고 입력하면 됩니다.

mysql> SET GTID_NEXT="1f3b100f-8275-11eb-b444-08002761d7ed:1403";

그리고 문제가 된 트랜잭션을 skip 할 수 있도록 임의의 트랜잭션을 생성하고 나서 다시 replication 을 시작합니다.

mysql> begin;
mysql> commit;

mysql> set gtid_next='AUTOMATIC';
mysql> start slave;

replication 상태를 조회해보면 정상적으로 skip이 되는 것을 확인할 수 있습니다.

mysql> show slave status\g

MSR의 Channel 사용시

Multi Source Replication(MSR) 으로 되어 있을 경우 아래와 채널 별 중지 및 시작 등으로 skip 을 수행할 수 있습니다.
   

binlog,Pos 사용 환경

mysql> stop slave for channel 'ch_master1';
mysql> set global sql_slave_skip_counter=1;

mysql> start slave for channel 'ch_master1';
mysql> show slave status for channel 'ch_master1'\G


# 복제 상태 정상으로 변경됨
# Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

[참고] error 가 skip 되었다면 sql_slave_skip_count 는 0 으로 됩니다
그래서 가령 여러 채널에서 에러가 발생되어 채널별로 skip을 진행한다면 "1번 채널 stop -> skip=1 -> 1번 채널 start , 그리고 2번 채널 stop -> skip=1 -> 2번 채널 start" 와 같이 skip 구문을 여러번 써주거나 skip을 처음 부터 숫자를 높이거나 하는 방법을 고려할 수 있습니다.
1건의 skip 마다 확인 후 진행하고자 한다면 skip=1 -> 시작 한다음 status 로 확인 후 여전히 skip 할 내역이 남아 있다면 stop-> skip=1 -> start 형태로 체크하면서 반복해서 진행하는 것도 고려할 수 있습니다.
            

GTID 사용 환경

gtid 즉 Global Transaction IDentifier 를 사용하여 Replication 을 사용하는 환경에서는 gtid_next 를 통해서 skip 을 진행할 수 있습니다.

포스팅에서는 1418 트랜잭션을 skip 해야 하는 상황입니다.
mysql> stop slave for channel 'ch_master1';

# 현재 Replication 상황
Last_SQL_Error: Error 'Unknown database 'tdb'' on query.
Default database: 'testdb1'. 
Query: 'create table tdb.tb_error1(no int)'
<중략>
Retrieved_Gtid_Set: 1f3b100f-8275-11eb-b444-08002761d7ed:1412-1418
Executed_Gtid_Set: 1f3b100f-8275-11eb-b444-08002761d7ed:1-1417

# 1418 트랜잭션을 skip 해야 하는 상황

절차는 아래와 같이 수행하면 됩니다.

mysql> SET GTID_NEXT="1f3b100f-8275-11eb-b444-08002761d7ed:1418";


# 그리고 문제가 된 트랜잭션을 skip 할 수 있도록 
# 임의의 트랜잭션을 생성 하고 나서 다시 replication 을 시작합니다.

mysql> begin;
mysql> commit;   
mysql> set gtid_next='AUTOMATIC';
mysql> start slave for channel 'ch_master1';

mysql> show slave status for channel 'ch_master1'\G

slave_skip_errors

slave_skip_errors 는 구성이나 데이터나 흐름 등의 이류로 자주 발생되는 에러가 있을 경우 slave 해당 에러만 skip 하여 replication 의 중단을 방지할 수 있습니다.

에러가 빈번하게 발생하고 구성이나 여러 이유로 skip 해야 할 경우 my.cnf 에서 slave_skip_errors 를 설정하여 사용할 수 있습니다.

[mysqld]
slave_skip_errors=1032

,(콤마) 를 사용하여 여러 개의 에러 코드를 설정할 수 있습니다.


관련된 다른 글

MySQL Replication(복제) 구성 및 설정 - Async - Semi Async

리플리케이션(Replication)은 DB의 내용을 복제하여 사용하는 의미 하며 복제 대상인 Master 가 1개 또는 다수의 Slave(Standby) 가 복제를 하여 사용하는 것을 의미 합니다

MySQL Shell - Dump Utility - mysqlsh

MySQL Shell은 MySQL 용 고급 클라이언트 및 코드 편집기입니다 mysql 클라이언트 와 유사한 SQL 기능 외에도 MySQL Shell은 JavaScript 및 Python에 대한 스크립팅 기능을 제공 하고 있습니다.

MySQL 8.0 업그레이드

MySQL 8.0 으로 업그레이드는 5.7의 GA 버전에서만 지원되고 있으며 업그레이드의 전반적인 절차 등을 설명 하고 있습니다.

오라클 클라우드 MySQL Database Service (1) - MDS - Oracle Cloud

오라클 클라우드 에서 MDS 는 MySQL Database Service 의 약자로 MySQL 데이터베이스의 PaaS형 클라우드 서비스를 의미 합니다.

MySQL FullText Search 전문검색 기능

전문검색이란 게시물의 내용이나 제목 등과 같이 문장이나 문서의 내용에서 키워드를 검색하는 기능입니다.

MySQL 쿼리 프로파일링(Query Profiling)

쿼리 프로파일링(Query Profiling) MySQL 에서 쿼리가 처리되는 동안 각 단계별 작업에 시간이 얼마나 걸렸는지 확인 할 수 있는 기능 입니다.

MySQL8 invisible columns

MySQL 8.0.23부터 invisible columns을 지원합니다 invisible columns은 일반적으로 쿼리에 숨겨져 있지만 명시 적으로 참조되는 경우 액세스 할 수 있습니다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io