Master-slave replication consistency detection in MySQL

In MySQL master-slave replication, it is often necessary to check some important tables for consistency.

Because of the delay in the synchronization of the master-slave data, the way of directly reading the server data cannot strictly guarantee the consistency of information. The data is in a constantly changing and incomplete state until the synchronization is completely completed. The possibility of locking the table is implemented, but performance is a concern. It's best to validate data. MySQL's checksum table instructions are sufficient for small lists, but large lists often require "chunking" to avoid overloading the checksum.

The Percona tool pt-table-checksum is not locked in the table.

Basic syntax:

    Pt-table-checksum   [Options]  [DSN]

Example:

    Pt-table-checksum--databases=monster--TABLES=ABC--replicate-checkh=192.168.1.186,u=chk,p=xxxx,p=3308

This tool is implemented by performing some checksum on master queries using the CRC32 function primarily to refer to--funcion parameters, which is the easiest to compute to check the consistency of master-slave replication and print the results. If the master-slave data is not the same, it will produce different results.

This column more highlights: http://www.bianceng.cn/database/MySQL/

Disadvantage: Server load may be increased.

--explain option, you can see how the tool verifies the data table.

The tool executes some checksum queries on master and then copies it to the slave.

At the same time it will only operate on a single table, so it will not do a lot of work before the start, or accumulate too much memory.

The reason it can work quickly on larger tables is that it divides each table into chunks of rows. Use Replace.. SELECT query to verify each chunk. And it dynamically adjusts chunk size (the tool keeps track of how quickly the "server is" able to execute the queries, and adjusts the chunks a S it learns more about the server ' s performance. It uses an exponential attenuation weighted average to maintain a stable block size). This avoids the entire statement running once on the table. This reduces the likelihood of a master-slave replication delay. The timeout is 0.5S for checksums on each chunk.

Dynamically adjusting the size of the chunk is a technique called ' nibbling '. It takes precedence over a primary key or a unique primary key for chunking or other primary keys, and if there is no index, and the table is not very large, the table will be chunk by the atmosphere.

The tool does not affect any operations of the database, including replication. (That means not locking the table) Pt-table-checksum detects replicas and connects to them automatically. When there are too many replication delays, the tool is automatically paused until slave is synchronized with master. If replication fails, pt-table-checksum pauses and waits. And there will be no output.

When the tool executes, it executes a explain on each chunk, and if it is too big, it skips. Can be restricted by –chunk-size-limit. If a table would be checksummed in a single chunk because it has a small number of rows, pt-table-checksum additionally Ifies That's table isn ' t oversized on replicas the tool will verify that the table is not too large on slave. This avoids the form of space-time on master, but the table is large on the slave.

There are several ways to ensure that the master load is not too high. Pt-table-checksum sets its session-level innodb_lock_wait_timeout to 1 second, so this if there is a lock wait, it'll be The victim instead of the causing other queries to time out. 。 The load is too heavy, he will pause. Pt-table-checksum'll pause if there are more than concurrently. Should probably set a sane value for your server with the--max-load option

If Pt-table-checksum stops unexpectedly, you can use the-resume option to reboot.

Afterpt-table-checksumfinishes checksumming all of the chunks in a table, it pauses and waits for all detected replicas to Finish executing the checksum queries. Once that are finished,it checks all of the replicas to if they have the same data as the master, and then prints a Lin E of output with the results. It'll also print a progress the IT pauses to the wait for replication to catch up, and the It is waiting to check R Eplicas for differences from the master

If you use the--REPLICATE-CHECK-ONLY option, only a different test result will be printed.