MySQL在线DDL工具 gh-ost的原理解析
简介
gh-ost是一个基于Percona OSC 原理的 MySQL online DDL 工具,它可以在线帮助MySQL用户快速升级表结构、移动数据,同时避免关闭业务及长时间占用MySQL资源等问题。此外,gh-ost的操作具有可回滚性,可以很方便的撤销变更操作。
原理
基本原理
gh-ost的原理与 Facebook 的 online schema change 工具有很多相似之处,大体上分为两个阶段:迁移阶段和同步阶段。
- 迁移阶段通过创建一个 gh-ost 的数据表
gh-ost
,并通过拷贝原表数据创建一个新表gh-ost<suffix>
(suffix
由当前时间戳和随机字符串组成),在新表中添加要修改的字段和索引,删除原表中的相应的字段和索引。 - 同步阶段通过 MySQL 的 Trigger 和 Binlog,将原表中的所有操作记录到
gh-ost
表中,然后通过 DML 语句(增、删、改语句)将这些操作同步到新表。
更多详细原理
gh-ost 的运行依赖于多个 MySQL 参数设置:
- 首先需要启用 MySQL 的 ROW 模式,以便使用 Trigger。
- 启用 binlog_row_image = FULL,以便在 DML 中包含所有数据。
- 设置 MySQL 参数 binlog_checksum=NONE,以避免在 binlog 传输过程中计算校验值,加快同步速度。
- 设置 max_triggers_per_row = 1000,以便每行数据最多放置 1000 个触发器。
- 可选设置,如果您需要将 gh-ost 运行到分布式系统上,则需要使用 MySQL Group Replication、MySQL Cluster 或 Galera Cluster。
在触发器的流程中,gh-ost 在原表添加触发器,用于捕获所有表的 DML 操作,然后将捕获到的数据记录下来写入到其记录表中。在触发器的 On Update 和 On Delete 事件中,gh-ost 需要把记录写入到两个表,分别是记录表和新表,On Insert 事件就只需要写入到新表。这样,新表的数据和原表保持同步。
一个大表修改结构批量操作时需要注意:
- 对于大表,gh-ost 可以通过
chunk-index
参数来控制每个事务处理的数据量,以优化性能。 - 在表上建立多个(>1)异步索引会影响gh-ost的性能,因此推荐在上一个后删除现有索引,然后添加新的索引。
- 表上存在外键约束和触发器时,需要先将它们禁用,等结构变更完成后再启用,否则可能会执行失败。
示例
下面我们看两个使用gh-ost更改表结构的示例:
示例1:增加字段
比如原有表结构如下:
-- 表结构
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
-- 插入数据
INSERT INTO `test` (`id`, `name`) VALUES (1, 'xiaoming');
现在我们需要在该表中增加一个收入字段income
,可以使用 gh-ost 实现在线修改:
$ gh-ost --user=root --host=127.0.0.1 --database=test \
--table=test --alter="ADD COLUMN income INT(11)" \
--concurrency=16 --throttle=200 --approve-renamed-columns=false \
--chunk-size=1000 --max-load=Threads_running=25
示例2:修改字段名
比如原有表结构如下:
-- 表结构
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
-- 插入数据
INSERT INTO `test` (`id`, `name`) VALUES (1, 'xiaoming');
现在我们需要将该表中原有的字段 name
修改为 username
,可以使用 gh-ost 实现在线修改:
$ gh-ost --user=root --host=127.0.0.1 --database=test \
--table=test --alter="CHANGE COLUMN name username varchar(20) NOT NULL" \
--concurrency=16 --throttle=200 --approve-renamed-columns=false \
--chunk-size=1000 --max-load=Threads_running=25
总结
gh-ost 可以帮助 MySQL 用户更加方便和快速地修改数据库表结构,同时自带了可回滚性,非常适合大表在线修改,避免了关闭业务、占用 MySQL 资源等问题。但对于不是特别关注在线迁移的用户,可以使用 Percona Toolkit 的 pt-online-schema-change 工具或者 MySQL 官方的 ALTER TABLE 操作,二者在效率上略逊于 gh-ost,但也可以满足大多数用户的需求。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:MySQL在线DDL工具 gh-ost的原理解析 - Python技术站