MySQL 동기화 에러 발생시 처리 방법

DB Master è Slave 서버 복제(replicate) 오류 발생시

 

1. 마스터 및 슬레이브 서버 상태 확인

 

mysql Master> show master status \G    ### 마스터 서버 확인

*************************** 1. row ***************************

            File: mysql-bin.000006

        Position: 51549280

    Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

 

mysql Slave> show slave status \G    ### 슬레이브 서버 확인

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.128.171

                  Master_User: 123

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000006

          Read_Master_Log_Pos: 52019836

               Relay_Log_File: 123db02-relay-bin.000024

                Relay_Log_Pos: 44116016

        Relay_Master_Log_File: mysql-bin.000006

             Slave_IO_Running: Yes

            Slave_SQL_Running: no

Last_Errno: 1539

                   Last_Error: Error 'Unknown event 'event_day'' on query. Default database: '123'. Query: 'drop event `BB`.`event_day`'

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 44115870

              Relay_Log_Space: 52020491

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

Seconds_Behind_Master: 3447

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 1539

               Last_SQL_Error: Error 'Unknown event 'event_day'' on query. Default database: '123'. Query: 'drop event `BB`.`event_day`'

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1

 

2. Slave 서버 에러 처리

 

mysql Slave> stop slave;    ### 슬레이브 동기화 중지

mysql Slave> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;    ### 슬레이브 에러 카운트 SKIP

mysql Slave> start slave;    ### 슬레이브 동기화 시작

 

mysql Slave> show slave status \G    ### 슬레이브 서버 확인 (해당 명령어 여러 번 반복 실행해서 Read Master Log Position 과 Relay Log Position 변경 되는지 확인

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.128.171

                  Master_User: 123

                  Master_Port: 3306

                Connect_Retry: 60

Master_Log_File: mysql-bin.000006

          Read_Master_Log_Pos: 52074668

               Relay_Log_File: 123db02-relay-bin.000024

                Relay_Log_Pos: 46225743

        Relay_Master_Log_File: mysql-bin.000006

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes             ç 정상 running 확인

Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 44115870

              Relay_Log_Space: 52020491

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

Seconds_Behind_Master: 3447

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 1539

               Last_SQL_Error: Error 'Unknown event 'event_day'' on query. Default database: '123'. Query: 'drop event `BB`.`event_day`'

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1