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

This article mainly explains "MySQL master inconsistent problem analysis", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "MySQL master inconsistent problem analysis" bar!

Today, she looked at a problem with her colleagues. She found data inconsistencies and primary key conflicts in a master-slave environment.

The error message for show slave status looks like the following.

Last_Error: Coordinator stopped because there were error (s) in the worker (s). The most recent failure being: Worker 0 failed executing transaction '0e454161-3169-11e7-98f6-02004d9000afailed executing transaction 665' at master log mysql-bin.000001, end_log_pos 274391. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. This is a master-slave environment of MySQL version 5.7. it has not been put into online business use yet. This kind of problem was encountered in the process of building.

In general, if the master-slave data is inconsistent, you can use the pt tool to try to check and fix it. This problem occurs when building master and slave, and master-slave building does not seem to have many skills. It is enough to turn on GTID, which sounds a little strange.

After failing to repair with the pt tool, colleagues are ready to rebuild, but the process of reconstruction is also tortuous, and slave always has conflicts with primary key data. We checked the main database side, the data is not conflicting, is this bug again, I think take a closer look.

I got the environment, ready to break through from the build slave library, because the amount of data is not large, so I re-import the data, using the simplest way to redirect to import.

# mysql-pxxxxx

< db-dump-201705121718.sql Logging to file '/home/mysql/query.log' mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. 但是我运行之后发现，导入的时候报错了，在导出的时候其实可以加一个选项，这样就不会有这类干扰了。 因为是重新搭建从库，所以我使用了reset master的方式， >

Reset master

Query OK, 0 rows affected (0. 01 sec) will be no problem if you import it again.

Then there is the setting of change master.

CHANGE MASTER TO MASTER_HOST='xxxx', MASTER_USER='rep_user', MASTER_PASSWORD='xxxx', MASTER_PORT=3306, MASTER_AUTO_POSITION = 1

After starting slave, I found that the error encountered by my colleague was gone.

On this issue, we communicated. Colleagues used source when importing, saying that they did not see any errors. We compared the building methods, and this place is different.

With the attitude of giving it a try, I used source to build it once.

> source db-dump-201705121718.sql sees that a lot of logs are output from the backend. Generally speaking, there is nothing unusual about it. Then the restart slave error can be reproduced. So through this process, we can basically conclude that it has nothing to do with bug.

At this time, our focus is gradually narrowing. After demonstration, it is the problem in this place. Let's explain it through a few small tests.

I wrote a few lines of SQL, and the file a.sql contains the operation of creating a table and inserting two rows of data.

# cat a.sql

Create table test (id int)

Insert into test values ('aaa')

Insert into test values; use mysql test

< a.sql 还是source的方式都没有任何报错。 运行后表test的数据为: >

Select * from test

+-+

| | id |

+-+

| | 0 |

| | 100 |

+-+ this does surprise me a bit. Of course, that's not the point of the problem, let's continue to change the script.

# cat a.sql

Create table test (id int)

Insert into test values ('aaa','aa')

Insert into test values; at this point the difference is obvious.

# mysql test

< a.sql Logging to file '/home/mysql/query.log' ERROR 1136 (21S01) at line 2: Column count doesn't match value count at row 1查看数据情况，是没有数据插入的。 >

Select * from test

For Empty set (0.00 sec) and using source, the log is as follows:

> source a.sql

Query OK, 0 rows affected (0.01 sec)

ERROR 1136 (21S01): Column count doesn't match value count at row 1

Query OK, 1 row affected (0. 01 sec) to view data, there is 1 row of data.

So a big difference lies in this, using the redirect method, if there is an error will directly exit, while using source will execute in turn, skip the wrong place, and continue to perform the following steps. Such a small place can be said to be contemplative and terrifying. It is especially important for us to do data change operations.

Thank you for your reading, the above is the content of "MySQL Master inconsistent problem Analysis". After the study of this article, I believe you have a deeper understanding of the problem of MySQL Master inconsistent problem analysis, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.