mysqlSlaveReplicationFailure

Author: Yanjun Wang

1. Step to Resolve Query Running Timeout Issue

• login into slave database
• run the following commands and copy the result which will be used for further investigation

show slave status\G
show processlist;
show engine innodb status\G
show open tables where In_Use > 0;

START to solve the issue

• run show slave status\G
• check the Last_Errno and Last_Error to identify if it's a timeout issue, and also copy the query for running later.
• run the command to skip that query(will be run manually later) and restart slave

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
START SLAVE;

• run the query you copied manually to keep the slave up-to-date

END to solve the issue

• if Last_Error doesn't provide the query or full query, you can either change the bin/relay log, or running percona to catch the difference for the table between master and slave by run this command from master server to check network_element table

pt-table-sync -u<user have writer permission> --print --sync-to-master <slave db server IP> --tables <databse name>.<target table name> --ask-pass > file_to_store_query.sql
e.g. pt-table-sync -uwriter --print --sync-to-master 10.230.10.15 --tables snmp_manager.network_element --ask-pass > ne.sql

• run the query you get from file_to_store_query.sql, if there is foreign key constraint fails, may check the two items on master and slave manually and find the difference to write an UPDATE query yourself and run the UPDATE query on the slave database