MySQL数据库一致性验证工具pt |
您所在的位置：网站首页 › 数据库一致性验证怎么做 › MySQL数据库一致性验证工具pt |
|
https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html Percona Toolkit 时 Percona公司提供的一个数据库管理工具包，包括常用的数据一致性验证、差异补齐、查找重复索引、分析二进制日志信息等，大大方便DBA查找和解决问题、优化数据库性能。但是作为一款工具，其使用仍然可能会对数据库产生影响，所以在使用该工具包执行任何操作执行，都需要完整的数据库备份。 以下内容主要从官档摘录。 Percona Toolkit is mature, proven in the real world, and well tested, but all database tools can pose a risk to the system and the database server. Before using this tool, please: Read the tool’s documentationReview the tool’s known “BUGS”Test the tool on a non-production serverBackup your production server and verify the backups 功能点 自助查找（满足过滤条件的）数据库、表每次操作一张表，所以不会占用大量的内存，也不会额外增加系统负载，即使是大表 以块为单位，批量处理大表；每次以 single REPLACE..SELECT query 批量检查在期望的时间内，动态的预估可以检查的块大小确保检查时对系统无影响并且不会增加同步压力、提升系统负载默认的期望检查时间在0.5秒持续跟踪数据库查询性能，根据性能变化，调整批量处理块大小 利用 an exponentially decaying weighted average 来决定块的大小当检测到系统负载较高时，会立刻改变(减少)块的大小当前检测的数据表必须有主键或者唯一约束，至少要有索引；如果没有索引，将会配置一个合适的块大小做检查自动发现并连接主备库，并不断检测复制关系；如果没有，指定 --recursion-method 如果备库延迟主库太多，则暂停运行直至主备无延迟如果备库报错或者停止了，则暂停运行并等待重新正常运行如果备库上设置了过滤条件 slave_skip_errors ，那么除非强制忽略它，否则将不会运行。 because the queries that pt-table-checksum executes could potentially conflict with them and cause replication to fail.限制数据块的大小，以保证能够安全的进行验证 使用EXPALIN检查块的大小，如果比期望的数据库块大，则跳过使用 --chunk-size-limit 限定块的大小如果主库上一个表的数据量很小，但是在备库上却又很大，是不会使用单一的块去做验证在session级别设置 innodb_lock_wait_timeout = 1 ，这样如果有锁需要等待，那么会首先把自己的session杀掉监控数据库的负载，如果数据库负载过高或者并发超过25，那么将会进入等待状态，可以设置 --max-load 来确认是否进入等待状态以低优先级运行，以不影响正常数据库服务为前提；如果需要杀掉验证线程，那么直接杀掉好了；如果在正常的验证过程中，连接中断，那么会重新连接配置--resume 可以在遇到错误导致验证中断的情况下，自动重新运行；从最后一次验证的表的最后一个块开始；可以使用Control-C终止连接当验证完一张表之后，pt-table-checksum 会等待所有的备库验证完成；当都结束后，比对备库的数据是否和主库一致，输出比对结果在验证过程中，会一直输出验证进度 每张表的进度因等待备库追赶上主库、或验证备库是否存在差异的过程中，同样数据等待信息可以选择 --quiet 参数，静默执行如果要查看已经验证的信息以及主备库表的差异，可以执行以下查询，每一行代表一个数据块的信息 SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM percona.checksums WHERE ( master_cnt this_cnt OR master_crc this_crc OR ISNULL(master_crc) ISNULL(this_crc)) GROUP BY db, tbl; 为了使用pt-table-sync version 1.0，可以做一下修改，让其与Version 2.0 of pt-table-checksum 兼容 ALTER TABLE checksums ADD boundaries VARCHAR(500); UPDATE checksums SET boundaries = COALESCE(CONCAT('id BETWEEN ', lower_boundary, ' AND ', upper_boundary), '1=1'); 限制 要求statement-based 复制模式，并且执行中在主库设置binlog_format=STATEMENT ，但是因为mysql 的限制，复制关系并不会受影响；会自动检查binlog_format，可以查看--[no]check-binlog-format如果存在主备库schema 、表结构不同，主备关系将会被破坏 参数 -[no]check-binlog-format：默认 yes，检查 binlog_format --check-interval：默认 1，Sleep time between checks for --max-lag. --[no]check-plan：默认 yes，首先 Expalin table，根据执行计划将表分为多个小块 --[no]check-replication-filters：默认 yes，检查复制关系中是否有过滤条件 --max-lag：当备库延迟多久之后，检测进程自动暂停 --check-slave-lag：当指定备库复制延时时，才会暂停 --[no]check-slave-tables：默认 yes，当主备库表不一致或者表上字段不一致，会导致复制异常 --chunk-index：使用指定的索引进行分块 --chunk-index-columns：使用复合索引的前几个字段做分块 --chunk-size：默认 1000，指定多少行作为一个块，或者以 k M G 为单位，最好不配置 --chunk-time：每个块的检查时间，超时则舍弃这个块，与 --chunk-size互斥 --chunk-size-limit：--chunk-size的倍数 --chunk-time：默认 0.5S，默认一个块的检测时间 --columns：逗号分隔，全局设置，只检查指定的字段；建议检查单个表的时候使用 --[no]create-replicate-table：创建记录检测信息的表 --databases：只检查指定的数据库 --[no]empty-replicate-table：删除当前检查表的之前的验证信息 --max-load：以当前 SHOW GLOBAL STATUS参数 value为基准，默认值超过+20%时，进程pause，或者直接指定参数=xxx --progress：默认30s，每隔多长时间输出日志 --quiet：只输出不一致的信息 --recurse：默认无限大，检测的备库的层级，M-S-S…… --recursion-method：发现复制关系的方法 METHOD USES =========== ============================================= processlist SHOW PROCESSLIST hosts SHOW SLAVE HOSTS cluster SHOW STATUS LIKE 'wsrep\_incoming\_addresses' dsn=DSN DSNs from a table none Do not find slaves 以 dsn方法为例：--recursion-method dsn=h=host,D=percona,t=dsns 必须要有一张表来显示的配置备库的信息： CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, # h=replica_host,u=repl_user,p=repl_pass PRIMARY KEY (`id`) ); --replicate：检测结果存储的表，默认--[no]create-replicate-table 会创建 CREATE TABLE checksums ( db CHAR(64) NOT NULL, tbl CHAR(64) NOT NULL, chunk INT NOT NULL, chunk_time FLOAT NULL, chunk_index VARCHAR(200) NULL, lower_boundary TEXT NULL, upper_boundary TEXT NULL, this_crc CHAR(40) NOT NULL, this_cnt INT NOT NULL, master_crc CHAR(40) NULL, master_cnt INT NULL, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (db, tbl, chunk), INDEX ts_db_tbl (ts, db, tbl) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --[no]replicate-check：检测数据内容（字段值）是否一致 --replicate-check-only：不检查表，只检查之前的检测结果然后退出 --replicate-check-retries：默认是1，当遇到差异时，重复检测的次数 --replicate-database：只检测当前指定的数据库 --resume：接着上次退出时检测到的位置运行 --retries：当遇到锁或者线程被杀时，重复尝试运行的次数 --run-time：进程执行的时间，超过则停止 --slave-skip-tolerance：主库表只有一个块，当预估的备库表数据量在主的多大范围内时，继续执行 --where：pt-table-checksum --where "ts > CURRENT_DATE - INTERVAL 1 DAY" 全局的配置 脚本 pt-table-checksum --nocheck-binlog-format *--nocheck-plan* --nocheck-replication-filters --replicate=percona.checksums --set-vars innodb_lock_wait_timeout=120 --host=192.168.3.25 --port=3306 --user=root --password=zhjie198 --databases=working --tables=x 输出 # A software update is available: # * The current version for Percona::Toolkit is 2.2.20. TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 01-16T16:57:55 0 0 2 1 0 0.418 working.x 报错 # 无索引 01-17T15:46:58 Cannot checksum table p95169.expert_hospdept_relation: There is no good index and the table is oversized. at /usr/bin/pt-table-checksum line 6559. CREATE TABLE `expert_hospdept_relation` ( `EXPERTHOSPDEPTUUID` varchar(40) DEFAULT NULL, `EXPERTUUID` varchar(40) DEFAULT NULL, `HOSPITALUUID` varchar(40) DEFAULT NULL, `HOSPDEPTUUID` varchar(40) DEFAULT NULL, `EXPERTSORTCODE` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 # 显示 progress当前处理进度 Checksumming sonar.project_measures: 36% 00:51 remain 01-17T15:49:33 0 0 3720214 74 0 48.901 sonar.project_measures # 数据块太大 01-17T15:50:15 Skipping chunk 1 of tigasedb.tig_ma_msgs because it is oversized. The current chunk size limit is 225674 rows (chunk size=112837 * chunk size limit=2.0), but MySQL estimates that there are 282244 rows in the chunk. Checksumming tigasedb.tig_ma_msgs: 36% 00:52 remain 01-17T15:51:11 0 0 2315665 98 25 55.961 tigasedb.tig_ma_msgs ………… 01-17T15:51:23 Skipping chunk 5 of tigasedb2.tig_ma_msgs because it is oversized. The current chunk size limit is 115534 rows (chunk size=57767 * chunk size limit=2.0), but MySQL estimates that there are 121246 rows in the chunk. 01-17T15:51:29 0 0 617401 17 5 9.202 tigasedb2.tig_ma_msgs 脚本 pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --replicate=percona.checksums --set-vars innodb_lock_wait_timeout=50 --host=192.168.3.25 --port=3306 --user=root --password=zhujie1986 --databases working --quiet --tables department # 检测到备库不存在数据表 01-17T17:43:00 Skipping table working.department because it has problems on these replicas: Table working.department does not exist on replica lx3-26 This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check. 01-17T17:43:00 Error checksumming table working.department: Error getting row count estimate of table working.department on replica lx3-26: DBD::mysql::db selectrow_hashref failed: Table 'working.department' doesn't exist [for Statement "EXPLAIN SELECT * FROM `working`.`department` WHERE 1=1"] at /usr/bin/pt-table-checksum line 6679. TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 01-17T17:43:00 1 0 0 0 0 0.002 working.department 脚本 **pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --replicate=percona.checksums --set-vars innodb_lock_wait_timeout=50 --host=192.168.3.25 --port=3306 --user=root --password=zhujie1986 --databases working --tables department --replicate-check** # 检测到数据不一致时 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 01-17T17:46:42 0 1 7 1 0 0.010 working.department |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |