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

This article mainly tells you briefly how to check the consistency of MySQL and how to achieve data synchronization. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on how to detect the consistency of MySQL and how to achieve data synchronization can bring you some practical help.

one。 Deploy percona tookit

Download the installation package

~] # wget https://www.percona.com/downloads/percona-toolkit/3.0.5/binary/redhat/7/x86_64/percona-toolkit-3.0.5-1.el7.x86_64.rpm

Installation

~] # yum install percona-toolkit-3.0.5-1.el7.x8664.rpm 2. Consistency checking tool pt-table-checksum1. Principle of consistency detection

Pt-table-checksum is one of a series of percona-toolkit tools used to detect consistency in master-slave databases. Working on only one table at a time will cut the table on the main library into a chunk, which depends on the index on the table. Therefore, the detection does not require a lot of memory and preliminary work, and the exponential attenuation algorithm can be used to quickly select the appropriate chunk size and reduce the pressure on the cloud server at the data peak. The chunk that cuts the table into one then checksum the chunk and record it. And compare whether the checksum on the slave database is consistent, so as to judge whether the data is consistent. And in the detection process will automatically determine the master load, as well as slave delay, once the threshold will be stopped. It has little impact on the online environment. And he can stop at any time, as long as he joins during the restart-resume will restart from the last test. Next, we will introduce the process in detail.

1) check of table structure

The check of the table structure is also called the calculation of the checksum value of a single row of data, and gets the data type of each column, converts all the data types into strings, and then uses the concat_ws () function to concatenate, thus calculating the checksum value of the row. Checksum is calculated using crc32 by default.

2) calculation of data block checksum

Pt-table-sync intelligently analyzes the indexes on the table, and then split the table's data into several chunk, which is calculated in chunk units. It can be understood as splicing the data of all the rows in the chunk, and then calculating the value of the crc32 to get the checksum value of the chunk. So it stores the checksum results in a statistical table, and then records the executed sql statements in binlog, and the task is complete. Then the SQL statements from the CVM are read to the binlog and executed in turn, and the checksum is saved in the table.

two。 Check 1) Authorization

Create database pt CHARACTER SET utf8

GRANT UPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON. TO 'checksums'@'192.168.239.135' identified by' check_pass'

GRANT ALL ON pt.* TO 'checksums'@'192.168.%'

Here we create a database to store the data generated by the consistency test. The master-slave synchronization tool pt-table-sync looks for inconsistent data based on the data in this database and synchronizes it. Among them, 135 are dominant and 136 are followers.

2) check (Master CVM is running)

Pt-table-checksum-- nocheck-binlog-format-- nocheck-plan-- nocheck-replication-filters-- replicate=pt.checksums-- set-vars innodb_lock_wait_timeout=120-- databases newtable-upright checksums'- packs checksums'- h292.168.239.135

#-h-u-p-P-S-d connection information

#-mysql configuration parameters such as binlog_ignore_db are ignored in nocheck-replication-filters detection.

#-nocheck-binlog-format does not detect the log format. The default format is statement. If the log of binlog is different from the default, the detection will fail. So we will turn off the detection of binlog format

#-replicate specifies the db and table stored in checksum, such as test.checksum

#-- chunk-size,-- chunk-size-limit are used to specify the size of the detection block. More controllable, Number of rows to select for each checksum query. The default is 1000. For-- chunk-size-limit, it can avoid excessive slave latency when the primary cloud server is empty and the slave service data is very large.

#-timeout setting for lock-wait-timeout innodb lock. Default is 1.

#-max-load: Examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than their thresholds

#-replicate-check-only only outputs information with inconsistent data.

#-- resume: after the pt-table-checksum stops, you can use this parameter to start where it stops.

Note:

The pt-table-checksum premise assumes that the master-slave table is consistent with the table structure, and pt-table-checksum will fail if it is inconsistent.

three。 Data synchronization tool pt-table-sync

Pt-table-sync is a MySQL data synchronization tool, which is not only to synchronize master and slave data, but also to synchronize tables on any host.

Pt-table-checksum is just a check, so it stores the checksum results in a statistical table, and then records the executed sql statements in binlog, and the task is complete.

Unlike pt-table-sync, the workflow is as follows:

A) Connect to the master library: the pt tool connects to the master library and then automatically discovers all slave libraries of the master library. Show full processlist is used by default to find the slave library, but this is only valid if the master and slave instance ports are the same. B) add a for update lock to each chunk on the master library. Once the lock is acquired, record the show master status value of the current main library. Execute the select master_pos_wait () function on the slave library, waiting for the sql thread from the library to execute to the location where show master status gets it. In order to ensure that the content of the master and subordinate about this chunk will not be changed. C) checksum this chunk, and then compare it with the checksum of the master library d) if the checksum is the same, it means that the master-slave data is the same, then move on to the next chunke.) if the checksum is different, the chunk is inconsistent. Dig inside the chunk, calculate the checksum line by line and compare f) if a line is found to be inconsistent, mark it down. Continue to detect the remaining rows until the end of the chunk g) use the replace into (insert if the data does not exist, update if the data does not exist, avoid primary key constraints) statement, execute once in the master database to generate the full binlog of the row, and synchronize to the slave library, which will repair the slave library based on the master database data Use replace to insert rows in the master database that are not available in the slave library (must not be insert); delete rows that are in the slave library but not in the master library by performing delete in the master library (pt-table-sync strongly recommends that all data repairs are carried out only in the master database, rather than directly modifying slave data; but there are also special cases, which will be discussed later). H) until all inconsistent lines of the chunk are fixed. Continue to check and fix the next chunki) until all the tables on the slave library are repaired. Start repairing the next one from library four. Experimental example 1. Experimental environment host IP: 192.168.239.135 192.168.239.136 host system: centos7.2MySQL version: 5.5.56

Two hosts have been configured for master and slave, of which 135 are master and 136 are slave

two。 Master-slave data consistency check 1) Authorization (on master host)

Mysql > CREATE DATABASE pt; # create the database pt to store the value of checksum

Mysql > GRANT UPDATE,INSERT,SELECT,PROCESS,SUPER,REPLICATION SLAVE ON. 'checksum'@'192.168.%' IDENTIFIED BY "check_pass"; # create a checksum user to perform the test and assign the permissions to be used for the test.

Mysql > GRANT ALL ON pt.* TO checksum@'192.168.%'; # checksum users want to write the value of checksum to the pt database, so they need to assign permissions to the checksum user.

Permission explanation: select / / View the tables of all libraries. You can add explain option to view process / / show processlistsuper / / set binlog_format='statement'replication slave / / show slave hosts.

Note: when performing consistency checking on master, master will view the slave host through show processlist and connect to slave through the account and password connected to master, so the account for consistency checking on master must be available on slave.

2) perform consistency checking on master

First of all, we need to create artificial inconsistencies and delete a record in slave

Perform a consistency check (both master and slave can be performed)

~] # pt-table-checksum-- nocheck-binlog-format-- nocheck-plan-- nocheck-replication-filters-- replicate=pt.checksums-- databases=hellodb-u 'checksum'-p' check_pass'-h 192.168.239.135 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE03-14T16:25:21 0 18 1 0 0.011 hellodb.classes03-14T16:25:21 0 0 14 1 0 0.017 hellodb.coc03-14T16:25:21 0 0 7 1 0 0.032 hellodb.courses03-14T16:25:21 0 0 15 1 0 0.015 hellodb.scores03-14T16:25:21 0 0 25 1 0 0.016 hellodb.students03-14T16:25:21 0 0 4 1 0 0.018 hellodb .teachers 03-14T16:25:21 0 0 01 0 0.016 hellodb.toc

Display data interpretation

TS: the time when the test table is completed, and the year is not displayed.

ERRORS: the number of errors and warnings that occurred during checksum

DIFFS: the number of different chunk between master and slave. If it is not 0, it indicates that the master-slave data is inconsistent.

ROWS: how many rows a chunk has when checking a table. If the-where option is used, the chunk in a table may be different

CHUNKS: how many chunk is the table cut into

SKIPPED: the number of chunk that is skipped for some reason

TIME: checksum the time spent on this table.

TABLE: indicated by checksum

From the above, we can see that there is data inconsistency in the classes table.

3. Master-slave synchronization

Master-slave synchronization is often achieved with the help of the checksum table generated by pt-table-checksum.

1) Manual synchronization

~] # pt-table-sync-- print-- sync-to-master hackers 192.168.239.136 under the guidance of upright checksummt packs checkpassing pass-- databases=hellodb-- replicate=pt.checksums

The slave host that needs to be synchronized, as well as the user name and password of the login

-- databases=hellodb: indicates the synchronized data

-- replicate=pt.checksums: the checksum database used for synchronization.

-- sync-to-master: use show slave status to automatically find the master CVM to synchronize data. If this parameter is not available, we need to specify both master and slave through h p u, that is, two sets of h p u.

-- print: master and slave data are only printed and not executed on the slave.

This command can be executed on both master and slave. The output information is as follows:

All we need to do is execute the sql statement REPLACE INTO hellodb.classes (classid, class, numofstu) VALUES ('1percent,' Shaolin Pai', '10') on the slave CVM.

2) automatic synchronization

~] # pt-table-sync-- execute-- sync-to-master hackers 192.168.239.136 under the guidance of upright checksummt packs checkpassing pass-- databases=hellodb-- replicate=pt.checksums

-- execute: automatically repairs master-slave data

The following error occurred in automatic synchronization:

Pt-table-sync does not operate directly on slave when it is implemented at the same time, but executes commands on master, which in turn affects slave. This way of modifying data is more secure. So master needs to have corresponding permissions on slave.

The image above shows that master does not have delete permission on slave. Check the permissions assigned by slave. As shown in the figure below, you can see that there is no delete permission. Just add the delete permission to the checksum user on master.

Modify the permissions of checksum on master. Due to master-slave synchronization, slave will also modify the corresponding user permissions.

Mysql GRANT UPDATE,INSERT,SELECT,DELETE,PROCESS,SUPER,REPLICATION SLAVE ON. TO 'checksum'@'192.168.%'

Perform data synchronization, perform checksum detection again, you can see that there is no difference, and look at the classes data in slave and find that the deleted data appears again.

MySQL consistency detection and data synchronization will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.