MySQL "Duplicate entry" Errors on the Slave break replication all the time

Question

I followed this tutorial:

https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

Unfortunately the replication stops on the slave all the time with:

[ERROR] Slave SQL: Error 'Duplicate entry '6443185' for key 'PRIMARY'' on query. Default database: 'testdb'. Query: 'INSERT INTO ultimate_cron_lock (name, current, expire) VALUES ('ultimate_cron_serial_launcher_1', '0', '1424077478.0243')', Error_code: 1062

If I drop all the dbs then how can I tell the Mysql slave server to create them automatically?

I didn't specify the binlog_do_db= on the Master, which means it should do the binary log for ALL dbs already, on the Slave I only ignore a couple of databases:

server-id       = 2
relay-log               = /var/lib/mysql/binlog/mysql-relay-bin.log
log_bin         = /var/lib/mysql/binlog/mysql-bin.log

replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=mysql

Thanks

Solution 1

The most destructive advice among all - is advice to automate the disaster using slave-skip-errors. This is the quick way to get the inconsistent slave database. Please avoid setting slave-skip-errors at all costs.

In the same time I can say that the most frequent cause of slave errors is the unawareness of the fact, that mysql slave is not read-only by default. If you are using frequent switchovers, some of your clients may still write changes to slave. To get rid of this you should really set the read_only = on on the slave servers.

Solution 2

You might be able to do so, but that requires replaying every single transaction since the original database was created. Besides the fact that doing so is quite inefficient, it also requires that the transaction log was already setup from the moment the to be replicated database was created and never truncated, which in my experience is rarely the case.

Solution 3

You can set the following in your slave's my.cnf:

[mysqld]
slave-skip-errors=1062

But as the documentation says:

Do not use this option unless you fully understand why you are getting errors.

The issue is likely caused by a timing / race condition in your application and you should look there for a solution.

As the documentation also warns:

Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.

If you do use this option rather than investigating your application you should run pt-table-checksum frequently.

Comments

[varnish121]

These answers doesn't really help. What really complicates the situation that as I see this restoration needs to be done in time before the Master rotates through the binary logs. ` -rw-rw---- 1 mysql mysql 66390664 févr. 16 16:31 mysql-bin.029943 -rw-rw---- 1 mysql mysql 52283383 févr. 16 17:18 mysql-bin.029944 ` Like right now it is at 29943 the time of db dump but in couple of hrs it will be over it and I have to do the whole dbdump, try to resync again.

[user9517]

Please do not edit your question to completely change it. When you do that it leaves answers like the one provided by @HBruijn out in the cold. That answer was correct for the original question. You should just ask a new question after considering upvotes/accepting the answers provided.