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

This article introduces the relevant knowledge of "why there is data inconsistency in the environment where mysql is the master and slave". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

M1:

Begin

Update T1 set c2 / b1' where C1 / 2

Commit

M2:

Begin

Update T1 set c2 / b2 'where C1 / 2

Commit

M1 and m2 are submitted at the same time, replication will not report an error, but the data of M1 and m2 are inconsistent, why?

Because the sql_thread thread updates the data based on the primary key, it does not validate the row data

How to avoid this problem:

Write only on a single node, such as keepalived+ dual host, MGR,PXC if multiple nodes write this problem occurs.

Example 1:

The table has a primary key and self-increment:

Root@localhost [testdb] > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int (11) NOT NULL AUTO_INCREMENT

`c2` varchar (10) DEFAULT NULL

PRIMARY KEY (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

M1:m2:

Root@localhost [testdb] > select * from T1

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | bbb |

| | 3 | ccc |

| | 4 | ccc |

| | 6 | ddd |

| | 8 | eee |

+-+ +

Root@localhost [testdb] > select * from T1

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | bbb |

| | 3 | ccc |

| | 4 | ccc |

| | 6 | ddd |

| | 8 | eee |

+-+ +

Root@localhost [testdb] > begin;root@localhost [testdb] > begin;root@localhost [testdb] > update T1 set c2 / where c1 / 2 / testdb > update T1 / set c2 / b2 / where C1 / 2

Root@localhost [testdb] > select * from T1

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | b1 |

| | 3 | ccc |

| | 4 | ccc |

| | 6 | ddd |

| | 8 | eee |

+-+ +

Root@localhost [testdb] > select * from T1

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | b2 |

| | 3 | ccc |

| | 4 | ccc |

| | 6 | ddd |

| | 8 | eee |

+-+ +

Root@localhost [testdb] > commit;root@localhost [testdb] > commit

Root@localhost [testdb] > select * from T1

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | b2 |

| | 3 | ccc |

| | 4 | ccc |

| | 6 | ddd |

| | 8 | eee |

+-+ +

Root@localhost [testdb] > select * from T1

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | b1 |

| | 3 | ccc |

| | 4 | ccc |

| | 6 | ddd |

| | 8 | eee |

+-+ +

Summary: update submitted a record at the same time. In the case of primary key, sql_thread matches row records according to the primary key and does not verify row data, so M1 updates the records of tables in m2, and m2 updates records of tables in M1.

Example 2: is there a case where the primary key updates a row of data at the same time:

Root@localhost [testdb] > show create table T2\ G

* * 1. Row *

Table: t2

Create Table: CREATE TABLE `t2` (

`c1` int (11) DEFAULT NULL

`c2` varchar (20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

M1m2

Root@localhost [testdb] > select * from T2

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | bbb |

+-+ +

Root@localhost [testdb] > select * from T2

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | bbb |

+-+ +

Root@localhost [testdb] > begin;root@localhost [testdb] > begin;root@localhost [testdb] > update T2 set c2 / where C1 / 2 / testdb > update T2 / set c2 / b2 / where C1 / 2

Root@localhost [testdb] > select * from T2

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | b1 |

+-+ +

Root@localhost [testdb] > select * from T2

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | b2 |

+-+ +

Root@localhost [testdb] > commit;root@localhost [testdb] > commit

Root@localhost [testdb] > select * from T2

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | b1 |

+-+ +

Root@localhost [testdb] > select * from T2

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | aaa |

| | 2 | b2 |

+-+ +

Root@localhost [testdb] > show slave status\ G

Last_Errno: 1032

Last_Error: Could not execute Update_rows event on table testdb.t2; Can't find record in't 2girls, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000013, end_log_pos 759

Root@localhost [testdb] > show slave status\ G

Last_Errno: 1032

Last_Error: Could not execute Update_rows event on table testdb.t2; Can't find record in't 2girls, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000026, end_log_pos 3064

Summary: update a record is submitted at the same time, with or without a primary key, sql_thread matches row records according to full table scan, so M1 update cannot find rows that need to be updated in m2, reporting a 1032 error, m2 update cannot find rows that need to be updated in M1, and 1032 error is also reported.

This is the end of the content of "Why there are data inconsistencies in the mysql master-slave environment". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.