MySQL Skip Duplicate Replication Errors : skip replication error

Normally MySQL replication will stop whenever there is an error running a query on the slave. This happens in order for us to be able to identify the problem and fix it, and keep the data consistent with the mater that has sent the query. You can skip such errors, even if this is not recommended, as long as you know really well what are those queries and why they are failing, etc.

For example you can skip just one query that is hanging the slave using:

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

There might be cases where you will want to skip more queries. For example you might want toskip all duplicate errors you might be getting (output from show slave status;):

 

“1062 | Error ‘Duplicate entry ‘xyz’ for key 1′ on query. Default database: ‘db’. Query: ‘INSERT INTO …”

 

If you are sure that skipping those errors will not bring your slave inconsistent and you want to skip them ALL, you would add to your my.cnf:

slave-skip-errors = 1062

As shown above in my example 1062 is the error you would want to skip, and from here we have: _ Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry ‘%s’ for key %d_

You can skip also other type of errors, but again don’t do this unless you understand very well what those queries are and what impact they have on your data:

slave-skip-errors=[err_code1,err_code2,…|all]

 

Related Posts

  • 44
    Easiest way to do this is to add a UNIQUE index on the column. When you write the ALTER statement, include the IGNORE keyword. Like so: ALTER IGNORE TABLE jobs ADD UNIQUE INDEX idx_name (site_id, title, company); This will drop all the duplicate rows. As an added benefit, future INSERTs…
    Tags: will, duplicate, error, mysql, query
  • 32
    A Shell script for killing slow MySQL queries: #!/bin/sh # Credentials for a MySQL user with PROCESS, SUPER permissions USERNAME= PASSWORD= # MySQL Server location HOST= PORT=3306 TIMEOUT=60 # 1 minute TARGET_USER= # MySQL user to monitor MYSQL="mysql -u $USERNAME --password=$PASSWORD -h $HOST -P $PORT -B" $MYSQL -N -e 'SHOW…
    Tags: mysql, queries, query
  • 31
    Here we will help you to setup master-slave replication between MySQL servers. Setup Details: Master Server: 10.0.10.12 Slave  Server: 10.0.10.18 Database: empmaster 1. Setup MySQL Master Server Create an mysql account on Master server with REPLICATION SLAVE privileges to which replication client will connect to master. Block write statement on…
    Tags: mysql, slave, replication
  • 31
    Very often we need to extract last 30 days, 7 days data from mysql DB. Here we will show easy way to get the data of given interval. SELECT DATE_FORMAT(create_date, '%m/%d/%Y') FROM mytable WHERE create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE() Also note that CURDATE() returns only the DATE…
    Tags: mysql, data, will, query
  • 30
    Here we will help you to set up Master-Master replication between MySQL servers. In this setup if any changes made on either server will update on an other one. Setup Details: Master-1: 10.0.10.12 Master-2: 10.0.10.18 Database: empmaster Step 1. Set Up MySQL Master-1 Server Edit MySQL configuration file and add the following…
    Tags: mysql, replication, slave

Leave a Reply

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

%d bloggers like this: