{"id":2042,"date":"2018-08-22T05:31:56","date_gmt":"2018-08-22T05:31:56","guid":{"rendered":"http:\/\/dbtut.com\/?p=2042"},"modified":"2018-11-11T21:13:01","modified_gmt":"2018-11-11T21:13:01","slug":"best-practice-to-avoid-duplicate-entry-errors-on-the-slave","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/08\/22\/best-practice-to-avoid-duplicate-entry-errors-on-the-slave\/","title":{"rendered":"Best practice to avoid duplicate entry errors on the slave"},"content":{"rendered":"

 <\/p>\n

In a Master\/Slave replication data consistency is a biggest concern and is very easy\u00a0 to break your replication with\u00a0 issues such as\u00a0duplicate keys, missing rows , accidental writes from application connected to the slave or even by a DBA connected to the slave \ud83d\ude41 “yes things happen” .<\/p>\n

1. read_only<\/strong><\/p>\n

When the\u00a0read_only<\/a>\u00a0system variable is enabled, the server permits no client updates except from users who have theSUPER<\/a>\u00a0privilege. This variable is disabled by default.<\/strong><\/p>\n

It\u2019s a good practice then to set read_only<\/span>=<\/span>1<\/span><\/span><\/span><\/code>\u00a0on slave servers to prevent any (accidental) writes.<\/p>\n

Unfortunately\u00a0users with the SUPER privilege can\u00a0override the setting and could\u00a0still run DML queries.<\/p>\n

\u00a0 2. super_read_only<\/strong><\/p>\n

However, since Percona Server 5.6.21<\/strong> and MySQL 5.7.8<\/strong>, you can use the\u00a0super_read_only<\/span><\/span><\/span>\u00a0<\/strong>feature to\u00a0extend the\u00a0read_only<\/span><\/span><\/span>\u00a0<\/strong> option and\u00a0apply it to users with SUPER privileges.\u00a0If the\u00a0 \u00a0super_read_only=1<\/code><\/a>\u00a0the server prohibits client updates even from users who have\u00a0SUPER<\/code><\/a>.<\/p>\n

3. temporary use of\u00a0slave-skip-errors<\/strong><\/p>\n

With slave-skip-errors<\/span>\u00a0you can make the replication slave ignore certain error codes (you can find the list of MySQL error codes here:\u00a0Server Error Codes and Messages<\/a>)<\/p>\n

Example : the purpose of the example below is to temporary skip error<\/p>\n