Whether the data brought by the ROW log mode of MYSQL is consistent in a dual MASTER environment.
Whether the data brought by the ROW log mode of MYSQL in the dual-MASTER environment is consistent, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.
# # Experimental environment: double MASTER structure
Master1 = = 10.249.160.132
Master2 = = 10.249.160.133
RHEL 5.4 X64, MYSQL 5.1.40
Binlog_format = MIXED
Tx_isolation = READ-COMMITTED
(here is a key point: READ-COMMITTED + INNODB, MYSQL enforces the use of ROW logging mode)
[@ more@]
# # initializing data
Use test
Set names gbk
Drop table if exists h2
Create table H2 (id int, name varchar (20), comment varchar (500), primary key (id))
Engine=innodb default charset = gbk
Insert into h2 values
(1, 1, 1, 2, 4, 2, 4, 4, 2, 4, 2, 4, 2, 2, 4, 2, 4, 2, 4, 2, 4, 4, 2, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 9, 6, 6, 6, 9, 9, 9, 6, 9, 9, 6, 9, 6, 6, 6, 9, 6, 6, 6, 6, 9, 6, 9, 6, 9, 9, 9, 6, 9, 6, 6, 6, 6, 9, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6
(2, 2, 2, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12 and 12
(3, 3, 3, 4, 4, 5, 4, 4, 4, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4
(4, 4, 4, 5, 5, 5, 5, 4, 4, 4, 4, 4, 4, 5, 5, 4, 4, 4, 4, 4, 4, 4, 4, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 4, 14, 14')
(5, 5, 5, 5, 5, 5, 5, 6, 6, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5
Flush logs
# # first of all, take a look at how MYSQL records UPDATE statements in ROW mode.
For example: update H2 set name='h-m@2' where id=5
The BINLOG log reads as follows:
BINLOG'
WX3rSxMCAAAALwAAAHAGAAAAACYAAAAAAAAABHRlc3QAAmgxAAMDDw8EKADoAwY=
WX3rSxgCAAAAPQAAAK0GAAAQACYAAAAAAAEAA///+AUAAAACaDUEAGgxMTX4BQAAAAVoLW1AMgQA
ADExNQ==
'/ *! * /
# UPDATE test.h2
# WHERE
# @ 1room5 / * INT meta=0 nullable=0 is_null=0 * /
# @ 2 / VARSTRING (40) meta=40 nullable=1 is_null=0 * /
# @ 3 hours / VARSTRING (1000) meta=1000 nullable=1 is_null=0 * /
# SET
# @ 1room5 / * INT meta=0 nullable=0 is_null=0 * /
# @ 2 / VARSTRING (40) meta=40 nullable=1 is_null=0 * /
# @ 3 hours / VARSTRING (1000) meta=1000 nullable=1 is_null=0 * /
# We found that MYSQL only records the number corresponding to the field. @ 1 without recording which field it is. (this is what I am worried about.)
# now let's use experiments to verify the problem.
# Step 1, at Master1, intended to delay the application of MASTER2's SQL on Master1.
Stop slave
# Step 2, at Master2
Update h2 set name='h-m@2' where id=5
Insert into H2 values (6 million dsflk`)
# Have not apply on Master1
# Step 3, at Master1
Alter table H2 add addr varchar (500) after name; # here the fault interrupts the original field order
Select * from H2
+-- +
| | id | name | addr | comment | |
+-- +
| | 1 | H2 | NULL | H211 |
| | 2 | h3 | NULL | H212 |
| | 3 | h4 | NULL | H213 |
| | 4 | h5 | NULL | h214 |
| | 5 | H6 | NULL | h215 |
+-- +
Start slave; # Start to apply sql log from Master 2
Select * from H2
+-- +
| | id | name | addr | comment | |
+-- +
| | 1 | H2 | NULL | H211 |
| | 2 | h3 | NULL | H212 |
| | 3 | h4 | NULL | H213 |
| | 4 | h5 | NULL | h214 |
| 5 | h-m@2 | h215 | h215 | # addr = h215?
| 6 | h7room2 | dsflk | NULL | # addr = dsflk?
+-- +
# At here. What we see?
# Column Addr, we have not do anything on it. Bug it have data.
# Column Comment for record 6, it should be "dsflk". Not "NULL"
# Step 4, at Master2, There are data looks right
Select * from H2
+-- +
| | id | name | addr | comment | |
+-- +
| | 1 | H2 | NULL | H211 |
| | 2 | h3 | NULL | H212 |
| | 3 | h4 | NULL | H213 |
| | 4 | h5 | NULL | h214 |
| | 5 | h-m@2 | NULL | h215 |
| | 6 | h7room2 | NULL | dsflk | |
+-- +
# at last
Data in Master1 and Master2 are not same anymore.
Of course, we don't have this problem if we add all the fields to the end when making table structure changes.
This should be treated as a BUG. Submit MYSQL
Another question left is: what is used to match rows in the MYSQL application log? Primary key? Or the conditions listed in the log must match.
The theoretical answer should be: primary key (if there is no primary key, it is the internal primary key that MYSQL generates for you. (students who are interested can test it themselves.
This is the answer to the question about whether the data brought about by the ROW log mode of MYSQL in the double MASTER environment is consistent. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.
