您的位置:新葡亰496net > 电脑系统 > MYSQL可以这样修改亿级数据表结构,6亿级数据表

MYSQL可以这样修改亿级数据表结构,6亿级数据表

发布时间:2019-07-07 22:00编辑:电脑系统浏览(105)

    摘  要:本文演讲了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亿级数据表结构。

     

    在贰个软件生命周期中,大家都清楚,**早期的表结构划虚拟计是可怜主要的**,因为当表数据量一上来后再实行表结构修改造危房慢性非常大,并且要操作的时辰也比较长。

     

    在作者加入的门类中,就曾遭逢这么三个难点,首先上去查看了须臾间该表的新闻,已有约2亿的数据量,何况每分钟还要并发写入4万条记下,而鉴于这么些表有三个字段先前时代规划过短,导致写入到数据库后,那些字段的值就直接乱码。因为该表在生育条件下采纳,影响到业务,须求马上修改这几个字段长度,並且修改该表结构时不能够停服务。那么如何消除这种主题材料吧?

    摘  要:正文解说了MySQL DDL 的标题现状、pt-online-schema-change的劳作原理,并实际应用pt-online-schema-change工具在线修改生产意况下1.6亿级数据表结构。

    MySQL ddl 的难点现状(未测验)

    MySQL5.6在线DDL不锁表(在线加多字段)

    解答你也看一下
    MySQL5.6在线DDL不锁表,未来自个儿有一张1亿的表,须求追加二个字段,若是自身令你去充实那一个字段,你应当小心怎样,具体怎么操作?

    操作如下:
    1.在意磁盘空间(不常表目录 参数 tmpdir ,因为急需创建有时表使用 algorithm=default,inplace,copy copy是用有时表的章程

    lock=default,none,shared,exclusive)
    2.当下内部存款和储蓄器剩余量
    3.当下有未有大的业务在实践
    4.innodb_online_alter_log_max_size参数
    5.然后在从上添加,再在主上增多(不记录binlog),管理完了后再展开

    设若直接先在主上操作,那么会促成基本延迟一点都不小(在量十分大的处境下)。因为主从复制,从库的SQL线程是单线程,它接受到主库的BINLOG,要一条一条施行,一条SQL卡住了,后边的SQL就能排队等待,那时一道就延迟了

     

    附注:

    在事先的版本,InnoDB引擎是因而以下步骤来开始展览DDL的:
    1 根据原始表(original_table)的表结构和DDL语句,新建叁个不可知的有时表(tmp_table)----> create table tmp_table like original_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。

    假使利用第三方工具推荐应用pt-online-schema-change

     

    表上不能够有任何的外键和触发器,不然无法在线DDL

    OSC为facebook的php脚本,用来mysql 在线DDL

    触发器将要线DDL时期的DML操作记录到缓存日志rowlog中,到copy的最终redo那一个DML日志到表上,完成数据一致

     

     

    pt-osc专门的学业规律:
    假使表有外键,除非采取 --alter-foreign-keys-method 钦定特定的值,否则工具不予实行。
    1 判别种种参数,是还是不是适合操作准则
    2 成立二个和原表一样的空表结构(tmp_table)
    3 实行表结构修改,然后从原表中copy数据到上边创立的新表中
    4 在原表上创造3个触发器(名称格式为pt_osc_库名_表名_操作类型),当copy 数据的经过中,把原表的更新操作更新到新表 (注意:假使表中早已定义了触发器这么些工具就无法源办公室事了)
    CREATE TRIGGER `pt_osc_dba_t_del` AFTER DELETE ON `dba`.`t` FOR EACH ROW DELETE IGNORE FROM `dba`.`_t_new` WHERE `dba`.`_t_new`.`id` <=> OLD.`id`
    CREATE TRIGGER `pt_osc_dba_t_upd` AFTER UPDATE ON `dba`.`t` FOR EACH ROW REPLACE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) VALUES (NEW.`id`, NEW.`a`, NEW.`b`, NEW.`c1`)
    CREATE TRIGGER `pt_osc_dba_t_ins` AFTER INSERT ON `dba`.`t` FOR EACH ROW REPLACE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) VALUES (NEW.`id`, NEW.`a`, NEW.`b`, NEW.`c1`)
    5 copy 完毕之后,用rename table 新表代替原表,私下认可删除原表 (可以钦命参数不删除原表操作)
    6 去除触发器
    此间要细心的是。。如果是小表。。直接在主上加多了
    深圳@邱治军 mysql 中文网 9:46:41

    大表要整合职业和岁月点思考是还是不是在主上如故先在从上加多

     

     那些工具只可以用在percona DB吗
    柏林(Berlin)@邱治军 mysql中文网群二零一四/5/29 10:14:02
    没啊
    mysql percona mariadb都支持
    只要是mysql版本
    那工具要维护表的时候最适用了
    定时整理mysql innodb 表碎片

     

    你刚才说在线加字段,必要有前提条件
    前提条件是怎么
    柏林(Berlin)@邱治军 mysql汉语网群  10:29:32
    没啥
    丰裕主键要小心一下
    一般说来字段没啥

     

     

    基本加字段方法 邱治军 在从库上增加,再在主库上增多
    1、在从库设置stop slave; set  sql_log_bin=0;
    2、在从库增多字段,不要用pt-osc,从库不能够创设触发器,直接加字段就行alter table add column
    3、在从库设置start slave; set  sql_log_bin=1;
    4、用mha做在线主从切换
    5、在主库设置set  sql_log_bin=0;stop slave;
    6、主库加字段  ,不要用pt-osc,从库不可能创设触发器,直接加字段就行alter table add column
    7、在主库设置start slave; set  sql_log_bin=1;

     

     一、MySQL DDL 的主题素材现状

    开班想了下,收缩那一个表的数据量再DDL,将这些表二31日在此以前的数据备份到叁个不常表,再删除那么些表十13日从前的数量。

    而在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 推出贰个工具 pt-online-schema-change ,其特色是修改进度中不会招致读写阻塞。

         

     

    在运行mysql数据库时,大家总会对数据表进行ddl 改变,修改加多字段也许索引,对于mysql 而已,ddl 鲜明是多个令全部MySQL dba 诟病的三个作用,因为在MySQL中在对表实行ddl时,会锁表,当表非常小比方小于1w上时,对前面三个影响十分小,当时蒙受千万级其他表 就能潜濡默化前端采纳对表的写操作。

    在八个软件生命周期中,我们都清楚,**MYSQL可以这样修改亿级数据表结构,6亿级数据表结构。开始时期的表结构设计是不行主要的**,因为当表数据量一上来后再进行表结构修改危急性异常的大,何况要操作的年月也正如长。

    二、pt-online-schema-change介绍

    【工具简单介绍】

    MYSQL可以这样修改亿级数据表结构,6亿级数据表结构。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

     

     

    注:要是对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 推出三个工具 pt-online-schema-change ,其特征是修改进程中不会促成读写阻塞。

         

     一、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 推出叁个工具 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 创立叁个和你要奉行 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测试(未测试)

    本文由新葡亰496net发布于电脑系统,转载请注明出处:MYSQL可以这样修改亿级数据表结构,6亿级数据表

    关键词: