MYSQL能够那样改过亿级数据表结构
分类:pc28.am神测网

摘  要:本文解说了MySQL DDL 的标题现状、pt-online-schema-change的劳作规律,并实际应用pt-online-schema-change工具在线修改分娩条件下1.6亿级数据表结构。

MySQL ddl 的主题素材现状(未测量试验)

 

在运行mysql数据库时,大家总会对数据表实行ddl 改变,改进增加字段或者索引,对于mysql 而已,ddl 明显是三个令全体MySQL dba 诟病的八个效用,因为在MySQL中在对表举办ddl时,会锁表,当表相当小譬喻小于1w上时,对前面三个影响相当小,此时蒙受千万级其他表 就会潜濡默化前端采用对表的写操作。

在二个软件生命周期中,大家都清楚,**开始时代的表结构划虚构计是不行首要的**,因为当表数据量风度翩翩上来后再实行表结构改革危殆性相当大,何况要操作的光阴也比较长。

近些日子InnoDB引擎是经过以下步骤来张开DDL的:

 

1 遵照原始表(original_table卡塔 尔(英语:State of Qatar)的表结交涉DDL语句,新建二个不可以知道的有的时候表(tmp_table)

在作者参预的门类中,就曾蒙受那样二个难点,首先上去查看了一下该表的音讯,原来就有约2亿的数据量,何况每分钟还要并发写入4万条记下,而鉴于这一个表有一个字段早先时代布置过短,导致写入到数据库后,这么些字段的值就间接乱码。因为该表在生产境遇下使用,影响到事情,需求及时纠正那些字段长度,并且改过该表结构时不能够停服务。那么什么样化解这种难点啊?

2 在原表上加write lock,梗塞全部更新操作(insert、delete、update等卡塔 尔(英语:State of Qatar)

 一、MySQL DDL 的主题素材现状

开端想了下,减弱这些表的数据量再DDL,将以此表三十二十八日之前的数据备份到三个有时表,再删除那么些表七日早先的数据。

而在MySQL中在对表进行ddl时,会锁表,当表相当的小比方小于1w条记下时,操作时间很短,对前面二个影响十分的小,那时候遇到千万以至上亿级等第的表(保留一周的数据量还会有1.6亿卡塔尔国,就能影响前端采纳对表的写操作。

因为脚下InnoDB引擎是通过以下步骤来打开DDL的:

1 依据原始表(original_table卡塔尔国的表结交涉DDL语句,新建二个不可以知道的有的时候表(tmp_table)

2 在原表上加write lock,梗塞全部更新操作(insert、delete、update等卡塔 尔(英语:State of Qatar)

3 执行insert into tmp_table select * from original_table

4 rename original_table和tmp_table,最后drop original_table

5 释放 write lock。

我们得以望见在InnoDB推行DDL的时候,原表是只可以读不能够写的。为此 perconal 推出一个工具 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是新表,这几个表就是此次要校勘的表。

 图片 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位

 上面请看多少个完全的图:

图片 2

 

 图片 3

 

 

注:如果对percona-toolkit工具安装及接纳有疑点的先查看下这两篇作品。

linux下percona-toolkit工具包的安装和动用(超详细版卡塔 尔(英语:State of Qatar)

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 创设多少个和您要奉行 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测试(未测试)

本文由pc28.am发布于pc28.am神测网,转载请注明出处:MYSQL能够那样改过亿级数据表结构

上一篇:CMD命令大全 下一篇:没有了
猜你喜欢
热门排行
精彩图文
  • Shell系列教程之
    Shell系列教程之
      Shell协助自定义变量。 Linux Shell体系教程之(三卡塔 尔(英语:State of Qatar)Shell变量,linuxshell 本文是Linux Shell系列教程的第(三)篇,更多shell教程请看
  • 磁盘文件排序,提醒货仓损坏的解决办法
    磁盘文件排序,提醒货仓损坏的解决办法
    ......BITMAPINFOHEADER bi;bi.biSize = sizeof(BITMAPINFOHEADER);bi.biWidth = bmpScreen.bmWidth;bi.biHeight = bmpScreen.bmHeight;bi.biPlanes = 1;bi.biBitCount = bmpScreen.bmBitsPixel; bi.biCompression =
  • rm命令总计,要做手術
    rm命令总计,要做手術
     功能说明:删除文件、目录 测试删除测试删除测试删除测试删除测试删除测试删除测试删除测试删除测试删除测试删除测试删除测试删除测试删除测试删
  • mv命令总结,Linux基础知识之文件管理命令
    mv命令总结,Linux基础知识之文件管理命令
    cp命令使用: Linux基础知识之文件管理命令(cp、mv、rm) 我们日常工作中对于文件的操作用到最多的几个我觉得应该是是复制、剪切、移动、重命名、删除这
  • windows虚构内部存款和储蓄器机制,有相当多图
    windows虚构内部存款和储蓄器机制,有相当多图
    在windows系统中个,每一种进程具有协和独自的设想地址空间(Virtual AddressSpace)。那大器晚成地点空间的轻重与Computer硬件、操作系统以至应用程序都有涉及。