MySQL Replication skip error

MySQL Replication skip error

There are two ways you can configure MySQL replication : normal and GTID based replication. From MySQL 5.6 GTID based replication is started. However, you can again explore how to setup MySQL Replication on the the following links

1. replication-howto
2. replication-gtids-howto

Sometimes, replication on Slave server is stopped for many a reasons and you need to apply “MySQL Replication skip error” technique to start quickly Slave SQL process. In the following output you will see Replication process is stopped due to Slave_SQL_Running is stopped:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master.example.com
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000185
Read_Master_Log_Pos: 308862289
Relay_Log_File: mysqld-rep-relay-bin.000457
Relay_Log_Pos: 765349146
Relay_Master_Log_File: mysql-bin.000177
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: reb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1146
Last_Error: Error 'Table 'reb_test.users_reb' doesn't exist' on query. Default database: 'reb'. Query: 'update reb_test.users_reb set pbs_id='5' where usr_reb='apitest''
Skip_Counter: 0
Exec_Master_Log_Pos: 765348983
Relay_Log_Space: 8899075028
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1146
Last_SQL_Error: Error 'Table 'reb_test.users_reb' doesn't exist' on query. Default database: 'reb'. Query: 'update reb_test.users_reb set pbs_id='5' where usr_reb='apitest''
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c
Master_Info_File: /data/mysql-replication-reb01/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 170909 21:59:28
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

You see “Slave_IO_Running: Yes” but “Slave_SQL_Running: No” that means your Slave IO process is running and retrieving data from Master but couldn’t execute  due to Slave_SQL_Running process is stopped.

Apply the following commands:

Step-1: Stop the slave server

mysql> stop slave;

Step-2 : Set counter so that slave skip the error sql

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

Step-3: Start slave

mysql> START SLAVE;

For GTID based replication see the following procedure

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master.example.com.bd
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000256
          Read_Master_Log_Pos: 191224912
               Relay_Log_File: mysqld-rep-relay-bin.001715
                Relay_Log_Pos: 52648565
        Relay_Master_Log_File: mysql-bin.000250
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1032
                  Last_Error: Could not execute Update_rows event on table dashboard.links; Can't find record in 'links', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000250, end_log_pos 52648692
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 52648339
              Relay_Log_Space: 6650632528
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table dashboard.links; Can't find record in 'links', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000250, end_log_pos 52648692
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c
             Master_Info_File: /data/mysql-data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 141203 17:47:36
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:183900240-183900398:188376296-241806257
            Executed_Gtid_Set: 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:1-234830504
                Auto_Position: 1
1 row in set (0.00 sec)

You see “Slave_IO_Running: Yes” but “Slave_SQL_Running: No” that means your Slave IO process is running and retrieving data from Master but couldn’t execute  due to Slave_SQL_Running process is stopped. You will find the reasons at line

Last_SQL_Error: Could not execute Update_rows event on table dashboard.links; Can't find record in 'links', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000250, end_log_pos 52648692:

You now need to know which exact  GTID needs to skip to start the replication again. You see at the output

Retrieved_Gtid_Set: 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:183900240-183900398:188376296-241806257

It means Slave retrieved the last GTID 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:241806257.
The next line of the output

Executed_Gtid_Set: 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:1-234830504

It means Slave could execute GTIDs upto 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830504. So GTIDs 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830505 to 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:241806257 are retrieved but  pending to be executed.

That means it stopped during execution of  GTID 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830505(one greater than last executed). So we need to skip this GTID to be executed and this is called “MySQL Replication skip error” tricks. Follow the step by step procedure to apply “MySQL Replication skip error” technique

1. Stop The slave

   mysql> stop slave;
   

2. Set global GTID_NEXT variable to intended GTID to be skipped

   mysql> SET GTID_NEXT='8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830505';
   

3. Execute an empty transaction

   mysql> BEGIN;
   mysql> COMMIT;
   

4. Set global GTID_NEXT variable to AUTOMATIC again

   mysql> SET GTID_NEXT='AUTOMATIC';
   

5. Start Slave again

   mysql> start slave;
   

6. Check Slave status again

   mysql> show slave status\G;
   *************************** 1. row ***************************
                  Slave_IO_State: Waiting for master to send event
                     Master_Host: master.example.com.bd
                     Master_User: repl
                     Master_Port: 3306
                   Connect_Retry: 60
                 Master_Log_File: mysql-bin.000010
             Read_Master_Log_Pos: 1074110656
                  Relay_Log_File: mysqld-rep-relay-bin.001715
                   Relay_Log_Pos: 61333514
           Relay_Master_Log_File: mysql-bin.000250
                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: 61333288
                 Relay_Log_Space: 6658029592
                 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: 970323
   Master_SSL_Verify_Server_Cert: No
                   Last_IO_Errno: 0
                   Last_IO_Error:
                  Last_SQL_Errno: 0
                  Last_SQL_Error:
     Replicate_Ignore_Server_Ids:
                Master_Server_Id: 1
                     Master_UUID: 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c
                Master_Info_File: /data/mysql-data/master.info
                       SQL_Delay: 0
             SQL_Remaining_Delay: NULL
         Slave_SQL_Running_State: System lock
              Master_Retry_Count: 86400
                     Master_Bind:
         Last_IO_Error_Timestamp:
        Last_SQL_Error_Timestamp:
                  Master_SSL_Crl:
              Master_SSL_Crlpath:
              Retrieved_Gtid_Set: 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:183900240-183900398:188376296-241808985
               Executed_Gtid_Set: 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:1-234836479
                   Auto_Position: 1
   1 row in set (0.00 sec)

After issuing “start slave” command , you may again face the same problem. You may need to repeat the procedure until replication process is started without error. Want to do it automatically, read the article “fixing mysql replication error automatically” .

Top Reviewed Books regarding MySQL at amazon.com