如何使用MySQL Online DDL工具来搭建数据库？

MySQL Online DDL工具是一种用于在线修改数据库表结构的工具，可以在不影响业务的情况下进行表结构的变更。

MySQL Online DDL工具的使用

MySQL数据库作为目前最流行的开源关系型数据库之一，广泛应用于各种互联网应用和企业系统中，随着业务需求的变化，数据库表结构常常需要进行调整，如添加或删除字段、修改索引等，传统的数据定义语言（DDL）操作会锁定表，导致在操作期间无法进行读写操作，从而影响业务的正常运行，为了解决这一问题，MySQL引入了在线DDL（OnLine DDL）功能，可以在不中断表或数据库的情况下进行DDL操作，本文将详细介绍MySQL原生Online DDL的历史演进、原理及使用方法，并介绍几种常用的Online DDL工具及其使用示例。

一、MySQL原生Online DDL的历史演进与原理

1. 历史演进

在MySQL 5.5版本中，Online DDL的实现方式如下：

按照原表的定义创建临时表。

对原表进行加写锁。

对新的临时表进行DDL操作。

将原表中的数据copy到临时表中。

释放原表的写锁。

将旧表删除，临时表重命名。

这种实现方式存在一些问题，如在进行数据copy的过程中会消耗大量的时间和存储空间，且在原表进行加锁时，业务会中断访问，为了解决这些问题，MySQL 5.6版本引入了新特性，Fast Index Create（FIC特性），支持更多的alter table语句来避免copy data，同时支持了在线上DDL的过程中不阻塞DML操作，MySQL 5.6还增加了一个参数innodb_online_alter_log_max_size，用于控制在线DDL操作时产生的日志大小，默认为128M，但在生产环境中可能需要调整为更大的值如512M以适应更大的DDL操作。

2. 原理

MySQL中的在线DDL功能允许在对表进行结构变更时，仍然能够进行读写操作，避免了整个表的锁定和阻塞，其核心原理包括以下几个步骤：

创建临时表：根据原表的结构创建一个空的临时表。

修改临时表结构：在临时表上执行DDL操作。

数据同步：通过触发器或其他机制将原表的数据同步到临时表中。

切换表名：将原表重命名为旧表名，临时表重命名为原表名。

删除旧表：删除重命名后的原表。

常用Online DDL工具及其使用

1. ptonlineschemachange

原理及限制

ptonlineschemachange是Percona公司开发的一种在线修改表结构的工具，其执行流程如下：

创建影子表：创建一个与原表结构相同的空表，表名是 _new 后缀。

修改表结构：修改步骤1创建的空表的表结构。

创建触发器：在原表上加三个触发器（delete/update/insert），用于copy数据过程中，将原表中要执行的语句在新表中执行。

数据同步：将原表数据以数据块（chunk）的形式copy到新表。

重命名表：rename原表为old表，并把新表rename为原表名，然后删除旧表。

删除触发器：删除触发器。

限制：

原表上要有primary key或unique index。

不能使用rename子句来重命名表。

列不能通过删除+添加的方式来重命名。

如果要添加的列是not null，则必须指定默认值。

删除外键约束时，外键约束名前面必须添加一个下划线 '_'。

使用示例

 安装percona toolkit yum install perconatoolkit y 执行ptonlineschemachange命令 ptonlineschemachange alter "MODIFY c varchar(200) not null default ''" D=test,t=sbtest1 criticalload Threads_running=50 maxload Threads_running=100 dryrun

2. ghost

原理：ghost是由GitHub开发提供的一种在线修改表结构的工具，其执行流程如下：

创建影子表：创建一个与原表结构相同的空表，表名是 _gho 后缀。

修改表结构：在影子表上执行DDL操作。

创建BinLog Streamer：模拟从库读取实例的binlog，应用增量操作到影子表中执行。

数据同步：根据主键或唯一键进行分片读取写入，循环直到全量数据读取同步完毕。

重命名表：影子表和源表相互转换表名（原子性，持有短暂时间的排他锁，阻塞DML）。

停止BinLog Streamer：删除被改名后的原表。

使用示例：

 安装ghost wget https://github.com/github/ghost/releases/download/v1.1.5/ghost1.1.51.x86_64.rpm yum y localinstall ghost1.1.51.x86_64.rpm 执行ghost命令 ghost user="gh_user" password="xxx" host=192.168.1.31 database="bbb" table="student" initiallydropoldtable alter="ADD COLUMN y1 varchar(10),add column y2 int not null default 0 comment 'test' "  execute

相关问答FAQs

1、问题一：为什么需要使用Online DDL工具？

解答：传统的DDL操作会锁定表，导致在操作期间无法进行读写操作，从而影响业务的正常运行，而Online DDL工具可以在不中断表或数据库的情况下进行DDL操作，大大提高了数据库管理的灵活性和效率。

2、问题二：如何选择适合自己的Online DDL工具？

解答：选择适合自己的Online DDL工具需要考虑以下几个因素：

易用性：是否易于安装和使用。

性能：在数据同步和DDL操作方面的性能如何。

兼容性：是否与自己的MySQL版本兼容。

云适配：是否支持主流云服务商的MySQL实例。

社区支持：是否有活跃的社区支持和维护。

MySQL Online DDL工具是提高数据库管理灵活性和效率的重要手段，通过了解MySQL原生Online DDL的历史演进、原理及使用方法，以及掌握常用的Online DDL工具如ptonlineschemachange和ghost的使用方法，可以更好地进行数据库结构的变更，确保业务的连续性和稳定性。