A case of MySQL master-slave synchronization failure and its solving process

There are two MySQL servers in the company from the synchronization, one day Nagios sent to alarm SMS,Mysqla are down ...Hurriedly contact the room, the room of the person feedback to the information isHARDWARE ERRORAfter the information is omitted, so that the computer room to write down the error message to let them help restart the next look is not normal up, the result is normal, and quickly export all data.
The problem comes up again, Nagios alarms, mysql_ab error, check from library
show slave status \g;Sure enough
Slave_io_running:yes
Slave_sql_running:no
And a 1062 error occurred, prompting
last_sql_error:error ' Duplicate entry ' 1001-164761-0 ' for key ' PRIMARY ' on query. Default database: ' Bugs '. Query: ' INSERT into Misdata (uid,mid,pid,state,mtime) VALUES (164761,1001,0,-1,1262623560) '
It is clear that the primary key conflict is not synchronized from the library data because of the main library reboot. Viewing the error log finds that the error log file has become much larger, nearly several times bigger than before,
Tail-f Mysql_error.log The first thing to see is this message.
Find this message
[ERROR] Slave sql:error ' Duplicate entry ' 1007-443786-0 ' for key ' PRIMARY "on query. Default database: ' UFO '. Query: ' INSERT into Misdata (Uid,mid,pid,sta
te,mtime) VALUES (443786,1007,0,-1,1262598003) ', error_code:1062
100104 17:39:05 [Warning] slave:duplicate entry ' 1007-443786-0 ' for key ' PRIMARY ' error_code:1062
100104 17:39:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "Slave START". We stopped at log ' ufolog.000058
8 ' position 55793296
The error is similar to the above meaning,

The first thing to think about is to manually sync, first stop slave from the library; stop syncing
Enter the main Library lock table,
FLUSH TABLES with READ LOCK;
Mysql>show master status;
+-------------------+-----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+-----------+--------------+------------------+
| ufo.000063 |              159164526 |                  | |
+-------------------+-----------+--------------+------------------+
1 row in Set (0.00 sec)
Enter from library
Mysql>Change master to master_host= ' 192.168.1.141 ', master_user= ' slave ',
master_password= ' xxx ',
master_port=3306,
master_log_file= ' ufo.000063 ',
master_log_pos=159164526;

After completing these
start slave;
Back to the main library
unlock tables;Unlock

Back to view from gallery
show slave status \g;
Found normal, the advantages of a breath. But not a minute, found to start the error, or the most beginning of the mistake, this is how it happened ...
So I thought of skipping the wrong way, (but I'm not very fond of using this method) immediately into the library
stop slave;
set global sql_slave_skip_counter=1;(1 means skipping an error)
slave start;
Againshow slave status \g;View
Or the error is only the original 164761 turned 165881, successive execution several times after
In addition to the above values are changing, the error is still
Depressed, it seems that can only be forced to skip the 1062 error, and then modify the/etc/my.cnf file from the library
Added a line underneath [mysqld]
slave-skip-errors = 1062(Ignore all 1062 errors)
Restart the mysql/etc/init.d/mysqld from the library restart
Againshow slave status \g;It's OK, but I know the data may be out of sync,
Looking at the log again, I was surprised that Tail-f Mysql_error.log had a lot of
.......
100106 16:54:21 [Warning] Statement may isn't safe to log in Statement format. Statement:delete from ' system_message_1 ' where ' to_uid ' = 181464 ORDER by ' id ' ASC LIMIT 1
.........
There are a lot of this warning in the log, meaning should be statement format is not safe, with vim open he looked, found a lot of such warnings, I said why the error log so big!!
Statement FormatShould be a binlog format, go to view from the library
show global variables like ' Binlog_format ';
Sure enough, the current format isStatement

I need to change the format toMixedFormat
modifying from a librarymy.cfg
In[Mysqld]Add the following line below
binlog_format=mixed

Then restart the MySQL service and find that the warnings in the error log have been stopped. This time is more quiet ~ ~

I suddenly think of one thing, I remember a friend said RBR mode can solve a lot of primary key conflicts caused by the master-slave synchronization situation, think of here I want to do not take slave-skip-errors = 1062 get rid of and try again,
so he went into the my.cnf and commented out the slave-skip-errors = 1062
Restart the MySQL service again
Enter from library
show slave status \g;
.........
Slave_io_running:yes
Slave_sql_running:yes
........

Restored!!! Have observed for a period of time did not appear problems this is assured that

It seems that the cause of MySQL master-slave replication error is a lot more than one way to repair, Binlog format is one of them.
Hope to meet with the same problem of friends see this article will get some inspiration and solve the problem of the method ~ ~

This article is from the "Story Sky" blog, be sure to keep this source http://storysky.blog.51cto.com/628458/259280

A case of MySQL master-slave synchronization failure and its solving process