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

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.

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.