Mysql DDL执行方式-pt-osc介绍 | 京东云技术团队

这篇具有很好参考价值的文章主要介绍了Mysql DDL执行方式-pt-osc介绍 | 京东云技术团队。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1 引言

大家好,接着上次和大家一起学习了《MySQL DDL执行方式-Online DDL介绍》,那么今天接着和大家一起学习另一种MySQL DDL执行方式之pt-soc

在MySQL使用过程中,根据业务的需求对表结构进行变更是个普遍的运维操作,这些称为DDL操作。常见的DDL操作有在表上增加新列或给某个列添加索引。

DDL定义:

Data Definition Language,即数据定义语言,那相关的定义操作就是DDL,包括:新建、修改、删除等;相关的命令有:CREATE,ALTER,DROP,TRUNCATE截断表内容(开发期,还是挺常用的),COMMENT 为数据字典添加备注。

注意:DDL操作是隐性提交的,不能rollback,一定要谨慎哦!

下图是执行方式的性能对比及说明:

Mysql DDL执行方式-pt-osc介绍 | 京东云技术团队

图1 易维平台说明图

下面本文将对DDL的执行工具之pt-osc进行简要介绍及分析。如有错误,还请各位大佬们批评指正。

2 介绍

pt-online-schema-change - ALTER tables without locking them.

pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.

pt-online-schema-change是Percona公司开发的一个非常好用的DDL工具,称为 pt-online-schema-change,是Percona-Toolkit工具集中的一个组件,很多DBA在使用Percona-Toolkit时第一个使用的工具就是它,同时也是使用最频繁的一个工具。它可以做到在修改表结构的同时(即进行DDL操作)不阻塞数据库表DML的进行,这样降低了对生产环境数据库的影响。在MySQL5.6之前是不支持Online DDL特性的,即使在添加二级索引的时候有FIC特性,但是在修改表字段的时候还是会有锁表并阻止表的DML操作,这样对于DBA来说是非常痛苦的,好在有pt-online-schema-change工具在没有Online DDL时解决了这一问题。

Percona 公司是成立于2006年,总部在美国北卡罗来纳的Raleigh。由 Peter Zaitsev 和 Vadim Tkachenko创立, 这家公司声称他们提供的软件都是免费的,他们的收入主要来与开源社区,企业的支持,以及使用他们软件的公司的支付他们提供support的费用。 而实际上这家公司"垄断"了业内最流行数据库支持类的软件,并且还开发了一些其他的与数据库相关的东西。

Percona-Toolkit工具集是Percona支持数据库人员用来执行各种MySQL、MongoDB和系统任务的高级命令行工具的集合,这些任务太难或太复杂而无法手动执行。这些工具是私有或“一次性”脚本的理想替代品,因为它们是经过专业开发、正式测试和完整记录的。它们也是完全独立的,因此安装快速简便,无需安装任何库。

Percona Toolkit 源自 Maatkit 和 Aspersa,这两个最著名的 MySQL 服务器管理工​具包。它由 Percona 开发和支持。

3 工作流程

pt-osc 用于修改表时不锁表,简单地说,这个工具创建一个与原始表一样的新的空表,并根据需要更改表结构,然后将原始表中的数据以小块形式复制到新表中,然后删除原始表,然后将新表重命名为原始名称。在复制过程中,对原始表的所有新的更改(insert,delete,update)都将应用于新表,因为在原始表上创建了一个触发器,以确保所有新的更改都将应用于新表。有关 pt-online-schema-change 工具的更多信息,请查阅手册文档 。

pt-osc大致的工作过程如下:

1.创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构);

2.在新表执行alter table 语句(速度应该很快);

3.在原表中创建触发器3个触发器分别对应insert,update,delete操作,如果表中已经定义了触发器这个工具就不能工作了;

4.以一定块大小从原表拷贝数据到临时表,拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表,保证数据不会丢失(会限制每次拷贝数据的行数以保证拷贝不会过多消耗服务器资源,采用 LOCK IN SHARE MODE 来获取要拷贝数据段的最新数据并对数据加共享锁阻止其他会话修改数据,不过每次加S锁的行数不多,很快就会被释放);

5.将原表Rename为old表,再把新表Rename为原表(整个过程只在rename表的时间会锁一下表,其他时候不锁表);

6.如果有参考该表的外键,根据alter-foreign-keys-method参数的值,检测外键相关的表,做相应设置的处理(根据修改后的数据,修改外键关联的子表),如果被修改表存在外键定义但没有使用
--alter-foreign-keys-method 指定特定的值,该工具不予执行;

7.默认最后将旧原表删除、触发器删除。

Mysql DDL执行方式-pt-osc介绍 | 京东云技术团队

图2 pt-osc工作过程示意图

4 用法

Percona Toolkit 是成熟的,但是官方还是建议在使用前做到以下几点:

•阅读该工具的详细文档

•查看该工具的已知“错误”

•在非生产服务器上测试该工具

•备份您的生产数据并验证备份

下载安装:

从官方网站下载percona-toolkit,然后执行下面的命令进行安装(示例):

# 安装依赖包
yum install perl-TermReadKey.x86_64 
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL


# 安装percona-toolkit
rpm -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm


执行类似下面的命令修改表结构:

pt-online-schema-change --alter="add column c1 int;" --execute D=test,t=table,u=user,p=password


alter参数指定修改表结构的语句,execute表示立即执行,D、t、u、p分别指定库名、表名、用户名和密码,执行期间不阻塞其它并行的DML语句。pt-online-schema-change还有许多选项,具体用法可以使用pt-online-schema-change --help查看联机帮助。

5 限制

pt-online-schema-change也存在一些局限性:

1.在使用此工具之前,应为表定义PRIMARY KEY或唯一索引,因为它是DELETE触发器所必需的;

2.如果表已经定义了触发器,则不支持 pt-osc ;(注:不是不能有任何触发器,只是不能有针对insert、update、delete的触发器存在,因为一个表上不能有两个相同类型的触发器);

3.如果表具有外键约束,需要使用选项
--alter-foreign-keys-method,如果被修改表存在外键定义但没有使用 --alter-foreign-keys-method 指定特定的值,该工具不予执行;

4.还是因为外键,对象名称可能会改变(indexes names 等);

5.在Galera集群环境中,不支持更改MyISAM表,系统变量 wsrep_OSU_method 必须设置为总序隔离(Total Order Isolation,TOI);

6.此工具仅适用于 MySQL 5.0.2 及更新版本(因为早期版本不支持触发器);

7.需要给执行的账户在 MySQL上授权,才能正确运行。(应在服务器上授予PROCESS、SUPER、REPLICATION SLAVE全局权限以及 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER 和 TRIGGER 表权限。Slave 只需要 REPLICATION SLAVE 和 REPLICATION CLIENT 权限。)

6 对比OnLine DDL

下面的表格是国外技术牛人进行的测试数据,是Online DDL和pt-osc对一个包含1,078,880行的表应用一些alter操作的对比结果,仅供参考:

online ddl pt-osc
更改操作 受影响的行 是否锁表 时间(秒) 受影响的行 是否锁表 时间(秒)
添加索引 0 3.76 所有行 38.12
下降指数 0 0.34 所有行 36.04
添加列 0 27.61 所有行 37.21
重命名列 0 0.06 所有行 34.16
重命名列更改其数据类型 所有行 30.21 所有行 34.23
删除列 0 22.41 所有行 31.57
更改表引擎 所有行 25.3 所有行 35.54

那么现在的问题是,我们应该使用哪种方法来执行alter语句呢?

虽然pt-osc允许对正在更改的表进行读写操作,但它仍然会在后台将表数据复制到临时表,这会增加MySQL服务器的开销。所以基本上,如果Online DDL不能有效工作,我们应该使用 pt-sc。换句话说,如果Online DDL需要将数据复制到临时表(algorithm=copy)并且该表将被长时间阻塞(lock=exclusive)或者在复制环境中更改大表时,我们应该使用 pt-osc工具。

pt-osc官方文档:
https://docs.percona.com/percona-toolkit/pt-online-schema-change.html

7 总结

本次和大家一起学习了解pt-online-schema-change工具,介绍了其产生的背景、基本工作流程、用法及相应的一些限制。还介绍了其与Online DDL执行方式的一些对比,如果错误还请指正。

目前可用的DDL操作工具包括pt-osc,github的gh-ost,以及MySQL提供的在线修改表结构命令Online DDL。pt-osc和gh-ost均采用拷表方式实现,即创建个空的新表,通过select+insert将旧表中的记录逐次读取并插入到新表中,不同之处在于处理DDL期间业务对表的DML操作。

到了MySQL 8.0 官方也对 DDL 的实现重新进行了设计,其中一个最大的改进是 DDL 操作支持了原子特性。另外,Online DDL 的 ALGORITHM 参数增加了一个新的选项:INSTANT,只需修改数据字典中的元数据,无需拷贝数据也无需重建表,同样也无需加排他 MDL 锁,原表数据也不受影响。整个 DDL 过程几乎是瞬间完成的,也不会阻塞 DML,不过目前8.0的INSTANT使用范围较小,后续再对8.0的INSTANT做详细介绍吧。

下一期文章将和大家一起学习、了解github的gh-ost,敬请期待哦!

作者:京东物流 刘邓忠

来源:京东云开发者社区文章来源地址https://www.toymoban.com/news/detail-464622.html

到了这里,关于Mysql DDL执行方式-pt-osc介绍 | 京东云技术团队的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 浅谈Mysql读写分离的坑以及应对的方案 | 京东云技术团队

    为什么我们要进行读写分离?个人觉得还是业务发展到一定的规模,驱动技术架构的改革,读写分离可以减轻单台服务器的压力,将读请求和写请求分流到不同的服务器,分摊单台服务的负载,提高可用性,提高读请求的性能。 上面这个图是一个基础的Mysql的主从架构,1主

    2024年02月10日
    浏览(48)
  • 基于mysql5.7制作自定义的docker镜像,适用于xxl-job依赖的数据库,自动执行初始化脚本(ddl语句和dml语句)

    xxl-job-admin依赖mysql数据库,且需执行初始化脚本,包括ddl和dml语句。 具体的步骤总结如下: 1、新建数据库xxl_job 2、创建mysql表table 3、执行dml语句,包括新建admin用户及密码,创建执行器和任务。 毫无疑问,人工每次去操作这些,不仅耗费人力和时间,还容易出错。 所以,本

    2024年02月11日
    浏览(164)
  • MySQL运维02-MySQL执行SQL文件的几种方式

    对于这种方式,还可以查看文件执行时的输出,有如下两种方法: 在MySQL服务器上执行SQL文件,一种方法是可以通过mysql -e或者直接登录mysql客户端后,通过source命令执行文件。另一种方法是将SQL文件作为mysql客户端的输入,即 mysql batch-file

    2024年02月11日
    浏览(70)
  • OSC_in 和 OSC_out 引脚 OSC32_IN和OSC32_OUT

    一般在单片机上都有 OSCin 和 OSCout 两个晶振接入端口 ,我们可以用无源的晶振加上起震电容可以配合单片机是晶振起震,为系统提供时钟源。 顾名思义在OSCout端可以输出正弦波,给别的器件提供振荡源,这个时候最好在两者之间加上一个56p左右的电容用来抑制频率的直流漂

    2024年02月11日
    浏览(33)
  • MySQL数据库归档工具之【pt-archiver】

    前言 当MySQL数据库积累了大量历史数据时,有效地管理这些数据变得至关重要。为了避免影响数据库性能,并确保其顺畅运行,我们可以利用 pt-archiver 工具来对历史数据进行归档。 什么是pt-archiver? pt-archiver是Percona Toolkit的一部分,是一个强大的MySQL数据归档工具。它能够帮

    2024年04月14日
    浏览(38)
  • 基于AIGC的京东购物助手的技术方案设想 | 京东云技术团队

    随着AIGC的爆火,ChatGPT,GPT-4的发布,我作为一个算法工作者,深感AI发展的迅猛。最近,OpenAI的插件和联网功能陆续向用户公开,我也在第一时间试用了这些最新的功能。在OpenAI的插件市场上,我被一个可以帮助分析食谱,并生成购物清单的功能所吸引。我开始思考,如果我

    2024年02月12日
    浏览(56)
  • 技术赋能-混流编排功能,助力京东618直播重保 | 京东云技术团队

    每每到618、双11这样的大型活动的时候,每天都有几个重要的大v或者品牌直播需要保障。 以往的重点场次监播方式是这么造的: 对每路直播的源流、各档转码流分别起一个ffplay播放窗口,再手动调整尺寸在显示器桌面进行布局,排到一屏里来监播。 这样做的缺点: 操作复杂

    2024年02月08日
    浏览(47)
  • MySQL DDL语法

    MySQL DDL(Data Definition Language)是用于定义和管理数据库结构的语言。它包括创建、修改和删除数据库、表、视图、索引和其他数据库对象的语句。DDL语法的重要性如下: 数据库结构定义:DDL语句用于创建和定义数据库对象的结构,例如创建表和定义字段、数据类型、约束和索

    2024年02月15日
    浏览(44)
  • MySQL DDL 通用语法

    🌹作者主页:青花锁 🌹简介:Java领域优质创作者🏆、Java微服务架构公号作者😄 🌹简历模板、学习资料、面试题库、技术互助 🌹文末获取联系方式 📝 专栏 描述 Java项目实战 介绍Java组件安装、使用;手写框架等 Aws服务器实战 Aws Linux服务器上操作nginx、git、JDK、Vue Jav

    2024年04月25日
    浏览(37)
  • MySQL操作DDL

    目录 1.概述 2.数据库的增删改查 3.表的增删改查 3.1.创建和查看表结构 3.2.修改表 3.3.查看所有的表 3.4.删除表 4.用户 5.DDL在实际应用场景中的作用 5.1.数据库设计 5.2.数据库维护 ​​​​​​​5.3.数据库迁移或重置 ​​​​​​​5.4.优化性能 ​​​​​​​5.5.小结      

    2024年04月12日
    浏览(37)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包