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