Best practice to avoid duplicate entry errors on the slave

 

In a Master/Slave replication data consistency is a biggest concern and is very easy  to break your replication with  issues such as duplicate keys, missing rows , accidental writes from application connected to the slave or even by a DBA connected to the slave 🙁 “yes things happen” .

1. read_only

When the read_only system variable is enabled, the server permits no client updates except from users who have theSUPER privilege. This variable is disabled by default.

It’s a good practice then to set read_only=1 on slave servers to prevent any (accidental) writes.

Unfortunately users with the SUPER privilege can override the setting and could still run DML queries.

  2. super_read_only

However, since Percona Server 5.6.21 and MySQL 5.7.8, you can use the super_read_only feature to extend the read_only  option and apply it to users with SUPER privileges. If the   super_read_only=1 the server prohibits client updates even from users who have SUPER.

3. temporary use of slave-skip-errors

With slave-skip-errors you can make the replication slave ignore certain error codes (you can find the list of MySQL error codes here: Server Error Codes and Messages)

Example : the purpose of the example below is to temporary skip error

  • 1062 : Duplicate entry ‘%s’ for key %d
  • 1054 : Unknown column ‘%s’ in ‘%s’
 

Caution : Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of sync with the master, with you having no idea why this has occurred.

 

Here are a couple bugs to consider on Percona Server 5.6:

For more information, please check following links:

Mikael HOUNDEGNON
Author: Mikael HOUNDEGNON

My name is Mikael HOUNDEGNON. I am an experienced MySQL DBA/Developer based in the greater Chicago area. You can find out more about me here. I blog here mostly about things I don’t want to forget ? most likely, MySQL Tips. My specialties : MySQL Replication (Master Slave, MultiMaster, Fail over, etc) MySQL Backups MySQL Query Optimization MySQL Performance Tuning MySQL Stored Procedures Storage Engine Tuning Do you have an interesting project idea? Or you just want to chat? Get in touch!

Leave a Reply

Your email address will not be published. Required fields are marked *