MySQL級聯複製下怎麼進行大表的字段擴容-mysql教程-PHP中文網

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 &#39;tablename&#39;\G # Rows 的值不准，有时误差有2倍

SELECT a.table_schema,a.table_name,concat(round(sum(DATA_LENGTH/1024/1024)+sum(INDEX_LENGTH/1024/1024),2) ,&#39;MB&#39;)total_size,concat(round(sum(DATA_LENGTH/1024/1024),2),&#39;MB&#39;) AS data_size,concat(round(sum(INDEX_LENGTH/1024/1024),2),&#39;MB&#39;) AS index_size FROM information_schema.TABLES a WHERE a.table_schema = &#39;dbname&#39; AND a.table_name = &#39;tablename&#39;; #看下此表的数据量

登入後複製

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

三、方案選擇

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

方式	優點	缺點	可行性
OnlineDDL	原生，使用中間暫存表	ALGORITHM=COPY時，會阻塞DML，建議版本>MySQL5.7	5星
#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 &#39;binlog_expire_logs_seconds&#39;; # 默认604800
set 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=&#39;ALL_NON_LOSSY&#39;; # 防止复制报错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 &#39;%innodb_io%&#39;; # 验证以上设置
screen 下执行:
time mysql -S /data/mysql/3306/data/mysqld.sock -p&#39;&#39; dbname -NBe "ALTER TABLE tablename MODIFY COLUMN open_id VARCHAR(500) NULL DEFAULT NULL COMMENT &#39;Id&#39; COLLATE &#39;utf8mb4_bin&#39;;"
查看DDL进度:
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED  FROM performance_schema.events_stages_current;
3.扩容完成后，等待延迟同步M-S1-S2 
数据同步至主从一致,对比主从Gtid
4.移除S1,建立M-S2的主从关系
S1 (可选)
stop slave;
reset slave all;
systemctl stop mysql_3306
S2
stop slave;
reset slave all;
# MASTER_HOST=&#39;M主机IP&#39;  
CHANGE MASTER TO
  MASTER_HOST=&#39;&#39;,
  MASTER_USER=&#39;&#39;,
  MASTER_PASSWORD=&#39;,
  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/data
chown -R mysql. data/
chown -R mysql. binlog/*
chown -R mysql. redolog/*
systemctl start mysql_3306
set global gtid_purged=&#39;&#39;;
reset slave all;
# MASTER_HOST=&#39;S2主机IP&#39;  ,已扩容变更完的主机
CHANGE MASTER TO
  MASTER_HOST=&#39;&#39;,
  MASTER_USER=&#39;&#39;,
  MASTER_PASSWORD=&#39;&#39;,
  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 &#39;caching_sha2_password&#39; reported error: Authentication requires secure connection.`
start slave;
6.应用停服，等待主从数据一致
主库停服+可设置read_only+flush privileges，对比主从Gtid
7.最终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=&#39;&#39;;
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;

登入後複製