MySQL級聯復制下如何進行大表的字段擴容

目錄

• MySQL級聯復制下進行大表的字段擴容
• 一、背景
• 二、庫表信息
• 三、方案選擇
• 四、如何進行操作
• 五、總結

MySQL級聯復制下進行大表的字段擴容

作者：雷文霆

愛可生華東交付服務部 DBA 成員，主要負責Mysql故障處理及相關技術支持。愛好看書，電影。座右銘，每一個不曾起舞的日子，都是對生命的辜負。

本文來源：原創投稿

*愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯系小編并注明來源。

一、背景

某客戶的業務中有一張約4億行的表，因為業務擴展，表中open_id varchar(50) 需要擴容到 varchar(500).
變更期間盡量減少對主庫的影響(最好是不要有任何影響->最終爭取了4個小時的窗口期)。

二、庫表信息

環境：Mysql 8.0.22
1主1從 基于Gtid復制

1.第一個問題，這是一張大表嗎? 是的，請看

此表的ibd 文件280G + count長時間無返回 + 使用備庫看了一下確認行數>4億

以下語句也可以查看：show table status from dbname like "tablename"\G # Rows 的值不準，有時誤差有2倍SELECT a.table_schema,a.table_name,concat(round(sum(DATA_LENGTH/1024/1024)+sum(INDEX_LENGTH/1024/1024),2) ,"MB")total_size,concat(round(sum(DATA_LENGTH/1024/1024),2),"MB") AS data_size,concat(round(sum(INDEX_LENGTH/1024/1024),2),"MB") AS index_size FROM information_schema.TABLES a WHERE a.table_schema = "dbname" AND a.table_name = "tablename"; #看下此表的數據量

既然是大表，我們應該使用什么方式做變更呢？

三、方案選擇

下文中的 M 表示主庫，S1 為從1 ，S2 為從2

方式優點缺點可行性OnlineDDL原生，使用中間臨時表ALGORITHM=COPY時，會阻塞DML，推薦版本>MySQL5.75星Gh-ost使用binlog+回放線程代替觸發器第三方工具，根據不同的參數導致執行時間較長4星Pt-osc版本兼容性好,使用觸發器保持主副表一致第三方工具，且使用限制較多3星M-S1-S2時間可預估級聯復制，人工操作1星

為什么我們沒有選擇前3種方案？

根據實際情況評估，本次業務側的需求是此表24h都有業務流量，且不接受超過4小時的業務不可用時間

OnlineDDL的方式，ALGORITHM=COPY時，期間會阻塞DML(只讀)，最后主副表rename操作時(不可讀寫)，直到DDL完成(其中需要的時間不確定)。

Gh-ost的方式，推薦的模式為連接從庫，在主庫轉換，此模式對主庫影響最小，可通過參數設置流控。致命的缺點是此工具的變更時間太長，4億的表，測試環境使用了70個小時。最后我們還需要下發切換命令及手動刪除中間表*_del。如果是1主2從還是比較推薦這種方式的，因為還有一個從庫可以保障數據安全。

Pt-osc 和Gh-ost都屬于第三方，Pt-osc 對大表的操作和OnlineDDL有一個共同的缺點就是失敗回滾的代價很大。

如果是低版本如MySQL<5.7可以使用，理論上OnlineDDL是在MySQL5.6.7開始支持，剛開始支持的不是很好，可適當取舍。

最后我們選擇了，DBA最喜愛(xin ku)的一種方式，在M-S1-S2級聯復制下進行。

四、如何進行操作

• 新建一個S1的從庫，構建M-S1-S2級聯復制
• 使用OnlineDDL在S2上進行字段擴容 (優點是期間M-S1的主從不受影響)
• 擴容完成后，等待延遲同步M-S1-S2 (降低S2與M的數據差異,并進行數據驗證)
• 移除S1,建立M-S2的主從關系(使S2繼續同步M的數據)
• 備份S2恢復S1，建立M-S2-S1級聯復制
• 應用停服，等待主從數據一致(優點是差異數據量的同步時間很短)
• 最終S2成為主庫，S1為從庫(應用需要修改前端連接信息)
• 應用進行回歸驗證

以上內容看上去很復雜，本質上就是備份恢復。讀者可將其做為備選方案。分享一下具體步驟?

環境裝備:開啟Gtid,注意M,S1 binlog保存時長,磁盤剩余空間大于待變更表的2倍show global variables like "binlog_expire_logs_seconds"; # 默認604800set global binlog_expire_logs_seconds=1209600; # 主庫和級聯主庫都需要設置1.搭建 1主2從的級聯復制，M -> S1 -> S2 ，安裝MySQL注意本次環境lower_case_table_names = 0 2.在S2 上做字段擴容。 預估 10個小時`參數設置:`set global slave_type_conversions="ALL_NON_LOSSY"; # 防止復制報錯SQL_Errno: 13146，屬于字段類型長度不一致無法回放set global interactive_timeout=144000;set global wait_timeout =144000;`磁盤IO參數設置:`set global innodb_buffer_pool_size=32*1024*1024*1024;# 增加buffer_pool 防止Error1206The total number of locks exceeds the lock table size 資源不足set global sync_binlog=20000;set global innodb_flush_log_at_trx_commit=2;set global innodb_io_capacity=600000;set global innodb_io_capacity_max=1200000; # innodb_io_capacity需要設置兩次show variables like "%innodb_io%"; # 驗證以上設置screen 下執行:time mysql -S /data/mysql/3306/data/mysqld.sock -p"" dbname -NBe "ALTER TABLE tablename MODIFY COLUMN open_id VARCHAR(500) NULL DEFAULT NULL COMMENT "Id" COLLATE "utf8mb4_bin";"查看DDL進度:SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED  FROM performance_schema.events_stages_current;3.擴容完成后，等待延遲同步M-S1-S2 數據同步至主從一致,對比主從Gtid4.移除S1,建立M-S2的主從關系S1 (可選)stop slave;reset slave all;systemctl stop mysql_3306S2stop slave;reset slave all;# MASTER_HOST="M主機IP"  CHANGE MASTER TO  MASTER_HOST="",  MASTER_USER="",  MASTER_PASSWORD=",  MASTER_PORT=3306,  MASTER_AUTO_POSITION=1,  MASTER_CONNECT_RETRY=10;start slave; (flush privileges;# 驗證數據可正常同步)5.備份S2恢復S1，建立M-S2-S1級聯復制物理備份S2,重做S2->S1 級聯主從rm -rf binlog/*rm -rf redolog/*xtrabackup --defaults-file=/data/mysql/3306/my.cnf.3306 --move-back --target-dir=/data/actionsky/xtrabackup_recovery/datachown -R mysql. data/chown -R mysql. binlog/*chown -R mysql. redolog/*systemctl start mysql_3306set global gtid_purged="";reset slave all;# MASTER_HOST="S2主機IP"  ,已擴容變更完的主機CHANGE MASTER TO  MASTER_HOST="",  MASTER_USER="",  MASTER_PASSWORD="",  MASTER_PORT=3306,  MASTER_AUTO_POSITION=1,  MASTER_CONNECT_RETRY=10;`MySQL8.0版本需要在上面語句中添加 GET_MASTER_PUBLIC_KEY=1; #防止 Last_IO_Errno: 2061 message: Authentication plugin "caching_sha2_password" reported error: Authentication requires secure connection.`start slave;6.應用停服，等待主從數據一致主庫停服+可設置read_only+flush privileges，對比主從Gtid7.最終S2成為主庫，S1為從庫應用更改配置連接新主庫。S2上:stop slave;reset slave all;set global read_only=0;set global super_read_only=0;`show master status\G 觀察是否有新事務寫入`收尾：還原第2步的參數設置。set global interactive_timeout=28800;set global wait_timeout =28800;set global innodb_buffer_pool_size=8*1024*1024*1024;set global slave_type_conversions="";set global sync_binlog=1;set global innodb_flush_log_at_trx_commit=1;set global innodb_io_capacity=2000;set global innodb_io_capacity_max=4000;

補充場景: 基于磁盤IO能力的測試

直接在主庫上修改，且無流量的情況下：
場景1，磁盤是NVME的物理機，4億數據大約需要5個小時(磁盤性能1G/s)。
場景2，磁盤是機械盤的虛擬機，此數據量大約需要40個小時(磁盤性能100M/s)。

五、總結

• 使用級聯，對于業務側來說，時間成本主要在應用更改連接和回歸驗證。如果從庫無流量，不需要等待業務低峰。
• OnlineDDL可通過修改參數，提高效率，其中雙一參數會影響數據安全，推薦業務低峰期操作。
• Gh-ost 適合變更時間寬裕的場景，業務低峰期操作，可調整參數加快進度，自定義切換的時間。
• 以上方式均不推薦多個DDL同時進行，即并行DDL。
• 大表操作和大數據量操作，需要我們貼合場景找到合適的變更方案，不需要最優，需要合適。

福利時間：分享一個速查表

到此這篇關于MySQL級聯復制下進行大表的字段擴容的文章就介紹到這了,更多相關mysql字段擴容內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！