Shulou(Shulou.com)06/01 Report--

⒈ the purpose of this article: many MySQL DBA think they have a deep understanding of mysql's line replication (this line of replication is not the content of the junior DBA, why do you ask such a low-level question), then it may surprise you.

The ⒉ question leads to:

Suppose there are two database instances, A (master)-> B (slave).

⑴ first asked:

① sets up the table structure on the main library as follows (note that there is a unique key here, but no primary key):

CREATE TABLE `test` (`a` int (11) DEFAULT NULL, `b` int (11) NOT NULL, `c` varchar (20) DEFAULT NULL, UNIQUE KEY `buique` (`b`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

②, we insert a record from library B:

Insert into test values (2, 2, 2, 22')

③ then inserts a record on main library A:

There is no doubt that there will be a 1032 error in the slave library and the only key conflict will occur in insert into test values.

Then skip an event from the library (gtid skips a thing); / / so that master-slave copy A-> B will be normal

④ next, we do an operation on the main library A:

Update test set caterpillar 333ab'where bread2

Please answer: does the master and slave of B library have an error of 1032, or is the master-slave replication normal? if so, what is the data of the test table of B library?

⑵ asked the second question:

① establishes the table structure on the main library as follows (note that there are primary keys and unique keys here):

CREATE TABLE `test1` (`id` int (11) NOT NULL AUTO_INCREMENT, `a` int (11) NOT NULL, `b` int (11) NOT NULL, `c` varchar (20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `b` (`b`) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

②, we insert a record from library B:

Insert into test values (2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 22')

③ then inserts a record on main library A:

There is no doubt that there will be a 1032 error in the slave library and the only key conflict will occur in insert into test values.

Then skip an event from the library (gtid skips a thing); / / so that master-slave copy A-> B will be normal

④ next, we do an operation on the main library A:

Update test set caterpillar 333ab'where bread2

Please answer: does the master and slave of B library have an error of 1032, or is the master-slave replication normal? if so, what is the data of the test table of B library?

⒊ problem analysis and conclusion:

Don't rush to see the answer, let's see what (main library) parses update lines to copy binlog prints?

BEGIN / *! * / # at 4255 # 190228 20:59:13 server id 212493307 end_log_pos 4307 CRC32 0x902463fc Table_map: `bcd`.`test1`mapped to number # at 4307 # 190228 20:59:13 server id 212493307 end_log_pos 4382 CRC32 0x06b71e7b Update_rows: table id 340 flags: STMT_END_F # UPDATE `bcd`.`test1` # # WHERE # # @ 1room3 / * INT meta=0 nullable=0 is_null=0 * / # # @ 2room3 / * INT meta=0 nullable=0 is_null=0 * / # # @ 3room3 / * INT meta=0 nullable=0 is_null=0 * / # @ 4 / 331 / * VARSTRING (80) meta=80 nullable=1 is_null=0 * / # SET # @ 1: 3 / * INT meta=0 nullable=0 is_null=0 * / # # @ 2: 3 / * INT meta=0 nullable=0 is_null=0 * / # # @ 3 / * INT meta=0 nullable=0 is_null=0 * / # # @ 4 / 3 / * VARSTRING (80) meta=80 nullable=1 is_null=0 * / # at 4382 # 190228 20: 59:13 server id 212493307 end_log_pos 4413 CRC32 0x9b13368e Xid = 14175 com bat Yes, he recorded the contents of the original line. The original line here is (3pje 3pje 3pjm 33333') and then it has been changed to (3pcr 3je 3pas 3Q 3aaa33').

Then after this binlog is copied to the slave library, it involves the problem of how to locate a row for modification:

Is it possible to locate a row through all tuples (3pyrm 3pyrm 33333')? Do you locate a row through the unique key of the primary key (3Magne3)? Is it through the primary key (3) to locate a row? Is it through the unique key (3) to locate a row?

In fact, none of them. Locate the line from the library as follows:

When there is a primary key in the table, locate a row through the primary key

When there is both a primary key and a unique key in the table, a row is located by the primary key; the primary key is different, but the unique key is the same, representing different rows

When the table has only a unique key, locate a row with the unique key

The primary key automatically generated by the system does not participate in replication and cannot locate a row

So, can you answer the above questions?

Problem 1: there will be no 1032 error in library B, and the final data from library B is (3pd2, "333ab').

Problem 2: there will be a 1032 error in the B library, and the final data in the B library is (2)

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.