软件开发培训班 >> 编程开发 >> MySQL


percona-toolkit对MySQL的复制和监控类操作教程

复制类工具
1.  pt-heartbeat
功能介绍：
监控mysql复制延迟
用法介绍：
pt-heartbeat [OPTION...] [DSN] --update|--monitor|--check|--stop
测量复制落后主mysql或者主PostgreSQL多少时间，你可以使用这个脚本去更新主或者监控复制，具体用法见后面的示例.
原理：pt-heartbeat通过真实的复制数据来确认mysql和postgresql复制延迟，这个避免了对复制机制的依赖，从而能得出准确的落后复制时间，包含两部分：第一部分在主上pt-heartbeat的--update线程会在指定的时间间隔更新一个时间戳，第二部分是pt-heartbeat的--monitor线程或者--check线程连接到从上检查复制的心跳记录（前面更新的时间戳），并和当前系统时间进行比较，得出时间的差异 。
你可以手工创建heartbeat表或者添加--create-table参数，推荐使用MEMORY引擎 。表结构为：

CREATE TABLE heartbeat (
 ts       varchar(26) NOT NULL,
 server_id    int unsigned NOT NULL PRIMARY KEY,
 file       varchar(255) DEFAULT NULL,  -- SHOW MASTER STATUS
 position     bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
 relay_master_log_file varchar(255) DEFAULT NULL,  -- SHOW SLAVE STATUS
 exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);

 
使用示例：
范例1：创建一个后台进程定期更新主上的test库的heartbeat表（）默认是1s，可以--interval指定，执行后会成一个heartbeat表，test库为我监控的同步库：

pt-heartbeat -D test --update --user=root --password=zhang@123 -h192.168.3.135 --create-table –daemonize

范例2：监控复制在slave上的落后程度（会一直监控）：

pt-heartbeat -D test --monitor --user=root --password=zhang@123 -h192.168.3.92

监控结果如下：

0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]

范例3：监控复制在slave上的落后程度（监控一次退出）：

pt-heartbeat -D test --check --user=root --password=zhang@123 -h192.168.3.92

范例4：监控PostgreSQL需要添加--dbi-driver Pg：

pt-heartbeat -D test --check --user=root --password=zhang@123 -h192.168.3.92 --dbi-driver Pg

 
2. pt-slave-delay
功能介绍：
设置从服务器落后于主服务器指定时间 。
用法介绍：
pt-slave-delay [OPTION...] SLAVE-HOST [MASTER-HOST]
原理：通过启动和停止复制sql线程来设置从落后于主指定时间 。默认是基于从上relay日志的二进制日志的位置来判断，因此不需要连接到主服务器，如果IO进程不落后主服务器太多的话，这个检查方式工作很好，如果网络通畅的话，一般IO线程落后主通常都是毫秒级别 。一般是通过--delay and --delay"+"--interval来控制 。--interval是指定检查是否启动或者停止从上sql线程的频繁度，默认的是1分钟检查一次 。
使用示例：
范例1：使从落后主1分钟，并每隔1分钟检测一次，运行10分钟

pt-slave-delay --user=root --password=zhang@123 --delay 1m --run-time 10m --host=192.168.3.92

如果不加--run-time参数会一直执行 。
范例2：使从落后主1分钟，并每隔15秒钟检测一次，运行10分钟

pt-slave-delay --user=root --password=zhang@123 --delay 1m --interval 15s --run-time 10m --host=192.168.3.92

运行结果如下：

2012-05-20T16:34:50 slave running 0 seconds behind
2012-05-20T16:34:50 STOP SLAVE until 2012-05-20T16:35:50 at master position mysql-bin.000032/4392054
2012-05-20T16:35:05 slave stopped at master position mysql-bin.000032/4397124
2012-05-20T16:35:20 slave stopped at master position mysql-bin.000032/4402194
2012-05-20T16:35:35 slave stopped at master position mysql-bin.000032/4407264
2012-05-20T16:35:50 no new binlog events
2012-05-20T16:36:05 START SLAVE until master 2012-05-20T16:35:05 mysql-bin.000032/4397124

 
3. pt-slave-find
功能介绍：
查找和打印mysql所有从服务器复制层级关系
用法介绍：
pt-slave-find [OPTION...] MASTER-HOST
原理:连接mysql主服务器并查找其所有的从，然后打印出所有从服务器的层级关系 。
使用示例：
范例1：查找主服务器为192.168.3.135的mysql有所有从的层级关系：

pt-slave-find --user=root --password=zhang@123 --host=192.168.3.135

4. pt-slave-restart
功能介绍：
监视mysql复制错误，并尝试重启mysql复制当复制停止的时候
用法介绍：
pt-slave-restart [OPTION...] [DSN]
监视一个或者多个mysql复制错误，当从停止的时候尝试重新启动复制 。你可以指定跳过的错误并运行从到指定的日志位置 。
使用示例：
范例1：监视192.168.3.92的从，跳过1个错误

pt-slave-restart --user=root --password=zhang@123 --host=192.168.3.92 --skip-count=1

         范例2：监视192.168.3.92的从，跳过错误代码为1062的错误 。

pt-slave-restart --user=root --password=zhang@123 --host=192.168.3.92 --error-numbers=1062

5. pt-table-checksum
功能介绍：
检查mysql复制一致性
用法介绍：
pt-table-checksum [OPTION...] [DSN]
工作原理：pt-table-checksum在主上执行检查语句在线检查mysql复制的一致性，生成replace语句，然后通过复制传递到从，再通过update更新master_src的值 。通过检测从上this_src和master_src的值从而判断复制是否一致 。
注意：使用的时候选择业务地峰的时候运行，因为运行的时候会造成表的部分记录锁定 。使用--max-load来指定最大的负载情况，如果达到那个负载这个暂停运行 。如果发现有不一致的数据，可以使用pt-table-sync工具来修复 。
注意：和1.0版本不同，新版本的pt-table-checksum只需要在master上执行即可 。
通过 –explain参数再结合二进制日志就可以看出脚本的工作原理，如我的test库有一个名字为zhang的表，我们通过抓取二进制日志来查看脚本的原理：

REPLACE INTO `test`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT test, zhang, 1, NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS(#, `id`, `name`, CONCAT(ISNULL(`name`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`zhang` /*checksum table*/;
UPDATE `test`.`checksums` SET chunk_time = 0.000563, master_crc = 31012777, master_cnt = 4 WHERE db = test AND tbl = zhang AND chunk = 1

从这里可以很明显的看出原理了，前面已经说了，这里就不赘述了 。
使用示例：
范例1：比较test数据库同步是否一致，结果显示所有的表 。

pt-table-checksum --nocheck-replication-filters --databases=test --replicate=test.checksums --create-replicate-table --host=192.168.3.135 --port 3306 -uroot -pzhang@123

参数说明：第一次运行的时候需要添加--create-replicate-table参数，如果不加这个就需要手工运行添加表结构的SQL,表结构SQL如下：

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;

之所以使用--nocheck-replication-filters参数是因为我的my.cnf配置了replicate-ignore-db和replicate-wild-do-table等参数 。另外需要特别注意执行的checksums所在的数据库必须是同步的数据库 。我刚开始使用的时候摸索的很久，官网也没有范例 。呵呵！
结果如下：

TS     ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-23T16:19:29 0  1    2  1 0 0.006 test.aaa
05-23T16:19:29 0  0    1  1 0 0.017 test.bbb
05-23T16:19:29 0  0    0  1 0 0.007 test.category_part
05-23T16:19:31 0  0  233617  6 0 1.887 test.collect_data
05-23T16:19:34 0  0  250346  5 0 2.709 test.effective_user
05-23T16:19:34 0  1    1  1 0 0.008 test.heartbeat
05-23T16:19:39 0  0 1000000 11 0 5.353 test.oss_pvinfo2

从结果中，我们可以看到test.aaa和test.heartbeat表的DIFFS不为0，那么就是这两个表不同步了 。
 
范例2：比较test数据库同步是否一致，结果只显示数据不一致的表（添加--replicate-check-only参数即可,这个值查看checksums数据不会重新对表进行比较，因此需要先运行上面的比较语句之后再运行这个语句，否则显示不了最新的变化 。） 。

pt-table-checksum --nocheck-replication-filters --databases=test --replicate=test.checksums --replicate-check-only --lock-wait-timeout=120 --host=192.168.3.135 --port 3306 --user=root --password=zhang@123

结果如下：

Differences on localhost.localdomain
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
test.aaa 1 1 1 
test.heartbeat 1 0 1

从结果可以看出，只显示了两个不同步的表 。

6. pt-table-sync
功能介绍：
高效同步mysql表的数据
用法介绍：
pt-table-sync [OPTION...] DSN [DSN...]
原理：总是在主上执行数据的更改，再同步到从上，不会直接更改成从的数据，在主上执行更改是基于主上现在的数据，不会更改主上的数据 。注意使用之前先备份你的数据，避免造成数据的丢失.执行execute之前最好先换成--print或--dry-run查看一下会变更哪些数据 。
使用示例：
范例1：同步3.135的test库的aaa表到192.168.3.92，在执行之前可以用--execute参数换成--print来查看会变更什么东西，后面那个主机必须是master，否则会报错推出 。

pt-table-sync --execute --user=root --password=zhang@123 h=192.168.3.135,D=test,t=aaa h=192.168.3.92

范例2：将主的test数据库同步到192.168.3.92，使从上具有一样的数据 。

pt-table-sync --execute --sync-to-master --user=root --password=zhang@123 h=192.168.3.92 --database test

范例3：只同步指定的表

pt-table-sync --execute --sync-to-master --user=root --password=zhang@123 h=192.168.3.92,D=test,t=aaa

范例4：根据pt-table-checksum的结果进行数据同步

pt-table-sync --execute --replicate test.checksums --user=root --password=zhang@123 h=192.168.3.135

范例5：根据pt-table-checksum使从的数据和主的数据一致

pt-table-sync --execute --replicate test.checksums --user=root --password=zhang@123 --sync-to-master h=192.168.3.92,D=test,t=aaa

监控类工具
1. pt-deadlock-logger
功能介绍：
提取和记录mysql死锁的相关信息
用法介绍：
pt-deadlock-logger [OPTION...] SOURCE_DSN
收集和保存mysql上最近的死锁信息，可以直接打印死锁信息和存储死锁信息到数据库中，死锁信息包括发生死锁的服务器、最近发生死锁的时间、死锁线程id、死锁的事务id、发生死锁时事务执行了多长时间等等非常多的信息 。详情见下面的示例 。
使用示例：
范例1：打印本地mysql的死锁信息

pt-deadlock-logger --user=root --password=zhang@123 h=localhost –print

范例2：将本地的mysql死锁信息记录到数据库的表中，也打印出来

pt-deadlock-logger --user=root --password=zhang@123 h=localhost --print D=test,t=deadlocks

2. pt-fk-error-logger
功能介绍：
提取和记录mysql外键错误信息
用法介绍：
pt-fk-error-logger [OPTION...] SOURCE_DSN
通过SHOW INNODB STATUS提取和保存mysql数据库最近发生的外键错误信息 。可以通过参数控制直接打印错误信息或者将错误信息存储到数据库的表中 。
使用示例：
我在服务器上运行的时候一直报如下错误：

Use of uninitialized value in concatenation (.) or string at /usr/bin/pt-fk-error-logger line 2045

我怀疑是这个程序有问题，回头换一个版本试一下或者调试一下那个程序 。
 
3. pt-mext
功能介绍：
并行查看SHOW GLOBAL STATUS的多个样本的信息 。
用法介绍：
pt-mext [OPTIONS] -- COMMAND
原理：pt-mext执行你指定的COMMAND，并每次读取一行结果，把空行分割的内容保存到一个一个的临时文件中，最后结合这些临时文件并行查看结果 。
使用示例：
范例1：每隔10s执行一次SHOW GLOBAL STATUS，并将结果合并到一起查看

pt-mext -- mysqladmin ext -uroot -pzhang@123 -i10 -c3

 
4. pt-query-digest
功能介绍：
分析查询执行日志，并产生一个查询报告，为MySQL、PostgreSQL、 memcached过滤、重放或者转换语句 。
用法介绍：
pt-query-digest [OPTION...] [FILE]
解析和分析mysql日志文件
使用示例：
范例1：分析本地的慢查询文件

pt-query-digest --user=root --password=zhang@123 /data/dbdata/localhost-slow.log

范例2：重新回顾满查询日志，并将结果保存到query_review中，注意query_review表的表结构必须先建好，表结构如下：

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
);

命令如下：

pt-query-digest --user=root --password=zhang@123 --review h=localhost,D=test,t=query_review /data/dbdata/localhost-slow.log

5. pt-trend
功能介绍：
居于一组时间序列的数据点做统计 。
用法介绍：
pt-trend [OPTION...] [FILE ...]
读取一个慢查询日志，并输出统计信息 。也可以指定多个文件 。如果不指定文件的话直接从标准输入中读取信息 。
使用示例：
范例1：读取本地慢查询日志并输出统计信息

pt-trend /data/dbdata/localhost-slow.log