分類
發燒車訊

mysql大表在不停機的情況下增加字段該怎麼處理

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進行添加,相當於新建一張添加了字段的新表,再降原表的數據複製到新表中,複製歷史數據期間的數據也會同步至新表,最後刪除原表,將新表重命名為原表表名,實現字段添加

  •  先在從庫添加 再進行主從切換

如果一張表數據量大且是熱表(讀寫特別頻繁),則可以考慮先在從庫添加,再進行主從切換,切換后再將其他幾個節點上添加字段

 

 

 

 

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計一頭霧水該從何著手呢? 台北網頁設計公司幫您輕鬆架站!

網頁設計公司推薦不同的風格,搶佔消費者視覺第一線

※想知道購買電動車哪裡補助最多?台中電動車補助資訊懶人包彙整

南投搬家公司費用,距離,噸數怎麼算?達人教你簡易估價知識!

※教你寫出一流的銷售文案?

※超省錢租車方案