MySQL5.6和mysql5.7對(duì)online DDL做了大幅度功能增強(qiáng)，但是仍然存在主庫執(zhí)行DDL，從庫存在大幅延遲的情況，故目前生產(chǎn)環(huán)境還是通過pt-online-schema-change工具來實(shí)現(xiàn)online DDL。但是pt-online-schema-change的使用是否就沒有限制呢？
成都創(chuàng)新互聯(lián)從2013年創(chuàng)立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目網(wǎng)站設(shè)計(jì)制作、做網(wǎng)站網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元景谷做網(wǎng)站,已為上家服務(wù),為景谷各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:18980820575
先看看官方文檔對(duì)pt-online-schema-change的工作原理的描述：
pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table. The data copy process is performed in small chunks of data, which are varied to attempt to make them execute in a specific amount of time (see --chunk-time). This process is very similar to how other tools, such as pt-tablechecksum, work. Any modifications to data in the original tables during the copy will be reflected in the new table, because the tool creates triggers on the original table to update the corresponding rows in the new table. The use of triggers means that the tool will not work if any triggers are already defined on the table. When the tool finishes copying data into the new table, it uses an atomic RENAME TABLE operation
接下來通過實(shí)驗(yàn)的方式看看pt-online-schema-change是如何工作的，記得打開mysql的general log。通過查看general日志驗(yàn)證pt-online-schema-change的工作機(jī)理。
shell>pt-online-schema-change -u linzj -h 192.168.110.131 -p linzj --alter='add column vid3 int' --execute D=sbtest,t=sbtest
1 創(chuàng)建一個(gè)和你要執(zhí)行 alter 操作的表一樣的空表結(jié)構(gòu)：
11 Query CREATE TABLE `sbtest`.`_sbtest_new` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `vid` int(11) DEFAULT NULL, `vid2` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8
2、執(zhí)行表結(jié)構(gòu)修改
170407 15:45:46 11 Query ALTER TABLE `sbtest`.`_sbtest_new` add column vid3 int
3、在原表上創(chuàng)建觸發(fā)器，如果表中已經(jīng)定義了觸發(fā)器這個(gè)工具就不能工作了。
11 Query CREATE TRIGGER `pt_osc_sbtest_sbtest_del` AFTER DELETE ON `sbtest`.`sbtest` FOR EACH ROW DELETE IGNORE FROM `sbtest `.`_sbtest_new` WHERE `sbtest`.`_sbtest_new`.`id` <=> OLD.`id` 11 Query CREATE TRIGGER `pt_osc_sbtest_sbtest_upd` AFTER UPDATE ON `sbtest`.`sbtest` FOR EACH ROW REPLACE INTO `sbtest`.`_sb test_new` (`id`, `k`, `c`, `pad`, `vid`, `vid2`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`, NEW.`vid`, NEW.`vid2`) 11 Query CREATE TRIGGER `pt_osc_sbtest_sbtest_ins` AFTER INSERT ON `sbtest`.`sbtest` FOR EACH ROW REPLACE INTO `sbtest`.`_sb test_new` (`id`, `k`, `c`, `pad`, `vid`, `vid2`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`, NEW.`vid`, NEW.`vid2`)
4、按主鍵or唯一索引進(jìn)行排序，分成若干chunk進(jìn)行數(shù)據(jù)copy
11 Query EXPLAIN SELECT * FROM `sbtest`.`sbtest` WHERE 1=1 11 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lo wer boundary*/ 11 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE `id` IS NOT NULL ORDER BY `id` LIMIT 1 /*key_len*/ 11 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE `id` >= '1' /*key_le n*/ 11 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) OR DER BY `id` LIMIT 999, 2 /*next chunk boundary*/ 11 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY ` id` LIMIT 999, 2 /*next chunk boundary*/ 11 Query SHOW WARNINGS 11 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 11 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) ORDER BY `id` LIMIT 19329, 2 /*next chunk boundary*/ 11 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) ORDER B Y `id` LIMIT 19329, 2 /*next chunk boundary*/ 11 Query EXPLAIN SELECT `id`, `k`, `c`, `pad`, `vid`, `vid2` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ' 1001')) AND ((`id` <= '20330')) LOCK IN SHARE MODE /*explain pt-online-schema-change 17219 copy nibble*/ 11 Query INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`, `vid`, `vid2`) SELECT `id`, `k`, `c` , `pad`, `vid`, `vid2` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) AND ((`id` <= '20330')) LOCK IN SHARE MODE /*pt-onlin e-schema-change 17219 copy nibble*/
5、rename表，默認(rèn)刪除舊表
11 Query RENAME TABLE `sbtest`.`sbtest` TO `sbtest`.`_sbtest_old`, `sbtest`.`_sbtest_new` TO `sbtest`.`sbtest` 11 Query DROP TABLE IF EXISTS `sbtest`.`_sbtest_old`
那這樣的話，如果我們?cè)谑褂胮t-online-schema-change工具在線online DDL某個(gè)表的時(shí)候，同時(shí)對(duì)該表的主鍵or唯一索引字段進(jìn)行DML，是否會(huì)存在異常呢？
實(shí)驗(yàn)場(chǎng)景如下：
第一個(gè)窗口：
shell>pt-online-schema-change -u linzj -h 192.168.110.131 -p linzj --alter='add column vid3 int' --execute D=sbtest,t=sbtest Found 2 slaves: mysql2 ansible Will check slave lag on: mysql2 ansible 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 `sbtest`.`sbtest`... Creating new table... Created new table sbtest._sbtest_new OK. Waiting forever for new table `sbtest`.`_sbtest_new` to replicate to mysql2... Altering new table... Altered `sbtest`.`_sbtest_new` OK. 2017-04-07T14:52:50 Creating triggers... 2017-04-07T14:52:50 Created triggers OK. 2017-04-07T14:52:50 Copying approximately 986400 rows... Copying `sbtest`.`sbtest`: 86% 00:04 remain 2017-04-07T14:53:27 Copied rows OK. 2017-04-07T14:53:27 Swapping tables... 2017-04-07T14:53:27 Swapped original and new tables OK. 2017-04-07T14:53:27 Dropping old table... 2017-04-07T14:53:27 Dropped old table `sbtest`.`_sbtest_old` OK. 2017-04-07T14:53:27 Dropping triggers... 2017-04-07T14:53:27 Dropped triggers OK. Successfully altered `sbtest`.`sbtest`.
第二個(gè)窗口：
root@localhost:mysql3306.sock 15:44: [sbtest]>select count(*) from sbtest; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.17 sec)
root@localhost:mysql3306.sock 15:44: [sbtest]>update sbtest set id=9999999 where id =110; Query OK, 1 row affected (1.33 sec) Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:mysql3306.sock 15:45: [sbtest]>update sbtest set id=9999998 where id =111; Query OK, 1 row affected (0.84 sec) Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:mysql3306.sock 15:46: [sbtest]>update sbtest set id=9999997 where id =112; Query OK, 1 row affected (0.75 sec) Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:mysql3306.sock 15:46: [sbtest]>select count(*) from sbtest; +----------+ | count(*) | +----------+ | 1000003 | +----------+ 1 row in set (0.70 sec)
root@localhost:mysql3306.sock 15:46: [sbtest]>select * from sbtest order by id desc limit 5; +---------+---+---+----------------------------------------------------+------+------+------+ | id | k | c | pad | vid | vid2 | vid3 | +---------+---+---+----------------------------------------------------+------+------+------+ | 9999999 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 9999998 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 9999997 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 1000000 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 999999 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | +---------+---+---+----------------------------------------------------+------+------+------+ 5 rows in set (0.00 sec)
root@localhost:mysql3306.sock 15:46: [sbtest]>select * from sbtest where id in (110,111,112); +-----+---+---+----------------------------------------------------+------+------+------+ | id | k | c | pad | vid | vid2 | vid3 | +-----+---+---+----------------------------------------------------+------+------+------+ | 110 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 111 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | | 112 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL | +-----+---+---+----------------------------------------------------+------+------+------+ 3 rows in set (0.02 sec)
同時(shí)對(duì)表的主鍵or唯一索引進(jìn)行修改的話，這時(shí)候就會(huì)出現(xiàn)新表的數(shù)據(jù)比舊表數(shù)據(jù)多的情況發(fā)現(xiàn)。這應(yīng)該算是pt-online-schema-change工具的一個(gè)bug，為何會(huì)出現(xiàn)這種情況，請(qǐng)仔細(xì)觀察下pt-online-schema-change工具在原表創(chuàng)建的3個(gè)觸發(fā)器的定義就可以很容易發(fā)現(xiàn)了。
建議大家，在使用pt-online-schema-change的時(shí)候，暫停對(duì)表主鍵or唯一索引列的數(shù)據(jù)更新。
pt_online_schema_change典型的用法：
1）添加一列，并不真正執(zhí)行
pt-online-schema-change –alter “add column c1 int” D=mydb,t=mytable –dry-run
2）更新存儲(chǔ)引擎為InnoDB，不刪除原表
pt-online-schema-change –alter “ENGINE=InnoDB” –no-drop-old-table –print –statistics –execute D=mydb,t=mytable –execute
3）復(fù)制環(huán)境下，忽略日志篩選和Slave復(fù)制延遲，刪除表字段
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）在我們的雙主復(fù)制環(huán)境中，設(shè)定了忽略mysql庫的復(fù)制，不是很在乎復(fù)制的延遲，有時(shí)有外鍵影響，希望盡量保留原表數(shù)據(jù)，必要時(shí)自行刪除。
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
重慶分公司
重慶分公司
