Execute steps in order. We call the two servers GOOD and BROKEN. GOOD is the one with the correct data, and BROKEN has corrupted data. The first procedure resets the replica from GOOD to BROKEN and then restores it from BROKEN (which is aligned) to GOOD. You do not have to run it twice. Read the steps carefully!
The first step is to stop the scriptures! It can be done in several ways:
Stop the program or programs that write in the database or if MySql is off it can be started with the skip-networking parameter set in /etc/my.cnf so it does not accept network connections.
Run Database Dump:
# Mysqldump --single-transaction --triggers --routines -u root -p database_name > /tmp/dump.sql
Copy the dump executed on the BROKEN node
# scp /tmp/dump.sql user@BROKEN:/tmp/
Run the slave stop
Mysql> stop slave;
Reset the master
Mysql> reset master;
Verify that GTID_EXECUTED is empty and remains blank for a while after the reset master, otherwise there is someone who is writing.
Mysql> shows global variables like 'GTID_EXECUTED'; + --------------- + ------- + | Variable Name | Value | + --------------- + ------- + | Gtid_executed | | + --------------- + ------- +
If GTID_EXECUTED is not empty repeat the procedure from the beginning, be careful to stop all scripts on BROKEN
Mysql> use database_name Mysql> source /tmp/dump.sql;
Verify that the GLOBAL.GTID_PURGED variable is set to the value inside the dump file
If we used skip-networking, remove it from the configuration file and reboot mysqld on the ROTTO node
Restore replication GOOD => BROKEN
Mysql> start the slave; Mysql> shows the slave status \G [...] Slave_IO_Running: Yes Slave_SQL_Running: Yes [...]
Restore reverse replica, from NOT_ANYMORE_BROKEN to GOOD
mysql> reset slave; mysql> start slave;