Py学习  »  DATABASE

MySQL:无锁变更工具pt-online-schema-change

IT大咖说 • 9 月前 • 272 次点击  


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

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

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

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

  • 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的基本工作原理:

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

  • 在新表上创建触发器,将对原表的写操作(如 INSERT、UPDATE 和 DELETE)同步到新表上。

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

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

◆ 三、使用场景

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

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

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

  • 兼容各种存储引擎: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 时,需要注意以下几点:

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

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

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

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


来源https://www.toutiao.com/article/7240294207251219000/?log_from=acc5367a37d24_1685929491170

“IT大咖说”欢迎广大技术人员投稿,投稿邮箱:aliang@itdks.com





来都来了,走啥走,留个言呗~




 IT大咖说  |  关于版权 

由“IT大咖说(ID:itdakashuo)”原创的文章,转载时请注明作者、出处及微信公众号。投稿、约稿、转载请加微信:ITDKS10(备注:投稿),茉莉小姐姐会及时与您联系!

感谢您对IT大咖说的热心支持!


相关推荐


推荐文章

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/156006
 
272 次点击