MySQL中給一張千萬甚至更大量級的表添加字段一直是比較頭疼的問題，遇到此情況通常該如果處理？本文通過常見的三種場景進行案例說明。
1、 環境準備
數據庫版本： 5.7.25-28（Percona 分支）
服務器配置： 3台centos 7虛擬機，配置均為2CPU 2G內存
數據庫架構： 1主2從的MHA架構（為了方便主從切換場景的演示，如開啟GTID，則兩節點即可），關於MHA搭建可參考此文 MySQL高可用之MHA集群部署
準備測試表： 創建一張2kw記錄的表，快速創建的方法可以參考快速創建連續數
本次對存儲過程稍作修改，多添加幾個字段，存儲過程如下：
DELIMITER $$
CREATE PROCEDURE `sp_createNum`(cnt INT )
BEGIN
DECLARE i INT DEFAULT 1;
DROP TABLE if exists tb_add_columns;
CREATE TABLE if not exists tb_add_columns(id int primary key,col1 int,col2 varchar(32));
INSERT INTO tb_add_columns(id,col1,col2) SELECT i as id ,i%7 as col1,md5(i) as col2;
WHILE i < cnt DO
BEGIN
INSERT INTO tb_add_columns(id,col1,col2) SELECT id + i as id ,( id + i) %7 as col1,md5( id + i) as col2 FROM tb_add_columns WHERE id <=cnt - i ;
SET i = i*2;
END;
END WHILE;
END$$
DELIMITER ;
調用存儲過程，完成測試表及測試數據的創建。
mysql> call sp_createNum(20000000);
2. 直接添加字段
使用場景: 在系統不繁忙或者該表訪問不多的情況下，如符合ONLINE DDL的情況下，可以直接添加。
模擬場景： 創建一個測試腳本，每10s訪問該表隨機一條記錄，然後給該表添加字段
訪問腳本如下：
#!/bin/bash
# gjc
for i in {1..1000000000} # 訪問次數1000000000，按需調整即可
do
id=$RANDOM #生成隨機數
mysql -uroot -p'123456' --socket=/data/mysql3306/tmp/mysql.sock -e "select a.*,now() from testdb.tb_add_columns a where id = "$id # 訪問數據
sleep 10 # 暫停10s
done
運行腳本
sh test.sh
給表添加字段
mysql> alter table testdb.tb_add_columns add col3 int;
此時，訪問正常。
附ONLINE DDL的場景如下，建議DBA們必須弄清楚
（圖片轉載於https://blog.csdn.net/finalkof1983/article/details/88355314）
（圖片轉載於https://blog.csdn.net/finalkof1983/article/details/88355314）
3. 使用工具在線添加
雖然Online DDL添加字段時，表依舊可以讀寫，但是生產環境使用場景中對大表操作使用最多的還是使用工具pt-osc或gh-ost添加。
本文主要介紹 pt-osc(pt-online-schema-change) 來添加字段，該命令是Percona Toolkit工具中的使用頻率最高的一種
關於Percona Toolkit的安裝及主要使用可以參考 五分鐘學會Percona Toolkit 安裝及使用
添加字段
root@mha1 ~]# pt-online-schema-change --alter "ADD COLUMN col4 int" h=localhost,P=3306,p=123456,u=root,D=testdb,t=tb_add_columns,S=/data/mysql3306/tmp/mysql.sock --charset=utf8mb4 --execute
主要過程如下：
1> Cannot connect to A=utf8mb4,P=3306,S=/data/mysql3306/tmp/mysql.sock,h=192.168.28.132,p=...,u=root
1> Cannot connect to A=utf8mb4,P=3306,S=/data/mysql3306/tmp/mysql.sock,h=192.168.28.131,p=...,u=root
No slaves found. See --recursion-method if host mha1 has slaves. # 因為使用的是socket方式連接數據庫 且未配置root遠程連接賬號，所以會有此提示
# A software update is available:
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 `testdb`.`tb_add_columns`...
Creating new table... # 創建中間表，表名為"_原表名_new"
Created new table testdb._tb_add_columns_new OK.
Altering new table... # 修改表，也就是在新表上添加字段，因新表無數據，因此很快加完
Altered `testdb`.`_tb_add_columns_new` OK.
2020-06-20T12:23:43 Creating triggers... # 創建觸發器，用於在原表拷貝到新表的過程中原表有數據的變動（新增、修改、刪除）時，也會自動同步至新表中
2020-06-20T12:23:43 Created triggers OK.
2020-06-20T12:23:43 Copying approximately 19920500 rows... # 拷貝數據，數據庫量是統計信息里的，不準確
Copying `testdb`.`tb_add_columns`: 11% 03:50 remain # 分批拷貝數據（根據表的size切分每批拷貝多少數據），拷貝過程中可以用show processlist看到對應的sql
Copying `testdb`.`tb_add_columns`: 22% 03:22 remain
Copying `testdb`.`tb_add_columns`: 32% 03:10 remain
Copying `testdb`.`tb_add_columns`: 42% 02:45 remain
Copying `testdb`.`tb_add_columns`: 51% 02:21 remain
Copying `testdb`.`tb_add_columns`: 62% 01:48 remain
Copying `testdb`.`tb_add_columns`: 72% 01:21 remain
Copying `testdb`.`tb_add_columns`: 81% 00:53 remain
Copying `testdb`.`tb_add_columns`: 91% 00:24 remain
2020-06-20T12:28:40 Copied rows OK. # 拷貝數據完成
2020-06-20T12:28:40 Analyzing new table... # 優化新表
2020-06-20T12:28:40 Swapping tables... # 交換表名，將原表改為"_原表名_old",然後把新表表名改為原表名
2020-06-20T12:28:41 Swapped original and new tables OK.
2020-06-20T12:28:41 Dropping old table... # 刪除舊錶（也可以添加參數不刪除舊錶）
2020-06-20T12:28:41 Dropped old table `testdb`.`_tb_add_columns_old` OK.
2020-06-20T12:28:41 Dropping triggers... # 刪除觸發器
2020-06-20T12:28:41 Dropped triggers OK.
Successfully altered `testdb`.`tb_add_columns`. # 完成
修改過程中，讀寫均不受影響，大家可以寫個程序包含讀寫的
注： 無論是直接添加字段還是用pt-osc添加字段，首先都得拿到該表的元數據鎖，然後才能添加（包括pt-osc在創建觸發器和最後交換表名時都涉及），因此，如果一張表是熱表，讀寫特別頻繁或者添加時被其他會話佔用，則無法添加。
例如： 鎖住一條記錄
用pt-osc添加字段，會發現一直卡在創建觸發器那一步
此時查看對應的SQL正在等待獲取元數據鎖
換成直接添加也一樣，例如
當達到鎖等待后將會報錯放棄添加字段
mysql> alter table testdb.tb_add_columns add col5 int;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
對於此情況，需等待系統不繁忙情況下添加，或者使用後續的在從庫創建再進行主從切換
4 先在從庫修改，再進行主從切換
使用場景： 如果遇到上例中一張表數據量大且是熱表（讀寫特別頻繁），則可以考慮先在從庫添加，再進行主從切換，切換后再將其他幾個節點上添加字段。
先在從庫添加（本文在備選節點添加）
mysql> alter table testdb.tb_add_columns add col5 int;
Query OK, 0 rows affected (1 min 1.91 sec)
Records: 0 Duplicates: 0 Warnings: 0
進行主從切換
使用MHA腳本進行在線切換
masterha_master_switch --conf=/etc/masterha/app1.conf --master_state=alive --orig_master_is_new_slave --new_master_host=192.168.28.131 --new_master_port=3306
切換完成后再對其他節點添加字段
/* 原主庫上添加192.168.28.128 */
mysql> alter table testdb.tb_add_columns add col5 int;
Query OK, 0 rows affected (1 min 8.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
/* 另一個從庫上添加192.168.28.132 */
mysql> alter table testdb.tb_add_columns add col5 int;
Query OK, 0 rows affected (1 min 8.64 sec)
Records: 0 Duplicates: 0 Warnings: 0
這樣就完成了字段添加。
5. 小結
生產環境MySQL添加或修改字段主要通過如下三種方式進行，實際使用中還有很多注意事項，大家要多多總結。
- 直接添加
如果該表讀寫不頻繁，數據量較小（通常1G以內或百萬以內），直接添加即可（可以了解一下online ddl的知識）
- 使用pt_osc添加
如果表較大 但是讀寫不是太大，且想盡量不影響原表的讀寫，可以用percona tools進行添加，相當於新建一張添加了字段的新表，再降原表的數據複製到新表中，複製歷史數據期間的數據也會同步至新表，最後刪除原表，將新表重命名為原表表名，實現字段添加
- 先在從庫添加 再進行主從切換
如果一張表數據量大且是熱表（讀寫特別頻繁），則可以考慮先在從庫添加，再進行主從切換，切換后再將其他幾個節點上添加字段
本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理【其他文章推薦】
※網頁設計一頭霧水該從何著手呢? 台北網頁設計公司幫您輕鬆架站!
※網頁設計公司推薦不同的風格，搶佔消費者視覺第一線
※想知道購買電動車哪裡補助最多?台中電動車補助資訊懶人包彙整
※南投搬家公司費用,距離,噸數怎麼算?達人教你簡易估價知識!
※教你寫出一流的銷售文案?
※超省錢租車方案
Orignal From: mysql大表在不停機的情況下增加字段該怎麼處理
留言
張貼留言