阅读量:0
MySQL Online DDL工具允许你在不中断服务的情况下执行DDL操作，如添加、删除或修改表结构。
MySQL Online DDL工具使用
在生产环境中，数据库表结构经常需要调整以适应业务需求的变化，传统的DDL（数据定义语言）操作会锁定表，导致服务中断，影响用户体验，为了解决这一问题，MySQL引入了Online DDL功能，允许在不阻塞表的情况下进行表结构修改，本文将详细介绍几种常见的MySQL Online DDL工具及其使用方法。
常见Online DDL工具
1、pt-online-schema-change：由Percona公司开发，支持MySQL 5.5及以上版本。
2、gh-ost：由GitHub开发，支持MySQL 5.6及以上版本。
3、NineData：SaaS模式，开箱即用，适配各主流云的MySQL实例并覆盖所有版本。
4、MySQL自带Online DDL：从MySQL 5.6开始引入，并在MySQL 8.0中进行了增强。
工具原理及限制
| 工具 | 原理 | 限制 |
| pt-online-schema-change | 创建与原表结构相同的空表，通过触发器同步数据，最后替换原表。 | 1. 原表需有主键或唯一索引。 2. 不能使用rename子句重命名表。 3. 列不能通过删除+添加的方式重命名。 4. 添加not null列必须指定默认值。 5. 删除外键约束需指定名称前加下划线。 |
| gh-ost | 创建影子表，通过BinLog Streamer读取binlog并应用到影子表，最后替换原表。 | 增量同步效率不高，但开销最小。 |
| NineData | SaaS模式，无需人工介入命令拼装，易用性高。 | 需付费使用。 |
| MySQL自带Online DDL | 根据不同算法（copy, inplace, instant）执行DDL操作。 | 1. 部分操作不支持Inplace方式。 2. 涉及主键和全文索引的操作可能需要重建表。 3. Convert字符集和Specify字符集操作不支持Inplace方式。 |
使用示例
1. pt-online-schema-change使用示例
-安装percona-toolkit yum install percona-toolkit -y -创建用户并授权 GRANT ... ON *.* TO 'ptosc'@'%'; -准备ALTER语句 ALTER TABLE sbtest1 MODIFY c VARCHAR(200) NOT NULL DEFAULT ''; -检查表结构和外键引用 DESC sbtest1; SELECT * FROM information_schema.key_column_usage WHERE ...; -执行DDL pt-online-schema-change --alter="ALTER TABLE sbtest1 MODIFY c VARCHAR(200) NOT NULL DEFAULT ''" --execute DSN
2. gh-ost使用示例
安装gh-ost wget https://github.com/github/gh-ost/releases/download/v1.0.1/gh-ost-v1.0.1-linux-amd64.tar.gz tar -xzf gh-ost-v1.0.1-linux-amd64.tar.gz cd gh-ost-v1.0.1-linux-amd64 执行DDL ./gh-ost --host=hostname --port=port --user=username --password=password --ddl="ALTER TABLE sbtest1 MODIFY c VARCHAR(200) NOT NULL DEFAULT ''" --execute
3. NineData使用示例
访问NineData官网，注册账号并登录，按照界面提示进行操作即可。
4. MySQL自带Online DDL使用示例
-使用inplace算法添加列 ALTER TABLE sbtest1 ADD COLUMN d INT ALGORITHM=INPLACE; -使用instant算法增加列（仅适用于MySQL 8.0.12及以上版本） ALTER TABLE sbtest1 ADD COLUMN e INT ALGORITHM=INSTANT;
性能对比
| 工具 | 易用性 | 版本支持 | 云适配 | 性能 |
| pt-osc | 中等 | 广泛 | 无 | 较好 |
| gh-ost | 中等 | 广泛 | 无 | 一般 |
| NineData | 高 | 广泛 | 有 | 优秀 |
| MySQL自带 | 高 | 内置 | 有 | 视具体操作而定 |
相关问题与解答
1.问题一：为什么在使用pt-online-schema-change时需要原表有主键或唯一索引？
答：因为pt-online-schema-change在执行过程中会创建一个DELETE触发器来更新新表，如果没有主键或唯一索引，就无法准确识别要删除的记录，从而导致数据不一致，如果必须在没有主键的表上使用该工具，可以通过在原表中的列上创建临时的主键或唯一索引来解决这个问题。
2.问题二：MySQL自带的Online DDL有哪些限制？
答：MySQL自带的Online DDL有以下限制：
1、创建普通索引：支持Inplace方式，不需要Copy Table，允许并发DML和查询。
2、创建全文索引：第一次创建需要Copy Table，后续创建支持Inplace方式，但不允许并发DML。
3、删除索引：支持Inplace方式，不需要Copy Table，允许并发DML和查询。
4、优化表：需要Copy Table，允许并发DML和查询（如果有全文索引则不支持algorithm=inplace选项）。
5、设置列默认值：支持Inplace方式，不需要Copy Table，允许并发DML和查询。
6、修改自增列值：支持Inplace方式，不需要Copy Table，允许并发DML和查询。
7、添加外键约束：支持Inplace方式，不需要Copy Table，允许并发DML和查询（需关闭foreign_key_checks）。
8、删除外键约束：支持Inplace方式，不需要Copy Table，允许并发DML和查询。
9、重命名列：支持Inplace方式（仅修改字段名称），不需要Copy Table，允许并发DML和查询。
10、添加列：需要Copy Table（除非是auto_increment列），允许并发DML和查询。
11、删除列：需要Copy Table（除非是auto_increment列），允许并发DML和查询。
12、修改各列顺序：需要Copy Table，允许并发DML和查询。
13、修改Row_Format属性：需要Copy Table，允许并发DML和查询。
14、修改Key_Block_Size属性：需要Copy Table，允许并发DML和查询。
15、设置列为空值Null：支持Inplace方式，不需要Copy Table，允许并发DML和查询（需设置SQL_MODE为STRICT_ALL_TABLES或STRICT_TRANS_TABLES）。
16、设置列不为空值NOT NULL：支持Inplace方式，不需要Copy Table，允许并发DML和查询（需设置SQL_MODE为STRICT_ALL_TABLES或STRICT_TRANS_TABLES，且列值中不能包含空值）。
17、修改列的数据类型：不支持Inplace方式，需要Copy Table，不允许并发DML，但允许并发查询。
18、添加主键：支持Inplace方式（除非涉及转换为NOT NULL的列），需要Copy Table，允许并发DML和查询。
19、删除主键：不支持Inplace方式，需要Copy Table，不允许并发DML和查询。
20、删除主键并添加新主键：支持Inplace方式（需在同一个Alter Table语句中完成），需要Copy Table，允许并发DML和查询。
21、转换字符集：不支持Inplace方式，需要Copy Table，不允许并发DML和查询。
22、指定字符集：不支持Inplace方式，需要Copy Table，不允许并发DML和查询。
