在"InnoDB Online DDL一瞥"中說到了Online DDL的局限性,主從復(fù)制環(huán)境中,若主數(shù)據(jù)庫上對一大表變更ALTER TABLE,耗時較長,該過程在從數(shù)據(jù)庫上回放,一般也要較長時間,這期間主數(shù)據(jù)庫上對該數(shù)據(jù)表的DML操作,將不能及時體現(xiàn)在從數(shù)據(jù)庫上,這樣從數(shù)據(jù)庫的可用性就受到了影響. 

10年積累的成都網(wǎng)站設(shè)計、做網(wǎng)站經(jīng)驗，可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識你，你也不認(rèn)識我。但先網(wǎng)站設(shè)計后付款的網(wǎng)站建設(shè)流程，更有昂仁免費網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

原因可歸結(jié)為,一個大事物未能及時執(zhí)行完畢,引起了復(fù)制延時(其實MySQL 5.6, 5.7版本中ALTER TABLE還不具有原子性,此處只是為了便于說明.).而對于大事物的優(yōu)化,首先嘗試分解成多個小事物,本文主角pt-online-schema-change工具(以下簡稱pt-osc)正是利用了該思想,其可有效的控制復(fù)制延時的問題.

pt-osc在不阻塞讀寫的情況下,進(jìn)行數(shù)據(jù)表變更.其先創(chuàng)建一個符合要求的新數(shù)據(jù)表,然后將原數(shù)據(jù)表中的數(shù)據(jù),以塊為單位,拷貝至新數(shù)據(jù)表中,這期間原數(shù)據(jù)表上的DML操作,都會通過其先前在原數(shù)據(jù)表上創(chuàng)建的觸發(fā)器,反映到新數(shù)據(jù)表上.整個過程中,該工具會通過多種方法將復(fù)制延時和主數(shù)據(jù)庫負(fù)載控制在合理范圍內(nèi).

看下實際中pt-osc使用的例子,其日志輸出也展示了它的工作過程.

mysql@db01: ~$pt-online-schema-change --alter "ADD COLUMN org_id BIGINT UNSIGNED NOTNULL DEFAULT 0" --nocheck-replication-filters --recursion-method=processlist h=192.168.19.168,P=3316,u=zz,p=123456,D=test,t=test_zzzz --execute

Found 1 slaves:

db02 ->192.168.19.190:3316

Will check slave lagon:

db02 ->192.168.19.190:3316

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`.`test_zzzz`...

Creating new table...

Created new tabletest._test_zzzz_new OK.

Waiting forever fornew table `test`.`_test_zzzz_new` to replicate to db02...

Altering new table...

Altered`test`.`_test_zzzz_new` OK.

2017-09-15T16:12:11Creating triggers...

2017-09-15T16:12:11Created triggers OK.

2017-09-15T16:12:11Copying approximately 4861821 rows...

Copying`test`.`test_zzzz`:   6% 07:42 remain

...

Copying`test`.`test_zzzz`:  89% 00:41 remain

Copying`test`.`test_zzzz`:  97% 00:08 remain

2017-09-15T16:18:42Copied rows OK.

2017-09-15T16:18:42Analyzing new table...

2017-09-15T16:18:42Swapping tables...

2017-09-15T16:18:42Swapped original and new tables OK.

2017-09-15T16:18:42Dropping old table...

2017-09-15T16:18:42Dropped old table `test`.`_test_zzzz_old` OK.

2017-09-15T16:18:42Dropping triggers...

2017-09-15T16:18:42Dropped triggers OK.

Successfully altered`test`.`test_zzzz`.

通過數(shù)據(jù)表變更過程中產(chǎn)生的general log,了解下pt-osc背后運行細(xì)節(jié),從而也可印證上面說的主要工作原理.

Step1,設(shè)置各種超時時間,以防遇到鎖等待等情況,可盡快退出,不影響其它操作.

57049 Query    SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'

57049 Query    SET SESSION innodb_lock_wait_timeout=1

57049 Query    SHOW VARIABLES LIKE 'lock\_wait_timeout'

57049 Query    SET SESSION lock_wait_timeout=60

57049 Query    SHOW VARIABLES LIKE 'wait\_timeout'

57049 Query    SET SESSION wait_timeout=10000

Step2,創(chuàng)建一個符合變更要求的新表.

57049 Query  CREATE TABLE `test`.`_test_zzzz_new` (

  `id` bigint(20)unsigned NOT NULL AUTO_INCREMENT,

  ...

  PRIMARY KEY(`id`),

  ...

) ENGINE=InnoDBAUTO_INCREMENT=5342221 DEFAULT CHARSET=utf8mb4

57049 Query  ALTER TABLE `test`.`_test_zzzz_new` ADD COLUMN org_id BIGINT UNSIGNED NOT NULL DEFAULT 0

Step3,創(chuàng)建觸發(fā)器,以便將原數(shù)據(jù)表上的DML操作,體現(xiàn)到新數(shù)據(jù)表上.

57049 Query  CREATE TRIGGER `pt_osc_test_test_zzzz_del`AFTER DELETE ON `test`.`test_zzzz` FOR EACH ROW DELETE IGNORE FROM`test`.`_test_zzzz_new` WHERE `test`.`_test_zzzz_new`.`id` <=> OLD.`id`

57049 Query  CREATE TRIGGER `pt_osc_test_test_zzzz_upd`AFTER UPDATE ON `test`.`test_zzzz` FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.`_test_zzzz_new`WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_test_zzzz_new`.`id` <=>OLD.`id`;REPLACE INTO `test`.`_test_zzzz_new` (`id`, ...) VALUES (NEW.`id`,...);END

57049 Query  CREATE TRIGGER `pt_osc_test_test_zzzz_ins`AFTER INSERT ON `test`.`test_zzzz` FOR EACH ROW REPLACE INTO`test`.`_test_zzzz_new` (`id`, ...) VALUES (NEW.`id`, ...)

Step4,分塊拷貝數(shù)據(jù),這期間其會監(jiān)控延時和負(fù)載情況.

57049 Query  INSERT LOW_PRIORITY IGNORE INTO`test`.`_test_zzzz_new` (`id`, ...) SELECT `id`, ... FROM `test`.`test_zzzz`FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '147592')) AND ((`id` <='148591')) LOCK IN SHARE MODE /*pt-online-schema-change 4924 copy nibble*/         

Step5,收尾工作.

57049 Query  ANALYZE TABLE `test`.`_test_zzzz_new` /*pt-online-schema-change */

57049 Query  RENAME TABLE `test`.`test_zzzz` TO`test`.`_test_zzzz_old`, `test`.`_test_zzzz_new` TO `test`.`test_zzzz`

57049 Query  DROP TABLE IF EXISTS `test`.`_test_zzzz_old`

57049 Query  DROP TRIGGER IF EXISTS`test`.`pt_osc_test_test_zzzz_del`

57049 Query  DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_zzzz_upd`

57049 Query  DROP TRIGGER IF EXISTS`test`.`pt_osc_test_test_zzzz_ins`

在主從復(fù)制環(huán)境下, pt-osc借助選項--recursion-method=processlist獲取從數(shù)據(jù)庫的信息.若某臺服務(wù)器使用命令行, mysqlbinlog --host=192.168.19.168 --port=3316 --user=zz --password=123456 --read-from-remote-server --raw --stop-never --to-last-log --stop-never-slave-server-id=4444 --result-file=/backup/binlog/ bin.000044,實時備份binlog,那processlist方式就失效了,此時要用dsn方式.先創(chuàng)建一個數(shù)據(jù)表,然后寫入從庫的IP信息,如下所示:

(root@localhost)[test]> SHOW CREATE TABLE dsns\G

***************************1. row ***************************

      Table: dsns

Create Table: CREATE TABLE `dsns` (

  `id` int(11)NOT NULL AUTO_INCREMENT,

  `parent_id`int(11) DEFAULT NULL,

  `dsn`varchar(255) NOT NULL,

  PRIMARY KEY(`id`)

) ENGINE=InnoDBAUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

1 row in set (0.00sec)

(root@localhost)[test]> SELECT * FROM dsns;

+----+-----------+---------------+

| id | parent_id | dsn          |

+----+-----------+---------------+

|  1 |     NULL | 192.168.19.190 |

+----+-----------+---------------+

1 row in set (0.05sec)

最后, pt-osc命令行如下所示:

pt-online-schema-change --alter "ADD COLUMN org_id BIGINT UNSIGNED NOT NULL DEFAULT 0" --nocheck-replication-filters --recursion-method=dsn=D=test,t=dsns h=192.168.19.168,P=3316,u=zz,p=123456,D=test,t=test_zzzz --execute

pt-osc的局限性

1.數(shù)據(jù)表要有主鍵,或唯一索引,其實這也是任一InnoDB數(shù)據(jù)表的設(shè)計規(guī)范.

2.有外鍵約束情況下,使用pt-osc會比較復(fù)雜,實際業(yè)務(wù)中一般是在應(yīng)用程序中實現(xiàn)邏輯上的外鍵約束的.

3. MySQL 5.6版本中,若要變更的數(shù)據(jù)表上已有觸發(fā)器, pt-osc將不能使用,該情況在5.7版本得了到改善.

pt-osc和OnlineDDL相比,執(zhí)行速度會慢,要求磁盤空間會大,但其保證了從庫的可用性.一般建議,數(shù)據(jù)表數(shù)據(jù)量較小時,可用Online DDL;若數(shù)據(jù)量較大(大于500萬或1000萬),這時要想到Online DDL會造成延時,可考慮pt-osc.