Manual repair of MySQL master-slave delay inconsistency in actual combat

The first 2 days are often feedback from colleagues CRM backend system and front desk, often reported inconsistent query before and after the problem. At first also very crazy, from the cluster, log tracking, network subcontracting and other aspects of the troubleshooting, have not found the reason, and later through and research and development colleagues to communicate, put forward the line MySQL master-slave database may be inconsistent, so immediately landed MySQL master server, view the replication status, to this very obvious problem to find.

Manual Repair Master-slave delay inconsistent, the main library full mysqldump export, note the parameters. Through practice, it is the result of successful online repair of the Lord's never-consistent results oh. There is a wonderful problem, is just repair the Lord never agree, and soon appeared, delay and pull very big. Do you think that the main library has a large amount of data written, copied from the library will be more and more slowly, I was checking the system crontab, indeed found that there is an automatic script, will be offline database large amounts of data synchronization to the main library. If you want to solve the problem completely, the idea is very important ah, it seems. The following will continue to post online check and repair blog, please pay attention to.

See what storage engine your MySQL has now provided:

Mysql> show engines;

Look at your MySQL current default storage engine:

Mysql> Show variables like '%storage_engine% ';

You want to see what engine the table is using (in the results, the storage engine that the table is currently using is the one behind the parameter engine):

Mysql> Show create table table name;

1. Get a snapshot version from the master server

If you're MyISAM or both MyISAM and InnoDB, use the following command on the master server to export a snapshot of the server: and copy the file to the database

Mysqldump-uroot-p--default-character-set=gbk--add-locks--lock-tables--lock-all-tables--events--triggers-- Routines--flush-logs--master-data=2--databases 51auto_v4 >db.sql

The only InnoDB is to try to use the following command: and copy the file to the database.

Mysqldump-uroot-p--default-character-set=gbk--single-transaction--events--triggers--routines--flush-logs-- master-data=2--databases 51auto_v4 > Db.sql

There are several parameters that need to be used:

--single-transaction This parameter applies only to InnoDB.

--databases the library name of all other databases following MySQL,

The--master-data parameter records the location of the MySQL binary log at the time of the export snapshot, which will be used later.

2. Restore the snapshot version to the slave server

Mysql-uroot-p 51auto_v4 < Db.sql

After the snapshot version is restored to the slave server, the data from the server is consistent with the data from the primary server.

3. Generate the Change master statement from the server: Use the grep command to find the name and location of the binary log

# grep-i "Change Master" Db.sql

--Change MASTER to master_log_file= ' mysql-bin.002515 ',

4. STOP SLAVE;

5. RESET SLAVE;

6. Perform Change master

Performed on #从库5.12

Change MASTER to master_host= ' 172.31.5.11 ', master_user= ' repuser ', master_password= ' 51auto ', master_log_file= ' mysql-bin.002515 ', master_log_pos=894830515;

Performed on #从库5.13

Change MASTER to master_host= ' 172.31.5.11 ', master_user= ' copy ', master_password= ' 51auto ', master_log_file= ' mysql-bin.002515 ', master_log_pos=894830515;

7. START SLAVE;

8. Show SLAVE status\g; Check the status of Slave_io_running and slave_sql_running, and if yes, you are done.

This article is from the "Hanyun.fang" blog, make sure to keep this source http://hanyun.blog.51cto.com/1060170/1621755

Manual repair of MySQL master-slave delay inconsistency in actual combat