MySQL——pt-online-schema-change工具的使用
作用: 功能为在 alter 操作更改表结构的时候不用锁定表,也就是说执行 alter 的时候不会阻塞写和读取操作
常见参数:
--alter 指定ALTER 语句,正常的ALTER TABLE TBNAME [ ADD | MODIFY | DROP | ALTER ] COLUMN COLUMN_NAME ...,去除前面的ALTER TABLE TABLE那么,直接指定后部分的内容
注意:rename 不支持,请直接使用rename table table_name to table_name_new;
如果表有数据,创建非空无默认值的列,会失败;如果非空,需要指定默认值, 如果直接使用 add column num int, num这个列的默认值为NULL;
如果表有数据,为一个列添加默认值时,旧数据为NULL的是不会被修改,依旧为NULL,以后新加入的数据则会默认设置为默认值
对于外键的删除情况,由于执行是在新表上执行DDL,所以其外键值的命名跟原表的命名不一样,假设删除原表的外键名是 fk_foo,那么新表的外键名就为 _fk_foo,所以删除的ALTER语句是: drop foreign key _fk_foo
--alter-foreign-keys-method 如果修改的表,是其他表外键reference的表,那么,最后rename的过程,需要确保一定成功,要不然这些子表就没能成功reference到其指定的表名,对子表的操作将会报错。比如 tba有一个外键 fk_tba引用表格 tbb,这个时候tbb需要做DDL操作,根据pt工具的原理得知,最后会有一个rename环节,这个环节可能会导致约束失效或者执行堵塞等问题
针对最后的rename这个环节,该工具提供了4种处理方法:
auto 自动选择rebuild_constraints 或者 drop_swap,优先选择rebuild_constraints
rebuild_constraints:指在rename table前,先删除子表的外键约束,然后重建外键约束指向到新表(ALTER TABLE语句添加),最后执行rename操作
注意:rename操作即使不成功,它也rename到新表,不会出现reference的表不存在情况,其弊端如果子表过大,添加外检约束的过程中,可能会对子表造成阻塞
drop_swap:执行rename之前禁用外键检查,然后删除原表,rename新表为原表名,这个执行过程非常快并且没有阻塞
注意:需要强制指定 --no-swap-tables 跟 --no-drop-old-table,其弊端当把原表删除而新表还没rename为原表的名字时,这段时间实际非常短,但是这段时
间内,等于原表名的表是不存在的,子表做一些DML的时候,可能会出现错误。rename期间,如果新表rename原表失败,但是已经删除原表,那么这段期间,其子表
的操作将会出现大面积问题,直到人工修复
none: 类似于drop_swap,不同在于对原表的处理
按正常的pt工具流程,禁用外键约束,rename原表为临时表,rename新表为原表名,删除临时表
弊端: 当把原表rename为临时表,而新表还没rename为原表的名字时,这段时间实际非常短,但是这段时间内,等于原表名的表格时不存在的,子表做一些DML的时
候,可能会出现错误
--drop-old-tables 操作成功后,原表是否保留,默认是删除;default:yes 可选:--no-drop-old-table
--dry-run 仅创建新表,但是不执行触发器、拷贝数据和替换原表
--execute 确认执行alter操作,注意,这个操作如果不指定,则仅做安全检查然后退出
--host 连接主机名
--max-lag 默认1秒
检查从库延迟的时间,如果超过,则停止copy data,休息--check-interval秒后,再重新开始copy数据;
查看通过延迟时间,是通过从库show slave status,查看Seconds_Behind_Master;
如果指定--check-slave-lag,该工具只检查该服务器的延迟,而不是所有服务器;
--check-interval 默认1秒
从库延迟超过指定的--max-lag,中断copy data休息的时间
--max-load 默认Threads_runing=25, --max-load=Threads_running=15
copy data的过程,监控数据库当前正在运行的thread,如果超过指定的Threads_running值,则停止拷贝数据,会在输出的内容中答应 Pausing because Threads_runing=15,直到运行的线程数小于给定的值,恢复copy data,如此循环,知道拷贝数据结束.
--password 数据库用户名密码
--port 数据库端口号
--socket 数据库socket文件
--user 数据库用户名
--recursion-method master寻找slave的方式,有4中方式
processlist show processlist
hosts show slave hosts
dsn=DNS DSNs from a table
none do not find slaves 不查找从库
注意:dsn,使用表格 tdsn存储从库信息(DSN的具体参数选项可以详细查看 3.3 DSN选线)
需要手动在需要DDL的数据库内,创建 dsns 表格
CREATE TABLE `dsns` (`id` int(11) NOT NULL AUTO_INCREMENT,`parent_id` int(11) DEFAULT NULL,`dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`));
存储从库信息
insert into dsns(dsn) values(h=slave_host,u=repl_user,p=repl_password,P=port );
该参数使用的时候,按以下格式(假设 dsns表格建立在数据库 dbosc)
--recursion-method dsn=D=dbosc,t=dsns
--statistics 增加影响行数打印,可以查看copy进度
--print 详细打印alter过程,不指定的时候,简略打印
主库准备数据:
create table t1 (id int, name varchar(10));
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t1 values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
安装依赖:
yum -y install perl-DBI
yum -y install perl-DBD-MySQL
yum -y install perl-Time-HiRes
yum -y install perl-IO-Socket-SSL
查看帮助文档
[root@db201 percona-toolkit-3.3.1]# ./bin/pt-online-schema-change --help
Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ./bin/pt-online-schema-change line 6340.
BEGIN failed--compilation aborted at ./bin/pt-online-schema-change line 6340.
原因是缺少一个perl-Digest-MD5的包,只需要安装即可:
yum -y install perl-Digest-MD5
[root@db201 ~]# /root/percona-toolkit-3.3.1/bin/pt-online-schema-change --socket=/root/data3307/my3307.sock --user=root --password=root D=db1,t=t1 --alter "add column num int " --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
Cannot connect to MySQL: DBI connect('db1;mysql_socket=/root/data3307/my3307.sock;mysql_read_default_group=client','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: 无法打开共享对象文件: 没有那个文件或目录 at /root/percona-toolkit-3.3.1/bin/pt-online-schema-change line 2345.
原因:mysql8.0.24的版本的加密插接default_authentication_plugin为caching_sha2_password;需要将加密插件改为mysql_native_password
[root@db201 ~]# /root/percona-toolkit-3.3.1/bin/pt-online-schema-change --host=192.168.221.201 --user=root --password=root P=3307,D=db1,t=t1 --alter "add column num int " --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
No slaves found. See --recursion-method if host db201 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /root/percona-toolkit-3.3.1/bin/pt-online-schema-change line 7119.
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /root/percona-toolkit-3.3.1/bin/pt-online-schema-change line 7119.
# A software update is available:
Cannot chunk the original table `db1`.`t1`: There is no good index and the table is oversized. at /root/percona-toolkit-3.3.1/bin/pt-online-schema-change line 6012.
原因是:t1表没有主键或者唯一索引
——t1表添加主键 alter table t1 add primary key (id);后测试
——添加一列
[root@db201 ~]# /root/percona-toolkit-3.3.1/bin/pt-online-schema-change --host=192.168.221.201 --user=root --password=root P=3307,D=db1,t=t1 --alter " add column num int " --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
No slaves found. See --recursion-method if host db201 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
No foreign keys reference `db1`.`t1`; ignoring --alter-foreign-keys-method.
Altering `db1`.`t1`...
Creating new table...
CREATE TABLE `db1`.`_t1_new` (
`id` int NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
Created new table db1._t1_new OK.
Altering new table...
ALTER TABLE `db1`.`_t1_new` add column num int
Altered `db1`.`_t1_new` OK.
2021-11-23T02:02:17 Creating triggers...
-----------------------------------------------------------
Event : DELETE
Name : pt_osc_db1_t1_del
SQL : CREATE TRIGGER `pt_osc_db1_t1_del` AFTER DELETE ON `db1`.`t1` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `db1`.`_t1_new` WHERE `db1`.`_t1_new`.`id` <=> OLD.`id`; END
Suffix: del
Time : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : UPDATE
Name : pt_osc_db1_t1_upd
SQL : CREATE TRIGGER `pt_osc_db1_t1_upd` AFTER UPDATE ON `db1`.`t1` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `db1`.`_t1_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `db1`.`_t1_new`.`id` <=> OLD.`id`; REPLACE INTO `db1`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`); END
Suffix: upd
Time : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : INSERT
Name : pt_osc_db1_t1_ins
SQL : CREATE TRIGGER `pt_osc_db1_t1_ins` AFTER INSERT ON `db1`.`t1` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `db1`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`);END
Suffix: ins
Time : AFTER
-----------------------------------------------------------
2021-11-23T02:02:17 Created triggers OK.
2021-11-23T02:02:17 Copying approximately 100259 rows...
INSERT LOW_PRIORITY IGNORE INTO `db1`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `db1`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 40669 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db1`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
2021-11-23T02:02:18 Copied rows OK.
2021-11-23T02:02:18 Analyzing new table...
2021-11-23T02:02:18 Swapping tables...
RENAME TABLE `db1`.`t1` TO `db1`.`_t1_old`, `db1`.`_t1_new` TO `db1`.`t1`
2021-11-23T02:02:18 Swapped original and new tables OK.
2021-11-23T02:02:18 Dropping old table...
DROP TABLE IF EXISTS `db1`.`_t1_old`
2021-11-23T02:02:18 Dropped old table `db1`.`_t1_old` OK.
2021-11-23T02:02:18 Dropping triggers...
DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t1_del`
DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t1_upd`
DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t1_ins`
2021-11-23T02:02:18 Dropped triggers OK.
Successfully altered `db1`.`t1`.
注意: 新添加的列,默认值为NULL
利用 pt-online-schema-change 添加索引,查看pt-online-schema-change 具体的执行流程
[root@db201 ~]# /root/percona-toolkit-3.3.1/bin/pt-online-schema-change --host=192.168.221.201 --user=root --password=root P=3307,D=db1,t=t1 --alter "ADD INDEX name(name) " --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
在执行之前,打开set global general_log=1;查看统一日志,如下:
[root@db201 data]# cat db201.log
省略
由以上的日志,可以看出具体的执行流程如下:
- 相关环境参数检查;
- 检查该表是否存在;
- show create table t1;
- CREATE TABLE `db1`.`_t1_new`
- ALTER TABLE `db1`.`_t1_new` ADD INDEX name(name)
- 创建删除触发器CREATE TRIGGER `pt_osc_db1_t1_del`
- 创建更新触发器CREATE TRIGGER `pt_osc_db1_t1_upd`
- 创建插入触发器CREATE TRIGGER `pt_osc_db1_t1_ins`
- 按块拷贝数据到新表,拷贝过程中对数据行持有S锁
- analyze新表 ANALYZE TABLE `db1`.`_t1_new` /* pt-online-schema-change */
- rename 表名 RENAME TABLE `db1`.`t1` TO `db1`.`_t1_old`, `db1`.`_t1_new` TO `db1`.`t1`
- 删除旧表 DROP TABLE IF EXISTS `db1`.`_t1_old`
- 删除新表上的删除,更新,插入触发器 DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t1_del`
Q1、alter操作期间,t1表是否支持DML操作??
ALTER过程采用Copy Table To New Table的方式,新建一个表t1,然后在原表上创建3个触发器:DELETE\UPDATE\INSERT触发器,拷贝数据到新表的过程中,如果原表数据发生变化,则会通过触发器更新到新表上
INSERT原表的时候,触发器根据其主键ID把新纪录INSERT到新表上;
UPDATE原表的时候,触发器根据其主键ID判断新旧ID是否一致,如果一致则删除,然后在REPLACE INTO新纪录到新表
DELETE原表的时候,触发器根据其主键ID直接删除行记录
如果数据修改的时候,还没有拷贝到新表,修改后再拷贝,虽然重复覆盖,但是数据也没有出错;如果是数据已经拷贝,原表发生修改,这时触发器同步修改数据,两种情况下都保证了数据的一致性;
Q2、整个操作流程锁情况是什么样的??
创建新表后,按照每一个chunk的大小拷贝数据到新表,每次SELECT都是share mode,带S锁,但是每个chunk都比较小,所以锁时间不大;
最后数据拷贝结束,会有一个rename操作,这个操作过程中,是不支持DML操作的,但其速度很快,不会造成长时间锁表情况;
该工具会设置该DDL操作的锁等待超时为1s,当出现异常的时候,会是ALTER操作异常,而不是其他业务操作异常,这样可以最大程度的不影响其他事务的进行;
Q3、执行期间有什么性能影响??
总体而言,对数据库的锁影响降低到了最小,执行期间允许DML操作;
但是注意,任何DDL SQL在这里,都是转换成copy table to new table的形式,这个过程中,会极大占用磁盘的IO跟CPU资源,同时给主从复制带来一定的影响;
copy data过程中,如果主从延迟异常超过 max-lag则停止copy data,等待主从延迟恢复,默认为1min,可以通过--max-lag设置;
检测到服务器负载异常,也会停止操作,可以通过 --max-load,--critical-load设置
Q4、该工具有什么限制情况??
1、 表格必须带有主键或者唯一索引
2、存在复制过滤掉表格,ALTER操作
3、copy data过程中,如果主从延迟异常超过 max-lag则停止copy data,等待主从延迟恢复,默认为1s,可以通过--max-lag设置
4、检测到服务器负载异常,也会停止操作,可以通过 --max-load,--critical-load设置
5、设置操作的锁等待超时为1s,当出现异常的时候,ALTER操作异常,而不是其他业务操作异常,这样可以最大程度的不影响其他事务的进行
6、默认情况下,存在 被外键引用的表格是不支持ALTER操作的,除非手动指定参数--alter-foreign-keys-method
7、不支持修改 Percona XtraDB Cluster (PXC)上节点的 myisam表格
Q5、在binlog日志中是怎么记录这个操作的??
会将整个alter 的流程全部记录下来
[root@db201 binlog]# ~/mysql8024/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000002 >2.sql
省略。。。。。。和general_log日志记录的详细步骤一致
修改某一列的默认值:
mysql> show create table t1\G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL,
`name` varchar(10) DEFAULT NULL,
`num` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
/root/percona-toolkit-3.3.1/bin/pt-online-schema-change --host=192.168.221.201 --user=root --password=root P=3307,D=db1,t=t1 --alter "ALTER column num SET DEFAULT 100 " --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
mysql> show create table t1\G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL,
`name` varchar(10) DEFAULT NULL,
`num` int DEFAULT '100',
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
mysql> insert into t1(id,name) values(200000,'200000');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1 where id=200000;
+--------+--------+------+
| id | name | num |
+--------+--------+------+
| 200000 | 200000 | 100 |
+--------+--------+------+
mysql> select count(*) from t1 where num=100;
+----------+
| count(*) |
+----------+
| 1 |
验证了:如果表有数据,为一个列添加默认值时,旧数据为NULL的是不会被修改,依旧为NULL,以后新加入的数据则会默认设置为默认值
考虑从库延迟的情况:
考虑从库延迟情况 ,意味这要注意这几个选项的设置
--max-lag
--check-interval
--recursion-method
--check-slave-lag
从库延迟超过max-lag则停止copy data,等待 check-interval 秒后再开始copy data。check-slave-lag指定slave的机器,只会对比这台slave的延迟情况。recursion-method是主库寻找从库的方法,有四个方法:processlist,hosts,dsn,none,具体查看上部分选项详细说明
假如还需要给表t1添加一列:hobby varchar(100) ,需要考虑从库的延迟情况
创建表格dsns,记录从库信息
CREATE TABLE `dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
#insert从库信息,有2个从库,分别为202服务器上的 3310跟3320
insert into dsns(dsn) select "h=192.168.221.202,u=repl,p=****,P=3310";
insert into dsns(dsn) select "h=192.168.221.202,u=repl,p=****,P=3320";
如果需要考虑多个从库的延迟情况,则可以考虑使用 dsns表格来记录从库信息,如果只需要考虑某一台从库的延迟情况,则既可以使用dsns表格也可以使用参数--check-slave-lag指定从库。
不考虑外键关系,考虑从库影响程度,检查到从库延迟超过1s,则休息5s,具体指令如下
pt-online-schema-change -P3307 --user=root --password=root D=db1,t=t1 --max-lag=1s --check-interval=10s --alter "ADD hobby varchar(100) NOT NULL DEFAULT ‘sleep‘ " --recursion-method dsn=D=db1,t=dsns --alter-foreign-keys-method auto --print --execute