MySQL “Duplicate entry” Errors on the Slave break replication all the time

MySQLreplication

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

Best Answer

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.

Related Topic