现在的位置: 首页Mysql>正文
使用pt-online-schema-change对大表进行alter table
2015年09月06日 Mysql 暂无评论 ⁄ 被围观 1,484 view+

前提:由于pt-online-schema-change会更新数据,为避免意外,使用前最好先备份数据哦~

pt-online-schema-change的作用:在不锁表的情况下安全快速地更新表结构,pt-online-schema-change是percona-toolkit中的一款工具。

安装

rpm -ivh http://pkgs.repoforge.org/perl-Time-HiRes/perl-Time-HiRes-1.9724-1.el6.rfx.x86_64.rpm
rpm -ivh http://pkgs.repoforge.org/perl-TermReadKey/perl-TermReadKey-2.30-3.el6.rfx.x86_64.rpm
rpm -ivh http://pkgs.repoforge.org/perl-Net-SSLeay/perl-Net-SSLeay-1.36-1.el6.rfx.x86_64.rpm
rpm -ivh http://pkgs.repoforge.org/perl-IO-Socket-SSL/perl-IO-Socket-SSL-1.44-1.el6.rfx.noarch.rpm

用法

pt-online-schema-change [OPTIONS] DSN

原理

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默认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,会进一步打印出内部事件的数目。

其他选项说明

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

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

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

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

5、--alter 结构变更语句,不需要alter table关键字。可以指定多个更改,用逗号分隔。如下场景,需要注意:

  • 不能用RENAME来重命名表。 * 列不能通过先删除,再添加的方式进行重命名,不会将数据拷贝到新列。
  • 如果加入的列非空而且没有默认值,则工具会失败。即其不会为你设置一个默认值,必须显示指定。
  • 删除外键(drop foreign key constrain_name)时,需要指定名称_constraint_name,而不是原始的constraint_name。

6、--dry-run 并不真正执行,可以看到生成的执行语句,了解其执行步骤与细节

7、--execute 真正执行。–dry-run与–execute必须指定一个

8、--[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

参考:http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

给我留言

留言无头像?


×
腾讯微博