• Master version: mysql-server-5.5.14-1.el5.remi
  • Slave version: Percona-XtraDB-Cluster-server-5.5.24-23.6.340
  • Binlog format: ROW based

I'm using PRM to setup HA for MySQL.

Sometime, MySQL slave stopped with some errors:

Error executing row event: 'Table 'reportingdb.tvc_ads_tag_date' doesn't exist'

[Warning] Slave SQL: Could not execute Update_rows event on table reportingdb.7k_banner_channel_tmp; Can't find record in '7k_banner_channel_tmp', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000854, end_log_pos 859094925, Error_code: 1032

120828 0:36:13 [Warning] Slave SQL: Could not execute Write_rows event on table reportingdb.7k_bookings_ver; Duplicate entry '1518' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001022, end_log_pos 509479, Error_code: 1062

Error 'Error in list of partitions to DROP' on query. Default database: 'reportingdb'. Query: 'ALTER TABLE v3_ban_date_cpm7k DROP PARTITION pcurrent_201298'

and the mysql.sock is missing but the MySQL processes still exist. Pacemaker cannot restart due to the below errors:

InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.

I have to login via TCP (-h and stop MySQL first, then let corosync start MySQL again.

In summary, the question is:

  1. How do I solve the above slave errors and prevent them from happening in the future?
  2. Why is the mysql.sock missing? How do I make the Pacemaker better handle this situation?

It's better not to skip by hundreds. Just loop one-by-one:

until mysql -e "show slave status\G;" | grep -i "Slave_SQL_Running: Yes";do
  mysql -e "stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;";
  sleep 1;

just stop the slave using

mysql> stop slave;

Then set the global variable sql_slave_skip_counter to 1, for example:

mysql> set global sql_slave_skip_counter=1;

Then start the slave:

mysql> start slave;

Then check if the slave is working or not:

mysql> show slave status \G;

If the error is still there, set a bigger value in sql_slave_skip_counter like:

mysql> set global sql_slave_skip_counter=1000;

Again, check the status of the slave.

If you find the skip_sql value is non zero in the slave status then stop the slave again and do:

mysql> set global sql_slave_skip_counter=0;
mysql> start slave;
  • 5
    skip slave counter is not something to take lightly. It is ONLY OK to use it when familiar with the context of the statement that failed replication and accepting that skipping it is an acceptable data drift on the slave. But as a general answer this is a way to break integrity of the slave as a true replica of the master – TechieGurl Jan 18 '13 at 3:07
  • 1
    I have to echo TechieGurl here --- intermixed with the 1000 that you skip might be some good rows. SKIP COUNTER skips all replication, not just the error ones. – David Hall Jan 29 '13 at 21:10
  • Dose set global sql_slave_skip_counter=1000; means ignore 1000 records or more? If i do so how much of my data is missed on my slave replication? – shgnInc Mar 1 '15 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.