1 原理介绍

• ALTER操作期间，表格是否支持DML？
• 如果支持DML，是如何把DML同步到新的临时表上？
• 整个操作流程锁情况是怎么样的？
• 执行期间有什么性能影响？
• 该工具有什么限制情况？

1.1 详细执行流程

1. 相关环境参数检查
2. 检查该表格是否存在
3. show create table tbosc
4. create table _tbosc_new
5. alter table _tbosc_new 
6. 创建删除触发器 pt_osc_dbddl_tbosc_del (如果数据修改的时候，还没有拷贝过来，修改后再拷贝则是覆盖，正确；如果是已经拷贝过来，再修改，也是正确，这里同时会检查是否具有主键或者唯一索引，如果都没有，这一步会报错，提示The new table `dbosc`.`_tbosc_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.）
7. 创建更新触发器 pt_osc_dbddl_tbosc_upd
8. 创建插入触发器 pt_osc_dbddl_tbosc_ins
9. 按块拷贝数据到新表，拷贝过程对数据行持有S锁
10. analyze 新表
11. rename 表名，RENAME TABLE `dbddl`.`tbosc` TO `dbddl`.`_tbosc_old`, `dbddl`.`_tbosc_new` TO `dbddl`.`tbosc`
12. 删除旧表
13. 删除新表上的删除、更新、插入 触发器

1.2 问题解答

• ALTER操作期间，表格是否支持DML？
  • ALTER过程采用Copy Table To New Table的方式，新建一个表格，然后在原表上创建3个触发器：DELETE\UPDATE\INSERT触发器，一旦新表，拷贝数据到新表的过程中，如果原表数据发生变化，则会通过触发器更新到新表上。
  • 如果支持DML，是如何把DML同步到新的临时表上？
  • ALTER过程采用Copy Table To New Table的方式，新建一个表格，然后在原表上创建3个触发器：DELETE\UPDATE\INSERT触发器，一旦新表，拷贝数据到新表的过程中，如果原表数据发生变化，则会通过触发器更新到新表上。
  • INSERT原表的时候，触发器根据其主键ID把新纪录INSERT到新表上；
  • UPDATE原表的时候，触发器根据其主键ID判断新旧ID是否一致，如果一致则删除，然后在REPLACE INTO新纪录到新表
  • DELETE原表的时候，触发器根据其主键ID直接删除行记录
  • 如果数据修改的时候，还没有拷贝到新表，修改后再拷贝，虽然重复覆盖，但是数据也没有出错；如果是数据已经拷贝，原表发生修改，这时触发器同步修改数据，两种情况下都保证了数据的一致性；
• 整个操作流程锁情况是怎么样的？
  • 创建新表后，按照每一个chunk的大小拷贝数据到新表，每次SELECT都是share mode，带S锁，但是每个chunk都比较小，所以锁时间不大
  • 最后数据拷贝结束，会有一个rename操作，这个操作过程中，是不支持DML操作的，但其速度很快，不会造成长时间锁表情况
  • 该工具会设置该DDL操作的锁等待超时为1s，当出现异常的时候，会是ALTER操作异常，而不是其他业务操作异常，这样可以最大程度的不影响其他事务的进行

• 执行期间有什么性能影响？

• • 总体而言，对数据库的锁影响降低到了最小，执行期间允许DML操作
  • 但是注意，任何DDL SQL在这里，都是转换成copy table to new table的形式，这个过程中，会极大占用磁盘的IO跟CPU资源，同时跟住从延时带来一定的影响，还是那句老话，重复了解DDL的影响程度后，再选择合适时机执行。
  • copy data过程中，如果主从延迟异常超过 max-lag则停止copy data，等待主从延迟恢复，默认为1min，可以通过--max-lag设置
  • 检测到服务器负载异常，也会停止操作，可以通过 --max-load，--critical-load设置

• 该工具有什么限制情况？
  • 表格必须带有主键或者唯一索引
  • 存在复制过滤掉表格，ALTER操作
  • copy data过程中，如果主从延迟异常超过 max-lag则停止copy data，等待主从延迟恢复，默认为1s，可以通过--max-lag设置
  • 检测到服务器负载异常，也会停止操作，可以通过 --max-load，--critical-load设置
  • 设置操作的锁等待超时为1s，当出现异常的时候，ALTER操作异常，而不是其他业务操作异常，这样可以最大程度的不影响其他事务的进行
  • 默认情况下，存在 被外键引用的表格是不支持ALTER操作的，除非手动指定参数--alter-foreign-keys-method
  • 不支持修改 Percona XtraDB Cluster （PXC）上节点的 myisam表格

2 环境准备

3 语法说明

3.1 主要选项

• --alter
  • 指定ALTER 语句，正常的ALTER TABLE TBNAME [ ADD | MODIFY | DROP | ALTER ] COLUMN COLUMN_NAME ...，去除前面的ALTER TABLE TABLE那么，直接指定后部分的内容
  • 注意事项
    • rename不支持，请直接使用RENAME TABLE tablename TO new_tablename;
    • 如果表格有数据，创建非空无默认值的列，会失败，如果非空，需要指定默认值；
    • 如果表格有数据，为一个可空的列添加默认值时，旧数据为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-table
      • 操作成功后，原表是否保留，默认是删除，
      • default：yes，可选：--no-drop-old-table

• --dry-run
  • 仅创建新表格，但是不执行触发器、拷贝数据跟替换原表
• --execute
  • 确定执行ALTER操作，注意，这个操作如果不指定，则仅做安全检查然后推出。
  • 充分了解工具的使用情况后，才执行，不要啥都不了解测试就线上执行，会挖坑的...
• --host
  • 连接主机名
• --max-lag
  • 默认1s
  • 检查从库延迟的时间，如果超过，则停止copy data，休息--check-interval秒后，再重新开始copy数据
  • 查看通过延迟时间，是通过从库show slave status，查看Seconds_Behind_Master
  • 如果指定--check-slave-lag，该工具只检查该服务器的延迟，而不是所有服务器。
• --check-interval
  • 从库延迟超过指定的--max-lag，中断copy data休息的时间
  • 默认为1s
• --max-load
  • copy data的过程，监控数据库当前正在运行的thread，如果超过指定的Threads_running值，则停止拷贝数据，会在输出的内容中答应 Pausing because Threads_runing=15，直到运行的线程数小于给定的值，恢复copy data，如此循环，知道拷贝数据结束。
  • Threads_runing默认为25
  • 举例：--max-load=Thread_running=15
• --password
  • 数据库用户名的密码
• --port
  • 数据库端口号
• --socket
  • 数据库socket文件
• --user
  • 数据库用户名

• --recursion-method
  • MASTER寻找SLAVE的方式（这个选项基本在涉及主从问题的pt工具中广泛应用到）
  • 有4个选项
    • 

• • processlist，使用show processlist查找从库
  • hosts，如果不是使用默认端口号3306，那么使用hosts方式来查找从库会更可靠
  • 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
  • none，不查找从库

3.3 DSN选项

• A
  • 字符集设置
• D
  • 需要Alter的表格是在哪个数据库，指定数据库
• F
  • mysql read default file，如果数据源的相关选项存储在文件中，则通过 F 来指定
• h
  • host，数据库主机名或IP
• p
  • password，数据库用户的密码
• P
  • port，数据库实例端口号
• S
  • socket，实例socket文件
• t
  • 表格名
• u
  • 用户名

4 使用举例

4.1 常规DDL

 1 CREATE TABLE `tbosc` (
 2   `id` int(11) NOT NULL AUTO_INCREMENT,
 3   `name` varchar(100) DEFAULT NULL,
 4   `age` int(11) DEFAULT NULL,
 5   PRIMARY KEY (`id`)
 6 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
 7  
 8 #添加列
 9 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "add column stunum int "  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
10  
11 #删除列
12 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "drop column stunum "  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
13  
14 #修改列数据类型
15 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "modify column age varchar(10)"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
16  
17 #加大列长度
18 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "modify column age varchar(100)"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
19  
20 #创建索引
21 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "ADD INDEX IX_AGE(AGE)"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
22  
23 #删除索引
24 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "DROP INDEX IX_AGE"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
25  
26 #设置默认值
27 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "ALTER column age SET DEFAULT 100"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
28  
29 #能否多个合成一个
30 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "ADD COLUMN onecol int ,add column twocol varchar(100),add index ix_onecol(onecol),alter column name set default 'xinysu' "  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute

4.2 考虑从库延迟情况

• --max-lag
• --check-interval
• --recursion-method
• --check-slave-lag

5 pt-osc还是online DDL？

• 如果MySQL版本是5.6之前，不支持online ddl操作的，pt-online-schema-change是一个非常好的选择；
• 如果MySQL的版本是5.6以上的，支持online-ddl的，优先考虑使用online ddl，但是如果是ddl SQL 在online DDL中 需要copy table to tmp table，则建议使用pt-online-schema-change来处理，比如修改列数据类型的DDL，online DDL则是需要copy table to tmp table，期间仅支持查询，不支持DML操作，这个时候，就可以使用pt-online-schema-change来处理，因为它也是拷贝临时表格，并且执行期间支持DML操作；
• 如果执行Online DDL，但是对从库的延迟非常敏感，针对需要copy table 跟rebuild table这两类DDL SQL，需要考虑是否可以在从库设置并行复制，如果不行，则优先选择pt-online-schema-change。

参考文档：https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有