澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

不停机不停服务,在线添加字段

2019-08-10 作者:澳门新萄京官方网站   |   浏览(108)

摘  要:本文阐述了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吗
深圳@邱治军 mysql中文网群2015/5/29 10:14:02
没啊
mysql percona mariadb都支持
只要是mysql版本
这工具要维护表的时候最适用了
定期整理mysql innodb 表碎片

 

你刚才说在线加字段,需要有前提条件
前提条件是什么
深圳@邱治军 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,将这个表一周以前的数据备份到一个临时表,再删除这个表一周以前的数据。

而在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上时,对前端影响较小,当时遇到千万级别的表 就会影响前端应用对表的写操作。

在一个软件生命周期中,我们都知道,**前期的表结构设计是非常重要的**,因为当表数据量一上来后再进行表结构修改危险性比较大,而且要操作的时间也比较长。

二、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

 

 

注:如果对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测试(未测试)

本文由澳门新萄京官方网站发布于澳门新萄京官方网站,转载请注明出处:不停机不停服务,在线添加字段

关键词: