How to solve the data gap between the master and slave servers of the MySQL database

Why?MySQL databaseOfMaster/Slave serversIs there a gap between data? The reason is that the master server is busy and the hardware difference between the master server and the slave server is large, which may lead to a large data gap between the master server and the slave server. In order not to affect the application of the MySQL database, we often perform regular data synchronization on the master and slave servers manually. The following describes the steps for manual data synchronization. This process is executed when the load ratio of the database is low. In this case, the master server cannot be updated.

1. Run the following command on the master server:

 
 

  
  
Mysql> flush tables with read lock; Use the read lock to lock existing tables to prevent updates to it.
  
  
 
  
  
Mysql> show master status \ G
  
  
 
  
  
* *************************** 1. row ***************************
  
  
 
  
  
File: binlog.000022
  
  
 
  
  
Position: 592429
  
  
 
  
  
Binlog_Do_DB:
  
  
 
  
  
Binlog_Ignore_DB:
 
 

Record the value after the FILE and the value after Postion, that is, the log name and its offset.

2. Execute the following statement on the slave server:

 
 

  
  
mysql> select MASTER_POS_WAIT('binlog.000022','592429')\G  
  
  
 
  
  
*************************** 1. row ***************************  
  
  
 
  
  
MASTER_POS_WAIT('binlog.000022','592429'): 0 
 
 

The first parameter of MASTER_POS_WAIT is the File value of the master above, and the second parameter is the Position value of the master above.

If the returned value is 0, the synchronization is successful. If the returned value is-1, the synchronization is timed out and exited.

3. operations on the master server:

 
 

  
  
mysql> unlock tables; 
 
 

After the preceding steps are completed, the gap between the master and slave servers of the MySQL database can be solved.