MYSQL replication error

MYSQL replication error 1146.

So far, we discuss the MYSQL replication and reasons for the replication error 1146. Now let’s see how our Support Engineers fix this error for our customers.

Solution 1

To fix the replication error we follow the below steps.

1. First, we log into the MYSQL.

mysql -u root -p

2. On the MySQL shell, we check the slave status.

mysql> SHOW SLAVE STATUS

The sample result as follows.

mysql> SHOW SLAVE STATUS
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.2.3.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001089
Read_Master_Log_Pos: 269214467
Relay_Log_File: slave-relay.000234
Relay_Log_Pos: 100125935
Relay_Master_Log_File: mysql-bin.001079
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: mydb
Last_Errno: 1146

If anyone of the Slave_IO_Running or Slave_SQL_Running is set as NO, it means the replication is broken.

So, we start to repair the MYSQL replication.

3. For that, we stop the slave from replication, using the below command.

mysql> STOP SLAVE;

4. Next, we tell the slave to simply skip the invalid SQL query. So we use the below command.

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

This query tells the slave to skip one query (which is the invalid one that caused the replication to stop).  If we like to skip two queries, we use the following code instead.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;

That’s it.

5. Again, we start the slave.

mysql> START SLAVE;

6. After that, we check if replication is working again.

mysql> SHOW SLAVE STATUS

mysql> SHOW SLAVE STATUS
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.2.3.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001089
Read_Master_Log_Pos: 269214467
Relay_Log_File: slave-relay.000234
Relay_Log_Pos: 100125935
Relay_Master_Log_File: mysql-bin.001079
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydb
Last_Errno: 1146 

Both Slave_IO_Running and Slave_SQL_Running are set to Yes now. And the replication is running without any error.

Then we leave the MySQL shell.

mysql> quit;