MySQL Master never synchronizes problem handling cases

when using in Mysql's master-slave replication architecture, there are two more headache issues:

1. how to deal with master-slave data after different steps

2. How to solve the problem of master-slave synchronization delay

This article will be based on the actual case to analyze the problem 1, as to the question 2 Most of the documentation is to enable multi-threaded replication to solve, the problem here is 1 can also be subdivided into two cases.

1. slave_io_running and slave_sql_running in the YES Situation, the master-slave data is not synchronized how to deal with?

2. slave_sql_running in no case, master-slave data is not synchronized how to handle?

The first situation is usually caused by manually modifying the data from the library to cause master-slave data inconsistency, this situation if not processed in time, when the main library also updated the corresponding data, it will become the second case.

As an example:

In the condition of the primary one, the current master-slave data is synchronous.

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/82/AF/wKioL1defkyRX4N5AAAsgdsSjK0970.png-wh_500x0-wm_3 -wmp_4-s_3473797334.png "title=" 1.png "alt=" Wkiol1defkyrx4n5aaasgdssjk0970.png-wh_50 "/>

To manipulate a table data from a library, in this case the asm_user table is the demo, where the ID field is the primary key

mysql> INSERT INTO Test.asm_user (id,name,salary) VALUES (1, ' a ', 10000);

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/82/AF/wKioL1defnOQRVV1AAAbXUTtCaw457.png-wh_500x0-wm_3 -wmp_4-s_2509679296.png "title=" 2.png "alt=" Wkiol1defnoqrvv1aaabxuttcaw457.png-wh_50 "/>

When this data of the main library is not changed, slave_io_running and slave_sql_running are still Yes in the current master-slave synchronization process. , currently just Asm_user This table data is not synchronized, corresponding to other Schema on the data will be maintained master-slave synchronization;

But if this is the case, the main library executes the same SQL statement:

mysql> INSERT INTO Test.asm_user (id,name,salary) VALUES (1, ' a ', 10000);

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/82/B1/wKiom1defYWD6mNsAAAulN7mfCk568.png-wh_500x0-wm_3 -wmp_4-s_103739847.png "title=" 3.png "alt=" Wkiom1defywd6mnsaaauln7mfck568.png-wh_50 "/>

The corresponding SQL apply to from the library when you will find duplicate key, This time the master-slave synchronization will be stopped off.

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/82/B0/wKioL1deg_aB1GSFAADVt0D2mrI706.jpg-wh_500x0-wm_3 -wmp_4-s_3095590387.jpg "title=" 1.jpg "alt=" Wkiol1deg_ab1gsfaadvt0d2mri706.jpg-wh_50 "/>

# tail-f/home/mydata/localhost.localdomain.err

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/82/B2/wKiom1degxHSg_fYAAHU5lGGFJE782.jpg-wh_500x0-wm_3 -wmp_4-s_3634734124.jpg "title=" 1.jpg "alt=" Wkiom1degxhsg_fyaahu5lggfje782.jpg-wh_50 "/>

In this case, we generally use the maatkit tool to verify the data differences of the master-slave database.

1 Span style= "font-family: ' The song Body '; >, slave_io_running and Span style= "FONT-FAMILY:CALIBRI;" >slave_sql_running in yes

# yum-y Install Perl-termreadkey # wget ftp://ftp.netbsd.org/pub/pkgsrc/distfiles/maatkit-7540.tar.gz# TAR-ZXVPF Maatkit-7540.tar.gz # CD maatkit-7540# perl makefile.pl # make && make install# mk-table-checksum h=192.168.115.6, u=root,p=123456,p=3306 h=192.168.115.7,u=root,p=123456,p=3306-d Test | mk-checksum-filter# mk-table-checksum h=192.168.115.6,u=root,p=123456,p=3306 h=192.168.115.7,u=root,p=123456,p= 3306-d Test

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/82/AF/wKioL1defwTiewiDAAAf3dxeEvM458.png-wh_500x0-wm_3 -wmp_4-s_2283794377.png "title=" 6.png "alt=" Wkiol1defwtiewidaaaf3dxeevm458.png-wh_50 "/>

data Synchronization with Mk-table-sync if the master-slave data is inconsistent

# mk-table-sync--execute--print--no-check-slave--transaction--databases test h=192.168.115.6,u=root,p=123456 h= 192.168.115.7,u=root,p=123456

Obviously the current test Library data is consistent, the current master-slave synchronization error can be ignored, so we have to skip this transaction to handle the master-slave database synchronization problem. usually in the production environment, the main library of data is constantly updated, here we in the master and slave data is not synchronized in the main library to continue to insert a piece of data to facilitate subsequent verification.

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/82/B1/wKiom1defiiSepqiAAAHhCqI68I693.png-wh_500x0-wm_3 -wmp_4-s_2961374103.png "title=" 7.png "alt=" Wkiom1defiisepqiaaahhcqi68i693.png-wh_50 "/>

Below we begin to deal with the main never sync problem:

when not enabled In the case of GTID replication, the following methods are used to skip transactions:

Mysql>slave stop;  Mysql>set GLOBAL sql_slave_skip_counter = 1; Skips a transaction mysql>slave start;

Mysql5.6 after the support GTID replication, open GTID Replication Benefits Many, specific can Baidu a bit! But when you turn on Gtid, you can't use the previous method to skip the transaction.

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/82/B1/wKiom1defmHANpENAAAbbunNJGg135.png-wh_500x0-wm_3 -wmp_4-s_1301407293.png "title=" 8.png "alt=" Wkiom1defmhanpenaaabbunnjgg135.png-wh_50 "/>

in the show slave status \g; the last few in the output,

relay log from master get location of the log

Executed_gtid_set: Record the binlog log location of the machine , if it is a slave, including the Master binlog Log location and binlog log location of the slave itself)

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/82/B1/wKiom1defnejlgvOAABIDzTvZPo455.png-wh_500x0-wm_3 -wmp_4-s_101777228.png "title=" 9.png "alt=" Wkiom1defnejlgvoaabidztvzpo455.png-wh_50 "/>

We're going to skip the transaction. GTID is logged in the error log

# tail-f/home/mydata/localhost.localdomain.err

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/82/B1/wKiom1defp6BFAf4AABxhFEkNRE916.png-wh_500x0-wm_3 -wmp_4-s_143939303.png "title=" 10.png "alt=" Wkiom1defp6bfaf4aabxhfeknre916.png-wh_50 "/>

Mysql> set session gtid_next= ' bd9e9912-2bc7-11e6-bade-000c29b8871c:1440 ';mysql> begin;commit;mysql> set Session gtid_next=automatic;

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/82/B1/wKiom1defruipUhkAAAWDyHizeU551.png-wh_500x0-wm_3 -wmp_4-s_561589258.png "title=" 11.png "alt=" Wkiom1defruipuhkaaawdyhizeu551.png-wh_50 "/>

mysql> start slave;mysql> show slave status \g;

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/82/B1/wKiom1deftez44ZfAAAxVx15lp4238.png-wh_500x0-wm_3 -wmp_4-s_1155162403.png "title=" 12.png "alt=" Wkiom1deftez44zfaaaxvx15lp4238.png-wh_50 "/>

Verify that the library data is consistent with the main library

Mysql> select * from Test.asm_user;

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/82/AF/wKioL1degAejA92LAAAJkqFK890385.png-wh_500x0-wm_3 -wmp_4-s_3013576113.png "title=" 13.png "alt=" Wkiol1degaeja92laaajkqfk890385.png-wh_50 "/>

In front of the simulation of slave_sql_running in no case, master-slave data synchronization process, in the real environment, often the situation is more complex, Here is a memory development library because of the failure of the master-slave data inconsistency due to power loss processing:

1. because of the power failure, resulting in the master-slave database all down, after power recovery, the main library started normally, from the library can not start, through the analysis of the log discovery may be caused by power failure from the library's SSD anomaly, many binlog file permissions appear??? , these files cannot even be viewed properly

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/82/AF/wKioL1degB-A0o4tAAIJHQ0T6_o437.png-wh_500x0-wm_3 -wmp_4-s_3188579251.png "title=" 14.png "alt=" Wkiol1degb-a0o4taaijhq0t6_o437.png-wh_50 "/>

1. Repair the bad block by fsck-y file system checksum, after the repair is completed from the library database can be started, but when the replication process is turned on the relay log is lost

2. in the absence of a way to use the main repository dump data, The method of re-source from the library online re-master from the data synchronization. During the entire operation, the data of the main library is continuously written.

Here are the general steps:

3.1, the main library to export the whole library data, be sure to use the --single-transaction parameter

#/usr/local/mysql/bin/mysqldump--all-databases--single-transaction--triggers--routines >/tmp/1.sql

3.2, copy the backup file to the source from the library

3.3, opening the copy process from the library

mysql> Change master to master_host= ' 192.168.1.15 ',

Master_user= ' Rep1 ', master_password= ' 123456 ', master_auto_position=1;

mysql> start slave;

This article from "Chop Month" blog, declined reprint!

MySQL Master never synchronizes problem handling cases