MYSQL REPLICATION ERROR 1053

MYSQL ERROR 1053

    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;

The error code 1053 which means as much as "server shutdown".

We checked the master and could not find anything unusual. The server had not been shut down at all and nothing seemed wrong with the master replication settings either.

In the end we found out what had caused the problem: Someone had tried to create a dump of the master server using  mysqldump --master-data. This implies a FLUSH TABLES statement. Because that statement took too long it was aborted using the MySQL KILL command. However because that statement had already been replicated to the slaves and was now aborted, the slaves took it for "partially executed" (which is usually something bad). The error code you can see on a killed client is 1053. So the slaves decided that something serious had happened and stopped the communication with their master.

Check on which instance is the mysql running then

Go to the same instance and the following CMD

ERROR OUTPUT

reports error in replication: 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: 'DELETE FROM tc_data_raw_ad_impression WHERE date_time <= '2013-01-29 13:59:59''

     Slave File          <<<      Master File

     master-bin.001769   <<<      master-bin.001797

     Slave Pos           <<<      Master Pos

     83027418            <<<      40366841

Solution:-

mysql> stop slave;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start slave;

mysql> quit

#rep_mon

OUTPUT

reports it is 3618 seconds behind

     Slave File          <<<      Master File

     master-bin.001798   <<<      master-bin.001801

     Slave Pos           <<<      Master Pos

     85916100            <<<      45308656

done.................