When you are working with a MySQL system, you may well need to run a lot of commands through it. Sometimes MySQL replication creates generic issues while setting up. While you are working, you may come across a problem called MySQL replication error 1146. The reasons behind this issue may be the in-existence of SQL queries or invalid MySQL queries.
If you are also getting this error of MySQL replication 1146 then you would need to verify the MySQL check slave resolution status for the same. With this post, we are going to take a detailed look at this error and solve it with some easy steps. So let’s get started.
About MySQL replication :
MySQL replication can be simply understood as this process enables and automatically copies entire data from a server to another database server, single or more than one. However, replication of the setup of MySQL master and slave with the same device is simple and similar for all the operating systems.
You can simply term it as transferring the data available on one server to some other server. This is to make sure that each and every user shares similar data without the occurrence of irregularities.
Here are the MySQL replication advantages:
A- High-performance
B- Data Backup and high-end security
C- Remote Desktop Access
Causes of MySQL replication error 1146:
MySQL replication error is nothing but a common error that usually occurs when the MySQL slave server faces some problems while replicating the data from the master. This issue, in most cases, happens because of the invalidity or inexistence of MySQL queries.
Before we move ahead, make sure that you check the MySQL check slave resolution status at your system to be assured. Now let’s move ahead to know about the solution for this error 1146.
Step-by-step solution for MySQL replication error 1146
Now that we have already discussed the reasons behind the MySQL replication error, let’s move forward to know about the solution to this error.
Method 1:
First you would start by checking up the mysql_upgrade` and if it looks up to date already then after trying forcefully, you might get the result as
MySQL.gtid_slave_pos Error- Table ‘mysql.gtid_slave_pos’ doesn’t exist in engine
Status- Operation failed
Move ahead, by checking on the dB/MySQL folder and checking if the .ibd and .frm already exist in your system. If you have these in your system:
gtid_slave_pos.frm
gtid_slave_pos.ibd
Then, quickly remove the files and recreate the table, which will quickly solve the error.
After this, go to the MySQL folder and then drop these files by using these commands:
rm gtid_slave_pos.ibd
rm gtid_slave_pos.frm
For creating the table, you can use the command as shown here:
CREATE TABLE table_name
(column_name column_type);
Now stop and start the slave using the below commands:
mysql]> stop slave;
mysql]> start slave;
It quickly solves the error without any problems.
Method 2:
If you are still getting replication errors then follow these easy steps:
Step 1- First login to MySQL
MySQL -u root -p
Step 2- Now, run a MySQL check slave replication status with MySQL shell which will give you:
mysql> SHOW SLAVE STATUS
It will give you the result as shown below :
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
Step 3- If any of the Slave_SQL_Running or Slave_IO_Running is set to No, then you can consider the replication as broken.
So, you need to initiate MySQLreplication repair.
Step 4- To do so, we need to end the slave replication through the command below:
mysql> STOP SLAVE;
Step 5- Now we advise the slave side to skip invalid MySQL query which is actually stopping the replication. Master-Slave performs this action with the help of commands available below:
mysql> SET GLOBAL
SQL_SLAVE_SKIP_COUNTER = 1;
If you wanted to skip 2 queries, use the command below:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;
And you are done.
Step 6- Now, Restart Slave again with the command here:
mysql> START SLAVE;
Step 7- After going through all of the abovementioned steps, check whether the replication process is working fine or not. For this purpose, use this command:
mysql> SHOW SLAVE STATUS
mysql> SHOW SLAVE STATUS
Repeat the commands in the ***1. Row*** here again
Both Slave_SQL_Running and Slave_IO_Running will be set to ‘Yes’ now.
Now, you can run the replication without the occurrence of errors. Finally, exit the shell, MySQL using the below command:
mysql> quit;
Final Verdict:
To conclude, MySQL replication error 1146 is caused by non-existing SQL or invalid MySQL queries. Hope you understand the process to fix this error now. Sit back and resolve this issue quickly without any worries.