Troubleshooting MySQL replication error last_sql_errno:1146

Background: When we are doing data migration or splitting, when using tablespace transcation this solution, it is very likely that there will be a copy error from the library, reported: last_sql_errno:1146

Then the specific error content may be as follows:

Last_sql_error:error ' Table ' spider.tb_city_population_rank ' doesn ' t exist ' on query. Default database: ' Spider '. Query: ' ALTER TABLE Tb_city_population_rank discard Tablespace '
Last_sql_error:error ' Table ' spider.tb_city_population_rank ' doesn ' t exist ' on query. Default database: ' Spider '. Query: ' ALTER TABLE Tb_city_population_rank import tablespace '

So how do we deal with such problems when we are confronted with them? Consider the following points:

1. How big is the capacity of our entire library?
2. How long is the maximum delay for business tolerance?
3. How long will it take us to recover? How easy is recovery?

By taking into account the above, we can make a choice according to the actual situation, what kind of method to recover from the library as soon as possible, for such a problem, the simple and rough solution is to rebuild from the library, of course, there are other ways, and listen to my slow way:

First, let's look at how we migrated the data through transport tablespace, with the following approximate steps:

1. Library A execution: show create TABLE xxx1; Get the summary statement;
2. Library b Execution: create TABLE xxx1; Establish the basic table structure from the library;
3. Library B execution: ALTER TABLE xxx1 discard tablespace; Let MySQL delete the IBD file by itself;
4. Library A execution: Flush tables xxxx1,xxxx2 for export, the memory of the dirty data to disk, so that the IBD file data consistency;
5. Library a execution: SCP xxxx1.ibd xxx2.ibd xxxx1.cfg xxx2.cfg slave_host:/data/Copy the IBD file to the slave library;
6. Library B executes: ALTER TABLE XXX1 import tablespace imports the data file.

Well, we know the exact steps of the migration, so we can easily deal with replication errors during the migration process.

So let's first look at:

last_sql_errno:1146
Last_sql_error:error ' Table ' spider.tb_city_population_rank ' doesn ' t exist ' on query. Default database: ' Spider '. Query: ' ALTER TABLE Tb_city_population_rank discard Tablespace '

The approximate meaning is that we didn't find the complete definition of the table from the library, and we looked at the files on the disk and we understood:

[Email protected]_nf_db_rp_002 spider]# ls-lhrt |grep Tb_city_population_rank
-rw-r-----1 mysql mysql 8.3G 20:03 tb_city_population_rank.ibd

Sure enough there is no frm file for this table, so what do I do? Because it is slave sql_thread thread error, then we can take trickery method:

To log in from the library with Superuser, first back up this ibd file: [email protected]_nf_db_rp_002 spider]# MV Tb_city_population_rank.ibd tb_city_population_ Rank.ibd.bak

Then get the table structure of this file, in the execution of the table statement from the library, so that we have the IBD frm file from the library, then we open slave sql_thread, then we will execute when the main library passed the statement:

ALTER TABLE Tb_city_population_rank discard tablespace; Then when we watch this disk data file again, the IBD file is missing, at this time slave has been wrong, the error message is as follows:

last_sql_errno:1146
Last_sql_error:error ' Table ' spider.tb_city_population_rank ' doesn ' t exist ' on query. Default database: ' Spider '. Query: ' ALTER TABLE Tb_city_population_rank import tablespace '

So what we need to do at this point is: to the MV of the file again MV back, that is to say: [email protected]_nf_db_rp_002 spider]# MV Tb_city_population_rank.ibd.bak tb_city _POPULATION_RANK.IBD; We will execute the start slave sql_thread after this command is completed; Now that the table is normal, we can execute select * from Tb_city_population_rank limit 100; To verify that it is readable.

So at this point, we have solved the problem of replication error caused by one table, then if we migrated multiple tables (often), then we can follow this solution, step-by-step to resolve replication errors. The general idea is, what we need to complement, what we get rid of.

Troubleshooting MySQL replication error last_sql_errno:1146