MYSQL：无锁变更工具Pt-online-schema-change

一、MySQL常用的无锁变更工具

1. Online Schema Change：Online Schema Change（OSC）工具是MySQL官方提供的一种无锁变更工具，它可以在不停止MySQL服务器的情况下对表结构进行修改。OSC利用了InnoDB存储引擎的特性，使用复制和重放日志的方式来实现无锁变更。

2. pt-online-schema-change：pt-online-schema-change是Percona Toolkit中的一个工具，它可以在不停止MySQL服务器的情况下对表结构进行修改。pt-online-schema-change使用了InnoDB存储引擎的特性来实现无锁变更。与OSC不同的是，pt-online-schema-change使用了一个代理表来实现表结构变更，而不是直接在原表上进行修改。

3. gh-ost：gh-ost是GitHub开源的一个工具，它可以在不停止MySQL服务器的情况下对表结构进行修改。gh-ost使用了InnoDB存储引擎的特性来实现无锁变更。与pt-online-schema-change不同的是，gh-ost使用了一个ghost表来实现表结构变更，而不是使用代理表。

4. Facebook OSC：Facebook OSC是Facebook开源的一个工具，它可以在不停止MySQL服务器的情况下对表结构进行修改。Facebook OSC使用了自己开发的存储引擎MyRocks来实现无锁变更。与其他工具不同的是，Facebook OSC可以在进行表结构变更的同时进行数据的转换、清理和处理。

二、Pt-online-schema-change原理

pt-online-schema-change 是一个用于在线更改 MySQL 表结构的工具，它是 Percona Toolkit 的一部分。它的原理是通过在线复制表数据，同时在新表上应用修改，从而避免了直接修改原始表结构导致的锁表和性能下降问题。以下是pt-online-schema-change的基本工作原理：

1. 创建一个与原表结构相同的新表，同时应用用户指定的表结构更改。

2. 在新表上创建触发器，将对原表的写操作（如 INSERT、UPDATE 和 DELETE）同步到新表上。

3. 逐步将原表的数据复制到新表，以便在新表上保持数据的一致性。

4. 数据复制完成后，将原表和新表互换，然后删除原表以及相关的触发器。

三、使用场景

pt-online-schema-change 在以下场景中特别有用：

1. 修改大型表的结构：对于包含数百万甚至数十亿行的大型表，直接修改表结构可能导致长时间的锁表和性能下降。pt-online-schema-change 通过在线方式避免了这些问题。

2. 避免业务中断：在需要修改生产环境数据库表结构时，pt-online-schema-change 可以在不影响业务正常运行的情况下进行表结构更改。

3. 兼容各种存储引擎：pt-online-schema-change 支持各种 MySQL 存储引擎，如 InnoDB 和 MyISAM。

四、使用示例

以下是一个使用 pt-online-schema-change 修改表结构的示例。

4.1 准备环境

首先，确保已经安装了 Percona Toolkit。如果没有安装，请参考 Percona Toolkit 官方文档 进行安装。

4.2 示例表结构

假设我们有一个名为 employees 的表，包含以下字段：

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

现在，我们需要添加一个新的字段 email 到 employees 表，并将其设置为唯一约束。

4.3 使用 pt-online-schema-change

添加新字段

运行以下命令以在线方式添加新字段：

pt-online-schema-change --alter "ADD COLUMN email VARCHAR(255) UNIQUE" D=my_database,t=employees --execute

上述命令中的参数：

• --alter：指定要执行的表结构更改。

• D=my_database：指定包含目标表的数据库名称。

• t=employees：指定要更改的表名称。

• --execute：执行表结构更改，而不仅仅是打印更改。

执行完成后，employees 表将包含新的 email 字段，并具有唯一约束。

修改字段

将表employees的comment字段的字符集修改为utf8mb4

 pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "modify column comment varchar(50) character set utf8mb4" --alter-foreign-keys-method=auto --charset=utf8 --execute

删除字段

 pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "drop column comment" --alter-foreign-keys-method=auto --charset=utf8 --execute

添加索引

 pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add index idx_fr_to_date(from_date,to_date)" --alter-foreign-keys-method=auto --charset=utf8 --execute

删除索引

 pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "drop index idx_fr_to_date" --alter-foreign-keys-method=auto --charset=utf8 --execute

删除外键

需要为外键指定名称为_forigen_key，因为在创建新表时候默认为新表上的外键创建这样的名称，如果没这样指定则无法删除。

 pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "drop foreign key _dept_emp_ibfk_1" --alter-foreign-keys-method=auto --charset=utf8 --execute

添加主键

使用选项--no-check-unique-key-change再次执行添加主键操作

pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees_ptosc --user=admin --ask-pass --alter "add primary key(emp_no)" --charset=utf8 --no-check-unique-key-change --charset=utf8 --execute

 

五、注意事项

在使用 pt-online-schema-change 时，需要注意以下几点：

1. 在执行过程中，避免对原表进行更改，否则可能导致数据不一致。

2. 确保在执行前进行充分的测试，以确保修改后的表结构符合预期。

3. 在执行过程中，可能会对数据库性能产生一定影响，因此最好在业务低峰期进行操作。

总之，pt-online-schema-change 是一个强大且灵活的工具，可以帮助您在不影响业务正常运行的情况下在线更改 MySQL 表结构。使用它时，请确保充分了解其工作原理和注意事项，以确保顺利完成表结构更改。