JIYIK CN >

You may find this topic a little ridiculous. What can we do if the server stops? Of course, we should restart the service. Restarting the service is no problem. The problem is that a lot of data has been written to the master database during the period when the slave server is stopped. How can we synchronize this data to the slave database?

If the master server is only updating data during the period when the slave server stops running, the slave I/O thread will be automatically started after the slave server starts the service to connect to the master server to request the update event of the binlog file. The Binlog dump thread of the master server will send the updated operation event to the Slave I/O thread, and the Slave I/O thread will write the received content to the relay log file. Then the Slave SQL thread will execute these events, thereby synchronizing the data.

Of course, there may be inconsistencies between the master_log_pos or master_log_file on the slave server and the master server.

Check the information from the server with the following command

mysql> show slave status\G

Check the information of the main server with the following command

mysql> show master status;

Then modify the information of connecting to the master server from the slave server by comparison. You can modify it with the following command

mysql> change master to master_log_pos=value,master_log_file=file

If the above situation goes smoothly, data can be synchronized in time. However, if during the period when the slave server stops running, the master server not only updates the data but also performs the following operations:

mysql> flush master;

This will initialize the bin log file on the master server, and all previous updates will disappear. In this case, simply enabling the service on the slave server or changing information such as master_log_pos will not help, and the data will not be synchronized in time. However, we need to reset the connection information of the slave server in time so that the data can be synchronized in time in the future.

mysql> stop slave;
mysql> reset slave;
mysql> start slave;

Through the above operations, the data updated after flushing the master server can be synchronized to the slave server. However, the previous data will be lost.

If you want to recover the lost data, you can back up the data on other normally operating slave servers and then import it to the current slave server, or directly back up the data on the master server.

All of the above is just one case. The specific problems that may occur during operation may be different for each person. But in general, no matter what the situation is, the solution should be centered around the bin log and the relay log on the slave server, as well as data backup. Therefore, good data backup is the best way to ensure that data will not be lost to the greatest extent.