如何避免 MySQL 修改表結構時導致表無法使用的問題

如何避免 MySQL 修改表結構時導致表無法使用的問題

MySQL 在修改表結構的時候可能會中斷產品的正常運行影響使用者體驗，甚至更壞的結果，遺失資料。不是所有的資料庫管理員、程式員、系統管理員都非常瞭解MySQL能避免這種情況。DBA會經常碰到這種生產中斷的情況，當升級指令碼修改了應用程式層和資料庫層，或者缺乏經驗的管理員、開發在不是很瞭解Mysql內部工作機制的情況下修改了規範檔案。

真相是：

直接修改表結構的過程中會鎖表（在5.6版本之前）

線上的資料定義語言 (Data Definition Language)在5.6版本不總是線上的而且也會鎖表

就算使用Percona工具包（線上修改定義檔案）也會有若干個步驟會鎖表

Percona MySQL  伺服器Team Dev鼓勵使用者在計劃或者執行資料庫遷移的時候先和我們溝通。我們的目標是基於使用者給出的各種情況給出最佳的方案。旨在避免鎖表當使用者對非常大的表執行DDL，以確保應用能像平常一樣正常運行，同時也在努力改善回應時間或增加系統功能。最差的情況是確保那些經不起當機的系統在黃金交易時間正常運行。

我們使用的大多數安裝包仍然小於MySQL5.6，這需要我們不停嘗試新的安裝環境來把資料庫遷移造成的損失降到最低。這可能需要一個能“線上修改規範定義檔案”的工具來升級或者修改規範檔案。MySQL5.6解決這一問題的做法是通過減少重建表和鎖表的情境，但這個方法不能覆蓋所有的可能的操作，例如當修改一列的資料類型時必然需要全表重構。Przemysław和 Malkowski在去年儘可能詳盡的討論了Mysql5.6運行中修改定義。

隨著 MySQL 5.7的新功能, 我們尋求不會鎖表的DDL操作 例如; 表最佳化 和 索引重新命名. (More info)

對於Mysql5.6的使用者，最好的建議是回顧一下數矩陣來熟悉在MYSQL之外執行定義的更改，好訊息是我們很擅長解決這一問題。

說實話，鎖表操作會經常被忽視，在操作30M大小的表時我們更傾向於直接修改，但是30G，300G的表就要考慮一下了。當使用率不高或者對鎖定時間要求不是很高的的系統來說直接操作也許更好。可是，我們常常會遇到一個需要立即執行的SQL，或者因為效能問題需要緊急增加一個索引來減少載入時間。

是否需要在系統線上期修改表定義

上面提到，線上修改表定義是工作流程中的一個模組。通常是不錯的解決方案，但也會遇到不能使用的場合，例如：當某個表使用了觸發器。瞭解pt-osc在我們項目中的工作過程很重要，讓我們來看一下原始碼：

[moore@localhost]$ egrep 'Step' pt-online-schema-change
# 步驟 1: 建立一個新表

# 步驟 2: 修改清空表. 這應該比較快,
# Step 3: 建立觸發器來捕獲原始表的改變 <--(鎖定中繼資料)

# Step 4: 複製資料.
# Step 5: 重新命名表: <--(鎖定中繼資料

# Step 6: 更新外鍵 如果是子表.

# Step 7: 刪除舊錶.

我把上面第三步到第五步高亮出來，這是鎖表可能引起系統停機的時間。但步驟六設計外鍵更新是一個迴圈的操作，是避免在更新關係的時候隱含地重建表。有很多方法可以確保表的完整性條件約束，在pt-osc的說明文檔中詳細說明了，在開始之前預覽你的表結構包括約束，並知道怎樣把修改表定義所造成的影響降到最低。

最近，我們通知了一個擁有高並發高事務量系統的使用者運行pt-osc在大型資料表上。這件事對於他們來說很平常，幾小時後我們的客服被告知該客戶遇到了最大串連數超過的問題。這個問題是如何產生的呢？當pt-osc運行到步驟五的時候會嘗試去鎖定資料並重新命名原表和隱藏表，然而這不會在開啟事務的時候立即執行，因此這條線程會被排在重新命名後面。這表現在使用者應用上就是系統停機。資料庫無法開啟新的串連並且所有的線程都被阻塞在重新命名命令之後。 

資料被鎖
5.5.3版本的說明，當開啟一個事務時會鎖定它會用到的所有表的資料（不依賴於儲存引擎），並在事務提交的時候釋放鎖。這樣做確保了在開啟事務期間不能修改表的定義。

長遠來看我們可以採用一些新的技術來避免這種情況，例如non-default pt-osc的選項，換言之就是不會刪除原表把資料換到新表。這種聯合脫離了隱藏表和觸發器，我們應該鼓勵將重新命名操作變得原子化。

校訂：2.2版本的percona工具新增了一個變數–tries  和變數–set-vars 共同被部署，解決了各種pt-osc操作可能會鎖表的情況。pt-osc (–set-vars)預設會設定如下的會話變數當串連到資料庫伺服器的時候。

wait_timeout=10000
innodb_lock_wait_timeout=1
lock_wait_timeout=60

當使用 –tries 我們可以顆粒化地鑒別操作，嘗試次數、在嘗試的間隔等待。這種組合可以確保pt-osc在合適的時機殺掉自己的等待會話進程，確保線程堆棧的空閑，並提供給我們迴圈操作來擷取管理因觸發器、重新命名、修改外鍵而造成的鎖。

–tries swap_tables:5:0.5,drop_triggers:5:0.5

說明文檔在這裡http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change–tries

它闡述了即便使用了諸如pt-osc之類的工具，充分瞭解你想解決的問題是很重要。下面的流程圖會協助你當你瞭解修改了MYSQL資料庫的結構的注意事項。請仔細閱讀建議儘管有些圖上未標出，例如磁碟空間，IO載入等。

選擇合適的DDL操作

確保能清楚瞭解在修改表結構對你的系統會產生何種影響，並選擇合適的方法來使這種影響降到最低。有時這意味著需要將改動延期直到系統到了不常使用的時候或者使用能在操作期間不鎖表的工具。當你表中有觸發器的時候一般直接修改表結構。

-大多數情況下pt-osc正是我們所需要的

-在很多案例中pt-osc是需要的，但是用法需要稍作調整

-在少數情況下pt-osc不是很合適，我們需要考慮本地阻塞修改，或者採用轉移的操作改成在複本集中複製。

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：