MySQL database master never synchronously resolves a case

Problem Description: Do the master and slave of MySQL, have not the decision from separation, cause colleague in MySQL from server performed write operation, cause out of sync (show slave status\g;slave_sql_running:no)

Workaround:

1,

Mysql > Slave stop; Mysql > Set global sql_slave_skip_counter = 1; Mysql > Slave start; Mysql > Show Slave status\g

Because the data inserted is more than 1, the operation is too slow and troublesome

2,

In the main query show Master Status\g, and then execute from this point

Change Master to master_host= ' master_ip ', master_user= ' user ', master_password= ' pwd ', master_port=3306, Master_log_ File= ' mysql-bin.00000* ', master_log_pos=000000***;

The data will be lost more.

So, the first method is chosen, but set global sql_slave_skip_counter=500 the value up, jumps over 500 at a time, loops through the 4 statements until

Slave_io_running:yes

Slave_sql_running:yes

Result: The result of this operation is to lose some data, but a little bit, and one of the tables is not synchronized (because there is a jump from the original should be executed), then solve the problem, choose the database insertion frequency is very small, it is best not to have data write at this time, To avoid affecting the production environment (just give examples to illustrate the steps to resolve)

3,

To log in to the main library with Navicat for MySQL, the following actions are performed in the main library

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/43/78/wKioL1PbMU_BkPRvAAMo-F26xnA808.jpg "title=" Qq20140801141335.jpg "alt=" Wkiol1pbmu_bkprvaamo-f26xna808.jpg "/>

1, in the main library query "select COUNT (*) from date.table;" Know now how many rows of this table, as the final verification, select "Copy table", the copied table name is table_name_copy, the replicated table data will not be synchronized

2, export the copied table, select "Dump SQL file----structure and data", save to the hard disk

3. Delete this copy of the table table_name_copy. Right-click "Run SQL File" in the database and import the data you just exported.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/43/78/wKiom1PbMOzB1BAAAADB04jtqr4962.jpg "title=" Qq20140801141335.jpg "alt=" Wkiom1pbmozb1baaaadb04jtqr4962.jpg "/>

Now the result is that the table you just imported table_name_copy master and slave must be synchronous

4, the key step, continue to check the main library "select COUNT (*) from date.table;" Look at the data table has no write content, if not write, hurriedly F2 table name change, 3 seconds

TABLE_NAME (data pair, not synchronized) changed to Table_name1

Table_name_copy (synchronized when data is not written table_name) to TABLE_NAME

Again "SELECT COUNT (*) from date.table;" Query to see if there is any data inserted

5, summary, the master never synchronization will cause the query data inconsistency, do this action before and use this table to discuss with the person, to ensure that there is no accident. Master and slave do a good job, either do read and write separation, or control permissions, from only check, no other permissions, so that there will be no problem

This article is from "a Stone Bbotte blog" blog, please be sure to keep this source http://bbotte.blog.51cto.com/6205307/1533740