博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5763818.html
前面已经出过
percona-toolkit的介绍，从本文开始后续介绍常用工具的使用。
pt-online-schema-change是percona-toolkit最常用到的开发类工具。功能为在 alter 操作更改表结构的时候不用锁定表，也就是说执行 alter 的时候不会阻塞写和读取操作。注意执行这个工具的时候必须做好备份。其实，建议做表结构变更的时候都应该做备份。
工作原理是创建一个和你要执行 alter 操作的表一样的空表结构，执行表结构修改，然后从原表中 copy 原始数据到表结构修改后的表，当数据 copy 完成以后就会将原表移走，用新表代替原表，默认动作是将原表 drop 掉。在 copy 数据的过程中，任何在原表的更新操作都会更新到新表，因为这个工具在会在原表上创建触发器，触发器会将在原表上更新的内容更新到新表。
注意：如果表中已经定义了触发器这个工具就不能工作了。
1.执行过程
[root@master01 data]# pt-online-schema-change --alter="add column city varchar(20) not null default '' after name" --user=root D=test,t=tc_user
--charset=utf8 --execute
Cannot connect to A=utf8,D=test,h=192.168.10.84,u=root
No slaves found. See --recursion-method if host master01 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `test`.`tc_user`...
Creating new table...
Created new table test._tc_user_new OK.
Altering new table...
Altered `test`.`_tc_user_new` OK.
2017-05-05T00:27:42 Creating triggers...
2017-05-05T00:27:42 Created triggers OK.
2017-05-05T00:27:42 Copying approximately 4 rows...
Cannot connect to A=utf8,D=test,h=192.168.10.84,u=root
2017-05-05T00:27:42 Copied rows OK.
2017-05-05T00:27:42 Analyzing new table...
2017-05-05T00:27:42 Swapping tables...
2017-05-05T00:27:42 Swapped original and new tables OK.
2017-05-05T00:27:42 Dropping old table...
2017-05-05T00:27:42 Dropped old table `test`.`_tc_user_old` OK.
2017-05-05T00:27:42 Dropping triggers...
2017-05-05T00:27:42 Dropped triggers OK.
Successfully altered `test`.`tc_user`.
分为如下过程：
1.创建新表，
2.通过触发器跟踪复制更新数据，
3.copy数据，
4.更新外键关联子表，
5.rename表，。
6.删除原表和触发器
2.pt-osc原理浅析
step1.执行变更前的检查
2017-05-04T16:49:12.041502Z 26 Connect root@localhost on test using Socket
2017-05-04T16:49:12.041675Z 26 Query set autocommit=1
2017-05-04T16:49:12.041849Z 26 Query /*!40101 SET NAMES "utf8"*/
2017-05-04T16:49:12.042012Z 26 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2017-05-04T16:49:12.044172Z 26 Query SET SESSION innodb_lock_wait_timeout=1
2017-05-04T16:49:12.044493Z 26 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
2017-05-04T16:49:12.046557Z 26 Query SET SESSION lock_wait_timeout=60
2017-05-04T16:49:12.046714Z 26 Query SHOW VARIABLES LIKE 'wait\_timeout'
2017-05-04T16:49:12.048398Z 26 Query SET SESSION wait_timeout=10000
以上是超时相关检查
2017-05-04T16:49:12.048536Z 26 Query SELECT @@SQL_MODE
2017-05-04T16:49:12.048633Z 26 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2017-05-04T16:49:12.048730Z 26 Query SELECT @@server_id /*!50038 , @@hostname*/
2017-05-04T16:49:12.049311Z 27 Connect root@localhost on test using Socket
2017-05-04T16:49:12.049645Z 27 Query set autocommit=1
2017-05-04T16:49:12.049782Z 27 Query /*!40101 SET NAMES "utf8"*/
2017-05-04T16:49:12.049908Z 27 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2017-05-04T16:49:12.051635Z 27 Query SET SESSION innodb_lock_wait_timeout=1
2017-05-04T16:49:12.051864Z 27 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
2017-05-04T16:49:12.054104Z 27 Query SET SESSION lock_wait_timeout=60
2017-05-04T16:49:12.054259Z 27 Query SHOW VARIABLES LIKE 'wait\_timeout'
2017-05-04T16:49:12.056477Z 27 Query SET SESSION wait_timeout=10000
2017-05-04T16:49:12.056632Z 27 Query SELECT @@SQL_MODE
2017-05-04T16:49:12.056731Z 27 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2017-05-04T16:49:12.056887Z 27 Query SELECT @@server_id /*!50038 , @@hostname*/
2017-05-04T16:49:12.057081Z 26 Query SHOW VARIABLES LIKE 'wsrep_on'
2017-05-04T16:49:12.059325Z 26 Query SHOW VARIABLES LIKE 'version%'
2017-05-04T16:49:12.061212Z 26 Query SHOW ENGINES
2017-05-04T16:49:12.061719Z 26 Query SHOW VARIABLES LIKE 'innodb_version'
2017-05-04T16:49:12.063859Z 26 Query SHOW VARIABLES LIKE 'innodb_stats_persistent'
2017-05-04T16:49:12.065907Z 26 Query SELECT @@SERVER_ID
2017-05-04T16:49:12.066060Z 26 Query SHOW GRANTS FOR CURRENT_USER()
2017-05-04T16:49:12.066198Z 26 Query SHOW FULL PROCESSLIST
2017-05-04T16:49:12.070568Z 26 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2017-05-04T16:49:12.071989Z 26 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2017-05-04T16:49:12.073650Z 26 Query SELECT CONCAT(@@hostname, @@port)
2017-05-04T16:49:12.074099Z 26 Query SHOW TABLES FROM `test` LIKE 'tc\_user'
2017-05-04T16:49:12.074534Z 26 Query SHOW TRIGGERS FROM `test` LIKE 'tc\_user'
2017-05-04T16:49:12.074926Z 26 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
----环境变量和状态检查，权限检查
2017-05-04T16:49:12.075022Z 26 Query USE `test`
2017-05-04T16:49:12.075119Z 26 Query SHOW CREATE TABLE `test`.`tc_user`
2017-05-04T16:49:12.075259Z 26 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2017-05-04T16:49:12.075691Z 26 Query EXPLAIN SELECT * FROM `test`.`tc_user` WHERE 1=1
2017-05-04T16:49:12.076480Z 26 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='test' AND referenced_table_name='tc_user'
2017-05-04T16:49:12.081589Z 26 Query SHOW VARIABLES LIKE 'wsrep_on'
2017-05-04T16:49:12.083498Z 26 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2017-05-04T16:49:12.083617Z 26 Query USE `test`
2017-05-04T16:49:12.083723Z 26 Query SHOW CREATE TABLE `test`.`tc_user`
2017-05-04T16:49:12.083878Z 26 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
---表检查，关联表检查
step2.创建新表，其命名规则是_+原表名+_new
2017-05-04T16:49:12.084022Z 26 Query CREATE TABLE `test`.`_tc_user_new` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL DEFAULT '0',
`mail` varchar(120) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
step3.根据语句更新新表结构
2017-05-04T16:49:12.118804Z 26 Query ALTER TABLE `test`.`_tc_user_new` add column city varchar(20) not null default '' after name
2017-05-04T16:49:12.224088Z 26 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
step4.在原表上创建到新表的触发器，实现数据的在线同步
2017-05-04T16:49:12.224255Z 26 Query USE `test`
2017-05-04T16:49:12.224612Z 26 Query SHOW CREATE TABLE `test`.`_tc_user_new`
2017-05-04T16:49:12.224920Z 26 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2017-05-04T16:49:12.226233Z 26 Query CREATE TRIGGER `pt_osc_test_tc_user_del` AFTER DELETE ON `test`.`tc_user` FOR EACH ROW DELETE IGNORE FROM `test`.`_tc_user_new` WHERE `test`.`_tc_user_new`.`id` <=> OLD.`id`
---创建触发器1，delete触发器
2017-05-04T16:49:12.251356Z 26 Query CREATE TRIGGER `pt_osc_test_tc_user_upd` AFTER UPDATE ON `test`.`tc_user` FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.`_tc_user_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_tc_user_new`.`id` <=> OLD.`id`;REPLACE INTO `test`.`_tc_user_new` (`id`, `name`, `mail`) VALUES (NEW.`id`, NEW.`name`, NEW.`mail`);END
---创建触发器2，update触发器
2017-05-04T16:49:12.275066Z 26 Query CREATE TRIGGER `pt_osc_test_tc_user_ins` AFTER INSERT ON `test`.`tc_user` FOR EACH ROW REPLACE INTO `test`.`_tc_user_new` (`id`, `name`, `mail`) VALUES (NEW.`id`, NEW.`name`, NEW.`mail`)
---创建触发器3，insert触发器
step5，通过explain来判断执行chunk拷贝的成本
2017-05-04T16:49:12.300640Z 26 Query EXPLAIN SELECT * FROM `test`.`tc_user` WHERE 1=1
2017-05-04T16:49:12.301992Z 26 Query EXPLAIN SELECT `id`, `name`, `mail` FROM `test`.`tc_user` LOCK IN SHARE MODE /*explain pt-online-schema-change 4707 copy table*/
step6，数据copy到新表
2017-05-04T16:49:12.302580Z 26 Query INSERT LOW_PRIORITY IGNORE INTO `test`.`_tc_user_new` (`id`, `name`, `mail`) SELECT `id`, `name`, `mail` FROM `test`.`tc_user` LOCK IN SHARE MODE /*pt-online-schema-change 4707 copy table*/
---在线copy数据
2017-05-04T16:49:12.311598Z 26 Query SHOW WARNINGS
2017-05-04T16:49:12.312039Z 26 Query SELECT @@SERVER_ID
2017-05-04T16:49:12.312190Z 26 Query SHOW GRANTS FOR CURRENT_USER()
2017-05-04T16:49:12.312592Z 26 Query SHOW FULL PROCESSLIST
2017-05-04T16:49:12.316742Z 26 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
---检查负载状态
step7，完成数据同步后对表进行检查
2017-05-04T16:49:12.318729Z 26 Query ANALYZE TABLE `test`.`_tc_user_new` /* pt-online-schema-change */
step8，交换表
2017-05-04T16:49:12.328526Z 26 Query RENAME TABLE `test`.`tc_user` TO `test`.`_tc_user_old`, `test`.`_tc_user_new` TO `test`.`tc_user`
step9，删除旧表
2017-05-04T16:49:12.379665Z 26 Query DROP TABLE IF EXISTS `test`.`_tc_user_old`
step10，删除触发器
2017-05-04T16:49:12.404253Z 26 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_tc_user_del`
2017-05-04T16:49:12.414229Z 26 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_tc_user_upd`
2017-05-04T16:49:12.419883Z 26 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_tc_user_ins`
11.检查表状态
2017-05-04T16:49:12.428199Z 26 Query SHOW TABLES FROM `test` LIKE '\_tc\_user\_new'
2017-05-04T16:49:12.428796Z 27 Quit
2017-05-04T16:49:12.430346Z 26 Quit
--The end
