Translated from 日本語 • View original Hello. This is Komiya.
This is a record of the error that came out earlier.
・Error log
>show slave status\G
Last_Error: Error 'Duplicate entry '1133523-2013-08-18 05:03:01' for key 'PRIMARY'' on query. Default database: '*****'. Query: 'INSERT INTO `event_****_logs` ...
It seems to be an error that feels like a duplicate because the time is the primary key.
I feel that logs and sessions are easy to duplicate.
I looked at the mysqldump option just in case, but
--skip-opt is not included, so --add-drop-table should be valid for the opt to be arbitrary.
MYDUMP_PAR='--single-transaction --dump-slave=2 --routines --include-master-host-port --all-databases'
http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html
There seemed to be no problem with the procedure, such as resetting slave after data input and starting slave according to the log file and position.
When I asked the customer to confirm whether it was okay to skip all duplicate entries.
Whether it's okay for the primary key to be covered by Insert will be confirmed separately by the development side, and for the time being, it's a story of skip.
(I think it was a priority to restore because the target did not want to use it for the service.) If you are concerned about inconsistencies, it is better to retake the dump from the master. ）
・How to skip an error query
If there is only one,
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
But if it comes full
Last_SQL_Errno: 1062
Skip this error number. It means skipping all duplicate entries.
If perconatoolkit is included
pt-slave-restart -u root -p`cat /root/.mysql_pwd` --error-numbers 1062 --verbose
It comes out as standard output, so if you redirect it, you can see the skipped query later.
If it's not included,
There is also a way to write in my.cnf as follows. (requires mysqld restart)
slave-skip-errors=1062
http://fr.slideshare.net/billkarwin/percona-toolkit
http://jitsu102.hatenablog.com/entry/2012/03/08/073448
There have been cases where data such as logs and sessions have duplicated in other projects.
If you want to re-take the dump, it may be a good idea to lock it so that it does not take an update, wait a few seconds, and confirm the position before taking a picture.
Even if you use --single-transaction in InnoDB, it seems that there may be duplicates.
I thought about it, but I found out the whole story of this incident, so I will record it as well.
In fact, instead of looking at the contents of the dump data for binary log files and positions,
show master status by hand after locking; I was dumping after that, so
About 11 queries that had been subtly updated The dump data was new, so it was duplicated! That's what it was.
head -30 dumpdata or zcat dumpdata.gz|head -30 seems to be useless.
---masta-data If you are dumping, you should take a good look.
*If you add --masta-data, the CHANGE MASTER statement will be recorded at the beginning of the dump data.
If it is 1, it will be executed, and if it is 2, it will be a comment. When the data was relocated, it was set to 2 to prevent it from facing the old master.
Thank you for watching the above.