背景

　　在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/

[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
[root@mysql ~]# yum -y install  percona-toolkit-3.2.1-1.el7.x86_64.rpm

pt-osc使用

# 范例
[root@mysql ~]# pt-online-schema-change \
--host="127.0.0.1" \
--port=3306 \
--user="root" \
--password="oldboy123" \
--charset="utf8mb4" \
--max-lag=10 \
--check-salve-lag='xxx.xxx.xxx.xxx' \
--recursion-method="hosts" \
--check-interval=2 \
D="testdb1",t="tb001" \
--alter="add column age int(4) default 0" \
--dry-run
--print
--execute

# 真实测试: 添加一个city列 varchar(255)
[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
No slaves found.  See --recursion-method if host localhost.localdomain 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
Altering `oldguo`.`oldguo`...
Creating new table...
CREATE TABLE `oldguo`.`__oldguo_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '??',
  `name` varchar(255) NOT NULL COMMENT '??',
  `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '??',
  `gender` enum('m','f','n') NOT NULL DEFAULT 'n',
  `telnum` char(11) NOT NULL COMMENT '???',
  PRIMARY KEY (`id`),
  UNIQUE KEY `telnum` (`telnum`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
Created new table oldguo.__oldguo_new OK.
Altering new table...
ALTER TABLE `oldguo`.`__oldguo_new` add column city varchar(255) not null default 'china'
Altered `oldguo`.`__oldguo_new` OK.
2021-04-27T14:30:59 Creating triggers...
2021-04-27T14:30:59 Created triggers OK.
2021-04-27T14:30:59 Copying approximately 2 rows...
INSERT 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*/
2021-04-27T14:30:59 Copied rows OK.
2021-04-27T14:30:59 Analyzing new table...
2021-04-27T14:30:59 Swapping tables...
RENAME TABLE `oldguo`.`oldguo` TO `oldguo`.`_oldguo_old`, `oldguo`.`__oldguo_new` TO `oldguo`.`oldguo`
2021-04-27T14:30:59 Swapped original and new tables OK.
2021-04-27T14:30:59 Dropping old table...
DROP TABLE IF EXISTS `oldguo`.`_oldguo_old`
2021-04-27T14:30:59 Dropped old table `oldguo`.`_oldguo_old` OK.
2021-04-27T14:30:59 Dropping triggers...
DROP TRIGGER IF EXISTS `oldguo`.`pt_osc_oldguo_oldguo_del`
DROP TRIGGER IF EXISTS `oldguo`.`pt_osc_oldguo_oldguo_upd`
DROP TRIGGER IF EXISTS `oldguo`.`pt_osc_oldguo_oldguo_ins`
2021-04-27T14:30:59 Dropped triggers OK.
Successfully altered `oldguo`.`oldguo`.