MySQL線上修改表結構pt-osc

標籤：程式   開發   online   影響   線上   

MySQL線上修改表結構pt-osc

    重所周知 MySQL的DDL操作操作是相比比較昂貴的。因為MySQL在修改表期間會阻塞任何讀寫操作。

    基本上業務處於癱瘓。如果資料量較大可能需要好幾個小時才能完成，無法容忍這個操作。Percona開發了一系列的工具 Percona Toolkit包，其中有一個工具pt-online-schema-change可以線上執行DDL操作，不會阻塞讀寫操作從而影響業務程式。當然也有其他的工具 例如 MySQL5.6的online ddl 還有gh-ost 本文主要講pt-online-schema-change線上修改表結構。

原理部分

環境概述 

Percona-Server-5.7.17-11 Percona-toolkit-3.0.3-1.el7.x86_64

表結構

CREATE TABLE `test` (  `id` int(40) NOT NULL,  `name` char(12) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

操作修改非主鍵 name欄位

一。準備工作

1. 設定當前回話參數 session層級

   
   

SHOW VARIABLES LIKE ‘innodb\_lock_wait_timeout‘; SET SESSION innodb_lock_wait_timeout=1SET SESSION lock_wait_timeout=60 SET SESSION wait_timeout=10000innodb_lock_wait_timeout=1  lock_wait_timeout=60  wait_timeout=10000

2.收集MySQL資訊

SHOW VARIABLES LIKE ‘version%‘ SHOW ENGINESSHOW VARIABLES LIKE ‘innodb_version‘SHOW VARIABLES LIKE ‘innodb_stats_persistent‘SELECT @@SERVER_IDSHOW GRANTS FOR CURRENT_USER()SHOW FULL PROCESSLISTSHOW GLOBAL STATUS LIKE ‘Threads_running‘SHOW GLOBAL STATUS LIKE ‘Threads_running‘SELECT CONCAT(@@hostname, @@port)SHOW TABLES FROM `test2` LIKE ‘test1‘SHOW TRIGGERS FROM `test2` LIKE ‘test1‘

二 正式開始

1.建立跟舊錶一模一樣的新表

 CREATE TABLE `test2`.`_test1_new` (  `id` int(30) NOT NULL,  `name` char(27) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

2.在新表上修改表結構

 ALTER TABLE `test2`.`_test1_new` modify name char(27)

3.建立觸發器

CREATE TRIGGER `pt_osc_test2_test1_del` AFTER DELETE ON `test2`.`test1` FOR EACH ROW DELETE IGNORE FROM `test2`.`_test1_new` WHERE `test2`.`_test1_new`.`id` <=> OLD.`id`

#刪除操作

 CREATE TRIGGER `pt_osc_test2_test1_upd` AFTER UPDATE ON `test2`.`test1` FOR EACH ROW BEGIN DELETE IGNORE FROM `test2`.`_test1_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test2`.`_test1_new`.`id` <=> OLD.`id`;REPLACE INTO `test2`.`_test1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

#更新操作

 CREATE TRIGGER `pt_osc_test2_test1_ins` AFTER INSERT ON `test2`.`test1` FOR EACH ROW REPLACE INTO `test2`.`_test1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

#插入操作

4.插入到舊錶

EXPLAIN SELECT `id`, `name` FROM `test2`.`test1` LOCK IN SHARE MODE

 IGNORE INTO `test2`.`_test1_new` (`id`, `name`) SELECT `id`, `name` FROM `test2`.`test1` LOCK IN SHARE MODE /*pt-online-schema-change 6291 copy table*/

#有鎖操作LOCK IN SHARE MODE

三 收尾工作

SHOW WARNINGSSELECT @@SERVER_IDSHOW GRANTS FOR CURRENT_USER()SHOW FULL PROCESSLISTSHOW GLOBAL STATUS LIKE ‘Threads_running‘ANALYZE TABLE `test2`.`_test1_new` /* pt-online-schema-change */RENAME TABLE `test2`.`test1` TO `test2`.`_test1_old`, `test2`.`_test1_new` TO `test2`.`test1`DROP TABLE IF EXISTS `test2`.`_test1_old`ROP TRIGGER IF EXISTS `test2`.`pt_osc_test2_test1_del`DROP TRIGGER IF EXISTS `test2`.`pt_osc_test2_test1_upd`DROP TRIGGER IF EXISTS `test2`.`pt_osc_test2_test1_ins`SHOW TABLES FROM `test2` LIKE ‘\_test1\_new‘

概述

1. 查看收集MySQL資訊

2. 建立一個和原表表結構一樣的new表 然後在new表中更改表結構。

3. 在原表建立3個觸發器 三個觸發器分別對應 insert update delete 操作

4. 從原表拷貝資料到new表 拷貝過程中原表進行的寫操作都會更新到暫存資料表

5. copy完成後rename 原表為old表 接著將new表rename原表 最後刪除old表和觸發器

   
   

四 操作注意事項

• Read the tool’s documentation

• Review the tool’s known “BUGS”

• Test the tool on a non-production server

• Backup your production server and verify the backups

     總結 先看一遍工具文檔，用之前先做測試，備份 備份 備份。在執行線上修改表結構的時候，最好選擇業務低峰期，不要把old表刪掉。

五 pt-osc限制

• In most cases the tool will refuse to operate unless a PRIMARY KEY or UNIQUE INDEX is present in the table. See --alter for details.

• The tool refuses to operate if it detects replication filters. See --[no]check-replication-filters for details.

• The tool pauses the data copy operation if it observes any replicas that are delayed in replication. See --max-lagfor details.

• The tool pauses or aborts its operation if it detects too much load on the server. See --max-load and --critical-load for details.

• The tool sets innodb_lock_wait_timeout=1 and (for MySQL 5.5 and newer) lock_wait_timeout=60 so that it is more likely to be the victim of any lock contention, and less likely to disrupt other transactions. These values can be changed by specifying --set-vars.

• The tool refuses to alter the table if foreign key constraints reference it, unless you specify --alter-foreign-keys-method.

• The tool cannot alter MyISAM tables on “Percona XtraDB Cluster” nodes.

六 注意事項

1.先看一遍工具文檔，用之前先做測試，備份 備份 備份。

2.在執行線上修改表結構的時候，最好選擇業務低峰期，不要把old表刪掉。

3.必須有主鍵，無法使用，必須有主鍵，必須有主鍵，必須有主鍵，必須有主鍵。

4.pt-osc如果改變外鍵約束，拒絕工作，除非指定--alter-foreign-keys-method。

5.操作的時候需要指定字元集 防止亂碼。

參考

https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

本文出自 “linux” 部落格，請務必保留此出處http://tplinux.blog.51cto.com/3917416/1933189

MySQL線上修改表結構pt-osc