pt-online-schema-change

原创 2016年08月30日 18:01:33
安装
[root@slave159 opt]# wget percona.com/get/percona-toolkit.tar.gz
[root@slave159 opt]# cd percona-toolkit-2.2.15/
[root@slave159 percona-toolkit-2.2.15]# perl Makefile.PL
[root@slave159 percona-toolkit-2.2.15]# make && make instakll

原理
pt-online-schema-change模拟了MySQL内部alter table的方式,但是其操作所更新的是复制表,所以原表不会被锁住。其原理我们通过示例进行解读:
shell> pt-online-schema-change –nocheck-replication-filters –recursion-method=none –alter “add newcol int” h=192.168.10.118,P=3306,u=sup,p=xxxx,D=dbteamdb,t=user –alter-foreign-keys-method rebuild_constraints –print –statistics –execute
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
Child tables:
`dbteamdb`.`user_privs` (approx. 1 rows)
Will use the rebuild_constraints method to update foreign keys.
Altering `dbteamdb`.`user`…
Creating new table…

#步骤1,创建空表,其命名规则是_+原表名+_new

CREATE TABLE `dbteamdb`.`_user_new` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `host` varchar(20) COLLATE utf8_bin NOT NULL,
  `eng_name` varchar(20) COLLATE utf8_bin NOT NULL,
  `chn_name` varchar(20) COLLATE utf8_bin NOT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `status` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user__host` (`host`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
Created new table dbteamdb._user_new OK.
Altering new table…

#步骤2,根据语句更新新表结构
ALTER TABLE `dbteamdb`.`_user_new` add newcol int
Altered `dbteamdb`.`_user_new` OK.
Creating triggers…

#步骤3,在原表上创建触发器,以便后续原表上的操作同步到新表

CREATE TRIGGER `pt_osc_dbteamdb_user_del` AFTER DELETE ON `dbteamdb`.`user` FOR EACH ROW DELETE IGNORE FROM `dbteamdb`.`_user_new` WHERE `dbteamdb`.`_user_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_dbteamdb_user_upd` AFTER UPDATE ON `dbteamdb`.`user` FOR EACH ROW REPLACE INTO `dbteamdb`.`_user_new` (`id`, `host`, `eng_name`, `chn_name`, `create_time`, `update_time`, `status`) VALUES (NEW.`id`, NEW.`host`, NEW.`eng_name`, NEW.`chn_name`, NEW.`create_time`, NEW.`update_time`, NEW.`status`)
CREATE TRIGGER `pt_osc_dbteamdb_user_ins` AFTER INSERT ON `dbteamdb`.`user` FOR EACH ROW REPLACE INTO `dbteamdb`.`_user_new` (`id`, `host`, `eng_name`, `chn_name`, `create_time`, `update_time`, `status`) VALUES (NEW.`id`, NEW.`host`, NEW.`eng_name`, NEW.`chn_name`, NEW.`create_time`, NEW.`update_time`, NEW.`status`)
Created triggers OK.
Copying approximately 44 rows…

#步骤4,拷贝原表数据到新表,数据量大时会根据主键进行分段chunk插入

INSERT LOW_PRIORITY IGNORE INTO `dbteamdb`.`_user_new` (`id`, `host`, `eng_name`, `chn_name`, `create_time`, `update_time`, `status`) SELECT `id`, `host`, `eng_name`, `chn_name`, `create_time`, `update_time`, `status` FROM `dbteamdb`.`user` LOCK IN SHARE MODE /*pt-online-schema-change 9378 copy table*/
Copied rows OK.
Swapping tables…

#步骤5,拷贝完成后,移走原表,用新表代替(RENAME TABLE)。其通过一个RENAME TABLE同时处理两个表,实现原子操作。

RENAME TABLE `dbteamdb`.`user` TO `dbteamdb`.`_user_old`, `dbteamdb`.`_user_new` TO `dbteamdb`.`user`
Swapped original and new tables OK.
Rebuilding foreign key constraints…

#如果更新的表是被子表外键引用的父表,那么需要相应的更新子表的外键指向。具体见下文”外键的影响”。
ALTER TABLE `dbteamdb`.`user_privs` DROP FOREIGN KEY `fk_user_privs__user_id`, ADD CONSTRAINT `_fk_user_privs__user_id` FOREIGN KEY (`user_id`) REFERENCES `dbteamdb`.`user` (`id`)
Rebuilt foreign key constraints OK.

#最后清除临时生成的表、触发器。默认情况下会删除原表(此时是已经重命名过的_user_old)。

Dropping old table…
DROP TABLE IF EXISTS `dbteamdb`.`_user_old`
Dropped old table `dbteamdb`.`_user_old` OK.
Dropping triggers…
DROP TRIGGER IF EXISTS `dbteamdb`.`pt_osc_dbteamdb_user_del`;
DROP TRIGGER IF EXISTS `dbteamdb`.`pt_osc_dbteamdb_user_upd`;
DROP TRIGGER IF EXISTS `dbteamdb`.`pt_osc_dbteamdb_user_ins`;
Dropped triggers OK.
# Event              Count
# ================== =====
# INSERT                 1
# rebuilt_constraint     1
Successfully altered `dbteamdb`.`user`.
注意:如果原表上已经有触发器,那么将无法使用本工具!
性能
数据的拷贝是基于小块(根据chunk-time参数指定)进行的,而且根据主键或者索引进行选择,所以对整体服务器性能影响较小。

相关选项:
–chunk-time <float> 默认0.5。根据每秒拷贝的记录数,动态调整每次chunk size,以便压力变化时能够开始适应,使得每次拷贝消耗这里的指定长时间。
而且,如果检测到服务器压力大,则会暂停或者退出,详见–max-load和–critical-load选项。
安全
有诸多措施来保障安全使用该工具:
只有指定了–execute才会真正执行
如果工具检测到服务器选项中有任何复制相关的筛选,如指定binlog_ignore_db和replicate_do_db此类。发现有这样的筛选,工具会报错且退出。其原因是,如果更新的表Master上存在,而Slave上不存在,会导致复制的失败。使用–no-check-replication-filters选项来禁用该检查。
如果复制环境中,有延迟,会暂停数据拷贝动作,详见–max-laq选项。
默认设置了–lock-wait-timeout为1秒,这样如果有锁等待,尽量自己先退出,不干扰其他事务。详见–lock-wait-timeout
如果被外键引用,工具不会执行操作,除非指定–alter-foreign-keys-method选项。
外键的影响(外键相关基本只涉及到InnoDB引擎)
外键使得以上操作变得更加复杂,如果有外键引用到要变更的表,那么就无法自动RENAME,因为外键还是引用到老表。工具必须更新外键引用到新表。其提供两种方式来实现这个功能(–alter-foreign-keys-method)。

相关选项:
–alter-foreign-keys-method “<string>”  设定该表被外键引用时该如何处理。因为涉及到RENAME,而引用它的表会跟随它,而不会自动切换到新表,所以需要将外键切到新表。可选值:
rebuild_constraints 使用ALTER TABLE将删除重建引用到更新表的子表的相应外键。但是如果子表很大的话,就不合适了。

drop_swap 禁用外键检测(foreign_key_checks=0),然后在新表替代旧表前,直接drop掉新表,这与默认的方式不同。默认情况下,使用RENAME TABLE操作两张表,是原子操作,对于客户端来说是透明的;而启用这个选项,是先DROP老表,然后RENAME TABLE将新表重命名为老表,这种情况下,由于不是原子操作,在两个操作的间隙表就不存在了,会导致针对该表的操作错误!而且,如果RENAME操作失败,那么也无法回滚!

auto 自动选择。其倾向于使用rebuild_constraints,如果预估子表的更新时间小于–chunk-time则选择rebuild_constraints,反之则选drop_swap。

none 会对原表RENAME成_TAB_old,然后set foreign_key_checks=0,再drop _TAB_old。这样就会导致子表依赖的父表被删除!这样后续需要DBA参与处理。
外键还有一个问题,就是新建表的外键名称和旧表的外键名称重复,工具保证最终表与原表有同样的外键和索引,但是名称可能会稍有不同,会添加前缀下划线。

输出
本工具会活动信息输出到标准输出STDOUT。在数据拷贝阶段,会打印–progress报告到STDERR。指定–print可以得到额外的信息。如果指定–statistics,会进一步打印出内部事件的数目。

其他选项说明
--max-lag <time> 默认为1s。每个chunk拷贝完成后,这个工具会查看所有复制Slave的延迟情况。其方式是连接到Slave,检查其Seconds_Behind_Master。如果有一个Slave延迟超过max-lag,则工具会睡眠–check-interval秒然后再检查。至于如何查询所有Slave,则根据–recursion-method(默认通过show processlist,可以指定none来不检查Slave)。

--max-load <array> 默认为Threads_running=25。每个chunk拷贝完后,会检查SHOW GLOBAL STATUS的内容,检查指标是否超过了指定的阈值。如果超过,则先暂停。这里可以用逗号分隔,指定多个条件,每个条件格式: status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE,那么工具会这只其为当前值的120%。

--critical-load  <array> 默认为Threads_running=50。用法基本与–max-load类似,如果不指定MAX_VALUE,那么工具会这只其为当前值的200%。如果超过指定值,则工具直接退出,而不是暂停。

--lock-wait-timeout <整型> 默认为1,即会话innodb_lock_wait_timeout的值为1秒。这个选项只针对InnoDB引擎。

--alter <string> 结构变更语句,不需要alter table关键字。可以指定多个更改,用逗号分隔。如下场景,需要注意:
不能用RENAME来重命名表。        * 列不能通过先删除,再添加的方式进行重命名,不会将数据拷贝到新列。
如果加入的列非空而且没有默认值,则工具会失败。即其不会为你设置一个默认值,必须显示指定。
删除外键(drop foreign key constrain_name)时,需要指定名称_constraint_name,而不是原始的constraint_name。
–dry-run 并不真正执行,可以看到生成的执行语句,了解其执行步骤与细节
–execute 真正执行。–dry-run与–execute必须指定一个
–[no]drop-old-table   删除RENAME后的原表。默认是yes。

典型用法

1)添加一列,并不真正执行
pt-online-schema-change –alter “add column c1 int” D=mydb,t=mytable –dry-run
2)更新存储引擎为InnoDB,不删除原表
pt-online-schema-change –alter “ENGINE=InnoDB” –no-drop-old-table –print –statistics –execute D=mydb,t=mytable –execute
3)复制环境下,忽略日志筛选和Slave复制延迟,删除表字段
pt-online-schema-change –no-check-replication-filters –recursion-method=none –alter “drop company_type,drop channel_code” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –print –statistics –execute
4)更新被子表引用到的父表
pt-online-schema-change –alter “add newcol int” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –alter-foreign-keys-method auto –print –statistics –execute
5)在我们的双主复制环境中,设定了忽略mysql库的复制,不是很在乎复制的延迟,有时有外键影响,希望尽量保留原表数据,必要时自行删除。
pt-online-schema-change –no-check-replication-filters –recursion-method=none –alter “drop newcol” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –alter-foreign-keys-method auto –no-drop-old-table –print –statistics –execute


限制:
目标表必须有主见







相关文章推荐

MySQL在线DDL工具pt-online-schema-change

mysql5.6中虽然引进了online DDL的功能,但是因为支持不够全面而且部分操作还是会阻塞DML语句,所以OSC还是相当有必要的 mysql online DDL原理:http://...

percona-toolkit之pt-online-schema-change(在线更改表结构)

传统方法修改表结构 类似alter table xx modify,在修改表结构时需要锁表,如果表很大,则操作时间会较长。目前,绝大多数业务要求24*7无间断服务,而此过程中,如果造成较长时间数据库无...

Mysql之pt-online-schema-change在线更新大表加索引

由于目前生产环境中大表比较多,这里选择了一个600M的小表做一次在线测试。 环境:Ubuntu 12.04 mysql版本 :5.6.29-log 表引擎:Innodb 一、pt-on...
  • wlzjsj
  • wlzjsj
  • 2016年05月30日 15:06
  • 2984

在线更改MySQL表结构工具pt-online-schema-change

总所周知,数据库最昂贵的操作莫过于DDL操作,因为MySQL在修改表期间会阻塞任何读写操作的,基本上你的业务出于瘫痪状态了。对于庞大的可能历时好几个小时才完成,简直就是个恶梦,没法容忍的操作。 Per...

pt-online-schema-change使用中的不当,引起的数据库不可写入问题

战战兢兢,如履薄冰这次不是pt-online-schema-change的锅,主要责任在我。背景5000万行表加字段操作,数据量30G,有主键,dba是一般的操作账号,除delete,drop,gra...

percona-toolkit 之 【pt-online-schema-change】说明

背景:       MySQL 大字段的DDL操作:加减字段、索引、修改字段属性等,在5.1之前都是非常耗时耗力的,特别是会对MySQL服务产生影响。在5.1之后随着Plugin Innodb的...

[MySQL] 表在线重定义 - pt-online-schema-change

MySQL不像Oracle本身就支持表的在线重定义,但我们可以通过开源软件percona-toolkit中的工具pt-online-schema-change进行在线重定义。 pt-online-sc...

pt-online-schema-change 过程详解

MySQL 大字段的DDL操作:加字段、索引等,在5.1之前都是非常耗时耗力的,长时间锁表,特别是会对MySQL服务产生影响,mysql在5.6版本增加了online ddl,但并不是所有的ddl都有...
  • wym408
  • wym408
  • 2017年07月25日 13:09
  • 132

在线更改表字段工具比较 pt-online-schema-change VS oak-online-alter-table

在上篇文章中提到了MySQL 5.6 Online DDL,如果是MySQL 5.5的版本在DDL方面是要付出代价的,虽然已经有了Fast index Creation,但是在添加字段还是会锁表的...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:pt-online-schema-change
举报原因:
原因补充:

(最多只允许输入30个字)