MySQL ONLINE DDL

ONLINE DDL

ONLINE DDL是MySQL 5.6开始引入的，能够在DDL执行期间不影响正常的DML操作，提高数据并发能力。Online DDL可分为三种情况：

copy(ALGORITHM=copy)：5.6之前DDL的执行方式，由Server层创建临时表，进行数据拷贝。在DDL执行期间，DML无法执行。innodb中不支持inplace方式执行的都会自动使用copy，而MyISAM表只能使用copy方式。
inplace(ALGORITHM=inplace)：DDL所有步骤都在innodb引擎完成，在DDL执行期间不影响DML操作，所有操作都是online的。inplace带有日志记录和重放功能，当在需要rebuild重建表时，会申请row log空间记录DDL期间所有DML操作，再重放至临时表。
inplace(offline)：DDL过程是ONLINE的一定采用inplace，但inplace的DDL并不一定ONLINE，截止到8.0中创建全文索引(fulltext index)或空间索引(spatial index)都是采用inplace但会阻塞DML的情况。

除了ONLINE DDL内部实现，还通过LOCK选项控制锁，不同的DDL有不同的表现形式，默认mysql尽可能不去锁表

LOCK=NONE：DDL过程中允许读写
LOCK=SHARED：DDL过程会阻塞写请求，允许读请求
LOCK=DEFAULT：mysql自动判断lock，尽可能不去锁表
LOCK=EXCLUSIVE：DDL过程中不允许读写请求

不同DDL操作的表现

操作	是否inplace	是否重建表	是否允许DML	是否只修改元数据	描述
添加索引	YES	NO	YES	NO	对全文索引不支持inplace
删除索引	YES	NO	YES	YES	仅修改表的元数据
OPTIMIZE TABLE	YES	YES	YES	NO	从 5.6.17开始使用ALGORITHM=INPLACE，如果表上有全文索引只支持COPY
SET DEFAULT	YES	NO	YES	YES	仅修改元数据
SET AUTO-INCREMENT	YES	NO	YES	NO	修改的内存数据
添加外键	YES	NO	YES	YES	关闭foreign_key_checks参数时，可以使用inplace，否则采用copy的方式
删除外键	YES	NO	YES	YES	foreign_key_checks参数不影响
修改列名	YES	NO	YES	YES	为了允许DML并发, 请保持相同数据类型，仅改变列名
添加列	YES	YES	YES	NO	添加auto-increment自增列时，不允许并发DML，需要重建表使得代价很高
删除列	YES	YES	YES	NO	需要重建表使得代价很高
修改列排序	YES	YES	YES	NO	需要重建表使得代价很高
修改ROW_FORMAT和KEY_BLOCK_SIZE	YES	YES	YES	NO	需要重建表使得代价很高
设置列为NULL	YES	YES	YES	NO	需要重建表使得代价很高
设置列非NULL	YES	YES	YES	该操作需要sql_mode设置STRICT_ALL_TABLES或STRICT_TRANS_TABLES，如果列中已经存在空值操作会失败
修改数据类型	NO	YES	NO	NO	只支持ALGORITHM=COPY
添加主键	YES	YES	YES	NO	支持ALGORITHM=INPLACE，如果列需要转化为NOT NULL，则不允许采用INPLACE
删除并添加主键	YES	YES	YES	NO	需要重建表使得代价很高
删除主键	NO	YES	NO	YES	当删除主键无需在同一ALTER TABLE语句中新建时，仅支持COPY
修改字符集	NO	YES	NO	NO	如果新的字符集编码不同，需要重建表
ALTER TABLE ENGINE=INNODB重建	YES	YES	YES	NO	支持ALGORITHM=INPLACE，带全文索引则不支持

copy online重建表的流程 1、根据原表定义一个新的临时表 2、对原表加写锁，无法执行DML 3、在临时表上执行DDL 4、将原表的数据拷贝到临时表中 5、释放原表的写锁 6、删除原表并重命名临时表为原表

inplace online的重建表流程

获取MDL写锁
创建临时文件
申请row log空间
MDL锁降级为读锁，可执行DML
扫描原表所有数据到临时文件中
将对原表的所有DML操作记录到row log
MDL升级为写锁，DML不可执行
重做row log日志
重命名临时文件，删除原表文件
提交事务，释放锁

查看ONLINE DDL进度

开启功能

1
2
3
4
5
6
7


mysql> UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

查看进度

1
2
3
4
5
6


mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) |            280 |           1245 |
+------------------------------------------------------+----------------+----------------+

GH-OST

gh-ost是一个用于实现online ddl的开源工具，通过模拟从库，从binlog中获取增量数据，再异步应用到ghost表中。并提供暂停，动态控制，审计和许多操作特权，其优点在于无触发器，对主库影响较小

gh-ost-general-flow

工作模式

连接到从库，在主库上迁移。这是gh-ost的默认的模式，gh-ost通过从副本查找到主副本，并连接到主副本。大致步骤如下：
- 在主库创建_gho表(与原表结构一致)，_ghc表(变更日志)，并修改gho表结构
- 读取副本上的二进制日志事件，将更改应用到主库gho表
- 在主库读取原表数据插入gho表
- 主库完成切换表

如果主库二进制日志格式是statement，就可以使用这种模式。但从库必须启用二进制日志(log_bin,log_slave_update)，还要将格式设置为ROW

主库上执行。如果是单实例或者不想连接到从副本执行，也可以在主副本上执行。gh-ost将直接在主库上执行所有操作。
- 主库binlog_format必须设置为ROW
- gh-ost必须使用–allow-on-master选项开启该模式
在从库上迁移/测试。将在从库上执行迁移。gh-ost将短暂连接到主库，此后对从库执行所有操作，而无需修改主库内容。在整个操作过程中，gh-ost将进行限制以使从库是最新的。
- –migrate-on-replica表示gh-ost必须直接在从库上迁移表。即使复制正在进行，它也将进行转换阶段
- –test-on-replica表示迁移仅用于测试目的。在进行转换之前，复制已停止。交换表，然后交换回去。原表将返回其原始位置。

DDL执行过程

检查有没有外键和触发器
检查表的主键信息
检查是否主库或从库，是否开启log_slave_updates以及binlog
检查gho和del结尾的临时表是否存在
创建ghc结尾的表，存数据迁移信息，以及binlog信息等
初始化stream的连接，添加binlog的监听
创建gho结尾的临时表，执行DDL在gho结尾的临时表上
开启事务，按照主键ID把源表数据写入到gho结尾的表，再提交，以及binlog apply
lock源表，重命名表
清理ghc表

数据迁移一致性

在gh-ost执行过程中对原表和中间表的操作包括：(1)对原表进行数据拷贝(2)期间业务执行的DML(3)中间表应用binlog。由于binlog是基于DML操作产生的，因此3一定排在2后面，因此执行顺序存在以下情况：

1->2->3	insert ingore into t2 select * from t1 where id>0 and id >10;	insert into t2(id,name) values(11,'lu');	replace into t2(id,name) values(11,'lu');
		update t2 set name='lu' where id=10;	update t2 set name='lu' where id=10;
		delete from t2 where id=10;	delete from t2 where id=10;
2->3->1	insert into t2(id,name) values(11,'lu');	replace into t2(id,name) values(11,'lu');	因为id=11的binlog比数据拷贝来的早，中间表已经有id=11的数据了，插入忽略
	update t2 set name='lu' where id=10;	id=10的记录还未拷贝，update空记录	将ID=10的数据拷贝过来
	delete from t2 where id=10;	id=10的记录还未拷贝，delete空记录	查询不到id=10的记录，不拷贝
2->1->3	insert into t2(id,name) values(11,'lu');	insert ignore into t2(id,name) values(1,'a'),(2,'b'),(11,'lu');	因为id=11的记录已经拷贝了，binlog会采用replace覆盖
	update t2 set values='lu' where id=1;	将id=1的最新数据拷贝到中间表	再次执行update操作，数据不变
	delete from t2 where id=1;	数据已删除，复制空行	数据不存在，binlog执行无效果

从上表来看，无论按哪种顺序执行，最后结果都是一致的。

cut-over步骤

cut-over是用于完成原表和中间表的原子性切换，其原理在于lock table阻塞之后rename优先级总是高于DML，无论是否DML先执行。下面分析以下其执行步骤：

开启一个会话(session-1)创建_del表
执行lock table锁住原表和_del表的写入，DML被阻塞
开启一个新的会话(session-2)设置锁等待时间并执行rename
session-2被session-1锁定
session-1检查session-2在执行rename并请求MDL锁
session-1删除_del表，此时原表的DML依然被阻塞
session-1执行unlock table释放锁，rename优先执行切换操作，DML请求可以在新表上执行

看到这不禁想问：要是执行过程中失败了会发生什么呢？会不会出现数据错乱？答案是什么都不会发生，下面就分析一下可能的情况：

创建_del表失败：此时程序直接退出
加锁失败：程序直接退出，DML正常执行
session-1在session-2执行rename前异常：session-1持有的锁释放，DML正常执行，rename也会因为表存在而失败
session-1在sesison-2执行rename被阻塞时异常：释放锁，rename因表存在而失败
session-2异常：查询不到rename操作，释放锁
两个会话都异常：释放锁，rename取消

常用参数

参数	描述
–allow-on-master	如果需要直接在主库执行需要设置该参数
–max-load string	设置多个状态值，以逗号分隔，例如"Threads_running=100,Threads_connected"，当超过设置的值迁移则暂停等待
–critical-load string	设置多个状态值，以逗号分隔，超过设置的值则迁移直接停止并退出
–chunk-size int	每次从原表迭代迁移的数据行数，范围为100-100000，默认1000
–initially-drop-ghost-table	在本次操作前删除之前遗留ghost临时表
–initially-drop-old-table	在本次操作前删除之前遗留的old表
–initially-drop-socket-file	删除存在的socket文件
–ok-to-drop-table	DDL完成后自动删除old表
–panic-flag-file string	当指定该参数后，如果创建该文件，gh-ost立刻中断退出，不会清理产生的临时表和文件
–exact-rowcount	精确的统计表数据行数而不是预估，即使不准确只是影响进度的计算，实际copy行数是由最大值和最小值确定，与其无关
–serve-socket-file string	socket文件
–assume-rbr	显示告诉gh-ost日志格式是row格式，如果没有该参数，gh-ost每次都会设置row格式并重启复制
–assume-master-host	显示告诉gh-ost master地址，如果不提供，gh-ost会根据从库查到master

示例

单实例DDL则相当于主库DDL，需开启–allow-on-master参数和ROW模式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--max-lag-millis=1500 \
--user="dba" \
--password="Abcd123#" \
--host=10.0.139.163 \
--port=33006 \
--allow-on-master \
--database="test" \
--table="tab1" \
--verbose \
--alter="add column comm varchar(10)" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--exact-rowcount \
--concurrent-rowcount \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--initially-drop-ghost-table \
--initially-drop-old-table \
--initially-drop-socket-file \
--execute

主从环境DDL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--throttle-control-replicas="10.0.139.161,10.0.139.162" \
--max-lag-millis=1500 \
--user="dba" \
--password="Abcd123#" \
--host=10.0.139.163 \
--port=33006 \
--database="test" \
--table="tab1" \
--verbose \
--alter="add column comm varchar(10)" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--exact-rowcount \
--concurrent-rowcount \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--initially-drop-ghost-table \
--initially-drop-old-table \
--initially-drop-socket-file \
--execute

从库DDL测试

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


gh-ost \
--user="dba" \
--password="Abcd123#" \
--host=10.0.139.163 \
--port=33006 \
--test-on-replica \
--database="test" \
--table="tab1" \
--verbose \
--alter="eadd column comm varchar(10)" \
--initially-drop-ghost-table \
--initially-drop-old-table \
--max-load=Threads_running=30 \
--switch-to-rbr \
--chunk-size=500 \
--cut-over=default \
--exact-rowcount \
--concurrent-rowcount \
--serve-socket-file=/tmp/gh-ost.test.sock \
--initially-drop-ghost-table \
--initially-drop-old-table \
--initially-drop-socket-file \  
--execute

Tips：上述操作并不会删除临时表，可以手动删除

暂停任务

1

echo throttle | socat - /tmp/gh-ost.test.tab1.sock

恢复任务

1

echo no-throttle | socat - /tmp/gh-ost.test.tab1.sock

使用建议

仔细阅读相关参数说明
添加参数–test-on-replica尝试几次
对每个主迁移，首先发出一个noop空操作
通过–execute真实执行

pt-online-schema-change

pt-online-schema-change是percona toolkit里的一个工具，也是用于非阻塞式的ONLINE DDL操作。

工作原理

创建_new后缀的临时表，与原表结构一致
修改临时表表结构
在原表上创建update/delete/insert三种操作类型的触发器,update是由replace into代替
同步数据到新表，数据量太多会根据主键分段chunk插入
切换表
删除原表，删除触发器

限制

table需要有主键或者唯一键
table上不能存在其它触发器
添加not null的列需要设置默认值
删除外键约束时，外键约束名前面需要添加一个下划线，例如’_constraint_name'

常用参数

参数	说明
–user	数据库用户名
–password	数据库连接密码
–charset	连接后执行SET NAMES
–progress	复制数据时打印进度报告
–print	打印工具执行的SQL语句到标准输出
–[no]check-replication-filters	检查复制中是否设置了过滤条件，设置了程序则退出
–set-vars	设置mysql的变量值
–check-slave-lag	检查主从延迟
–max-load	每次chunk后，检查show global status，如果超过阈值则暂停。默认为Threads_running=25
–critical-load	每次chunk后，检查show global status，如果超过阈值则终止。默认为Threads_running=50
–execute	建立触发器，进行数据迁移变更。不加则只是检查后退出
–dry-run	只是创建和修改临时表，不创建触发器同步数据并切换
–max-lag	每个chunk拷贝完成后，会查看所有复制Slave的延迟情况，超过阈值则暂停。默认为1s
–check-interval	–max-lag 检查的睡眠时间
–recursion-method	发现从的方法，默认是show processlist，也可以设置host，不过从上要设置report_host
–check-slave-lag	指定从库的DSN连接，如果从库超过–max_lag的值，就会暂停操作
–alter-foreign-keys-method	处理原表的外键，引用到新表上。auto表示rebuild_constraints和drop_swap二选一；rebuild_constraints表示先删除外键约束再添加。如果外键表很大会长时间阻塞；drop_swap表示执行FOREIGN_KEY_CHECKS=0禁止外键约束，删除原表再重命名新表，但存在风险；none类似于drop_swap，但不会删除原表，并且外键关系会随着重命名转到原表上
–alter	DDL语句，不需要输出alter table，可用逗号分隔执行多个更改。需要注意的是：1.不能用rename重命名表；2.列不能以先删除再添加的方式重命名；3.如果加入的列非空且没有默认值，工具会失败；删除外键时需要指定名称_constraint_name，而不是constraint_name
D	数据库
t	数据表

示例

检查表上是否存在主键或非空唯一键

1
2
3
4
5
6


root@test 16:40:  show index from test.tab1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tab1  |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

检查是否有其它表外键引用该表，如果有则需要添加–alter-foreign-keys-method选项

1

root@test 16:40:  select * from information_schema.key_column_usage where referenced_table_schema='test' and referenced_table_name='tab1'\G

检查触发器，如果有则需要添加–preserve-triggers选项

1

sql> select * from information_schema.triggers where EVENT_OBJECT_SCHEMA='test' and EVENT_OBJECT_TABLE='tab1';

执行dry run

1
2
3
4
5
6
7
8


pt-online-schema-change --print --statistics \
--progress time,30 --preserve-triggers \
--user='dba' --pssword='Abcd123#' \
--alter='modify id (int 12) auto_increment' \
h=10.0.139.161,P=33006,D=test,t=tab1 \
--pause-file=/tmp/alter.txt --max_load=threads_running=100,threads_connected=200 \
--critical-load=threads_running=1000 --chunk-size=1000 \
--alter-foreign-keys-method auo --dry-run

执行修改

1
2
3
4
5
6
7
8


pt-online-schema-change --print --statistics \
--progress time,30 --preserve-triggers \
--user='dba' --pssword='Abcd123#' \
--alter='modify id (int 12) auto_increment' \
h=10.0.139.161,P=33006,D=test,t=tab1 \
--pause-file=/tmp/alter.txt --max_load=threads_running=100,threads_connected=200 \
--critical-load=threads_running=1000 --chunk-size=1000 \
--alter-foreign-keys-method auo execute

附录

如何选择DDL方式

在MySQL5.6之前还不支持ONLINE DDL，建议选择pt-osc或者gh-ost
在MySQL5.6及更高版本，对于不支持inplace的DDL，建议选择pt-osc或者gh-ost
针对不需要重建表的DDL，可以选择MySQL Online DDL
针对需要重建表的DDL，应该结合自身情况选择，例如表大小，能否接收从库延迟等，如果要求比较严格的话可以使用pt-osc或者gh-ost

参考链接