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

Part I-introduction

Pt-table-checksum executes the check of the data block in the master database through SQL, then transmits the same statement to the slave database, calculates the check of the data block on the slave database, and finally compares the check values of the same block in the master-slave database to distinguish whether the master-slave data is inconsistent.

Pt-table-sync is used to repair the inconsistency of master-slave replicated data so that they can be repaired to the final consistency, and it can also be repaired to consistency by multiple instances or multiple irrelevant database instances with double or multiple writes. At the same time, it also integrates the verification function of pt-table-checksum, which can be repaired at the same time, or based on the calculation results of pt-table-checksum.

Part II-installation of tools

Methods 1-RPM installation 1. Software download: [root@MySQL-01 ~] # wget http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.7-1.noarch.rpm 2. Install the software package that the tool depends on: [root@MySQL-01 ~] # yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes-y 3. Software installation: [root@MySQL-01 ~] # rpm-ivh percona-toolkit-2.2.7-1.noarch.rpm Preparing... # [1:percona-toolkit # #] method 2-Source Code installation 1. Software download: both tools are included in percona-toolkit, online download address: https://www.percona.com/downloads/percona-toolkit/2.2.2/. The instructions downloaded directly on the device are as follows Download and decompress for use: wget https://www.percona.com/downloads/percona-toolkit/2.2.2/percona-toolkit-2.2.2.tar.gz 2, installation dependent package yum install perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL 3, software installation tar zxvf percona-toolkit-2.2.13.tar.gz cd percona-toolkit-2.2.13 perl Makefile.PL make & & make install

Part III-using checksum to verify data consistency

1. The main library creates the following objects

Create a database

CREATE DATABASE IF NOT EXISTS percona

Create tables use percona Create Table: CREATE TABLE `checksums` (`db` char (64) NOT NULL, `tbl` char (64) NOT NULL, `chunk` int (11) NOT NULL, `chunk_ time` float DEFAULT NULL, `chunk_ index` varchar (200) DEFAULT NULL, `lower_ boundary` text, `upper_ boundary` text, `this_ crc` char (40) NOT NULL, `this_ cnt` int (11) NOT NULL, `master_ crc` char (40) DEFAULT NULL, `master_ cnt` int (11) DEFAULT NULL `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`db`, `tbl`, `chunk`), KEY `tbl` (`ts`, `db`, `tbl`) ENGINE=InnoDB DEFAULT CHARSET=latin1 if you use the automatically created table mysql > alter table checksums modify ts timestamp not null default current_timestamp if you use an existing database Then create this table under this library. Use-- replicate=test.checksums to specify

2. The users and authorization required for the creation of the main library

This user is used as the pt-table-checksum connection master / slave library for data verification, and there must be the same user who can log in to the master / slave library.

GRANT UPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON. TO 'procheck'@'172.31.150.37' identified by' Mysql.37'

GRANT ALL ON percona.* TO 'procheck'@'172.31.150.37'

3. Execute the command for data verification

Execute the command pt-table-checksum-upright procheck'-packs Mysql.37'-h272.31.150.37-P 3316-- databases=agati-- ignore-tables=sys_log-- nocheck-binlog-format-- nocheck-plan-- nocheck-replication-filters-- recursion-method=processlist other parameters and alternate parameter interpretation-- uplinlxh'-- pairMysql.163'-- h 192.168.XXX.XX main library IP address -- P 3306 main library port-- databases=db1 check db1 library-- tb1 table of tables=tb1 If there is no such parameter, the whole database and table will be checked. -- the master-slave binlog_format checked by no-check-binlog-format must be statement. If it is not statement-based, add this parameter-- nocheck-plan checks the execution plan of query (giving priority to indexes that can group tables into chunk)-- nocheck-replication-filters does not check replication filters, which is recommended. -- recursion-method= "processlist" has four parameters: processlist/hosts/dsn=DSN/no, which is used to determine whether the way to find slave is show full processlist or show slave hosts, or whether it is directly specified on the command line or is not going to find the slave library at all. Use the dns method to configure CREATE TABLE `dsns` (`id` int (11) NOT NULL AUTO_INCREMENT, `dsn` int (11) DEFAULT NULL, `dsn` varchar (255) NOT NULL, PRIMARY KEY (`id`)) The meaning of the column name parent_id: a needs no special meaning dsn: configuration content, h means host refers to slave ip,u means user refers to the created inspector user, p refers to this user password, P refers to the library port INSERT INTO dsns (parent_id,dsn) values (1gimml172.31.150.36) INSERT INTO dsns (parent_id,dsn) values (2 minichetti 172.29.147.32 pr. Upright procheck.packs Mysql.37 pr. 3376') Use-- recursion-method=dsn=h=172.31.150.37,D=percona,t=dsns D refers to the name of the database where the configuration table is stored T refers to the name of the table that stores the configuration-the timeout setting of the set-varsinnodb_lock_wait_timeout=120 lock. The default is 1-replicate=test.checksums to specify the name of the table to store the calculation results, and the default is percona.checksums. By default, the tool automatically creates the library percona and the table checksums and inputs the check results of checksum into this table. If you use this parameter to specify the table, the table structure must be the table structure created above.

4. Check the verification results.

The result can appear after the execution of the command

You can also query previously created tables

The meaning of each column of the inspection result TS: the time when the inspection was completed. ERRORS: the number of errors and warnings that occurred during the check. DIFFS: 0 means consistent, and greater than 0 indicates inconsistency. It mainly depends on whether there are inconsistent data in this column. ROWS: the number of rows in the table. CHUNKS: the number of blocks divided into the table. SKIPPED: the number of blocks skipped due to errors or warnings or too large. TIME: time of execution. TABLE: the name of the table being checked.

Part IV-error reporting and resolution

Question (1) 01-07T15:19:02 Error checksumming table test.test1: Error executing checksum query: DBD::mysql::st execute failed: Field 'ts' doesn't have a default value [for Statement "REPLACE INTO `perc ona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT?, COUNT (*) AS cnt COALESCE (LOWER (CONV (BIT_XOR (CAST (CRC32 (CONCAT_WS ('#', `host`, `user`, `select_ priv`, `insert_ priv`, `update_ priv`, `delete_ priv`, `create_ priv`, `drop_ priv`, `reload_ priv`, `shutdown_ priv`, `process_ priv`, `file_ priv`, `grant_ priv`, `references_ priv`, `index_ priv`, `alter_ priv`, `show_db_ priv`, `super_ priv`, `create_tmp_table_ priv`, `lock_tables_ priv`, `execute_ priv`, `repl_slave_ priv`, `repl_client_ priv`) `alter_routine_ priv`, `create_user_ priv`, `event_ priv`, `trigger_ priv`, `create_tablespace_ priv`, `ssl_ type`, `ssl_ cipher`, `x509 _ issuer`, `x509 _ Secrett`, `max_ connections`, `max_ updates`, `max_ connections`, `max_user_ connections`, `plugin`, `authentication_ string`, `password_ connections`, `password_last_ changed` + 0, `password_ lifetime`, `account_ locked`, CONCAT (ISNULL (`authentication_ string`), `plugin`, `authentication_ string`) ISNULL (`password_ lifetime`) AS UNSIGNED), 10,16)), 0) AS crc FROM `test`.`test1` / * checksum table*/ "with ParamValues: 0 3=undef test1, 2 # 1, 3=undef, 4=undef, 5=undef] at / usr/local/bin/pt-table-checksum line 10305. Resolve mysql > alter table checksums modify ts timestamp not null default current_timestamp

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.