Summary of the problems after the database master-slave synchronization

in the first week after work, the boss let me go to the master-slave synchronization of the database, mainly a server in Zhaoqing machine room, first of all, the company's database architecture, mainly a multi-master one from, and I need to operate this from the library is mariadb-10.0.13 version, from mariadb-10.0 version, it can be used as a repository for multiple master libraries at the same time. While the main library I am synchronizing is the mysql5.1 version, follow the general procedure as follows:

1, in addition to the main library in the backup time to specify the required backup of the library, I also added the --lock-all-tables and --flush-logs parameters, so that can quickly lock masterinfo binlog file name and POS Point

2, after the backup, the data from the backup to the library, and then into the library

3. Modify masterinfo information According to the refreshed binlog and pos points

4, turn on the master-slave synchronization switch (because it is a multi-master from, so in the configuration parameters from the library to the different main database of the alias settings), such as start slave 'BBS ';

All of the above steps are very smooth and then show slave ' BBS ' status\g View the status of Master-slave synchronization,

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4B/43/wKioL1Qo-Cyw34iLAARX2c6J_fI862.jpg "title=" 1.jpg " alt= "Wkiol1qo-cyw34ilaarx2c6j_fi862.jpg"/>

There seems to be no problem, but open the monitoring page (this is a PHP Program for the main library and data from the library), some library table data are different,

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4B/43/wKioL1Qo-BqxkrELAACQNbD2Abw796.jpg "title=" 2.jpg " alt= "Wkiol1qo-bqxkrelaacqnbd2abw796.jpg"/>

Data differences of dozens of or even hundreds, this is very normal, because in the import of data from the library, the main library will continue to have data updates, but as long as the master-slave synchronization, from the library will slowly sync up

(half an hour later ...) ）

looking at the monitoring page again, it was surprising to find that the differences in data between master and slave were constantly expanding ( that is, the main library is constantly updating data, but the data from the three tables corresponding to the library are not updated from beginning to end This head is big, what is going on, has not encountered this kind of situation before.

Before I fix this, I've done a few things, and the results are all failing, but here's a bit of sharing:

① I export the data from the main library to the difference between the libraries, and then import it into the library, the main parameters are --replace , The main function is to replace insert into with replace into, then align the data, then look again after a while, the difference becomes larger, and the failure ends. This method is very time and energy, because to Brahma from the difference between the table part, if you choose this table to export, small table Fortunately, the big table is very time-consuming.

② after getting the boss's consent, in the early hours of a small period of time to stop the library, in order to ensure that the master-slave data are aligned, opened the main library and master-slave synchronization, just beginning to see there is no problem, but after a while there is a situation before, depressed!!

Finally the eldest brother let me gradually to exclude, in the process of troubleshooting found clues, the specific steps are:

1. in cases where both SQL threads and IO threads are normal, first look at whether the Pos point from the library is changing at all times, if pos the point has changed in case the description IO thread work is normal.

2, then use tail-f to view The update status of Relay-log, can always see the update of SQL statements, and also found the cdb_members table has data updates,

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4B/41/wKiom1Qo-G3SX3kiAATxXBvzFnw456.jpg "title=" 3.jpg " alt= "Wkiom1qo-g3sx3kiaatxxbvzfnw456.jpg"/>

By troubleshooting, you can basically determine that these SQL statements are not written to the database. But why did not write to the database inside, after consulting the boss, the boss and I said there may be a cross-Library update situation. After searching the Internet, we finally have a certain concept of cross-Library update.

first of all, explain the key parameters involved,replicate_do_dband thereplicate_wild_do_table, you can use either of these parameters when you want to synchronize a library (table) or a few libraries (tables) from the library.replicate_ignore_dband thereplicate_wild_ignore_tableto ignore libraries and tables that do not need to be updated, now mostlyreplicate_do_dband thereplicate_wild_do_tableThe difference between the two parameters:

For example:

A two database has been implemented master-slave synchronization, now there are two databases in the main library test01 and test02 test01 Span style= "font-family: ' The song Body '; There is a table if my.cnf The parameters are set replicate_do_db=test01,test02

Use test01 ;

Update Test01.table1 set...

The result of the execution is that the master-slave library can see the updated data

But if it is another type of execution, update the statement

Use test02 ;

Update Test01.table1 set...

The result of the execution is that the main library can see the data, but the updated data cannot be seen from the library

Cause: After setting replicate_do_db ,MySQL checks the current default database before executing SQL, so the cross-Library UPDATE statement Slave will be ignored.

For cross-Library update SQL statements,replicate_wild_do_table can be resolved, that is, in the parameters of my.cnf set

(correct wording)

replicate_wild_do_table=test01.%

replicate_wild_do_table=test02.%

(Wrong wording)

replicate_wild_do_table=test01.%,test02.%

Note libraries that need to be synchronized must be written in line and cannot be separated by commas in the same row, otherwise this parameter does not take effect when synchronizing

Here is a description of the MySQL official documentation

Warning

To specify multiple databases your must use multiple instances for this option. Because database names can contain commas, if you supply a comma separated list then the list

would be treated as the name of a single database.

This article is from the "Mfy Road" blog, please be sure to keep this source http://fyywzl.blog.51cto.com/7824086/1559380

Summary of the problems after the database master-slave synchronization