Reset MySQL master-slave synchronization (MySQL reset master-slave replication)

In the process of MySQL master-slave synchronization, the master database and slave database may not be synchronized for various reasons. Although there are some solutions on the Internet, it is sometimes difficult to completely solve the problem, resetting the Master/Slave server may not be the fastest way, but it is the most secure and effective.

Next, we will summarize the steps for resetting the master-slave synchronization in case of unexpected requirements.

Master and slave are both used: centos6.0 + MySQL 5.1.61. If db1 is available, two databases of DB2 need hot backup.

In this article, both shell and MySQL use the root account. in the real environment, replace the account as needed.

 

1. Stop the master-slave synchronization of the slave server

To prevent synchronization of Master/Slave Data, you must stop the synchronization service on slave.

STOP SLAVE;

 

2. Lock the database of the master server

To avoid database update during backup, you must lock the database.

FLUSH TABLES WITH READ LOCK;

If it is a web server, you can also disable the Apache or nginx service, and the effect is the same.

 

3. Back up data on the master

mysqldump -u root -p -databases db1 db2 > bak.sql

 

4. Reset the master service

RESET MASTER;

This is the core syntax for resetting the master. Let's take a look at the official explanation.

Reset master removes all binary log files that are listed in the index file, leaving only a single, empty binary log file with a numeric Suffix. 000001, whereas the numbering is not reset by purge binary logs.

Reset master is not intended to be used while any replication slaves are running. the behavior of reset master when used while slaves are running is undefined (and thus unsupported), whereas purge binary logs may be safely used
While replication slaves are running.

Generally, the reset master will delete all binary logs and create an empty log of. 000001. The reset master does not affect the working status of the slave server. Therefore, blindly executing this command will cause the slave to fail to find the BINLOG of the master, resulting in synchronization failure.

But we just need to reset the synchronization, so we must execute it.

 

5. Unlock the database of the master server

UNLOCK TABLES;

If you stop Apache or nginx, enable them.

 

6. Copy the backup file on the master to the slave server.

You do not need to use winscp to download it to your local computer and then upload it to slave. You can directly use the SCP command to copy data between servers, which is faster.

scp -r root@XXX.XXX.XXX.XXX:/root/bak.sql ./

 

7. delete old data on the slave server

Before deleting a backup, check whether all of the backups are backed up.

DROP DATABASE db1;DROP DATABASE db2;

 

8. Import Data

SOURCE /root/bak.sql;

 

9. Reset the slave Service

RESET SLAVE;

Let's take a look at the official explanation.

Reset slave makes the slave forget its replication position in the master's binary log. this statement is meant to be used for a clean start: it deletes the master.info and relay-log.info files, all the relay log files, and
Starts a new relay log file. To use reset slave, the slave replication threads must be stopped (use stop slave if necessary ).

In general, the reset slave will clear the synchronization location on the slave, delete all old synchronization logs, and start again with the new logs. This is exactly what we want. You must stop the slave service first. We have stopped it in the first step.

 

10. Enable the slave Service

START SLAVE;

Post: http://www.cnblogs.com/sunyuxun/archive/2012/09/13/2683338.html