Mysql master-slave consistency verification tool-pt, mysql master-slave consistency-pt

Mysql master-slave consistency verification tool-pt, mysql master-slave consistency-pt
I. Environment

1. System Environment

System	IP	Host Name	Description	Server_id
Centos6.7	MasterIP	Master	Database: Master	177
Centos6.7	SlaveIP	Slave	Database: slave	148

2. Software Environment

Software	Version	Installation Method	Description
Pt Tool	3.0.4	Compile and install	This is a comprehensive toolkit that contains many pt commands
Mysql database	5.6.37	Install yum	Master/Slave Environment

3. databases required

Database Name	Table Name	Purpose
Percona	Checksums	Store the pt command monitoring results. The first time you run the detection command, you will create your own The repair tool will read the table during restoration.

# This table can also be created by yourself. You can specify the name of the database and table when using the pt tool. For details, see the following parameter description.

Note: The self-built database table test has not passed.

Ii. Why do I need to perform master-slave consistency monitoring?

1. Master-slave replication is based on binlog logical replication, which may lead to the risk of inconsistent replication data.

2. This risk will not only cause inconsistency between user data access

3. It will also cause a 1032 or 1062 error in subsequent replication, which will lead to a risk that the replication architecture will be stuck.

4. to promptly discover and solve this problem

5. We need to perform regular or irregular verification and repair of master-slave replication Data Consistency

Iii. Principle of master-slave consistency monitoring

Iv. pt tool monitoring

Command used:

1. pt-table-check # monitor master-slave consistency

2. pt-table-sync # Fix master-slave consistency

Mysql master-slave consistency check, based on the pt tool.

# Restrictions and Problems

1. During the check phase, if there are more than 1000 rows of data, if no index or primary key is set, an error is returned, and the check will be skipped.

2. During the repair phase, if no primary key or index is set for the table, an error is returned. You can manually fix the error.

3. monitoring is based on blocks. If the data in the mysql table is not segmented, when the table is too large, the system will skip table modification if it finds no problem after monitoring for a period of time.

4. When the two data in the database are inconsistent, but the checksum check is consistent, there is no such inconsistency.

The master database and slave database account are the same, and the password is inconsistent.

Cause: the value of chencksum does not change for password modification.

5. Install the pt Tool

It is recommended that all master databases be installed from the slave Database

1. Install Dependencies

Yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl-DBI-y

Yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker-y

2. Download the installation package

1. Download from the official website

3. Installation

Tar xzvf percona-toolkit-3.0.4_x86_64.tar.gz

Cd percona-toolkit-3.0.4

Perl Makefile. PL -- install to a non-default path PREFIX =$ {HOME}

Make

Make test

Make install

Vi. Common commands of pt tools

1. Create a monitoring account

Grant all on *. * to 'zxfly _ check' @ '2017. 192.% 'identified by 'zxfly ';

Flush privileges;

2. The monitoring table is percona. checksum, which is automatically created.

Monitoring:

# Specify the Database Name

Pt-table-checksum -- databases zxfly-u 'zxfly '-p' zxfly'-hMasterIP-P3306 2>/logs/pt_error.log 1>/logs/pt_info.log

# Monitor all databases without specifying databases (except information_schema, percona, and cece_schema databases)

Pt-table-checksum -- quiet-u 'zxfly '-p 'zxfly'-hMasterIP-P3306 2>/logs/pt_error.log 1>/logs/pt_info.log

Pt-table-checksum -- replicate-check-only-u 'zxfly '-p 'zxfly'-hMasterIP-P3306

Print repair SQL: Specifies the database table

Pt-table-sync -- databases = dataname -- tables = table1, table2 h = MasterIP, u = zxfly, p = zxfly h = SlaveIP, u = zxfly, p = zxfly -- charset = utf8 -- print

Fix:

Pt-table-sync -- databases = dataname -- tables = table1, table2 h = MasterIP, u = zxfly, p = zxfly h = SlaveIP, u = zxfly, p = zxfly -- charset = utf8 -- exec

 

VII. Common pt tool parameters

1. pt-table-checksum

Parameters	Parameter description	Remarks
-- [No] check-replication-filters	Do not check the replication filter. We recommend that you enable it. You can use -- databases to specify the database to be checked.	This parameter is not required in the current environment. It is enabled by default.
-- No-check-binlog-format	The replication binlog mode is not checked. If the binlog mode is ROW, an error is returned.	The default value is monitoring. The default value is used. If this parameter is added, diff may not be available.
-- Replicate-check-only	Only information that is not synchronized is displayed.	Enabling this reduces output and displays inconsistent slave database host names However, this only displays inconsistent information that has been detected and does not display the current information.
-- Replicate =	Write the checksum information to the specified table. We recommend that you write it directly to the database to be checked. You do not need to specify	By default, the checksum table under the percona database is created.
-H-u-p-P	MasterIP Monitoring account password Port
-- Databases =	Specifies the database to be checked. Multiple databases are separated by commas.
-- Tables =	Specifies the tables to be checked. Multiple tables are separated by commas (,).
-- Recursion-method	Specifies the monitoring slave database mode. processlist is used by default. You can also specify the dsn	Multiple slave databases can be specified as follows. -- Recursion-method = dsn = h = host, D = pt, t = dsns D database name t table name
-- Quiet	Quiet Mode, minimize printing, paper printing error lines	Similar to -- replicate-check-only, but does not display slave database information

The structure and usage of the dsn database table are as follows:

1. Create table 'dss' ('id' int (11) not null AUTO_INCREMENT, 'parent _ id' int (11) default null, 'dsn 'varchar (255) not null, primary key ('id '));
2. -- Write slave database information
3. Insert into dsns (parent_id, dsn) values (1, "h = replica_host, u = checksums, p = password, P = 3306 ");
4. -- If multiple slave databases exist, multiple records are inserted.
5. -- Can also be abbreviated as follows
6. Insert into dsns (parent_id, dsn) values (1, "h = replica_host ");

2. pt-table-sync

Parameters	Parameter description	Remarks
-- Replicate =	Specifies the table obtained through pt-table-checksum.	By default, you do not need to specify the checksum table in the percona database.
-- Databases =	Database for synchronization	Used when only the specified library is repaired
-- Tables =	Specifies the tables to be repaired. Multiple tables are separated by commas (,).
-- Sync-to-master	Specify a DSN, that is, the slave IP address.	You can use show processlist or show slave status to automatically find the Master. If the primary database cannot be found
H = u = p =	Server address, account, and password	There are two ip addresses in the command. The first ip address is the Master Address and the second ip address is the Slave address.
-- Print	Print the fixed SQL statement	Print only.
-- Exec or -- execute	Execute repair
-- Algorithms = c	Repair Algorithm	Default Chunk, Nibble, GroupBy, Stream must specify an algorithm when reporting an error algorithm
-- Charset =	Specify the default Character Set	If the data contains Chinese characters and the character set is not specified, the restoration fails.

3. Explanation of output information

TS: The time when the check is completed.
ERRORS: number of ERRORS and warnings during the check.
DIFFS: 0 indicates consistency, and 1 indicates inconsistency. When -- no-replicate-check is specified, it is always 0. When -- replicate-check-only is specified, different information is displayed.
ROWS: number of ROWS in the table.
CHUNKS: Number of CHUNKS divided into tables.
SKIPPED: Number of SKIPPED blocks due to errors or warnings.
TIME: the execution TIME.
TABLE: the name of the TABLE to be checked.

8. Common pt tool errors

1. Monitoring error (slave database cannot be found, use -- recursion-method to specify the mode)

Diffs cannot be detected because no slaves were found. Please read the -- recursion-method documentation for information.

2. binlog mode problems (the -- no-check-binlog-format is used to skip monitoring because it is specified as row replication. However, this may fail to monitor the inconsistency of the master, the row mode does not require master-slave monitoring)

Replica centos-1 has binlog_format ROW which cocould cause pt-table-checksum to break replication. please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation. if you understand the risks, specify -- no-check-binlog-format to disable this check.

3. If there is no index or primary key, when there is little data (less than 1000 rows, this information is not tested, and more than 1000 rows report an error)

Cannot checksum table test. t: There is no good index and the table is oversized. at./pt-table-checksum line 6370.

4. Waiting information, the percentage detected. This is because the primary key is set but there is no index, so there is no partition and the table is too large.

Checksumming database. table: 27% 0:17 remain

9. Monitoring results for all data in multiple databases

Pt-table-checksum:

Data size	Monitoring time spent	Monitoring error table	Cause
93G	30m28. 523 s	Four	# Character set bug (the tool comes with it and cannot be solved) None of the tables on the official Platform
		1	Not detected (Chinese table name not monitored) This table is not available on the official Platform
		1	No primary key or index (number of data entries: 132835)

10. pt fixed the problem encountered by pt-table-sync.

After pt-table-checksum is used for detection, you can use the pt-table-sync tool to repair the inconsistency of the master.

The principle is: Based on the pt-table-checksum monitoring results to check and generate repair statements to repair the data in the slave database.

Error:

1. There is no prompt during the fix, but an error is returned. Use-print to print the repair statement and find garbled characters.

Solution: view the character set of the table and use the -- charset = command to specify the default character set.

2. Error: Failed to prepare TableSyncChunk plugin: Cannot chunk table 'zxfly _ zxfly1 '. 'mongo _ task_data' using the character column guid, most likely because all values start with the same character. this table must be synced separately by specifying a list of -- algorithms without the Chunk algorithm at/usr/local/bin/pt-table-sync line 4088. while doing table on 192.168.0.177

The reason is that in the default algorithm, it is necessary to ensure that the data in the primary key field has different characters before it appears, and the first character of the table's primary key data is the same.

Solution:

Use the -- algorithms = parameter to specify the algorithm. Of course, we recommend that you perform database/table sharding for restoration.

6. Fixed the error (cause: If there is no unique index or primary key, 1000 or less, more than 1000 rows have no index or the primary key will be skipped during monitoring .)

Can't make changes on the master because no unique index exists at/usr/local/bin/pt-table-sync line 10591.