Mysql replication error last_sql_errno:1146 Solution _mysql

Background: When we do data migration or split, when using tablespace transcation This solution, we are likely to encounter errors from the library, reported: last_sql_errno:1146

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 large is our entire library?
2. How long is the maximum delay in business tolerance?
3. How long will it take us to recover? What is the ease of recovery?

By taking into account the above points, 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 crude plan is to rebuild from the library, and of course there are other ways, and listen to my slow way:

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

1. Library A execution: show create TABLE xxx1; Get the summary statement;
2. Library b Execution: create TABLE xxx1; Establishing a basic table structure from the library;
3. Library B execution: ALTER TABLE xxx1 discard tablespace; Let MySQL delete the IBD file itself;
4. Library A execution: Flush tables xxxx1,xxxx2 for export; The dirty data of the memory is brushed to disk, which makes the data of IBD file consistent;
5. Library A implementation: SCP xxxx1.ibd xxx2.ibd xxxx1.cfg xxx2.cfg slave_host:/data/to copy the IBD files from the library;
6. Library B execution: ALTER TABLE XXX1 import tablespace the data file.

Okay, now that we know the exact steps of the entire migration, we can easily handle the problem of replication errors during the migration process.

So let's take a look at the first:

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 did not find the complete definition information of this table from the library, and looked at the files on the disk and we understood:

[root@gz_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 to do? Since it is the slave sql_thread thread that complains, then we can take a tricky approach:

With Superuser login from the library, first back up this IBD file: [root@gz_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 implementation of the table from the library statement, so that we have an IBD from the library frm file, then we open slave Sql_thread, then the main library will be executed when the statement:

ALTER TABLE Tb_city_population_rank discard tablespace; So when we look at this data file again, the IBD file disappears again, at this time slave also has made a mistake, its 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 '

Then we need to do at this time is: the MV file again MV back, that is to say: [root@gz_nf_db_rp_002 spider]# MV Tb_city_population_rank.ibd.bak tb_city_ POPULATION_RANK.IBD: After completing this command, we execute the start slave sql_thread; 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've solved the problem of replication errors caused by a table, so if we migrate more than one table (often), we can follow this solution step-by-step to resolve replication errors. The general idea is that what we need to make up for, what we remove.

The following is another netizen's supplement, according to the error information returned to adjust.

MySQL master copy (dual-master replication) error last_sql_errno:1146

Error message:

Copy Code code as follows:

last_errno:1146
Last_error:error ' Table ' test.user ' doesn ' t exist ' on query. Default database: ' Test '. Query: ' INSERT into user values (20, ' in library ') '

Workaround:

mysql> set global sql_slave_skip_counter=20;
Mysql> STOP SLAVE;
Mysql> START SLAVE;

Problem solving