工具下载：【链接: https://pan.baidu.com/s/1r96CrIZ9VSW9jMoDaXStJQ 密码: qfml】。
安装
安装 pt 工具：
$ yum localinstall percona-toolkit-3.1.0-2.el7.x86_64.rpm -y
pt 工具是用来对数据库进行操作的，参考「二进制包形式安装MySQL5.7」安装数据库并启动。
使用
系统信息总览
pt-summary 用来收集当前操作系统相关信息：
$ pt-summary
# Percona Toolkit System Summary Report ######################
Date | 2020-05-21 00:59:27 UTC (local TZ: CST +0800)
Hostname | centos7-200
Uptime | 19 min, 2 users, load average: 0.01, 0.03, 0.05
System | VMware, Inc.; VMware Virtual Platform; vNone (Other)
Service Tag | VMware-56 4d f8 8c 43 72 d7 87-a4 2d 43 e0 64 ec 13 9d
Platform | Linux
Release | CentOS Linux release 7.5.1804 (Core)
Kernel | 3.10.0-862.el7.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
Threading | NPTL 2.17
SELinux | Disabled
Virtualized | VMWare
...
表归档
常用选项：
# 重要参数
--limit 100 每次取100行数据用pt-archive处理
--txn-size 100 设置100行为一个事务提交一次，
--where 'id<3000' 设置操作条件
--progress 5000 每处理5000行输出一次处理信息
--statistics 输出执行过程及最后的操作统计。
--charset=UTF8 指定字符集为UTF8—这个最后加上不然可能出现乱码。
--bulk-delete 批量删除source上的旧数据(例如每次1000行的批量删除操作)
--for-update： 在每个select语句后面加入for update
注意，在下面示例执行归档操作时，
autocommit参数值必须为1，且归档的目标表结构必须事先创建好。
例 1：本地归档，将 10.0.1.200 的 world 库的 city 表中的 id<1000 的行归档到 city2 表。
$ pt-archiver --source h=10.0.1.200,D=world,t=city,u=root,p=123 --dest h=10.0.1.200,D=world,t=city2,u=root,p=123 --where 'id<1000' --no-check-charset --no-delete --limit=100 --commit-each --progress 200 --statistics
例 2：异地归档，将 10.0.1.200 的 world 库的 city 表中的 id<1000 的行归档到 10.0.1.201 的 world 库的 city 表。
$ pt-archiver --source h=10.0.1.200,D=world,t=city,u=root,p=123 --dest h=10.0.1.201,D=world,t=city2,u=root,p=123 --where 'id<1000' --no-check-charset --no-delete --limit=100 --commit-each --progress 200 --statistics
例 3：清理表数据，将 10.0.1.200 的 world 库的 city 表中的 id<1000 的行删除。
$ pt-archiver --source h=10.0.1.200,D=world,t=city,u=root,p=123 --where 'id<1000' --purge --limit=1 --no-check-charset
例 4：导出数据，将 10.0.1.200 的 world 库的 city 表中的 id>3000 的行导出到 csv 文件中。
$ pt-archiver --source h=10.0.1.200,D=world,t=city,u=root,p=123 --where 'id>3000' --no-check-charset --no-delete --file="/tmp/archiver.csv"
Online DDL
Online DDL 可使用 pt-osc 工具实现。
原理：
- 检查更改表是否有主键或唯一索引，是否有触发器；
- 检查修改表的表结构，创建一个临时表，在新表上执行
ALTER TABLE语句； - 在源表上创建三个触发器分别对于
INSERT、UPDATE、DELETE操作； - 从源表拷贝数据到临时表，在拷贝过程中，对源表的更新操作会写入到新建表中；
- 将临时表和源表
rename（需要元数据修改锁，需要短时间锁表）； - 删除源表和触发器，完成表结构的修改；
限制：
- 源表必须有主键或唯一索引，如果没有工具将停止工作；
- 如果线上的复制环境过滤器操作过于复杂，工具将无法工作；
- 如果开启复制延迟检查，但主从延迟时，工具将暂停数据拷贝工作；
- 如果开启主服务器负载检查，但主服务器负载较高时，工具将暂停操作；
- 当表使用外键时，如果未使用
--alter-foreign-keys-method参数，工具将无法执行； - 只支持 Innodb 存储引擎表，且要求服务器上有该表 1 倍以上的空闲空间；
alter语句限制：
- 不需要包含
alter table关键字，可以包含多个修改操作，使用逗号分开，如drop clolumn c1, add column c2 int； - 不支持
rename语句来对表进行重命名操作； - 不支持对索引进行重命名操作；
- 如果删除外键，需要对外键名加下划线，如删除外键
fk_uid, 修改语句为DROP FOREIGN KEY _fk_uid；
pt-osc 之命令模板；
## --execute 表示执行
## --dry-run 表示只进行模拟测试
## 表名只能使用参数 t 来设置，没有长参数
$ pt-online-schema-change \
--host="127.0.0.1" \
--port=3306 \
--user="root" \
--password="123" \
--charset="utf8" \
--max-lag=10 \
--check-salve-lag='xxx.xxx.xxx.xxx' \
--recursion-method="hosts" \
--check-interval=2 \
--database="testdb1" \
t="tb001" \
--alter="add column c4 int" \
--execute
例 1：给 10.0.1.200 的 world 库中的 city 表添加一个 test 列。
$ pt-online-schema-change --user=root --password=123 --host=10.0.1.200 --alter "add column test int default 0" D=world,t=city --print --execute
主从一致性校验
环境准备
要保证主库和从库的
autocommit=1。
1、在主库执行如下操作：
mysql> create database pt CHARACTER SET utf8;
mysql> GRANT ALL ON *.* TO 'checksum'@'10.0.1.%' IDENTIFIED BY 'checksum';
mysql> flush privileges;
2、在从库的 my.cnf 中添加如下配置：
# 从库 IP 地址
report_host=10.0.1.201
# 从库端口
report_port=3306
3、在主库中检查是否可以看到从库的 host 信息：
mysql> show slave hosts;
+-----------+------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------------+------+-----------+--------------------------------------+
| 2 | 10.0.1.201 | 3306 | 1 | ca83bdb3-941e-11ea-9974-000c29496b80 |
+-----------+------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
校验
校验工具为 pt-table-checksum，其常用选项如下：
--[no]check-replication-filters：是否检查复制的过滤器，默认是yes，建议启用不检查模式；
--databases | -d：指定需要被检查的数据库，多个库之间可以用逗号分隔；
--[no]check-binlog-format：是否检查 binlog 文件的格式，默认值 yes。建议开启不检查。因为在默认的 row 格式下会出错；
--replicate：把 checksum 的信息写入到指定表中；
--replicate-check-only：只显示不同步信息；
例 1：在主库所在主机执行下面命令针对 test 库的 t1 表校验：
$ pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test --tables=t1 h=10.0.1.200,u=checksum,p=checksum,P=3306
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
05-21T20:22:06 0 0 2 0 1 0 0.023 test.t1
例 2：在主库所在主机执行下面命令针对 test 库所有表校验：
$ pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test h=10.0.1.200,u=checksum,p=checksum,P=3306
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
05-21T20:24:14 0 0 2 0 1 0 0.019 test.t1
05-21T20:24:14 0 0 0 0 1 0 0.015 test.t2
脚本模板
#!/bin/bash
date >> /root/db/checksum.log
pt-table-checksum --nocheck-binlog-format --nocheck-plan
--nocheck-replication-filters --replicate=pt.checksums --set-vars
innodb_lock_wait_timeout=120 --databases test -u'checksum' -p'checksum'
-h'10.0.1.201' >> /root/db/checksum.log
date >> /root/db/checksum.log
差异同步
差异同步其实就是以主库为中心同步从库，可使用 pt-table-sync 工具，其常用参数如下：
--replicate ：指定通过pt-table-checksum得到的表.
--databases : 指定执行同步的数据库。
--tables ：指定执行同步的表，多个用逗号隔开。
--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
h= ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。
u= ：帐号。
p= ：密码。
--print ：打印，但不执行命令。
--execute ：执行命令。
例 1：查看同步操作要执行的 SQL 语句：
$ pt-table-sync --replicate=pt.checksums h=10.0.1.200,u=checksum,p=checksum,P=3306 --databases=test --print
DELETE FROM `test`.`t2` WHERE `id`='1' LIMIT 1 /*percona-toolkit src_db:test src_tbl:t2 src_dsn:P=3306,h=10.0.1.200,p=...,u=checksum dst_db:test dst_tbl:t2 dst_dsn:P=3306,h=10.0.1.201,p=...,u=checksum lock:1 transaction:1 changing_src:pt.checksums replicate:pt.checksums bidirectional:0 pid:17118 user:root host:centos7-200*/;
例 2：执行同步操作：
$ pt-table-sync --replicate=pt.checksums h=10.0.1.200,u=checksum,p=checksum,P=3306 --execute
查看主从结构
$ pt-slave-find -h10.0.1.200 -P3306 -uroot -p123
10.0.1.200
Version 5.7.28-log
Server ID 1
Uptime 30:52 (started 2020-05-21T20:10:30)
Replication Is not a slave, has 1 slaves connected, is not read_only
Filters
Binary logging ROW
Slave status
Slave mode STRICT
Auto-increment increment 1, offset 1
InnoDB version 5.7.28
+- 10.0.1.201
Version 5.7.28
Server ID 2
Uptime 24:52 (started 2020-05-21T20:16:30)
Replication Is a slave, has 0 slaves connected, is not read_only
Filters
Binary logging ROW
Slave status 0 seconds behind, running, no errors
Slave mode STRICT
Auto-increment increment 1, offset 1
InnoDB version 5.7.28
检查表的重复索引
1、查看原来的索引情况
mysql> show index from world.city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city | 0 | PRIMARY | 1 | ID | A | 3080 | NULL | NULL | | BTREE | | |
| city | 1 | CountryCode | 1 | CountryCode | A | 162 | NULL | NULL | | BTREE | | |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
2、添加重复索引：
mysql> alter table world.city add index idx(CountryCode,Population);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
3、检查重复索引：
$ pt-duplicate-key-checker --host=10.0.1.200 --user='root' --password='123' --databases=world --tables=city
# ########################################################################
# world.city
# ########################################################################
# CountryCode is a left-prefix of idx
# Key definitions:
# KEY `CountryCode` (`CountryCode`),
# KEY `idx` (`CountryCode`,`Population`),
# Column types:
# `countrycode` char(3) not null default ''
# `population` int(11) not null default '0'
# To remove this duplicate index, execute:
ALTER TABLE `world`.`city` DROP INDEX `CountryCode`;
# ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes 9240
# Total Duplicate Indexes 1
# Total Indexes 4
4、删除重复索引：
mysql> ALTER TABLE `world`.`city` DROP INDEX `CountryCode`;
5、再次检查：
$ pt-duplicate-key-checker --host=10.0.1.200 --user='root' --password='123' --databases=world --tables=city
# ########################################################################
# Summary of indexes
# ########################################################################
# Total Indexes 3
监控主从延时
1、主库执行：
# 执行完成后会在主库的 test 库中创建一张 heartbeat 表
$ pt-heartbeat --user=root --ask-pass --host=10.0.1.200 --port=3306 --create-table -D test --interval=1 --update --replace --daemonize
2、从库执行：
# 实时通过主库的 heartbeat 表获取延时信息
$ pt-heartbeat --user=root --ask-pass --host=10.0.1.201 --port=3306 -D test --table=heartbeat --monitor
Enter password:
0.01s [ 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 ]
...
用户迁移
# 执行会输出库中所有的用户创建语句和授权语句
$ pt-show-grants -h10.0.1.200 -P3306 -uroot -p123
-- Grants dumped by pt-show-grants
-- Dumped from server 10.0.1.200 via TCP/IP, MySQL 5.7.28-log at 2020-05-21 20:55:34
-- Grants for 'checksum'@'10.0.1.%'
CREATE USER IF NOT EXISTS 'checksum'@'10.0.1.%';
ALTER USER 'checksum'@'10.0.1.%' IDENTIFIED WITH 'mysql_native_password' AS '*E5E390AF1BDF241B51D9C0DBBEA262CC9407A2DF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'checksum'@'10.0.1.%';
-- Grants for 'mysql.session'@'localhost'
CREATE USER IF NOT EXISTS 'mysql.session'@'localhost';
ALTER USER 'mysql.session'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost';
GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
GRANT SUPER ON *.* TO 'mysql.session'@'localhost';
-- Grants for 'mysql.sys'@'localhost'
CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost';
ALTER USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';
GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';
GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';
-- Grants for 'root'@'10.0.1.%'
CREATE USER IF NOT EXISTS 'root'@'10.0.1.%';
ALTER USER 'root'@'10.0.1.%' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.1.%';
-- Grants for 'root'@'localhost'
CREATE USER IF NOT EXISTS 'root'@'localhost';
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
参数建议
# 执行就会输出对当前连接的数据库提供的参数建议
$ pt-variable-advisor 10.0.1.200 -uroot -p123
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.
# WARN key_buffer_size: The key buffer size is set to its default value, which is not good for most production systems.
# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.
# WARN expire_logs_days: Binary logs are enabled, but automatic purging is not enabled.
# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.
# NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance.
# WARN myisam_recover_options: myisam_recover_options should be set to some value such as BACKUP,FORCE to ensure that table corruption is noticed.