MySQL教程之Percona-Toolkit 之 pt-table-checksum 总结(2)

当前位置:

首页 > 数据库 > MySQL >

MySQL教程之Percona-Toolkit 之 pt-table-checksum 总结(2)

checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --columns=first_name --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns

只打印校验查询语句

指定选项--explain。

# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns --explain
Enter MySQL password:

Checking if all tables can be checksummed ...
Starting checksum ...
--
-- employees.employees_ptchksum
--

REPLACE INTO `percona`.`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('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= ?)) AND ((`emp_no` <= ?)) /*checksum chunk*/

REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*), '0' FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` < ?)) ORDER BY `emp_no` /*past lower chunk*/

REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*), '0' FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` > ?)) ORDER BY `emp_no` /*past upper chunk*/

SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= ?)) ORDER BY `emp_no` LIMIT ?, 2 /*next chunk boundary*/

指定校验分块(chunk)大小

会覆盖工具动态调整chunk大小的行为。

# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns --chunk-size=66666

指定每个分块(chunk)校验时间

动态调整chunk大小，使得校验操作可以在指定的时间内完成。

# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns --chunk-time=2

回到顶部

工作流程

通过general log来了解pt-table-checksum工具如何进行校验，以及如何进行校验语句查询的，以下以校验employees_ptchksum表为例。

-- 初始的一些检查数据库参数、负载信息这里不再细说
51 Connect  admin@dbabd1 on  using TCP/IP
51 Query    SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
51 Query    SET SESSION innodb_lock_wait_timeout=1
51 Query    SHOW VARIABLES LIKE 'wait\_timeout'
51 Query    SET SESSION wait_timeout=10000
51 Query    SELECT @@SQL_MODE
51 Query    SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
51 Query    SELECT @@server_id /*!50038 , @@hostname*/
51 Query    SELECT @@SQL_MODE
51 Query    SET SQL_MODE=',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
51 Query    SHOW VARIABLES LIKE 'version%'
51 Query    SHOW ENGINES
51 Query    SHOW VARIABLES LIKE 'innodb_version'

-- 设置会话级binlog row格式为STATEMENT
51 Query    SELECT @@binlog_format
51 Query    /*!50108 SET @@binlog_format := 'STATEMENT'*/

-- 设置会话级隔离级别为RR(REPEATABLE READ)
51 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
51 Query    SHOW /*!40103 GLOBAL*/ VARIABLES
51 Query    SELECT VERSION()
51 Query    SHOW ENGINES
51 Query    SHOW VARIABLES LIKE 'wsrep_on'
52 Connect  admin@dbabd1 on percona using TCP/IP
52 Query    SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
52 Query    SET SESSION innodb_lock_wait_timeout=1
52 Query    SHOW VARIABLES LIKE 'wait\_timeout'
52 Query    SET SESSION wait_timeout=10000
52 Query    SELECT @@SQL_MODE
52 Query    SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
52 Query    SELECT @@server_id /*!50038 , @@hostname*/
52 Query    SHOW VARIABLES LIKE 'wsrep_on'
52 Query    SELECT dsn FROM `percona`.`dsns` ORDER BY id
52 Quit 
51 Query    SHOW VARIABLES LIKE 'wsrep_on'
51 Query    SELECT @@SERVER_ID
51 Query    SHOW VARIABLES LIKE 'wsrep_on'
51 Query    SELECT @@SERVER_ID

-- 创建存储校验结果表percona.checksums
51 Query    SHOW DATABASES LIKE 'percona'
51 Query    CREATE DATABASE IF NOT EXISTS `percona` /* pt-table-checksum */
51 Query    USE `percona`
51 Query    SHOW TABLES FROM `percona` LIKE 'checksums'
51 Query    CREATE TABLE IF NOT EXISTS `percona`.`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

-- 检查数据库服务器运行状态
51 Query    SHOW GLOBAL STATUS LIKE 'Threads_running'
51 Query    SELECT CONCAT(@@hostname, @@port)
51 Query    SELECT CRC32('test-string')
51 Query    SELECT CRC32('a')
51 Query    SELECT CRC32('a')
51 Query    SHOW VARIABLES LIKE 'wsrep_on'
51 Query    SHOW DATABASES
51 Query    SHOW /*!50002 FULL*/ TABLES FROM `employees`
51 Query    /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

-- 开始对表employees_ptchksum进行分析
51 Query    USE `employees`
51 Query    SHOW CREATE TABLE `employees`.`employees_ptchksum`
51 Query    /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

-- 获取表信息，通过主键或唯一索引，获取校验第一个chunk下边界起点，这里为emp_no = 10001
51 Query    EXPLAIN SELECT * FROM `employees`.`employees_ptchksum` WHERE 1=1
51 Query    SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) ORDER BY `emp_no` LIMIT 1 /*first lower boundary*/

-- 通过索引获取校验表行数
51 Query    SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX (`PRIMARY`) WHERE `emp_no` IS NOT NULL ORDER BY `emp_no` LIMIT 1 /*key_len*/
51 Query    EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `employees`.`employees_ptchksum` FORCE INDEX (`PRIMARY`) WHERE `emp_no` >= '10001' /*key_len*/

-- 清除表percona.checksums中有关employees_ptchksum表的校验结果信息
51 Query    USE `percona`
51 Query    DELETE FROM `percona`.`checksums` WHERE db = 'employees' AND tbl = 'employees_ptchksum'

-- 确定每个chunk包含的行数，首个块默认为1000行，可以根据系统状态调整，调整每个块校验完成默认时间为0.5s
51 Query    USE `employees`
51 Query    EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) ORDER BY `emp_no` LIMIT 999, 2 /*next chunk boundary*/
51 Query    SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) ORDER BY `emp_no` LIMIT 999, 2 /*next chunk boundary*/

-- 确定本次chunk校验查询的执行计划，并通过replace into方式写入校验结果表
51 Query    EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')) /*explain checksum chunk*/
51 Query    REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'employees', 'employees_ptchksum', '1', 'PRIMARY', '10001', '11000', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')) /*checksum chunk*/

-- 再次查看校验chunk的行数和校验结果，并更新校验结果表
51 Query    SHOW WARNINGS
51 Query    SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '1'
51 Query    UPDATE `percona`.`checksums` SET chunk_time = '0.007819', master_crc = '4f6eb3dc', master_cnt = '1000' WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '1'

-- 查看数据库服务器状态，并进行下一个chunk的检查校验
51 Query    SHOW GLOBAL STATUS LIKE 'Threads_running'
51 Query    EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) ORDER BY `emp_no` LIMIT 63946, 2 /*next chunk boundary*/
51 Query    SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) ORDER BY `emp_no` LIMIT 63946, 2 /*next chunk boundary*/
51 Query    EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) AND ((`emp_no` <= '74947')) /*explain checksum chunk*/
51 Query    REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'employees', 'employees_ptchksum', '2', 'PRIMARY', '11001', '74947', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) AND ((`emp_no` <= '74947')) /*checksum chunk*/
51 Query    SHOW WARNINGS
51 Query    SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '2'
51 Query    UPDATE `percona`.`checksums` SET chunk_time = '0.148209', master_crc = '48c0faee', master_cnt = '63947' WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '2'

……省略……

51 Query    SHOW GLOBAL STATUS LIKE 'Threads_running'
51 Query    EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '474878')) ORDER BY `emp_no` LIMIT 291764, 2 /*next chunk boundary*/
51 Query    SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '474878')) ORDER BY `emp_no` LIMIT 291764, 2 /*next chunk boundary*/
51 Query    SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) ORDER BY `emp_no` DESC LIMIT 1 /*last upper boundary*/
51 Query    EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '474878')) AND ((`emp_no` <= '499999')) /*explain checksum chunk*/
51 Query    REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'employees', 'employees_ptchksum', '4', 'PRIMARY', '474878', '499999', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '474878')) AND ((`emp_no` <= '499999')) /*checksum chunk*/
51 Query    SHOW WARNINGS
51 Query    SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '4'
51 Query    UPDATE `percona`.`checksums` SET chunk_time = '0.036433', master_crc = '6ca4b1c9', master_cnt = '25122' WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '4'

-- 上述校验到chunk = 4其实已经包含整张表的数据，以下的chunk范围为emp_no < 10001和emp_no > 4999999，这样做的意图很明显，因为工具并不能确认从库在这两个chunk所对应的数据范围内没有数据存在，为了保证检查数据的完整性，所以进行这样的检查操作，包括了所有的可能性。

'对应emp_no < 10001'
51 Query    SHOW GLOBAL STATUS LIKE 'Threads_running'
51 Query    EXPLAIN SELECT  COUNT(*), '0' FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` < '10001')) ORDER BY `emp_no` /*explain past lower chunk*/
51 Query    REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'employees', 'employees_ptchksum', '5', 'PRIMARY', NULL, '10001', COUNT(*), '0' FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` < '10001')) ORDER BY `emp_no` /*past lower chunk*/
51 Query    SHOW WARNINGS
51 Query    SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '5'
51 Query    UPDATE `percona`.`checksums` SET chunk_time = '0.062096', master_crc = '0', master_cnt = '0' WHERE db = 'employees' AND


  
  
   
    共3页: 
上一页
1
2
3
下一页


 
 
  
 
 
  

  
 
 
 
栏目列表
  
      
      SQL教程
      
      Access数据库
      
      MongoDB
      
      PostgreSQL教程
      
      T-SQL
      
      sql语句
      
      sql数据库
      
      Oracle教程
      
      MySQL
      
      
      
      
    
    
    

    
    
    


 




 





 



 






 




 
  
 
         
    最新更新
        

    
                      
              nodejs爬虫
     
Python正则表达式完全指南
     
爬取豆瓣Top250图书数据
     
shp 地图文件批量添加字段
     
爬虫小试牛刀（爬取学校通知公告）
     
【python基础】函数-初识函数
     
【python基础】函数-返回值
     
HTTP请求：requests模块基础使用必知必会
     
Python初学者友好丨详解参数传递类型
     
如何有效管理爬虫流量？
     



    
                  SQL SERVER中递归
     
2个场景实例讲解GaussDB(DWS)基表统计信息估
     
常用的 SQL Server 关键字及其含义
     
动手分析SQL Server中的事务中使用的锁
     
openGauss内核分析：SQL by pass & 经典执行
     
一招教你如何高效批量导入与更新数据
     
天天写SQL，这些神奇的特性你知道吗？
     
openGauss内核分析：执行计划生成
     
[IM002]Navicat ODBC驱动器管理器 未发现数据
     
初入Sql Server 之 存储过程的简单使用
     



              这是目前我见过最好的跨域解决方案！
     
减少回流与重绘
     
减少回流与重绘
     
如何使用KrpanoToolJS在浏览器切图
     
performance.now() 与 Date.now() 对比
     
一款纯 JS 实现的轻量化图片编辑器
     
关于开发 VS Code 插件遇到的 workbench.scm.
     
前端设计模式——观察者模式
     
前端设计模式——中介者模式
     
创建型-原型模式






 




相关教程





python基础教程
软件开发
PHP教程
sql教程
vb.net教程
sql语句大全
asp.net教程
dreamweaver教程
javascript教程
flash教程
vb教程
计算机二级vfp
access教程
html5教程
C#教程
web前端 
python入门教程 





关于我们--广告服务--免责声明--本站帮助-友情链接--版权声明--联系我们

  
  
黑ICP备07002182号