背景

　　在 MySQL 5.6 版本以前，修改表结构如添加索引、修改列，需要锁表，期间不能写入，对于一个有几千万数据的大表来说，这个简直不能接收，对于分秒必争的互联网时代，服务中断很长时间的后果是很严重的。
　　升级 MySQL 到 5.6 版本后，情况会好转，支持 online-DDL, 但是实际在 Alter 表的时候，还会有可能锁表。

　　pt-online-schema-change 是 Percona-toolkit 一员， 支持在不锁表的情况下，在线改表。

pt-osc 的工作原理

• 创建一个和要执行 alter 操作的表一样的新的空表，后缀默认是 new。
• 在新表执行 alter table 语句，因为是空表，执行速度很快。
• 在原表中创建触发器 3 个触发器分别对应 insert,update,delete 操作。
• 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表，注意这里是 Replace 操作。
• 表明替换 将原表名 table 修改为 tableold, 将 tablenew 表明修改为原表名 table
• 如果有参考该表的外键，根据 alter-foreign-keys-method 参数的值，检测外键相关的表，做相应设置的处理
• 默认最后将旧原表删除

pt-osc 工具的限制

1、源表不能有触发器存在（insert、update、delete）
2、源表必须要有主键或唯一索引，如果没有工具将停止工作
3、源表有外键，必须使用–alter-foreign-keys-method 指定特定的值
4、如果线上的复制环境过滤器操作过于复杂，工具将无法工作
5、如果开启复制延迟检查，但主从延迟时，工具将暂停数据拷贝工作
6、如果开启主服务器负载检查，但主服务器负载较高时，工具将暂停操作
7、只支持 Innodb 存储引擎表，且要求服务器上有该表 1 倍以上的空闲空间。
8、修改索引、外键、列名时，优先采用 online ddl，并指定 ALGORITHM=INPLACE

安装 pt-osc 工具

下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/

1[root@mysql ~]# wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/redhat/7/x86_64/percona-toolkit-3.2.1-1.el7.x86_64.rpm
2[root@mysql ~]# yum -y install  percona-toolkit-3.2.1-1.el7.x86_64.rpm

pt-osc 使用

 1# 范例
 2[root@mysql ~]# pt-online-schema-change \
 3--host="127.0.0.1" \
 4--port=3306 \
 5--user="root" \
 6--password="oldboy123" \
 7--charset="utf8mb4" \
 8--max-lag=10 \
 9--check-salve-lag='xxx.xxx.xxx.xxx' \
10--recursion-method="hosts" \
11--check-interval=2 \
12D="testdb1",t="tb001" \
13--alter="add column age int(4) default 0" \
14--dry-run
15--print
16--execute
17
18# 真实测试: 添加一个city列 varchar(255)
19[root@mysql ~]# pt-online-schema-change --host=127.0.0.1 --port=3306 --user=root --password=oldboy123 D=oldguo,t=oldguo --alter="add column city varchar(255) not null default 'china'" --print --execute
20No slaves found.  See --recursion-method if host localhost.localdomain has slaves.
21Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
22Operation, tries, wait:
23  analyze_table, 10, 1
24  copy_rows, 10, 0.25
25  create_triggers, 10, 1
26  drop_triggers, 10, 1
27  swap_tables, 10, 1
28  update_foreign_keys, 10, 1
29Altering `oldguo`.`oldguo`...
30Creating new table...
31CREATE TABLE `oldguo`.`__oldguo_new` (
32  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '??',
33  `name` varchar(255) NOT NULL COMMENT '??',
34  `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '??',
35  `gender` enum('m','f','n') NOT NULL DEFAULT 'n',
36  `telnum` char(11) NOT NULL COMMENT '???',
37  PRIMARY KEY (`id`),
38  UNIQUE KEY `telnum` (`telnum`)
39) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
40Created new table oldguo.__oldguo_new OK.
41Altering new table...
42ALTER TABLE `oldguo`.`__oldguo_new` add column city varchar(255) not null default 'china'
43Altered `oldguo`.`__oldguo_new` OK.
442021-04-27T14:30:59 Creating triggers...
452021-04-27T14:30:59 Created triggers OK.
462021-04-27T14:30:59 Copying approximately 2 rows...
47INSERT LOW_PRIORITY IGNORE INTO `oldguo`.`__oldguo_new` (`id`, `name`, `age`, `gender`, `telnum`) SELECT `id`, `name`, `age`, `gender`, `telnum` FROM `oldguo`.`oldguo` LOCK IN SHARE MODE /*pt-online-schema-change 25468 copy table*/
482021-04-27T14:30:59 Copied rows OK.
492021-04-27T14:30:59 Analyzing new table...
502021-04-27T14:30:59 Swapping tables...
51RENAME TABLE `oldguo`.`oldguo` TO `oldguo`.`_oldguo_old`, `oldguo`.`__oldguo_new` TO `oldguo`.`oldguo`
522021-04-27T14:30:59 Swapped original and new tables OK.
532021-04-27T14:30:59 Dropping old table...
54DROP TABLE IF EXISTS `oldguo`.`_oldguo_old`
552021-04-27T14:30:59 Dropped old table `oldguo`.`_oldguo_old` OK.
562021-04-27T14:30:59 Dropping triggers...
57DROP TRIGGER IF EXISTS `oldguo`.`pt_osc_oldguo_oldguo_del`
58DROP TRIGGER IF EXISTS `oldguo`.`pt_osc_oldguo_oldguo_upd`
59DROP TRIGGER IF EXISTS `oldguo`.`pt_osc_oldguo_oldguo_ins`
602021-04-27T14:30:59 Dropped triggers OK.
61Successfully altered `oldguo`.`oldguo`.
62