摘  要:正文演讲了MySQL
DDL 的难题现状、pt-online-schema-change的工作规律
,并实际利用pt-online-schema-change工具在线修改生产环境下1.6亿级数据表结构。

MySQL使用pt-online-change-schema工具在线修改1.6亿级数据表结构,ptonlinechangeschema

摘  要:正文演讲了MySQL
DDL 的难点现状、pt-online-schema-change的工作规律
,并实际利用pt-online-schema-change工具在线修改生产环境下1.6亿级数据表结构。

 

在3个软件生命周期中,大家都明白,**中期的表结构设计是那些重大的**,因为当表数据量一上来后再开展表结构修改危险性比较大,而且要操作的日子也正如长。

 

在小编插足的门类中,就曾碰着这么1个难点,首先上去查看了一晃该表的音讯,已有约2亿的数据量,而且每分钟还要并发写入4万条记下,而出于那几个表有八个字段中期规划过短,导致写入到数据库后,那一个字段的值就径直乱码。因为该表在生养条件下行使,影响到工作,需求立刻修改那个字段长度,并且修改该表结构时无法停服务。那么怎样消除那种难题呢?

摘  要:正文演讲了MySQL
DDL 的题材现状、pt-online-schema-change的办事原理
,并实际上行使pt-online-schema-change工具在线修改生产环境下1.6亿级数据表结构。

MySQL ddl 的标题现状(未测试)

 

 一、MySQL DDL 的题材现状

千帆竞发想了下,裁减这么些表的数据量再DDL,将这些表二五日以前的数据备份到一个一时表,再删除那个表七日此前的数码。

而在MySQL中在对表进行ddl时,会锁表,当表比较小比如小于1w条记下时,操作时间较短,对前者影响较小,当时碰到千万乃至上亿级级其他表(保留九日的数据量还有1.6亿),就会潜移默化前端采取对表的写操作。

因为近来InnoDB引擎是经过以下步骤来展开DDL的:

1
根据原始表(original_table)的表结构和DDL语句,新建3个不可知的权且表(tmp_table)

2 在原表上加write lock,阻塞全数更新操作(insert、delete、update等)

3 执行insert into tmp_table select * from original_table

4 rename original_table和tmp_table,最后drop original_table

澳门金沙国际,5 释放 write lock。

咱俩得以瞥见在InnoDB执行DDL的时候,原表是只能够读无法写的。为此 perconal
推出2个工具 pt-online-schema-change
,其性状是修改进度中不会造成读写阻塞。

     

 

在运行mysql数据库时,我们总会对数据表举办ddl
变更,修改添加字段大概索引,对于mysql 而已,ddl 明显是1个令全体MySQL
dba
诟病的一个效能,因为在MySQL中在对表进行ddl时,会锁表,当表相比小比如小于1w上时,对前者影响较小,当时遇见千万级其他表
就会影响前端选择对表的写操作。

在2个软件生命周期中,大家都知道,**最初的表结构设计是丰富关键的**,因为当表数据量一上来后再拓展表结构修改危险性相比大,而且要操作的时刻也比较长。

二、pt-online-schema-change介绍

【工具简介】

schema工具在线修改1,不停机不停服务。pt-osc模仿MySQL内部的改表格局开展改表,但整整改表进度是由此对原始表的正片来完毕的,即在改表进度中原始表不会被锁定,并不影响对该表的读写操作。

第1,osc创造与原始表相同的不分包数据的新表并根据要求举办表结构的改动,然后将原始表中的数据按chunk大小逐步拷贝到新表中,当拷贝完结后,会自行同时修改原始表和新表的名字并暗中同意将原始表删除

【工具安装及接纳】

参见下边下边那篇小说

linux下percona-toolkit工具包的设置和使用(超详细版)

【工作规律】

1 创制八个和您要推行 alter 操作的表结构同样的空表。如图:

说明:t_ad_req_log就是原表;

_t_ad_req_log_ol是旧表,这几个表是用来当你执行破产的时候,还原回来的原表结构;

_t_ad_req_log_new是新表,那么些表就是本次要修改的表。

 澳门金沙国际 1

 

2 执行表结构修改,然后从原表中的数据到copy到
表结构修改后的表(即_t_ad_req_log_new)

3 在原表上开创触发器将 copy
数据的经过中,在原表的更新操作更新到新表.

   注意:即使表中曾经定义了触发器那一个工具就不可以做事了。

4 copy 已毕之后,用rename table 新表代替原表,暗中认同删除原表。

 

修改的吩咐如下:

/usr/local/bin/pt-online-schema-change --user=用户名 --password=密码 --host=127.0.0.1 --port=端口号 --charset=utf8 --nodrop-old-table --alter="modify  media_code varchar(64) DEFAULT NULL COMMENT '当前视频编码' " D=ad_api,t=t_ad_req_log --exec

参数表明:

--user=用户名     指定用户名

--password=用户名     指定用户密码

--port=端口号     指定端口号

--charset=utf8   指定字符编码

--alter=    后面就是接需要修改的内容,比如上面表示的就是修改ad_api数据库t_ad_req_log表的media_code 字段长度为64位

 上面请看3个完完全全的图:

澳门金沙国际 2

 

 

注:假如对percona-toolkit工具安装及应用有问号的先查看下那两篇文章。

linux下percona-toolkit工具包的安装和动用(超详细版)

pt-online-schema-change解读

 

 

摘 要: 本文讲演了 MySQL DDL 的标题现状、 pt-online-schema-change
的…

在贰个软件生命周期中,大家都领悟,**最初的表结构设计是相当主要的**,因为当表数据量一上来后再拓展表结构修改危险性相比较大,而且要操作的时日也正如长。

时下InnoDB引擎是通过以下步骤来展开DDL的:

 

 

1
依照原始表(original_table)的表结构和DDL语句,新建多少个不可知的临时表(tmp_table)

在小编参与的种类中,就曾遇到这么一个标题,首先上去查看了须臾间该表的音讯,已有约2亿的数据量,而且每分钟还要并发写入4万条记下,而出于那么些表有二个字段先前时代安排过短,导致写入到数据库后,这么些字段的值就径直乱码。因为该表在生养环境下利用,影响到工作,须求登时修改那么些字段长度,并且修改该表结构时不大概停服务。那么怎么着化解那种题材吗?

在我出席的类型中,就曾遭逢那样二个难题,首先上去查看了一晃该表的新闻,已有约2亿的数据量,而且每分钟还要并发写入4万条记下,而出于那些表有八个字段前期规划过短,导致写入到数据库后,那个字段的值就径直乱码。因为该表在生养条件下利用,影响到工作,需求马上修改这一个字段长度,并且修改该表结构时不或许停服务。那么怎么样缓解那种难题呢?

2 在原表上加write lock,阻塞全数更新操作(insert、delete、update等)

 一、MySQL DDL 的难点现状

始发想了下,减弱那些表的数据量再DDL,将以此表七日在此以前的数据备份到三个权且表,再删除那些表八日在此之前的数目。

而在MySQL中在对表举行ddl时,会锁表,当表比较小比如小于1w条记下时,操作时间较短,对前者影响较小,当时赶上千万乃至上亿级级其他表(保留二十六日的数据量还有1.6亿),就会潜移默化前端采纳对表的写操作。

因为日前InnoDB引擎是经过以下步骤来进展DDL的:

1
依照原始表(original_table)的表结构和DDL语句,新建一个不可知的权且表(tmp_table)

2 在原表上加write lock,阻塞全部更新操作(insert、delete、update等)

3 执行insert into tmp_table select * from original_table

4 rename original_table和tmp_table,最后drop original_table

5 释放 write lock。

咱们得以瞥见在InnoDB执行DDL的时候,原表是不得不读不可以写的。为此 perconal
推出3个工具 pt-online-schema-change ,其特征是修改进程中不会招致读写阻塞。

     

 一、MySQL DDL 的难点现状

开始想了下,裁减这些表的数据量再DDL,将这一个表一周以前的数据备份到三个权且表,再删除那几个表5日在此以前的多寡。

而在MySQL中在对表进行ddl时,会锁表,当表相比小比如小于1w条记下时,操作时间较短,对前者影响较小,当时遇见千万甚至上亿级级其余表(保留一周的数据量还有1.6亿),就会潜移默化前端选拔对表的写操作。

因为方今InnoDB引擎是通过以下步骤来拓展DDL的:

1
根据原始表(original_table)的表结构和DDL语句,新建一个不可知的临时表(tmp_table)

2 在原表上加write lock,阻塞全数更新操作(insert、delete、update等)

3 执行insert into tmp_table select * from original_table

4 rename original_table和tmp_table,最后drop original_table

5 释放 write lock。

笔者们可以看见在InnoDB执行DDL的时候,原表是不得不读无法写的。为此 perconal
推出3个工具 pt-online-schema-change ,其特点是修改进度中不会促成读写阻塞。

     

3 执行insert into tmp_table select * from original_table

二、pt-online-schema-change介绍

【工具简介】

pt-osc模仿MySQL内部的改表格局开展改表,但一切改表进程是经过对原始表的正片来完结的,即在改表进度中原始表不会被锁定,并不影响对该表的读写操作。

首先,osc创制与原始表相同的不带有数据的新表并依照须要进行表结构的改动,然后将原始表中的数据按chunk大小逐步拷贝到新表中,当拷贝达成后,会自行同时修改原始表和新表的名字并暗许将原始表删除

【工具安装及应用】

参见下边上边那篇文章

linux下percona-toolkit工具包的设置和动用(超详细版)

【工作规律】

1 制造多个和你要举办 alter 操作的表结构同样的空表。如图:

说明:t_ad_req_log就是原表;

_t_ad_req_log_ol是旧表,这几个表是用来当你执行破产的时候,还原回来的原表结构;

_t_ad_req_log_new是新表,这些表就是本次要修改的表。

 澳门金沙国际 3

 

 

2 执行表结构修改,然后从原表中的数据到copy到
表结构修改后的表(即_t_ad_req_log_new)

3 在原表上创制触发器将 copy
数据的过程中,在原表的更新操作更新到新表.

   注意:尽管表中已经定义了触发器这一个工具就不大概工作了。

4 copy 达成之后,用rename table 新表代替原表,暗许删除原表。

 

修改的下令如下:

/usr/local/bin/pt-online-schema-change --user=用户名 --password=密码 --host=127.0.0.1 --port=端口号 --charset=utf8 --nodrop-old-table --alter="modify  media_code varchar(64) DEFAULT NULL COMMENT '当前视频编码' " D=ad_api,t=t_ad_req_log --exec

参数表明:

--user=用户名     指定用户名

--password=用户名     指定用户密码

--port=端口号     指定端口号

--charset=utf8   指定字符编码

--alter=    后面就是接需要修改的内容,比如上面表示的就是修改ad_api数据库t_ad_req_log表的media_code 字段长度为64位

 下边请看一个总体的图:

澳门金沙国际 4

 

 澳门金沙国际 5

 

 

注:如果对percona-toolkit工具安装及使用有疑问的先查看下那两篇文章。

linux下percona-toolkit工具包的安装和利用(超详细版)

pt-online-schema-change解读

 

 

二、pt-online-schema-change介绍

【工具简介】

pt-osc模仿MySQL内部的改表格局开展改表,但一切改表进程是经过对原始表的正片来形成的,即在改表进度中原始表不会被锁定,并不影响对该表的读写操作。

首先,osc创立与原始表相同的不带有数据的新表并依据须求举办表结构的修改,然后将原始表中的数据按chunk大小逐步拷贝到新表中,当拷贝达成后,会活动同时修改原始表和新表的名字并默认将原始表删除

【工具安装及利用】

参见下边下边那篇小说

linux下percona-toolkit工具包的设置和动用(超详细版)

【工作规律】

1 创设七个和你要举办 alter 操作的表结构同样的空表。如图:

说明:t_ad_req_log就是原表;

_t_ad_req_log_ol是旧表,这几个表是用来当你执行破产的时候,还原回来的原表结构;

_t_ad_req_log_new是新表,这么些表就是这一次要修改的表。

 澳门金沙国际 6

 

2 执行表结构修改,然后从原表中的数据到copy到
表结构修改后的表(即_t_ad_req_log_new)

3 在原表上创建触发器将 copy
数据的长河中,在原表的翻新操作更新到新表.

   注意:如若表中一度定义了触发器这一个工具就无法办事了。

4 copy 达成将来,用rename table 新表代替原表,专擅认同删除原表。

 

修改的通令如下:

/usr/local/bin/pt-online-schema-change --user=用户名 --password=密码 --host=127.0.0.1 --port=端口号 --charset=utf8 --nodrop-old-table --alter="modify  media_code varchar(64) DEFAULT NULL COMMENT '当前视频编码' " D=ad_api,t=t_ad_req_log --exec

参数表明:

--user=用户名     指定用户名

--password=用户名     指定用户密码

--port=端口号     指定端口号

--charset=utf8   指定字符编码

--alter=    后面就是接需要修改的内容,比如上面表示的就是修改ad_api数据库t_ad_req_log表的media_code 字段长度为64位

 上边请看一个完整的图:

澳门金沙国际 7

 

 

 

注:如果对percona-toolkit工具安装及运用有疑难的先查看下那两篇小说。

linux下percona-toolkit工具包的装置和采取(超详细版)

pt-online-schema-change解读

 

 

4 rename original_table和tmp_table,最后drop original_table

5 释放 write lock。

小编们可以瞥见在InnoDB执行DDL的时候,原表是不得不读不可以写的。为此 perconal
推出一个工具 pt-online-schema-change
,其特色是修改进度中不会促成读写阻塞。

行事原理:

比方表有外键,除非采取 –alter-foreign-keys-method
内定特定的值,否则工具不予执行。

1 创制2个和你要实施 alter 操作的表一样的空表结构。

2 执行表结构修改,然后从原表中的数据到copy到 表结构修改后的表,

3 在原表上成立触发器将 copy 数据的经过中,在原表的翻新操作 更新到新表.

   注意:如若表中曾经定义了触发器这么些工具就无法做事了。

4 copy 已毕之后,用rename table 新表代替原表,暗中认同删除原表。

 

用法介绍:

pt-online-schema-change [OPTIONS] DSN

options 可以自行查看 help,DNS
为您要操作的数据库和表。那里有七个参数需求介绍一下:

–dry-run 

 那一个参数不树立触发器,不拷贝数据,也不会交替原表。只是创造和改动新表。

–execute 

其一参数的效用和前面工作规律的介绍的同一,会确立触发器,来担保最新变更的多少会影响至新表。注意:借使不加这些参数,这么些工具会在实践一些检查后脱离。

 

借助条件

1操作的表必须有主键否则 报如下错误。

[root@rac1 bin]#  ./pt-online-schema-change -u root -h 10.250.7.50
 -p yang –alter=’add column vid int ‘ –execute D=houyi,t=ga      

Cannot connect to D=houyi,h=127.0.0.1,p=…,u=root

Cannot chunk the original table `houyi`.`ga`: There is no good index
and the table is oversized. at ./pt-online-schema-change line 5353.

 

测试例子:

1 添加字段

[root@rac1 bin]#  ./pt-online-schema-change -u root -h 10.250.7.50
 -p yang –alter=’add column vid int ‘ –execute D=houyi,t=ga  

Cannot connect to D=houyi,h=127.0.0.1,p=…,u=root

Operation, tries, wait:

  copy_rows, 10, 0.25

  create_triggers, 10, 1

  drop_triggers, 10, 1

  swap_tables, 10, 1

  update_foreign_keys, 10, 1

Altering `houyi`.`ga`…

Creating new table…

Created new table houyi._ga_new OK.

Altering new table…

Altered `houyi`.`_ga_new` OK.

Creating triggers…

Created triggers OK.

Copying approximately 746279 rows…

Copied rows OK.

Swapping tables…

Swapped original and new tables OK.

Dropping old table…

Dropped old table `houyi`.`_ga_old` OK.

Dropping triggers…

Dropped triggers OK.

Successfully altered `houyi`.`ga`.

2 添加索引

[root@rac1 bin]# ./pt-online-schema-change -u root -h 10.250.7.50  -p
yang –alter=’add key indx_vid(vid) ‘ –execute D=houyi,t=ga    

3 删除字段         

[root@rac1 bin]# ./pt-online-schema-change -u root -h 10.250.7.50  -p
yang –alter=’drop  column vid ‘ –execute D=houyi,t=ga             

详尽的教程请参见:


mysql 5.6 online ddl测试(未测试)

相关文章