MySQL在線修改表結(jié)構(gòu)pt-osc

MySQL在線修改表結(jié)構(gòu)pt-osc

我們提供的服務(wù)有：網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站制作、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、興隆臺(tái)ssl等。為千余家企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的興隆臺(tái)網(wǎng)站制作公司

    重所周知 MySQL的DDL操作操作是相比比較昂貴的。因?yàn)镸ySQL在修改表期間會(huì)阻塞任何讀寫操作。

    基本上業(yè)務(wù)處于癱瘓。如果數(shù)據(jù)量較大可能需要好幾個(gè)小時(shí)才能完成，無(wú)法容忍這個(gè)操作。Percona開(kāi)發(fā)了一系列的工具 Percona Toolkit包，其中有一個(gè)工具pt-online-schema-change可以在線執(zhí)行DDL操作，不會(huì)阻塞讀寫操作從而影響業(yè)務(wù)程序。當(dāng)然也有其他的工具 例如 MySQL5.6的online ddl 還有g(shù)h-ost 本文主要講pt-online-schema-change在線修改表結(jié)構(gòu)。

原理部分

環(huán)境概述 

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

表結(jié)構(gòu)

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

操作修改非主鍵 name字段

一。準(zhǔn)備工作

  1. 設(shè)置當(dāng)前回話參數(shù) session級(jí)別

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

2.收集MySQL信息

SHOW VARIABLES LIKE 'version%' 
SHOW ENGINES
SHOW VARIABLES LIKE 'innodb_version'
SHOW VARIABLES LIKE 'innodb_stats_persistent'
SELECT @@SERVER_ID
SHOW GRANTS FOR CURRENT_USER()
SHOW FULL PROCESSLIST
SHOW 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'

二 正式開(kāi)始

1.創(chuàng)建跟舊表一模一樣的新表

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

2.在新表上修改表結(jié)構(gòu)

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

3.創(chuàng)建觸發(fā)器

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 WARNINGS
SELECT @@SERVER_ID
SHOW GRANTS FOR CURRENT_USER()
SHOW FULL PROCESSLIST
SHOW 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. 創(chuàng)建一個(gè)和原表表結(jié)構(gòu)一樣的new表 然后在new表中更改表結(jié)構(gòu)。

  3. 在原表創(chuàng)建3個(gè)觸發(fā)器 三個(gè)觸發(fā)器分別對(duì)應(yīng) insert update delete 操作

  4. 從原表拷貝數(shù)據(jù)到new表 拷貝過(guò)程中原表進(jìn)行的寫操作都會(huì)更新到臨時(shí)表

  5. copy完成后rename 原表為old表 接著將new表rename原表 最后刪除old表和觸發(fā)器

四 操作注意事項(xiàng)

  • 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

     總結(jié) 先看一遍工具文檔，用之前先做測(cè)試，備份 備份 備份。在執(zhí)行在線修改表結(jié)構(gòu)的時(shí)候，最好選擇業(yè)務(wù)低峰期，不要把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.

六 注意事項(xiàng)

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

2.在執(zhí)行在線修改表結(jié)構(gòu)的時(shí)候，最好選擇業(yè)務(wù)低峰期，不要把old表刪掉。

3.必須有主鍵，無(wú)法使用，必須有主鍵，必須有主鍵，必須有主鍵，必須有主鍵。

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

5.操作的時(shí)候需要指定字符集 防止亂碼。

參考

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

本文標(biāo)題：MySQL在線修改表結(jié)構(gòu)pt-osc
文章網(wǎng)址：http://muchs.cn/article14/pphsde.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供用戶體驗(yàn)、微信小程序、關(guān)鍵詞優(yōu)化、網(wǎng)站設(shè)計(jì)、靜態(tài)網(wǎng)站、移動(dòng)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

h5響應(yīng)式網(wǎng)站建設(shè)