Shulou(Shulou.com)05/31 Report--

This article will explain in detail what to do about the failure of master-slave replication caused by Mysql table id problems. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

0. Guide reading

In the master-slave replication environment, IO and SQL threads are normal, and no filtering rules are set, but the data cannot be copied to slave. What is the reason?

1. Problem description

As a matter of fact, this case has been going on for a while, and I haven't sorted it out until now.

It is found that in a master-slave environment, the status of io_thread and sql_thread on slave is normal, and relay log also receives event from master normally, but these event cannot be applied normally on slave, and individual table data is not copied. And binlog on slave does not record operations on these tables.

2. Cause analysis.

After receiving the scene, the first reaction is to check whether the ignore/do rule is set, and found that it is not caused by this reason.

My own manual test created a new test table, wrote several pieces of data, and found that the table could be created on slave, but the written test data still could not be copied. This shows that replication on slave is not completely invalid, but only in special circumstances.

Combined with the above problems, I thought that the failure might be caused by binlog format and transaction isolation level, so I made the following attempt.

/ / first, modify the transaction isolation level to RR (previously RC) to ensure the consistency of master and slave data as much as possible

Root@imysql [mydb] > set session transaction isolation level repeatable read

/ / Test writes 2 pieces of data

Root@imysql [mydb] > insert into z select 5

Root@imysql [mydb] > insert into z select 6

After observation, these two pieces of data can not be copied to slave.

/ / change binlog format to statement (previously row), and write 2 more pieces of data

Root@imysql [mydb] > set session binlog_format='statement'

Root@imysql [mydb] > insert into z select 7

Root@imysql [mydb] > insert into z select 8

After observation, these two pieces of data can be copied to slave.

Now, at least on the surface, it seems to be caused by a combination of binlog format+ transaction isolation levels, so let's compare the differences between binlog under different binlog format.

Of these logs, the first two are logs in row mode, and the last two are in statement mode. We notice that the red box reads: table_id: 24874588093, it is for this reason that slave cannot copy data properly.

Normally, the binlog event in row mode should look like this:

In the log above, what we see is: table_id: 108, in which case it can be copied normally.

The problem is now clear that it cannot be replicated because of the table id exception in binlog. So, what on earth caused the exception of table id?

3. Case suggestion

I searched some information and found that others had the same problem. I will not be too verbose, you can take a look at the following reference article for a detailed understanding. In short, the main reason for this problem is that there is not enough table cache, which leads to frequent opening and closing of table, resulting in the rapid growth of table id, which leads to the failure of master-slave data replication.

There are several solutions:

Increase the table_cache_size, or table_open_cache value, and the table_definition_cache option. The general setting is not less than 1.5 times the total number of table. For more rigorous purposes, look at the two status values of Open_tables and Opened_tables. Open_tables represents the number of table currently being opened, while Opened_tables represents the total number of times table has been opened repeatedly in history. If the Opened_tables value is particularly high, it is likely that table cache is not enough.

Choose the machine to restart the main library instance and let the value of table id start counting from 0 again.

Temporary solution: change binlog format to statement and transaction isolation level to RR to avoid the risk of data inconsistency as much as possible.

About Mysql table id problems lead to master-slave replication failure how to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.