闖禍了，生成環境執行了DDL操作《死磕MySQL系列十四》

2022 年 1 月 11 日
筆記
MySQL

由於業務隨着時間不停的改變，起初的表結構設計已經滿足不了如今的需求，這時你是不是想那就加字段唄！加字段也是個藝術活，接下來由本文的主人咔咔給你吹。

試想一下這個場景

事務A在執行一個非常大的查詢

事務B毫不猶豫的執行了DDL操作

接下來會發生什麼，你如果已經知道答案的話恭喜你又掌握了一個MySQL中重要的知識點。

事務A執行查詢時會持有MDL鎖，而事務B同樣也需要MDL鎖，但事務A在進行大查詢，所以導致事務B後的所有操作都會被堵塞。

這時你應該知道了在MySQL中所有對錶的增刪改查都需要申請MDL讀鎖。

接下來聊聊如何安全的給表加個字段

一、為什麼不建議你在生產環境下進行DDL

上期文章跟大家聊過的MDL鎖的知識還記得吧！不記得也沒事，那就在這篇文章中再進行複習。

MDL鎖不需要顯示調用，當對一個表做增刪改查時會默認加MDL讀鎖，而執行修改表結構時會默認加MDL寫鎖。

這也就是文章開頭給大家說的事務B是不能在事務A沒釋放MDL讀鎖之前執行。

事務B需要的是MDL寫鎖，MDL讀鎖與寫鎖是互斥關係，因此事務B的DDL操作會一直等待事務A提交並釋放MDL鎖

但你有沒有想過一個問題，此時的事務B執行的在線DDL操作，需要的是MDL寫鎖，上文也說了對一個表做增刪改查時會默認加MDL讀鎖，這不就意味着後續對這個表的所有操作都會堵塞嗎？

所以說堅決不要在生產環境進行在線DDL，現在的客戶端都有會重試機制，當堵塞的語句超時後會再起一個新的事務在請求，這張表假設是一個熱表，MySQL庫的線程會非常快就爆滿，等待的結果就是用戶那邊遲遲響應不了結果。

這裡給大家一個方案，當你十分緊急需要添加一個字段時，可以給語句設置一個時間，如果在這個設定的時間內能拿到MDL寫鎖最好，拿不到也會阻塞後續的業務語句。

當設置的這個時間超過後，這個指令就結束了，之後可以再次重複執行這個指令即可。

執行語法為alter table table_nam wait 10 add cloumn

這個方案也是在你的表不大的情況下才可以進行執行的，假設你的表就看第二種方案哈！要不你會死的很慘。

咔咔在一張近9000W數據的開發表上做過一次DDL操作，大概用了23s這樣一個時間，這要是在線上想想都害怕

二、如何安全給表加個字段

目前咔咔知道的第三方工具有gh-ost和pt-online-schema-change，咔咔所在的公司使用的是後者，對於這兩個插件後者的使用還是多點。

還記得在前幾期文章中提到了表數據都刪完了，但表空間依然沒有縮小，在那期就簡單的提了一下。

想要縮小表空間可以新建一模一樣的表結構，然後根據主鍵ID的順序把數據從就舊錶中逐行插入新表，這樣就可以減少表空洞的問題。

同理今天要說的pt-online-schema-change這個插件的工作流程大致如下

新建一模一樣的表，表名可以起為_new後綴
接着在這個新表執行更改字段操作
接着在原表上加三個觸發器，分別為delete、update、insert，將原表中要執行的語句也在新表中執行
最後將原表的數據拷貝到新表中，替換掉原表

接下來咔咔將親自實戰一下此操作，可以跟着咔咔的步驟一起來

三、使用pt-online-schema-change

安裝步驟

yum -y install perl perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5

wget //www.percona.com/downloads/percona-toolkit/3.1.0/binary/redhat/7/x86_64/percona-toolkit-3.1.0-2.el7.x86_64.rpm

rpm -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm

rm -f percona-toolkit-3.1.0-2.el7.x86_64.rpm

安裝完成後執行./bin/pt-online-schema-change --help，出現以下界面證明你就安裝成功了

參數認識

參數是非常多的，咔咔簡單的說幾個足夠修改表結構的

–user 連接mysql用戶名
–password 連接mysql密碼
–host 連接msyql地址
p 連接mysql端口號
D 連接mysql庫名
t 連接msyql表名
–alter 修改表結構的語句
–execute 執行修改表結構
–charset=utf8 使用utf8編碼，避免中文亂碼
–no-version-check 不檢查版本，在阿里雲服務器中一般加入此參數，否則會報錯

接下來使用pt-online-schema-change修改一下表結構

這個是目前默認的數據結構，現在想要添加郵箱字段

在這塊你大概率會遇到兩個錯誤，一個是你的密碼中標點符號，另一個是pt版本問題

處理完密碼中的標點符號就會來到下面這個錯誤

執行代碼./bin/pt-online-schema-change --charset=utf8 --no-version-check --user="root" --password="Fang1996" --host="127.0.0.1" D="kaka",t=evt_sms --alter "add column email varchar(255) not null default '' after phone" --print --execute

這個問題是pt-online-schema-change版本造成的

刪除之前的版本

yum remove percona-toolkit

重新安裝新版本的rpm包即可，安裝步驟如下

wget //downloads.percona.com/downloads/percona-toolkit/3.2.0/binary/redhat/7/x86_64/percona-toolkit-3.2.0-1.el7.x86_64.rpm

rpm -ivh percona-toolkit-3.2.0-1.el7.x86_64.rpm

這一步部分小夥伴會遇到其它資料給的地址太過老舊，導致一直下載不了，咔咔提供的這個地址是官網提取的，非常好用，//www.percona.com/downloads/percona-toolkit/3.2.0/binary/redhat/7/這裡可以獲取各個版本的rpm包

咔咔親測虛擬機的下載速度要不服務器快，於是先將文件下載到了虛擬機

然後使用scp直接把虛擬機的文件傳輸到主機上，執行命令為 scp percona-toolkit-3.2.0-1.el7.x86_64.rpm root@ip:/

使用語法非常簡單scp local_file remote_username@remote_ip:remote_folder

local_file ：本地文件

remote_username：遠程用戶名

remote_ip：遠程機器IP地址

remote_folder 遠程目錄

執行完成後需要輸入服務器密碼，然後等待傳輸完成就可以在服務器上看到對應的文件

這裡分別在虛擬機、服務器查看了文件大小，文件是沒有一點問題的

到這裡對於pt-online-schema-change的安裝才完成，接下來重試之前執行的命令

./bin/pt-online-schema-change --charset=utf8 --no-version-check --user="root" --password="Fang1996" --host="127.0.0.1" D="kaka",t=evt_sms --alter "add column email varchar(255) not null default '' after phone" --print --execute

以上就是修改成功的信息，可以看到處理數據有46W，基本是毫無壓力，建議大家實測一下

可以看到要加的email字段已經加上了，這個就是我們想要的結果

四、簡化pt-online-schema-change的執行命令

在執行修改表結構命令時輸入了很多配置信息，這些信息可以放在腳本裡邊復用的

#!/bin/bash

database=$1
table=$2
alter_conment=$3

pt_host='127.0.0.1'
pt_user='root'
pt_pwd='Fang1996'

echo "$table"

echo "$alter_conment"

/bin/pt-online-schema-change --charset=utf8 --no-version-check --user=${pt_user} --password=${pt_pwd} --host=${pt_host} P=3306,D=$database,t=$table --alter "${alter_conment}" --print --execute

那麼使用pt-online-schema-change則可以這樣寫

sh pt.sh kaka evt_sms "add column kaka1 varchar(255) default '咔咔'"

這裡給大家提供了添加字段的方法，修改、刪除都是一致的，只需要把對應的語句放到引號裡邊即可

在這裡你會發現只有進到腳本的目錄才能執行對應的腳本，那麼如何讓你的腳本可以在任意地方都可以執行呢？

五、總結

本期文章給大家詳細說明了為什麼不建議大家直接在線DDL，因為會阻塞SQL導致業務停擺

給大家介紹了第三方腳本pt-online-schema-change的安裝、使用，使用還是非常簡單的，參數也只給大家說明了常用的，如果要放到生產環境使用，你需要把所有的參數都簡單過一遍。

最後用一個腳本優化了修改表信息時需要輸入的一大堆重複信息。

堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的互聯網上能給你帶來一點幫助，我是咔咔，下期見。

Tags: MySQL