MySQL master-slave Replication Event validation mysql Replication Event Checksum

Mysql master-slave replication is fast, easy to use, and reliable.

However, when you check that the Master/Slave Data is inconsistent, it is difficult to determine the problem (software problems? Hardware problems? Network Transmission Problems ?)

A common case is that an error occurs during hardware and software transmission or network transmission. As a result, the SQL statements running on the master server are inconsistent with those running on the slave server (called event processing upt ).

To solve this problem, mysql developers added the replication event checksum (master-slave replication event validation) feature in version 5.6 Milestone Development Release.

MySQL 5.6 master-slave replication Part 1 [Introduction and configuration]

MySQL 5.6 master-slave replication Part 2 [restore an slave server]

MySQL 5.6 master-slave replication Part 3 [upgrade slave server to master server]

MySQL 5.6 master-slave replication Part 4 [some ignored Operation Details]

MySQL master-slave Replication Event validation MySQL Replication Event Checksum

Use pt-table-checksum to check whether master-slave replication is normal

---------------------------------------- Split line ----------------------------------------

When an event is written to binary log (binary log), checksum is also written to binary log, and after the event is transmitted over the network to the slave server (slave, verify the slave server and write it to the slave server's relay log.

Because event and checksum are recorded in each step, we can quickly locate the problem.

Checksum uses the CRC-32 Algorithm in zlib, more specifically, is the ISO-3309 CRC-32 algorithm, although this algorithm is very efficient, but always brings some additional calculations, as for the extent to which performance will be affected, there is no benchmark yet.

[Figure 1]

1. When the arrow leaves the thread, the checksum can be generated. When the arrow enters the thread, the checksum can be verified.

However, for some reason, not all arrows are checksum.

The event checksum function introduces three new parameters:

Binlog_checksum

The default value is NONE, indicating that the checksum is not generated in Arrow 1 in Figure 1, so that it is compatible with the old version of mysql.

In addition, you can only set it to CRC32.

Master_verify_checksum

It can be set to 0 or 1 (the default value is 0 ). Corresponds to Arrow 2 in Figure 1.

If it is set to 1, not only will dump thread verify the event, but will also verify the event when the show binlog events command is executed on the master.

Set to 1 to ensure that the event is completely written to the binlog of the master server.

However, this is usually set to 0.

Slave_ SQL _verify_checksum

Similar to master_verify_checksum, this value can only be set to 0 or 1 (1 by default ).

If this parameter is set to 1, a checksum is generated at Arrow 4 in Figure 1, and then checksum is verified at arrow 5.

After reading these three parameters and comparing them with figure 1, we can see that no verification is performed on Arrow 3.

The explanation in the original article is:

When the IO thread writes the event to the relay log, the checksum is verified.

This is not necessary since the checksum is verified when the event is written to the relay log at point 4, and the I/O thread just does a straight copy of the event.

What happens when a checksum error occurs? Try it.

master> CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(50));Query OK, 0 ROWS affected (0.04 sec) master> INSERT INTO t1(name) VALUES ('Mats'),('Luis');Query OK, 2 ROWS affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0 master> SHOW BINLOG EVENTS FROM 261;+-------------------+-----+------------+-----------+-------------+-----------------------------------------------------------+| Log_name          | Pos | Event_type | Server_id | End_log_pos | Info                                                      |+-------------------+-----+------------+-----------+-------------+-----------------------------------------------------------+| master-bin.000001 | 261 | Query      |         1 |         333 | BEGIN                                                     || master-bin.000001 | 333 | Intvar     |         1 |         365 | INSERT_ID=1                                               || master-bin.000001 | 365 | Query      |         1 |         477 | USE `test`; INSERT INTO t1(name) VALUES ('Mats'),('Luis') || master-bin.000001 | 477 | Query      |         1 |         550 | COMMIT                                                    |+-------------------+-----+------------+-----------+-------------+-----------------------------------------------------------+4 ROWS IN SET (0.00 sec)

This is consistent with the old version of mysql, and the shadow of checksum is not visible yet.

1 2 Next Page