MysqL> show create table t2\G*************************** 1. row *************************** table: t2Create table: CREATE table `t2` ( `ID` int(11) NOT NulL auto_INCREMENT,PRIMARY KEY (`ID`)) ENGINE=InnoDB auto_INCREMENT=1005764 DEFAulT CHARSET=utf81 row in set (0.19 sec)@H_404_0@该表中只有1列，ID，自增主键。@H_404_0@ @H_404_0@其中，表中已经存在一部分数据
MysqL> select count(*) from t2;+----------+| *) || 1005763 0.31 sec)@H_404_0@ @H_404_0@利用pt-online-schema-change对该表新增一列@H_404_0@# pt-online-schema-change --execute --alter "ADD ColUMN c1 DATETIME" D=test,t=t2
Found 2 slaves: test hbaseWill check slave lag on: test hbaseOperation,trIEs,wait: analyze_table,10,1 copy_rows,1); Font-weight: bold">0.25 create_triggers,1)"> drop_triggers,1)"> swap_tables,1)"> update_foreign_keys,1)">Altering `test`.`t2`...Creating new ... `test`.`_t2_new` ( `ID` utf8Created new test._t2_new OK.Waiting forever for new table `test`.`_t2_new` to replicate to test...Altering new ALTER table `test`.`_t2_new` ADD ColUMN c1 DATETIMEAltered `test`.`_t2_new` OK.2016-11-21T12:49:18 Creating triggers...TRIGGER `pt_osc_test_t2_del` AFTER DELETE ON `test`.`t2` FOR EACH ROW DELETE IGnorE FROM `test`.`_t2_new` WHERE `test`.`_t2_new`.`ID` <=> olD.`ID`TRIGGER `pt_osc_test_t2_upd` AFTER UPDATE FOR EACH ROW REPLACE INTO `test`.`_t2_new` (`ID`) VALUES (NEW.`ID`)TRIGGER `pt_osc_test_t2_ins` AFTER INSERT (NEW.`ID`) Created triggers OK.18 copying approximately 1005075 rows...INSERT LOW_PRIORITY IGnorE SELECT `ID` FROM `test`.`t2` FORCE INDEX(`PRIMARY`) WHERE ((`ID` >= ?)) AND ((`ID` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 2352 copy nibble*/SELECT !40001 sql_NO_CACHE */ `ID` >= ?)) ORDER BY `ID` liMIT ?,1); Font-weight: bold">2 next chunk boundary*/copying `test`.`t2`: 40% 00:44 remaincopying `test`.`t2`: 8212 remain50:31 copIEd rows OK.31 Analyzing new ...32 SwapPing tables...REname table `test`.`t2` TO `test`.`_t2_old`,`test`.`_t2_new` TO `test`.`t2`35 Swapped original and new tables OK.35 DropPing old DROP table IF EXISTS `test`.`_t2_old`36 Dropped old `test`.`_t2_old` OK.36 DropPing triggers...TRIGGER `test`.`pt_osc_test_t2_del`; `test`.`pt_osc_test_t2_upd`; `test`.`pt_osc_test_t2_ins`; Dropped triggers OK.Successfully altered `test`.`t2`.@H_404_0@ @H_404_0@查看general log中的输出
161017 11:22:56 1052 Connect root@localhost test 1052 query set autocommit1 1052 query SHOW VARIABLES liKE 'innodb\_lock_wait_timeout' SET SESSION innodb_lock_wait_timeoutlock\_wait_timeoutSET SESSION lock_wait_timeout60 wait\_timeoutSET SESSION wait_timeout10000 @@sql_mode SET @@sql_QUOTE_SHOW_CREATE = !40101,@@sql_mode='NO_auto_VALUE_ON_ZERO,STRICT_TRANS_tableS,NO_ENGINE_SUBSTITUTION'*/ @@server_ID !50038,@@hostname*/@H_404_0@上述主要是设置会话的变量信息，包括innodb_lock_wait_timeout，wait_timeout和sql_QUOTE_SHOW_CREATE。@H_404_0@
1053 Connect root1053 query 1053 query SHOW VARIABLES */
wsrep_onversion%1052 query SHOW ENGInes innodb_versioninnodb_stats_persistent@@SERVER_ID 1052 query SHOW GRANTS FOR CURRENT_USER() 1052 query SHOW FulL PROCESSList query SHOW SLAVE HOSTS 1052 query SHOW GLOBAL STATUS Threads_runningSELECT CONCAT(@@hostname,@@port) 1052 query SHOW tableS FROM `test` t21052 query SHOW TRIGGERS !40101 SET @olD_sql_mode := @@sql_mode,@@sql_mode := '',@olD_QUOTE := @@sql_QUOTE_SHOW_CREATE,@@sql_QUOTE_SHOW_CREATE := 1 USE `test` `test`.`t2` !40101 SET @@sql_mode := @olD_sql_mode,@@sql_QUOTE_SHOW_CREATE := @olD_QUOTE 1052 query EXPLAIN SELECT * FROM `test`.`t2` WHERE 1SELECT table_schema,table_name FROM information_schema.key_column_usage WHERE referenced_table_schema=test' AND referenced_table_name*/@H_404_0@解释： @H_404_0@1. 查看参数变量，当前用户的权限，slave的信息，会话变量@H_404_0@2. 确认t2是否存在，t2上是否有触发器@H_404_0@3. 查看执行计划@H_404_0@4. 查看是否t2表是否被其它表外键关联。@H_404_0@
39 query `test` 39 query SHOW `test`.`t2` */ 161121 12:18 DATETIME `test`.`_t2_new` WHERE `test`.`_t2_new`.`ID` <=> olD.`ID` (NEW.`ID`) VALUES (NEW.`ID`)@H_404_0@解释：@H_404_0@1. 根据目标表结构创建一张新表。@H_404_0@2. 对新表添加字段，可以看出pt-online-shema-change对表结构进行变更依赖的还是MysqL自身的Online DDL。@H_404_0@3. 针对目标表创建三个触发器，DELETE，UPDATE和INSERT，因为REPLACE *** 作只有在主键或唯一索引存在的情况下才有意义，这也就解释了为什么目标表上要有主键或唯一索引。@H_404_0@
39 query EXPLAIN 1 BY `ID` liMIT 1 first lower boundaryINDEX (`WHERE `ID` IS NulL key_len*/ WHERE `ID` >= 1' ')) 999,1)">next chunk boundary')) AND ((`ID` <= 1000')) LOCK explain pt-online-schema-change 2352 copy nibble39 query SHOW WARNINGS @@SERVER_ID 39 query SHOW GRANTS () PROCESSList PROCESSList20 SELECT pt-online-schema-change keepalive'21 PROCESSList@H_404_0@解释：@H_404_0@上述输出只包含两个chunk的选择。其它chunk的选择基本相同。@H_404_0@1. SHOW GLOBAL STATUS liKE 'Threads_running'用于监控当前的系统负载。@H_404_0@2. 可以看出pt-online-schema-change是以chunk为单位进行目标表数据的拷贝。@H_404_0@3. 在拷贝的过程中，对目标表的相关记录加了共享锁，此时，会堵塞客户端对这些记录的DML *** 作。@H_404_0@
39 query SHOW GLOBAL STATUS ' 100128516,1)">29517*/
39 query ANALYZE pt-online-schema-change */32 39 query REname 35 36 `test`.`pt_osc_test_t2_del` `test`.`pt_osc_test_t2_upd` `test`.`pt_osc_test_t2_ins` 39 query SHOW tableS \_t2\_new37 40 Quit 39 Quit@H_404_0@解释：@H_404_0@1. 在完成数据的拷贝后，会对新表执行ANALYZE *** 作，这样，可及时更新新表的统计信息。@H_404_0@官档的解释如下：
This circumvents a potentially serIoUs issue related to InnoDB optimizer statistics. If the table being alerted isbusy and the tool completes quickly,the new table will not have optimizer statistics after being swapped. Thiscan cause fast,1)">index-using querIEs to do full table scans until optimizer statistics are updated (usually after 10seconds). table is large and the server very busy,this can cause an outage.@H_404_0@2. 对目标表和新表进行REname *** 作。@H_404_0@3. 删除原来的目标表@H_404_0@4. 删除触发器。@H_404_0@ 总结
以上是内存溢出为你收集整理的pt-online-schema-change的实现原理全部内容，希望文章能够帮你解决pt-online-schema-change的实现原理所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）