mysql主从同步(3)-percona-toolkit工具（数据一致性监测、延迟监控）使用梳理

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,
   PRIMARY KEY (db, tbl, chunk),
   INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;

Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
01-08T04:04:54      0      0        4       1       0   0.009 huanqiu.haha

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 10 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

mysql> show processlist;
+----+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User  | Host                | db   | Command     | Time | State                                                                 | Info             |
+----+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 10 | root  | localhost           | NULL | Query       |    0 | init                                                                  | show processlist |
| 18 | slave | 192.168.1.102:37115 | NULL | Binlog Dump |    5 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
+----+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
01-08T04:11:03      0      0        4       1       0   1.422 huanqiu.haha

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
01-08T04:18:07      0      1        4       1       0   0.843 huanqiu.haha

REPLACE INTO `huanqiu`.`haha`(`id`, `name`) VALUES ('1', 'wangshibo') /*percona-toolkit src_db:huanqiu src_tbl:haha src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl:haha dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/;
REPLACE INTO `huanqiu`.`haha`(`id`, `name`) VALUES ('2', 'wangshikui') /*percona-toolkit src_db:huanqiu src_tbl:haha src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl:haha dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/;
REPLACE INTO `huanqiu`.`haha`(`id`, `name`) VALUES ('3', 'limeng') /*percona-toolkit src_db:huanqiu src_tbl:haha src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl:haha dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/;
REPLACE INTO `huanqiu`.`haha`(`id`, `name`) VALUES ('4', 'wanghi') /*percona-toolkit src_db:huanqiu src_tbl:haha src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl:haha dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/;

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
01-08T04:36:43      0      0        4       1       0   0.040 huanqiu.haha

#!/bin/bash
NUM=$(/usr/bin/pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=huanqiu.checksums --databases=huanqiu  h=192.168.1.101,u=root,p=123456,P=3306|awk -F" " '{print $3}'|sed -n '2p')
if [ $NUM -eq 1 ];then
  /usr/bin/pt-table-sync --replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --print
  /usr/bin/pt-table-sync --replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --execute
else
  echo "data is ok"
fi

#!/bin/bash
NUM=$(/usr/bin/pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=huanpc.checksums --databases=huanpc  h=192.168.1.101,u=root,p=123456,P=3306|awk -F" " '{print $3}'|sed -n '2p')
if [ $NUM -eq 1 ];then
  /usr/bin/pt-table-sync --replicate=huanpc.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --print
  /usr/bin/pt-table-sync --replicate=huanpc.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --execute
else
  echo "data is ok"
fi

mysql> use huanqiu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_huanqiu |
+-------------------+
| checksums         |
| haha              |
+-------------------+
2 rows in set (0.00 sec)

mysql> drop table checksums;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_huanqiu |
+-------------------+
| haha              |
+-------------------+
1 row in set (0.00 sec)

mysql> show tables;         //过一段时间再次查看，发现checksums表还在
+-------------------+
| Tables_in_huanqiu |
+-------------------+
| checksums         |
| haha              |
+-------------------+
2 rows in set (0.00 sec)

不仅这张表删除不了，这张表所在的库也删除不了，删除后过一会儿就是自动生成。
mysql> drop database huanqiu;
Query OK, 1 row affected (0.01 sec)

mysql> drop database huanpc;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| huanqiu            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database huanqiu;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| huanpc             |
| huanqiu            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> show grants for 'root'@'192.168.1.101';     
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.1.101                                                                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'root'@'192.168.1.101' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> revoke SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, SUPER, REPLICATION SLAVE ON *.* FROM 'root'@'192.168.1.101';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'root'@'192.168.1.101';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.1.101                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'192.168.1.101' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select user,host,password from mysql.user;
+-------+---------------+-------------------------------------------+
| user  | host          | password                                  |
+-------+---------------+-------------------------------------------+
| root  | localhost     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root  | master-server |                                           |
| root  | 127.0.0.1     |                                           |
| root  | ::1           |                                           |
|       | localhost     |                                           |
|       | master-server |                                           |
| root  | 192.168.1.101 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| slave | 192.168.1.102 | *4F0FF134CC4C1A2872D972373A6AA86CA0A81872 |
+-------+---------------+-------------------------------------------+
8 rows in set (0.00 sec)

mysql> delete from mysql.user where user="root" and host="192.168.1.101";   //这一步其实不必操作，此步删除操作不能在上面revoke执行前进行，否则revoke回收权限失败！
Query OK, 1 row affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+-------+---------------+-------------------------------------------+
| user  | host          | password                                  |
+-------+---------------+-------------------------------------------+
| root  | localhost     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root  | master-server |                                           |
| root  | 127.0.0.1     |                                           |
| root  | ::1           |                                           |
|       | localhost     |                                           |
|       | master-server |                                           |
| slave | 192.168.1.102 | *4F0FF134CC4C1A2872D972373A6AA86CA0A81872 |
+-------+---------------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> use huanpc;
Database changed
mysql> show tables;
+------------------+
| Tables_in_huanpc |
+------------------+
| checksums        |
| heihei           |
+------------------+
2 rows in set (0.00 sec)

mysql> drop table checksums;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_huanpc |
+------------------+
| heihei           |
+------------------+
1 row in set (0.01 sec)

mysql> use huanqiu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_huanqiu |
+-------------------+
| checksums         |
| haha              |
+-------------------+
2 rows in set (0.00 sec)

mysql> drop table checksums;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_huanqiu |
+-------------------+
| haha              |
+-------------------+
1 row in set (0.00 sec)

注意：需要指定的参数至少有 --stop，--update，--monitor，--check。
其中--update，--monitor和--check是互斥的，--daemonize和--check也是互斥。
--ask-pass     隐式输入MySQL密码
--charset     字符集设置
--check      检查从的延迟，检查一次就退出，除非指定了--recurse会递归的检查所有的从服务器。
--check-read-only    如果从服务器开启了只读模式，该工具会跳过任何插入。
--create-table    在主上创建心跳监控的表，如果该表不存在，可以自己手动建立，建议存储引擎改成memory。通过更新该表知道主从延迟的差距。
CREATE TABLE heartbeat (
  ts                    varchar(26) NOT NULL,
  server_id             int unsigned NOT NULL PRIMARY KEY,
  file                  varchar(255) DEFAULT NULL,
  position              bigint unsigned DEFAULT NULL,
  relay_master_log_file varchar(255) DEFAULT NULL,
  exec_master_log_pos   bigint unsigned DEFAULT NULL
);
heratbeat   表一直在更改ts和position,而ts是我们检查复制延迟的关键。
--daemonize   执行时，放入到后台执行
--user=-u，   连接数据库的帐号
--database=-D，    连接数据库的名称
--host=-h，     连接的数据库地址
--password=-p，     连接数据库的密码
--port=-P，     连接数据库的端口
--socket=-S，    连接数据库的套接字文件
--file 【--file=output.txt】   打印--monitor最新的记录到指定的文件，很好的防止满屏幕都是数据的烦恼。
--frames 【--frames=1m,2m,3m】  在--monitor里输出的[]里的记录段，默认是1m,5m,15m。可以指定1个，如：--frames=1s，多个用逗号隔开。可用单位有秒（s）、分钟（m）、小时（h）、天（d）。
--interval   检查、更新的间隔时间。默认是见是1s。最小的单位是0.01s，最大精度为小数点后两位，因此0.015将调整至0.02。
--log    开启daemonized模式的所有日志将会被打印到制定的文件中。
--monitor    持续监控从的延迟情况。通过--interval指定的间隔时间，打印出从的延迟信息，通过--file则可以把这些信息打印到指定的文件。
--master-server-id    指定主的server_id，若没有指定则该工具会连到主上查找其server_id。
--print-master-server-id    在--monitor和--check 模式下，指定该参数则打印出主的server_id。
--recurse    多级复制的检查深度。模式M-S-S...不是最后的一个从都需要开启log_slave_updates，这样才能检查到。
--recursion-method     指定复制检查的方式,默认为processlist,hosts。
--update    更新主上的心跳表。
--replace     使用--replace代替--update模式更新心跳表里的时间字段，这样的好处是不用管表里是否有行。
--stop    停止运行该工具（--daemonize），在/tmp/目录下创建一个“pt-heartbeat-sentinel” 文件。后面想重新开启则需要把该临时文件删除，才能开启（--daemonize）。
--table   指定心跳表名，默认heartbeat。

mysql> use huanqiu;                    
Database changed

mysql> CREATE TABLE heartbeat (            //主库上的对应库下创建heartbeat表，一般创建后从库会同步这张表（不同步的话，就在从库那边手动也手动创建）
    ->   ts                    varchar(26) NOT NULL,
    ->   server_id             int unsigned NOT NULL PRIMARY KEY,
    ->   file                  varchar(255) DEFAULT NULL,
    ->   position              bigint unsigned DEFAULT NULL,
    ->   relay_master_log_file varchar(255) DEFAULT NULL,
    ->   exec_master_log_pos   bigint unsigned DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

#!/bin/bash
cat /opt/master-slave.txt > /opt/master_slave.txt
echo > /opt/master-slave.txt
max_time=`cat /opt/master_slave.txt |grep -v '^$' |awk '{print $1}' |sort -k1nr |head -1`
NUM=$(echo "$max_time"|cut -d"s" -f1)
if [ $NUM == "0.00" ];then
   echo "Mysql主从数据一致"
else
   /usr/local/bin/sendEmail -f ops@huanqiu.cn -t wangshibo@huanqiu.cn -s smtp.huanqiu.cn -u "Mysql主从同步延迟" -o message-content-type=html -o message-charset=utf8 -xu ops@huanqiu.cn -xp WEE78@12l$ -m "Mysql主从数据同步有延迟"
fi

[root@master-server ~]# crontab -e
#mysql主从同步延迟检查
* * * * * /bin/bash -x /root/check-slave-monit.sh > /dev/null 2>&1

mysql> show create table huanqiu.haha;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| haha  | CREATE TABLE `haha` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=91 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@master-server ~]# pt-online-schema-change --user=root --password=123456 --host=localhost --alter="ENGINE=InnoDB" D=huanqiu,t=haha --execute --check-replication-filters
Found 1 slaves:
  slave-server
Will check slave lag on:
  slave-server
Replication filters are set on these hosts:
  slave-server
    slave_skip_errors = ALL
    replicate_ignore_db = mysql
    replicate_do_db = huanqiu,huanpc
Please read the --check-replication-filters documentation to learn how to solve this problem. at /usr/bin/pt-online-schema-change line 8083.

[root@master-server ~]# pt-online-schema-change --user=root --password=123456 --host=localhost --alter="ENGINE=InnoDB" D=huanqiu,t=haha --execute --nocheck-replication-filters        
Found 1 slaves:
  slave-server
.......
2017-01-16T10:36:33 Dropped old table `huanqiu`.`_haha_old` OK.
2017-01-16T10:36:33 Dropping triggers...
2017-01-16T10:36:33 Dropped triggers OK.
Successfully altered `huanqiu`.`haha`.

mysql> show create table huanqiu.haha;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| haha  | CREATE TABLE `haha` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@slave-server ~]# pt-online-schema-change --user=root --password=123456 --host=192.168.1.102 --alter="ENGINE=InnoDB" D=huanqiu,t=haha --execute
No slaves found.  See --recursion-method if host slave-server has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
.......
2017-01-15T21:40:35 Swapped original and new tables OK.
2017-01-15T21:40:35 Dropping old table...
2017-01-15T21:40:35 Dropped old table `huanqiu`.`_haha_old` OK.
2017-01-15T21:40:35 Dropping triggers...
2017-01-15T21:40:35 Dropped triggers OK.
Successfully altered `huanqiu`.`haha`.

[root@master-server ~]# pt-duplicate-key-checker  --host=localhost --user=root --password=123456  --databases=huanqiu 
# ########################################################################
# Summary of indexes                                                      
# ########################################################################

# Total Indexes  6

# [root@master-server ~]# pt-duplicate-key-checker  --host=localhost --user=root --password=123456  --databases=huanqiu --table=haha
# ########################################################################
# Summary of indexes                                                      
# ########################################################################

# Total Indexes  1

[root@master-server ~]# pt-slave-find --user=root --password=123456 --host=192.168.1.101
192.168.1.101
Version         5.6.33-log
Server ID       101
Uptime          5+02:59:42 (started 2017-01-11T10:44:14)
Replication     Is not a slave, has 1 slaves connected, is not read_only
Filters         
Binary logging  MIXED
Slave status    
Slave mode      STRICT
Auto-increment  increment 1, offset 1
InnoDB version  5.6.33
+- 192.168.1.102
   Version         5.6.34-log
   Server ID       102
   Uptime          4+22:22:18 (started 2017-01-11T15:21:38)
   Replication     Is a slave, has 0 slaves connected, is not read_only
   Filters         slave_skip_errors=ALL; replicate_ignore_db=mysql; replicate_do_db=huanqiu,huanpc
   Binary logging  MIXED
   Slave status    265831 seconds behind, running, no errors
   Slave mode      STRICT
   Auto-increment  increment 1, offset 1
   InnoDB version  5.6.34

[root@master-server ~]# pt-show-grants --host='localhost' --user='root' --password='123456'
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11:22:12
-- Grants for ''@'localhost'
GRANT USAGE ON *.* TO ''@'localhost';
-- Grants for 'data_check'@'%'
.......

[root@master-server ~]# pt-show-grants --host='localhost' --user='root' --password='123456' --database='huanqiu'
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11:23:16
-- Grants for ''@'localhost'
GRANT USAGE ON *.* TO ''@'localhost';
-- Grants for 'data_check'@'%'
GRANT SELECT ON *.* TO 'data_check'@'%' IDENTIFIED BY PASSWORD '*36B94ABF70E8D5E025CF9C059E66445CBB05B54F';
-- Grants for 'mksync'@'%'
GRANT ALL PRIVILEGES ON *.* TO 'mksync'@'%' IDENTIFIED BY PASSWORD '*B5E7409B1A22D47C6F1D8A693C6146CEB6570475';
........

[root@master-server ~]# pt-show-grants --host='localhost' --user='root' --password='123456'  --revoke
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11:24:58
-- Revoke statements for ''@'localhost'
REVOKE USAGE ON *.* FROM ''@'localhost';
-- Grants for ''@'localhost'
..........

[root@master-server ~]# pt-upgrade h=192.168.1.101 h=192.168.1.102 --user=root --password=123456 /root/test.sql 

#-----------------------------------------------------------------------
# Logs
#-----------------------------------------------------------------------
.......
host1:
  DSN:       h=192.168.1.101
  hostname:  master-server
  MySQL:     Source distribution 5.6.33

host2:
  DSN:       h=192.168.1.102
  hostname:  slave-server
  MySQL:     Source distribution 5.6.34
......
queries_read          1
queries_with_diffs    0
queries_with_errors   0

[root@master-server ~]# pt-upgrade h=192.168.1.101 h=192.168.1.102 --user=root --password=123456 /data/mysql/data/mysql-slow.log
.......

[root@master-server ~]# pt-index-usage --host=localhost --user=root --password=123456 /data/mysql/data/mysql-slow.log

[root@master-server ~]# pt-index-usage --host=localhost --user=root --password=123456 /data/mysql/data/mysql-slow.log  --no-report --create-save-results-database

mysql> select * from huanqiu.haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | wangshihuan |
|  3 | 王世博      |
| 10 | wangshiman  |
+----+-------------+
4 rows in set (0.00 sec)

[root@master-server ~]# mysql -uroot -p123456  -e "explain select * from huanqiu.haha" |pt-visual-explain
Warning: Using a password on the command line interface can be insecure.
Table scan
rows           4
+- Table
   table          haha
   
[root@master-server ~]# mysql -uroot -p123456  -e "explain select * from huanqiu.haha where id=3" |pt-visual-explain
Warning: Using a password on the command line interface can be insecure.
Bookmark lookup
+- Table
|  table          haha
|  possible_keys  PRIMARY
+- Constant index lookup
   key            haha->PRIMARY
   possible_keys  PRIMARY
   key_len        4
   ref            const
   rows           1

[root@master-server ~]# pt-visual-explain --connect /root/test.sql --user=root --password=123456

[root@master-server ~]# pt-config-diff h=localhost h=192.168.1.102 --user=root --password=123456
18 config differences
Variable                  master-server             slave-server
========================= ========================= =========================
binlog_checksum           NONE                      CRC32
general_log_file          /data/mysql/data/maste... /data/mysql/data/slave...
hostname                  master-server             slave-server
innodb_version            5.6.33                    5.6.34
log_bin_index             /data/mysql/data/maste... /data/mysql/data/slave...
log_slave_updates         OFF                       ON
relay_log_recovery        OFF                       ON
secure_file_priv                                    NULL
server_id                 101                       102
server_uuid               d8497104-d7a7-11e6-911... d8773e51-d7a7-11e6-911...
slave_net_timeout         3600                      5
slave_skip_errors         OFF                       ALL
sync_binlog               1                         0
sync_master_info          10000                     1
sync_relay_log            10000                     1
sync_relay_log_info       10000                     1
system_time_zone          CST                       EST
version                   5.6.33-log                5.6.34-log

[root@master-server ~]# pt-config-diff /usr/local/mysql/my.cnf h=localhost h=192.168.1.102 --user=root --password=123456
6 config differences
Variable                  /usr/local/mysql/my.cnf master-server      slave-ser
========================= ================= ================== ===============
binlog_checksum           none              NONE               CRC32
innodb_read_io_threads    1000              64                 64
innodb_write_io_threads   1000              64                 64
log_bin_index             master-bin.index  /data/mysql/dat... /data/mysql/...
server_id                 101               101                102
sync_binlog               1                 1                  0

[root@master-server ~]# pt-mysql-summary -- --user=root --password=123456 --host=localhost

[root@master-server ~]# pt-deadlock-logger  --user=root --password=123456 h=localhost D=test,t=deadlocks
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2017-01-11T11:00:33 188 0 0 root  192.168.1.101 huanpc checksums PRIMARY RECORD X w 1 REPLACE INTO `huanpc`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'huanpc', 'heihei', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `member`, `city`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `huanpc`.`heihei` /*checksum table*/
localhost 2017-01-11T11:00:33 198 0 0 root  192.168.1.101 huanpc checksums PRIMARY RECORD X w 0 REPLACE INTO `huanpc`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'huanpc', 'heihei', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `member`, `city`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `huanpc`.`heihei` /*checksum table*/

[root@master-server ~]# pt-mext  -- mysqladmin ext -uroot -p123456  -i10 -c3

[root@master-server ~]# pt-query-digest --user=root --password=123456 /data/mysql/data/mysql-slow.log

# 260ms user time, 30ms system time, 24.85M rss, 204.71M vsz
# Current date: Mon Jan 16 13:20:39 2017
# Hostname: master-server
# Files: /data/mysql/data/mysql-slow.log
# Overall: 18 total, 2 unique, 0 QPS, 0x concurrency _____________________
# Time range: all events occurred at 2017-01-11 11:00:33
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           812s      2s     92s     45s     80s     27s     52s
# Lock time              0       0       0       0       0       0       0
# Rows sent              0       0       0       0       0       0       0
# Rows examine           0       0       0       0       0       0       0
# Query size         1.10k      62      63   62.56   62.76    0.50   62.76

# Profile
# Rank Query ID           Response time  Calls R/Call  V/M   Item
# ==== ================== ============== ===== ======= ===== =============
#    1 0x50B84661D4CCF34B 467.9075 57.6%    10 46.7907 16.48 CREATE DATABASE `huanqiu`
#    2 0x9CC34439A4FB17E3 344.2984 42.4%     8 43.0373 16.22 CREATE DATABASE `huanpc`

# Query 1: 0 QPS, 0x concurrency, ID 0x50B84661D4CCF34B at byte 2642 _____
# This item is included in the report because it matches --limit.
# Scores: V/M = 16.48
# Time range: all events occurred at 2017-01-11 11:00:33
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         55      10
# Exec time     57    468s      2s     92s     47s     80s     28s     52s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    55     630      63      63      63      63       0      63
# String:
# Hosts
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  #######
#  10s+  ################################################################
CREATE DATABASE IF NOT EXISTS `huanqiu` /* pt-table-checksum */\G

# Query 2: 0 QPS, 0x concurrency, ID 0x9CC34439A4FB17E3 at byte 3083 _____
# This item is included in the report because it matches --limit.
# Scores: V/M = 16.22
# Time range: all events occurred at 2017-01-11 11:00:33
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         44       8
# Exec time     42    344s      2s     82s     43s     80s     26s     56s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    44     496      62      62      62      62       0      62
# String:
# Hosts
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  #########
#  10s+  ################################################################
CREATE DATABASE IF NOT EXISTS `huanpc` /* pt-table-checksum */\G

整个输出分为三大部分：
1）整体概要（Overall）
这个部分是一个大致的概要信息(类似loadrunner给出的概要信息)，通过它可以对当前MySQL的查询性能做一个初步的评估，比如各个指标的最大值(max)，平均值(min)，95%分布值，中位数(median)，标准偏差(stddev)。
这些指标有查询的执行时间（Exec time），锁占用的时间（Lock time），MySQL执行器需要检查的行数（Rows examine），最后返回给客户端的行数（Rows sent），查询的大小。

2）查询的汇总信息（Profile）
这个部分对所有“重要”的查询(通常是比较慢的查询)做了个一览表。
每个查询都有一个Query ID，这个ID通过Hash计算出来的。pt-query-digest是根据这个所谓的Fingerprint来group by的。
Rank整个分析中该“语句”的排名，一般也就是性能最常的。
Response time  “语句”的响应时间以及整体占比情况。
Calls 该“语句”的执行次数。
R/Call 每次执行的平均响应时间。
V/M 响应时间的差异平均对比率。
在尾部有一行输出，显示了其他2个占比较低而不值得单独显示的查询的统计数据。

3）详细信息
这个部分会列出Profile表中每个查询的详细信息：
包括Overall中有的信息、查询响应时间的分布情况以及该查询”入榜”的理由。
pt-query-digest还有很多复杂的操作，这里就不一一介绍了。比如：从PROCESSLIST中查询某个MySQL中最慢的查询：

mysql> use test;
Database changed

mysql> CREATE TABLE query_review (
    ->    checksum     BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    ->    fingerprint  TEXT NOT NULL,
    ->    sample       TEXT NOT NULL,
    ->    first_seen   DATETIME,
    ->    last_seen    DATETIME,
    ->    reviewed_by  VARCHAR(20),
    ->    reviewed_on  DATETIME,
    ->    comments     TEXT
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> select * from query_review;
Empty set (0.00 sec)
 
[root@master-server ~]# pt-query-digest --user=root --password=123456 --review h=localhost,D=test,t=query_review /data/mysql/data/mysql-slow.log

mysql> select * from query_review;
+----------------------+------------------------------------------+-----------------------------------------------------------------+----------
| checksum | fingerprint | sample | first_see
+----------------------+------------------------------------------+-----------------------------------------------------------------+----------
| 5816476304744969035 | create database if not exists `huanqiu` | CREATE DATABASE IF NOT EXISTS `huanqiu` /* pt-table-checksum */ | 2017-01-1
| 11295947304747079651 | create database if not exists `huanpc` | CREATE DATABASE IF NOT EXISTS `huanpc` /* pt-table-checksum */ | 2017-01-1
+----------------------+------------------------------------------+-----------------------------------------------------------------+----------
2 rows in set (0.00 sec)

[root@master-server ~]# tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes

然后打开另一个终端窗口：
[root@master-server ~]# pt-query-digest --type tcpdump mysql.tcp.txt
Pipeline process 3 (TcpdumpParser) caused an error: substr outside of string at /usr/bin/pt-query-digest line 3628, <> chunk 93.
Will retry pipeline process 2 (TcpdumpParser) 100 more times.

# 320ms user time, 20ms system time, 24.93M rss, 204.84M vsz
# Current date: Mon Jan 16 13:24:50 2017
# Hostname: master-server
# Files: mysql.tcp.txt
# Overall: 31 total, 4 unique, 4.43 QPS, 0.00x concurrency _______________
# Time range: 2017-01-16 13:24:43.000380 to 13:24:50.001205
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           30ms    79us     5ms   967us     4ms     1ms   159us
# Rows affecte          14       0       2    0.45    1.96    0.82       0
# Query size         1.85k      17     200   61.16  192.76   72.25   17.65
.........

[root@master-server ~]# pt-slave-delay --user=root --password=123456 --delay 1m --run-time 10m --host=192.168.1.102
2017-01-16T13:32:31 slave running 0 seconds behind
2017-01-16T13:32:31 STOP SLAVE until 2017-01-16T13:33:31 at master position mysql-bin.000005/102554361

[root@master-server ~]# pt-slave-delay --user=root --password=123456 --delay 1m --interval 15s --run-time 10m --host=192.168.1.102
2017-01-16T13:38:22 slave running 0 seconds behind
2017-01-16T13:38:22 STOP SLAVE until 2017-01-16T13:39:22 at master position mysql-bin.000005/102689359

[root@master-server ~]# pt-slave-restart --user=root --password=123456 --host=192.168.1.101 --skip-count=1

[root@master-server ~]# pt-slave-restart --user=root --password=123456 --host=192.168.1.101 --error-numbers=1062

[root@master-server ~]# pt-diskstats

[root@master-server ~]# pt-diskstats  --devices-regex vdc1
  #ts device    rd_s rd_avkb rd_mb_s rd_mrg rd_cnc   rd_rt    wr_s wr_avkb wr_mb_s wr_mrg wr_cnc   wr_rt busy in_prg    io_s  qtime stime
  0.9 vdc1       0.0     0.0     0.0     0%    0.0     0.0     5.9     4.0     0.0     0%    0.0     1.0   0%      0     5.9    0.6   0.4
  1.0 vdc1       0.0     0.0     0.0     0%    0.0     0.0     2.0     6.0     0.0    33%    0.0     0.7   0%      0     2.0    0.0   0.7

[root@master-server ~]# pt-summary

[root@master-server ~]# pt-stalk  --function status --variable Threads_running --threshold 20 --dest /tmp/test  -- -uroot -p123456  -h192.168.1.101

[root@master-server ~]# pt-stalk  --function processlist --variable State --match statistics --threshold 20 --dest /tmp/test -- -uroot -p123456  -h192.168.1.101
.......
2017_01_15_17_31_49-hostname
2017_01_15_17_31_49-innodbstatus1
2017_01_15_17_31_49-innodbstatus2
2017_01_15_17_31_49-interrupts
2017_01_15_17_31_49-log_error
2017_01_15_17_31_49-lsof
2017_01_15_17_31_49-meminfo

源数据库机器192.168.1.101的huanqiu库下的haha表在转移前的信息：
mysql> select * from huanqiu.haha;                                                                                                             
+----+---------------+
| id | name          |
+----+---------------+
|  1 | changbo       |
|  2 | wangpengde    |
|  4 | guocongcong   |
|  5 | kevin         |
|  8 | mamin         |
|  9 | shihonge      |
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
8 rows in set (0.01 sec)

目标数据库机器192.168.1.102的wangshibo库下的wang_test表在转移前的信息:
mysql> select * from wangshibo.wang_test;
+------+-----------+
| id   | name      |
+------+-----------+
|   20 | guominmin |
|   21 | gaofei    |
|   22 | 李梦楠    |
+------+-----------+
3 rows in set (0.00 sec)

接着在192.168.1.101机器上执行转移命令：
[root@master-server ~]# pt-archiver --source h=192.168.1.101,D=huanqiu,t=haha --user=root --password=123456 --dest h=192.168.1.102,D=wangshibo,t=wang_test --file '/var/log/haha_archive_20170115.log' --where "id<=10"  --commit-each

上面命令执行成功后，再次观察转移前后信息
发现源数据库机器192.168.1.101的huanqiu.haha表数据在转移后，源数据也删除了！
mysql> select * from huanqiu.haha;
+----+---------------+
| id | name          |
+----+---------------+
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
2 rows in set (0.00 sec)

查看归档日志：
[root@master-server ~]# tail -f /var/log/haha_archive_20170115.log
1   changbo
2   wangpengde
4   guocongcong
5   kevin
8   mamin
9   shihonge

目标数据库192.168.1.102的wangshibo.wang_test表内已经移转到了新数据
mysql> select * from wangshibo.wang_test;
+------+-------------+
| id   | name        |
+------+-------------+
|   20 | guominmin   |
|   21 | gaofei      |
|   22 | 李梦楠       |
|    1 | changbo     |
|    2 | wangpengde  |
|    4 | guocongcong |
|    5 | kevin       |
|    8 | mamin       |
|    9 | shihonge    |
+------+-------------+
9 rows in set (0.00 sec)

mysql> select * from huanqiu.haha;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | changbo       |
|  2 | wangpengde    |
|  4 | guocongcong   |
|  5 | kevin         |
|  8 | mamin         |
|  9 | shihonge      |
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
8 rows in set (0.00 sec)

[root@master-server ~]# pt-archiver --source h=192.168.1.101,D=huanqiu,t=haha --user=root --password=123456 --file 'haha_log_archive_2017.10.10.log' --where "id<=10" --commit-each 

转移后的源数据已被删除
mysql> select * from huanqiu.haha;
+----+---------------+
| id | name          |
+----+---------------+
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
2 rows in set (0.00 sec)

查看归档文件
[root@master-server ~]# cat haha_log_archive_2017.10.10.log
1   changbo
2   wangpengde
4   guocongcong
5   kevin
8   mamin
9   shihonge

mysql> select * from huanqiu.haha;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | changbo       |
|  2 | wangpengde    |
|  4 | guocongcong   |
|  5 | kevin         |
|  8 | mamin         |
|  9 | shihonge      |
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
8 rows in set (0.00 sec)

[root@master-server ~]# pt-archiver --source h=192.168.1.101,D=huanqiu,t=haha --user=root --password=123456 --purge --where 'id<=10' --no-check-charset
再次查看，发现数据已成功删除！
mysql> select * from huanqiu.haha;
+----+---------------+
| id | name          |
+----+---------------+
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
2 rows in set (0.00 sec)

[root@master-server ~]# pt-find --ctime +1  --host=192.168.1.101 --engine InnoDB --user=root --password=123456
`huanpc`.`_heihei_new`
`huanpc`.`checksums`
`huanqiu`.`_haha_new`
`huanqiu`.`checksums`
`huanqiu`.`heartbeat`
`mysql`.`innodb_index_stats`
`mysql`.`innodb_table_stats`
`mysql`.`slave_master_info`
`mysql`.`slave_relay_log_info`
`mysql`.`slave_worker_info`

先查找出192.168.1.101上1天以前更改过的数据库名字匹配%huanqiu%的并且引擎为Myisam的表
[root@master-server ~]# pt-find --ctime +2 --dblike huanqiu --host=192.168.1.101 --engine Myisam --user=root --password=123456
`huanqiu`.`_haha_new`
`huanqiu`.`checksums`
`huanqiu`.`heartbeat`
[root@master-server ~]# pt-find --ctime +2 --dblike huanpc --host=192.168.1.101 --engine Myisam --user=root --password=123456
`huanpc`.`_heihei_new`
`huanpc`.`checksums`

再将查找出的表的引擎改为Innodb
[root@master-server ~]# pt-find --ctime +2 --dblike huanqiu --host=192.168.1.101 --engine Myisam --user=root --password=123456 --exec "ALTER TABLE %D.%N ENGINE=InnoDB"
[root@master-server ~]# pt-find --ctime +2 --dblike huanpc --host=192.168.1.101 --engine Myisam --user=root --password=123456 --exec "ALTER TABLE %D.%N ENGINE=InnoDB"

最后再检查对应数据表的引擎

[root@master-server ~]# pt-find --empty huanqiu huanpc --host=192.168.1.101 --user=root --password=123456  --exec-plus "DROP TABLE %s"

[root@master-server ~]# pt-find --tablesize +100M --host=192.168.1.101 --user=root --password=123456

[root@master-server ~]# pt-kill --busy-time 60 --print --host=192.168.1.101 --user=root --password=123456

[root@master-server ~]# pt-kill --busy-time 60 --kill --host=192.168.3.135 --user=root --password=123456

[root@master-server ~]# mysql -uroot -p123456 -h192.168.1.101 -e "show processlist" > processlist.txt
Warning: Using a password on the command line interface can be insecure.
[root@master-server ~]# pt-kill --test-matching processlist.txt --busy-time 60 --print