Resync MySQL Master/Slave Replication

I have encounter following error from the MySQL replication monitoring alert:

Error 'Duplicate entry '72264-4-10-2011' for key 1' on query. Default database: 'grad_data'. Query: 'INSERT INTO tblusr_log
                                          ( ID,UserType,Stats,Month,Year )
                                          VALUES
                                          ( '72264','4',1,MONTH(NOW()),YEAR(NOW()))'

This situation stopped the data replication process, which cause database in slave server fall behind and not syncing with the database at master server. There are several way to solve this issue:

Skip the error once and for all

1. Stop slave:

mysql> STOP SLAVE;

2. Add following line into /etc/my.cnf under [mysqld] directive and restart slave replication:

slave-skip-errors = 1062

3. Start slave:

mysql> START SLAVE;

Skip the error one by one

1. Run following command in mysql console to skip once:

mysql> STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; START SLAVE;

2. Check again the mysql slave status:

mysql> SHOW SLAVE STATUS\G

If the error appear again, repeat step 1 and step 2 accordingly until the ‘Slave_SQL_Running‘ turn to ‘Yes’. You also can use following command to monitor the replication status via console or SSH:

$ watch -n1 'mysql -e "SHOW SLAVE STATUS\G"'

Re-sync back the database and re-start replication

Here is the recommended way. It safest and reliable enough to make sure all data are replicated correctly. This steps takes longer time but it worth to do. We will need to dump the database in master server, restore it in the new server and re-start the replication. Variable as below:

Master server IP: 192.168.11.2
Slave server IP: 192.168.11.3
Database name: grad_data
MySQL database user: grad_user
MySQL database password: Gr55db@
MySQL slave user: slaver
MySQL slave password:  slaverp4ss

1. Dump the database in master server:

$ mysqldump -u grad_user -p"Gr55db@" --master-data --single-transaction grad_data > /root/grad_data.master.sql

2. Copy it over to the slave server using scp:

$ scp /root/grad_data.master.sql root@192.168.11.3:/root

3. Recreate the database in slave server:

mysql> DROP DATABASE grad_data;
mysql> CREATE DATABASE grad_data;

4. Restore back the database in slave server:

$ mysql -u grad_user -p"Gr55db@" grad_data < /root/grad_data.master.sql

5. Review the binlog file and position in the sqldump file. Open /root/grad_data.master.sql using text editor and find line similar to below:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=1017207641;

You need to take note the log_file name and log_pos number.

6. Restart the slave replication (make sure the MASTER_LOG_FILE and MASTER_LOG_POS in command below same as value in step 5):

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.11.2', MASTER_PORT=3306, MASTER_USER='slaver', MASTER_PASSWORD='slaverp4ss', MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=1017207641;
mysql> START SLAVE;

Verify slave status

Check the slave replication status:

 mysql> SHOW SLAVE STATUS\G

You should see something like below to indicate that your MySQL replication is running fine:

*************************** 1. row ***************************
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes

Related Posts

• Install MySQL Cluster in Debian
• Customize and Disable PHPmyAdmin ‘Export’ Menu
• High Availability: cPanel with MySQL Cluster, Keepalived and HAProxy
• Monitor MySQL Galera Cluster from Split-Brain
• CentOS 6: Install MySQL Cluster – The Simple Way
• High Availability: Configure Piranha for HTTP, HTTPS and MySQL
• Fixing Auto Start and Auto Shutdown Issue in VMware ESXi 5.0
• CentOS: Integrate ClusterControl into Existing MySQL Galera Cluster
• Easiest Way to Install A Complete MySQL Galera Cluster
• MySQL – Recover Data Using mysqlbinlog