MySQL dual master use reset Master to clear logs be careful

Suppose A, b two servers do MySQL dual master.

There's a Table tab.

CREATE TABLE tab (ID int primary KEY auto_increment,name varchar (20));

And then

If it is MySQL master, A is the main, then a machine

Reset Master;

Then the B-machine

Stop slave;

Reset slave;

Start slave;

If after the A-machine reset master, before the B-machine stop Slave, there are new data insertion, deletion and other operations, when the B machine start slave, B machine will not lose data, those new data will be synchronized.

B machine Why to stop Slave;reset Slave;start slave; ？

Since reset master, to the B-machine show slave status\g Although still shows the synchronization, but you can go to the A-machine show master status, you will find with the B-machine show slave status\g display Master_log_ File, Relay_master_log_file is not a log file that points to the A-machine show Master status, because after reset Master, the Binlog log for a machine starts with xxx.000001 and the previous logs are deleted. The log that points to is not the log of a machine, B machine is of course impossible to synchronize. Even if a new record is inserted in a machine, the Master_log_file and relay_master_log_file of the B machine do not point to the new log, and restarting MySQL is not valid.

Error message:

Last_io_error:got fatal Error 1236 from master if reading data from binary log: ' Could not ' find next log '

Only stop Slave;reset Slave;start slave to re-point to the new log. Before the B machine executes these commands, the data inserted into the A machine will be synced back after running these commands, so there is no need to worry about data loss.

But the MySQL two owners will cause some special situation to cause the data problem and will affect the synchronization.

Assuming the previous Table tab, there is data

+----+------+

| ID | name |

+----+------+

| 1 | A |

| 2 | B |

| 3 | C |

Because of the dual-master synchronization, the A-machine and the B-machine data are consistent

A machine

Reset Master;

Then the B-machine

Stop slave;

Reset slave;

Start slave;

After the a machine reset master, before the B-machine stop Slave, there are new data inserted into a and b respectively, then the problem comes

In a machine

Insert into tab (name) values (' d ');

SELECT * from tab;

Get

+----+------+

| ID | name |

+----+------+

| 1 | A |

| 2 | B |

| 3 | C |

| 4 | D |

B Machine

SELECT * from tab;

+----+------+

| ID | name |

+----+------+

| 1 | A |

| 2 | B |

| 3 | C |

Before I said the reason, I stopped talking.

And then on the B-machine

Insert into tab (name) values (' E ');

SELECT * from tab;

Get

+----+------+

| ID | name |

+----+------+

| 1 | A |

| 2 | B |

| 3 | C |

| 4 | e |

At this time a machine table Tab,id is 4, name is the D,b machine table Tab,id is 4,name is E, the table ID is the primary key, in this case, the execution of Stop slave;reset Slave;start slave;

Last_error:error ' Duplicate entry ' 4 ' for key ' PRIMARY "on query. Default database: '. Query: ' INSERT into Test.tab (name) VALUES (' d ') '

The reason is that the ID of the table is the primary key, B machine ID has 4, so that a machine

ID Name

| 4 | D |

Synchronization but come, there is such a mistake, can only manually synchronize the data, and then re-master ...

Summary: To reset master, the user should be forbidden to increase the deletion. If it is to provide database services for the site, it will close the site, to prevent additions and deletions, and then reset it. Want to make reset master a script and run it in a scheduled task? Want to think twice good 650) this.width=650; "src=" Http://img.baidu.com/hi/jx2/j_0057.gif "alt=" J_0057.gif "/>

MySQL dual master use reset Master to clear logs be careful