mysql replication sync가 되지 않을 때, 조치 방법
mysql replication 운영중에 slave 서버에 sync 오류가 발생하는 경우 아래와 같이 조치가 가능하다.
1. slave 서버에서 replication 상태 확인
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: MASTER
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 96305365
Relay_Log_File: slave-relay-bin.000006
Relay_Log_Pos: 73184178
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: THEEYE
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error ‘Duplicate entry ‘116069-10002826′ for key 1’ on query. Default database: ‘THEEYE’. Query: ‘INSERT INTO TABLE ( IDX, NAME, REG_DATE ) VALUES ( 3, “GOOD”, NOW() )’
Skip_Counter: 0
Exec_Master_Log_Pos: 73184876
Relay_Log_Space: 96304951
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
mysql>
-> Read_Master_Log_Pos: 값과 Exec_masterlog_pos 값이 차이가 날 경우, 정상적으로 replication 이 실행되지 않는 상태
2. replication 에러가 발생한 error 코드 확인
show slave status\G; 에서 errno 확인
Last_Errno: 1062
3. my.cnf 파일에 slave-skip-error 옵션을 추가하고 restart
-> my.cnf 파일에서 slave-skip-errors 옵션을 추가하고, 에러가 발생한 코드가 skip 되도록 설정한 후, slave 서버의 mysql을 restart 한다.
[mysqld]
…
slave-skip-errors = 1053,1062,1146
4. mysql 접속하여 replication sync 상태를 확인
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.4
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000613
Read_Master_Log_Pos: 235524924
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 1256444
Relay_Master_Log_File: mysql-bin.000613
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 235524924
Relay_Log_Space: 1256444
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
-> 그동안 맞춰지지 않던 Exec_Master_Log_Pos 값이 점점 올라가면서 Read_Master_Log_Pos 값과 동일하게 맞춰지면 동기화 완료