Master/Slave desyncronisation

Question

Some manual operations (writes) have been made on SLAVE instead of MASTER resulting in the replication being stopped.

Could not execute Delete_rows_v1 event on table xxx.newsletter; 
Can't find record in 'newsletter', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; 
the event's master log master-bin.000061, end_log_pos 30109220

I've manually recreated the row which has been deleted on the SLAVE.

Is there a way to easily "resume" it without stopping the master, exporting the DB, and starting the whole process again please ? (This happened months ago so the SLAVE is really out of date)

Running MariaDB 10.x Thanks.

Answer 1

Assuming you have corrected the slave drift, you can just "start slave" again, but

• You may need to skip some transactions, if they have been manually applied to the slave- you can do that with SET GLOBAL sql_slave_skip_counter
• You probably have other differences between the master and the slave, because of the operations you mentioned or because of the skipping of transactions. In that case, using pt-table-checksum and pt-table-sync can help you resync the slave without having to reload the full contents.

Answer 2

A smaller shell script can help the cause assuming you have catered all the differences between master/slave..

provided you have login path created if not use -uroot -ppass otherwise. Moreover you may need to check output log and need to correct the out of sync tables manually.

c=mysql --login-path=lgp --protocol=TCP -NBe "show status like 'slave_running' " |awk '{print $2}'
echo $c
while [ $c == "OFF" ]
do
mysql --login-path=lgp --protocol=TCP -NBe "stop slave"
mysql --login-path=lgp --protocol=TCP -NBe "SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1"
mysql --login-path=lgp --protocol=TCP -NBe "STart SLAVE;do sleep(2);"
mysql --login-path=lgp --protocol=TCP -e "SHOW SLAVE STATUS \G" >>out1.txt

done

Hope it helps