Reset mysql Master-Slave synchronization (MySQL Reset Master-Slave Replication)

During 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. Under www.2cto.com, We will summarize the steps for resetting the master-slave synchronization to meet your needs.
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 the master-slave data from being synced, you must first stop the synchronization service on the slave. Stop slave; 2. Lock the database on 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 apache or nginx services, and the effect is the same. Www.2cto.com 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. Please refer to 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 unlock tables of the master server database. If you stop apache or nginx, enable them at www.2cto.com 6. copy the backup files on the master node to the slave server. You do not need to use WinScp to download the files to the local computer and then upload them to the slave server. You can directly use the scp command to copy the backup files between servers, which is faster. Scp-r root@XXX.XXX.XXX.XXX:/root/bak. SQL./7. before deleting old data on the slave server, make sure that all of the backup is backed up. Drop database db1; drop database db2; 8. import Data SOURCE/root/bak. SQL; 9. RESET the slave service reset slave; check the official explanation of 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 ). www.2cto.com
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. The success is that show slave status \ G checks the synchronization STATUS, and everything is normal.