HOW TO BYPASS ALL DUPLICATE KEY ERRORS
There are two ways to bypass duplicate key errors
METHOD #1 : Configure it in
Add this line under the
[mysqld] group header in
then restart mysql.
Once replication begins, all duplicate key errors will be bypassed. Once
Seconds_Behind_Master is 0, you are all caught up. However, the data on the Slave is out-of-sync with the Master. What do you do next ???
METHOD #2 : Use an Automated Tool
I am sure you don't have the time to run
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
over and over again. Wouldn't it be nice if an automated tool can do that for you ? Well, such a tool exists. It's called
You would run it something like this:
pt-slave-restart --error-numbers 1062
Put this in a crontab to go off every minute.
* * * * * pt-slave-restart --error-numbers 1062
Even better, you can make a server daemon and not use a crontab
pt-slave-restart --error-numbers 1062 --daemonize
and you do not need to restart mysqld.
pt-slave-restart will skip that error over and over again until
Seconds_Behind_Master is 0. When you are done having the Slave get caught up, stop using
However, you still have the same problem as METHOD #1: the data on the Slave is out-of-sync with the Master. What do you do next ???
OK, WHAT DO YOU DO NEXT ???
You must synchronize the data on the Slave with your Master. Wouldn't it be nice if a utility exists that can do that for you ? Well, such a tool exists. In fact, there are two tools. They are called
What do they do ???
pt-table-checksum to check on MySQL replication integrity. If you encounter any table that have different checksums between Master and Slave, such a table on the Slave is out-of-sync.
pt-table-sync to synchronize MySQL table data.
You can run
pt-table-sync --print > sync_file.txt
to see what will change (this is like a dry run of the real thing)
You then run
to perform the actual sync.
PLEASE READ THE PERCONA TOOLKIT DOCUMENTATION.
ALWAYS HANDLE PERCONA TOOLKIT RESPONSIBLY !!!
NOTE : Sadly, there is absolutely nothing you can with MySQL 5.5 and GTID.