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`