下载安装pt工具
下载网址
https://www.percona.com/downloads/percona-toolkit/LATEST/
[root@db01 ~]# yum install -y percona-toolkit-3.1.0-2.el7.x86_64.rpm
1. 系统配置总览
pt-summary
作用: 系统同状态总览。
2. 表归档:pt-archiver
重要参数
--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
归档到数据库(本地)
将51 上world.city的id<1000的数据归档到51上school.city里
1.查询city表的建表语句
[root@db01 /data/mysql]# mysqlfrm --diagnostic /data/mysql/world/city.frm
2在school库下执行建表语句
3执行归档语句
pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123456 --dest h=10.0.0.51,D=school,t=city,u=root,p=123456 --where 'id<1000' --no-check-charset --no-delete --limit=100 --commit-each --progress 200 --statistics
归档到数据库(异地)
将51 上world.city的id<1000的数据归档到32上test.city2里
1.查询city表的建表语句
[root@db01 /data/mysql]# mysqlfrm --diagnostic /data/mysql/world/city.frm
2.在32 test库里执行建表语句
3 执行归档语句
pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123 --dest h=10.0.0.32,D=test,t=city2,u=root,P=3307,p=123 --where 'id<1000' --no-check-charset --no-delete --limit=100 --commit-each --progress 200 --statistics
只清理数据
pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123456 --where 'id<1000' --purge --limit=1 --no-check-charset
只把数据导出到外部文件,但是不删除源表里的数据
pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123 --where 'id>900' --no-check-charset --no-delete --file="/tmp/archiver.dat"
只把数据导出到外部文件,但是不删除源表里的数据
pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123456 --where 'id>900' --no-check-charset --no-delete --file="/tmp/archiver.dat"
等价于:select * from world.city where id<900 into outfile '/tmp/data.csv'
3. pt-osc Online DDL
原理:
1、检查更改表是否有主键或唯一索引,是否有触发器
2、检查修改表的表结构,创建一个临时表,在新表上执行ALTER TABLE语句
3、在源表上创建三个触发器分别对于INSERT UPDATE DELETE操作
4、从源表拷贝数据到临时表,在拷贝过程中,对源表的更新操作会写入到新建表中
5、将临时表和源表rename(需要元数据修改锁,需要短时间锁表)
6、删除源表和触发器,完成表结构的修改。
pt-osc工具限制
1、源表必须有主键或唯一索引,如果没有工具将停止工作
2、如果线上的复制环境过滤器操作过于复杂,工具将无法工作
3、如果开启复制延迟检查,但主从延迟时,工具将暂停数据拷贝工作
4、如果开启主服务器负载检查,但主服务器负载较高时,工具将暂停操作
5、当表使用外键时,如果未使用--alter-foreign-keys-method参数,工具将无法执行
6、只支持Innodb存储引擎表,且要求服务器上有该表1倍以上的空闲空间。
pt-osc之alter语句限制
1、不需要包含alter table关键字,可以包含多个修改操作,使用逗号分开,如"drop clolumn c1, add column c2 int"
2、不支持rename语句来对表进行重命名操作
3、不支持对索引进行重命名操作
4、如果删除外键,需要对外键名加下划线,如删除外键fk_uid, 修改语句为"DROP FOREIGN KEY _fk_uid"
pt-osc之命令模板
--execute
表示执行
--dry-run
创建和修改新表,但不会创建触发器、复制数据、和替换原表。并不真正执行,可以看到生成的执行语句,了解其执行步骤与细节。--dry-run与--execute必须指定一个,二者相互排斥。和--print配合最佳
pt-online-schema-change \
--host="127.0.0.1" \
--port=3358 \
--user="root" \
--password="123456" \
--charset="utf8" \
--max-lag=10 \
默认1s。每个chunk拷贝完成后,会查看所有复制Slave的延迟情况。要是延迟大于该值,则暂停复制数据,直到所有从的滞后小于这个值,使用Seconds_Behind_Master。如果有任何从滞后超过此选项的值,则该工具将睡眠--check-interval指定的时间,再检查。如果从被停止,将会永远等待,直到从开始同步,并且延迟小于该值。如果指定--check-slave-lag,该工具只检查该服务器的延迟,而不是所有服务器
--check-salve-lag='xxx.xxx.xxx.xxx' \
指定一个从库的DSN连接地址,如果从库超过--max-lag参数设置的值,就会暂停操作。
--recursion-method="hosts" \
默认是show processlist,发现从的方法,也可以是host,但需要在从上指定report_host,通过show slave hosts来找到,可以指定none来不检查Slave。
METHOD USES
=========== ==================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
dsn=DSN DSNs from a table
none Do not find slaves
指定none则表示不在乎从的延迟。
--check-interval=2 \
默认是1。--max-lag检查的睡眠时间。
--database="testdb1" \
t="tb001" \
--alter="add column c4 int" \
--execute
例子:(插入列)
pt-online-schema-change --user=root --password=123456 --host=10.0.0.51 --alter "add column age int default 0" D=world,t=city --print --execute
4. 主从一致性校验
(1) 创建数据库(主库)
create database pt character set utf8;
(2)创建用户checksum并授权
grant all on *.* to 'checksum'@'10.0.0.%' identified by 'checksum';
flush privileges;
--[no]check-replication-filters:是否检查复制的过滤器,默认是yes,建议启用不检查模式。
--databases | -d:指定需要被检查的数据库,多个库之间可以用逗号分隔。
--[no]check-binlog-format:是否检查binlog文件的格式,默认值yes。建议开启不检查。因为在默认的row格式下会出错。
--replicate:把checksum的信息写入到指定表中。
--replicate-check-only:只显示不同步信息
小坑
所有库必须有这个参数:
autocommit=1
从库 :
report_host=10.0.0.52 #从库信息
report_port=3306 #从库信息
应用:
针对表校验:(在主库)
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test --tables=t3 h=10.0.0.51,u=checksum,p=checksum,P=3306
针对库校验:(在主库)
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test h=10.0.0.51,u=checksum,p=checksum,P=3306
主库(以主库为主同步数据)
pt-table-sync --replicate=pt.checksums h=10.0.0.32,u=root,p=123456,P=3306 --execute
5pt-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 :执行命令。
(以主库为主同步数据)
pt-table-sync --replicate=pt.checksums h=10.0.0.32,u=root,p=123456,P=3306 --print
pt-table-sync --replicate=pt.checksums h=10.0.0.32,u=root,p=123456,P=3306 --execute
6 显示主从结构:pt-slave-find
[root@db01 tmp]# pt-slave-find -h10.0.0.51 -P3306 -uchecksum -pchecksum
10.0.0.51
Version 5.7.28-log
Server ID 51
Uptime 27:57 (started 2020-05-15T13:24:15)
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.0.52
Version 5.7.28-log
Server ID 52
Uptime 28:18 (started 2020-05-15T13:23:54)
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
7. 监控主从延时
pt-heartbeat
主库:
pt-heartbeat --user=root --ask-pass --host=10.0.0.51 --create-table -D test --interval=1 --update --replace --daemonize
从库:
pt-heartbeat --user=root --ask-pass --host=10.0.0.52 -D test --table=heartbeat --monitor
8 pt-show-grants(备份用户信息)
pt-show-grants -h10.0.0.51 -P3306 -uchecksum -pchecksum >/tmp/yonghu.sql
source /tmp/yonghu.sql
9 给出参数建议:
pt-variable-advisor 10.0.0.51 -uchecksum -pchecksum