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

This article mainly shows you "how to use pt tools to verify and repair master-slave". The content is simple and easy to understand and the organization is clear. I hope it can help you solve your doubts. Let Xiaobian lead you to study and learn "how to use pt tools to verify and repair master-slave" this article.

Use pt tool to verify master-slave consistency and repair [principle description]

pt-table-checksum is one of the components of Percona-Toolkit, which is used to check whether the data of MySQL master and slave libraries is consistent. The principle is to execute SQL statements based on statements in the master database to generate checksums of data blocks in the master database, transfer the same SQL statements to the slave database for execution, and calculate checksums of the same data blocks in the slave database. Finally, compare the checksum values of the same data blocks in the master and slave databases to determine whether the master and slave data are consistent. The detection process divides the table into rows into chunks based on unique indexes to avoid locking the table for unit calculations. When detecting, it will automatically judge the load of replication delay and master, and will automatically suspend detection after exceeding the threshold value to reduce the impact on online services.

pt-table-checksum can handle most scenarios by default, officials say, even if thousands of libraries, trillions of rows, it can still work well, this comes from the design is very simple, check a table at a time, do not need too much memory and redundant operations; when necessary, pt-table-checksum will dynamically change the chunk size according to the server load, reduce the delay from the library.

To minimize interference with the database, pt-table-checksum also automatically detects and connects to the slave, although if it fails, you can specify the--recursion-method option to tell where the slave is. Its ease of use is also reflected in the fact that if there is a delay in replication, the checksum of the slave library will pause until it catches up with the calculation time point of the master library (also through the option--set a maximum allowable delay value, beyond which it is considered inconsistent).

To secure the primary database service, the tool implements a number of safeguards:

1) Automatically set innodb_lock_wait_timeout to 1s to avoid causing locks

By default pt-table-checksum pauses when the database has more than 25 concurrent queries. You can set this threshold by setting the--max-load option

3) When Ctrl+C stops the task, the tool will normally complete the current chunk detection, and the next time you use the--resume option, you can resume and continue to the next chunk.

[Working process]

1. Connect to master library: pt tool connects to master library and then automatically discovers all slave libraries of master library. By default show slave hosts is used to find slaves, but this only works if the slave and slave instance ports are the same.

2. Find out if the master or slave library has a copy filter rule: this is the option checked by default for security reasons. You can turn off this check, but this may cause checksum sql statements to either not sync to the slave or to reach the slave and find that the slave has no tables to checksum, both of which will cause the slave to sync the card library.

3. Start getting tables, one by one.

4. If it is the first chunk of the table, then the chunk-size is generally 1000; if it is not the first chunk of the table, then the result analyzed in step 12 is used.

5. Check the table structure, perform data type conversion, etc., and generate the sql statement of checksum.

6. The method of choosing the most appropriate split table based on the index and distribution of data on the table.

7. Start checksum table.

8. By default, before chunking a table, delete the calculation results related to the last table. Unless-resume.

9. Depending on the result of the explanation, determine whether the chunk size exceeds the chunk-size limit you defined. If exceeded, this chunk is ignored in order not to affect online performance.

10. Add the for update lock to the row to checksum and compute.

11. Store the result of the calculation in the master_crc master_count column.

12. Resize the next chunk.

13. Waiting for the slave to catch up with the master. If there are no slaves running with delayed backups, it is best to check all slaves and if the largest slave is found to be delayed more than max-lag seconds, pt tool will pause here.

14. If the max-load of the master library is found to exceed a certain threshold, the pt tool pauses here.

15. Continue with the next chunk until the table is finished with chunks.

16. Wait for checksums to be performed from the library so that summary statistics can be generated. Each table is summarized and counted once.

17. Cycle through each table until the end.

[Verification Script]

If pt tool is not used properly, it will affect the normal use of the business, and even deadlock occurs. In combination with production experience, use the following parameters to verify

TS: Timestamp of completion of the examination.

ERRORS: The number of errors and warnings that occurred during the check.

DIFFS: Number of inconsistent chunks. When--no-replicate-check is specified, it will always be 0; when--replicate-check-only is specified, it will not check but only calculate crc32 from checksums table, and only display inconsistent information (after all, most of the output should be consistent, which is easy to cause interference).

ROWS: Number of rows in the table compared.

CHUNKS: Number of blocks partitioned into the table.

SKIPPED: Number of blocks skipped due to errors or warnings or too large.

TIME: Time of execution.

TABLE: The name of the table being checked

[Repair Script]

Click here to fold or open

user=""

password=""

charset="utf8mb4"

replicate="pt.checksum"

chunk_size="1500"

pid="/data/script/mysql/pt-table-sync.pid"

sync_to_master="h=10.9.129.33,P=3306"

tables="kuaikan.device_push_info"

/usr/bin/pt-table-sync \

--user=${user} \

--password=${password} \

--pid=${pid} \

--bin-log \

--tables="${tables}" \

--buffer-in-mysql \

--no-buffer-to-client \

--charset=${charset} \

--no-check-child-tables \

--no-foreign-key-checks \

--check-master \

--replicate=${replicate} \

--sync-to-master ${sync_to_master} \

--check-slave \

--check-triggers \

--chunk-size=${chunk_size} \

--print \

--transaction \

--verbose

[Repair script parameter description]

1) print only prints the command that needs to be executed. After confirmation, change the parameter-print to-execute.

2) sync_to_master Fill in the address of the slave library here. Just fill in the address of the slave library. The information of the master library will be automatically obtained from the slave library show slave status. There is no need to write the address of the master library. After writing the address of the slave library, the slave library will be changed according to the difference between the slave libraries. In any case, it is executed on the master side. Differences in other slave libraries are not fixed.

3) no-buffer-to-client If this option is disabled, MySQL will send all rows at once, for large tables.

4) lock parameter If the-replicate or-sync-to-master parameter is used, the slave end will not lock the table. When locking tables, lock tables are used, but if--transaction is used, the lock table starts from the beginning of the transaction to the commit.

The above is "how to use pt tools to verify repair master-slave" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.