mysql,replication – MySQL Replication: Query Partially Completed on Master (Error 1053)

MySQLreplication

I got the below error.

Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'optimize table '

=================

Now, I researched and got something

 Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'FLUSH TABLES'
    Here the server panicked on the "FLUSH TABLES" command.
    Research showed the master db was restarted at the time of the error.
    It seems perfectly safe to run "FLUSH TABLES" on the slave
    Connect to slave database
    Run "SHOW SLAVE STATUS;"
    Note "Last Erno" field is 1053 and "Last Error" is above error
    Run "STOP SLAVE;"
    Run "FLUSH TABLES;"
    Run "SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;"
    Run "SHOW SLAVE STATUS;" and verify "Last Erno" is now 0 (zero) and "Last Error" is blank.
    Exit mysql
    Run "cat /var/lib/mysql/relay-log.info" a few times and verify the index values are updating. If a long command is running it may take several minutes to see an update
    Note: It may take a while for the replication db to catch back up to the primary database.
    Continue to monitor occasionally until the mysql-bin.NNNN file in relay-log.info is the same as the latest binlog on the primary database (in /var/lib/mysql). This means the replication has caught back up.

===================================

you can see, they are asking to run "Flush tables" query again on the slave.

while my query was "optimize table ".

Do I really need to run this 'optimize table' query again on my slave ? because i think its too large table and it will stop again.

======================================

What should I do, Please Help. Should I run Start Slave in Slave machine ?

Best Answer

If the query was OPTIMIZE TABLE table_name, there is no worry about data inconsistency. You can run SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; to skip the error on the slave, and re-run the optimization on each server by itself if needed. Use LOCAL so the statement is not replicated:

OPTIMIZE LOCAL TABLE table_name

Related Question