MySQL級(jí)聯(lián)復(fù)制下如何進(jìn)行大表的字段擴(kuò)容
目錄
- MySQL級(jí)聯(lián)復(fù)制下進(jìn)行大表的字段擴(kuò)容
- 一、背景
- 二、庫表信息
- 三、方案選擇
- 四、如何進(jìn)行操作
- 五、總結(jié)
MySQL級(jí)聯(lián)復(fù)制下進(jìn)行大表的字段擴(kuò)容
作者：雷文霆
愛可生華東交付服務(wù)部 DBA 成員，主要負(fù)責(zé)Mysql故障處理及相關(guān)技術(shù)支持。愛好看書，電影。座右銘，每一個(gè)不曾起舞的日子，都是對(duì)生命的辜負(fù)。
本文來源：原創(chuàng)投稿
*愛可生開源社區(qū)出品，原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用，轉(zhuǎn)載請(qǐng)聯(lián)系小編并注明來源。
一、背景
某客戶的業(yè)務(wù)中有一張約4億行的表，因?yàn)闃I(yè)務(wù)擴(kuò)展，表中open_id varchar(50) 需要擴(kuò)容到 varchar(500).
變更期間盡量減少對(duì)主庫的影響(最好是不要有任何影響->最終爭(zhēng)取了4個(gè)小時(shí)的窗口期)。
二、庫表信息
環(huán)境：Mysql 8.0.22
1主1從 基于Gtid復(fù)制
1.第一個(gè)問題，這是一張大表嗎? 是的，請(qǐng)看
此表的ibd 文件280G + count長(zhǎng)時(shí)間無返回 + 使用備庫看了一下確認(rèn)行數(shù)>4億
以下語句也可以查看：show table status from dbname like "tablename"\G # Rows 的值不準(zhǔn)，有時(shí)誤差有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"; #看下此表的數(shù)據(jù)量
既然是大表，我們應(yīng)該使用什么方式做變更呢？
三、方案選擇
下文中的 M 表示主庫，S1 為從1 ，S2 為從2
為什么我們沒有選擇前3種方案？
根據(jù)實(shí)際情況評(píng)估，本次業(yè)務(wù)側(cè)的需求是此表24h都有業(yè)務(wù)流量，且不接受超過4小時(shí)的業(yè)務(wù)不可用時(shí)間
OnlineDDL的方式，ALGORITHM=COPY時(shí)，期間會(huì)阻塞DML(只讀)，最后主副表rename操作時(shí)(不可讀寫)，直到DDL完成(其中需要的時(shí)間不確定)。
Gh-ost的方式，推薦的模式為連接從庫，在主庫轉(zhuǎn)換，此模式對(duì)主庫影響最小，可通過參數(shù)設(shè)置流控。致命的缺點(diǎn)是此工具的變更時(shí)間太長(zhǎng)，4億的表，測(cè)試環(huán)境使用了70個(gè)小時(shí)。最后我們還需要下發(fā)切換命令及手動(dòng)刪除中間表*_del。如果是1主2從還是比較推薦這種方式的，因?yàn)檫€有一個(gè)從庫可以保障數(shù)據(jù)安全。
Pt-osc 和Gh-ost都屬于第三方，Pt-osc 對(duì)大表的操作和OnlineDDL有一個(gè)共同的缺點(diǎn)就是失敗回滾的代價(jià)很大。
如果是低版本如MySQL<5.7可以使用，理論上OnlineDDL是在MySQL5.6.7開始支持，剛開始支持的不是很好，可適當(dāng)取舍。
最后我們選擇了，DBA最喜愛(xin ku)的一種方式，在M-S1-S2級(jí)聯(lián)復(fù)制下進(jìn)行。
四、如何進(jìn)行操作
- 新建一個(gè)S1的從庫，構(gòu)建M-S1-S2級(jí)聯(lián)復(fù)制
- 使用OnlineDDL在S2上進(jìn)行字段擴(kuò)容 (優(yōu)點(diǎn)是期間M-S1的主從不受影響)
- 擴(kuò)容完成后，等待延遲同步M-S1-S2 (降低S2與M的數(shù)據(jù)差異,并進(jìn)行數(shù)據(jù)驗(yàn)證)
- 移除S1,建立M-S2的主從關(guān)系(使S2繼續(xù)同步M的數(shù)據(jù))
- 備份S2恢復(fù)S1，建立M-S2-S1級(jí)聯(lián)復(fù)制
- 應(yīng)用停服，等待主從數(shù)據(jù)一致(優(yōu)點(diǎn)是差異數(shù)據(jù)量的同步時(shí)間很短)
- 最終S2成為主庫，S1為從庫(應(yīng)用需要修改前端連接信息)
- 應(yīng)用進(jìn)行回歸驗(yàn)證
以上內(nèi)容看上去很復(fù)雜，本質(zhì)上就是備份恢復(fù)。讀者可將其做為備選方案。分享一下具體步驟?
環(huán)境裝備:開啟Gtid,注意M,S1 binlog保存時(shí)長(zhǎng),磁盤剩余空間大于待變更表的2倍show global variables like "binlog_expire_logs_seconds"; # 默認(rèn)604800set global binlog_expire_logs_seconds=1209600; # 主庫和級(jí)聯(lián)主庫都需要設(shè)置1.搭建 1主2從的級(jí)聯(lián)復(fù)制，M -> S1 -> S2 ，安裝MySQL注意本次環(huán)境lower_case_table_names = 0 2.在S2 上做字段擴(kuò)容。 預(yù)估 10個(gè)小時(shí)`參數(shù)設(shè)置:`set global slave_type_conversions="ALL_NON_LOSSY"; # 防止復(fù)制報(bào)錯(cuò)SQL_Errno: 13146，屬于字段類型長(zhǎng)度不一致無法回放set global interactive_timeout=144000;set global wait_timeout =144000;`磁盤IO參數(shù)設(shè)置:`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需要設(shè)置兩次show variables like "%innodb_io%"; # 驗(yàn)證以上設(shè)置screen 下執(zhí)行: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進(jìn)度:SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;3.擴(kuò)容完成后，等待延遲同步M-S1-S2 數(shù)據(jù)同步至主從一致,對(duì)比主從Gtid4.移除S1,建立M-S2的主從關(guān)系S1 (可選)stop slave;reset slave all;systemctl stop mysql_3306S2stop slave;reset slave all;# MASTER_HOST="M主機(jī)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;# 驗(yàn)證數(shù)據(jù)可正常同步)5.備份S2恢復(fù)S1，建立M-S2-S1級(jí)聯(lián)復(fù)制物理備份S2,重做S2->S1 級(jí)聯(lián)主從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主機(jī)IP" ,已擴(kuò)容變更完的主機(jī)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.應(yīng)用停服，等待主從數(shù)據(jù)一致主庫停服+可設(shè)置read_only+flush privileges，對(duì)比主從Gtid7.最終S2成為主庫，S1為從庫應(yīng)用更改配置連接新主庫。S2上:stop slave;reset slave all;set global read_only=0;set global super_read_only=0;`show master status\G 觀察是否有新事務(wù)寫入`收尾：還原第2步的參數(shù)設(shè)置。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;
補(bǔ)充場(chǎng)景: 基于磁盤IO能力的測(cè)試
直接在主庫上修改，且無流量的情況下：
場(chǎng)景1，磁盤是NVME的物理機(jī)，4億數(shù)據(jù)大約需要5個(gè)小時(shí)(磁盤性能1G/s)。
場(chǎng)景2，磁盤是機(jī)械盤的虛擬機(jī)，此數(shù)據(jù)量大約需要40個(gè)小時(shí)(磁盤性能100M/s)。
五、總結(jié)
- 使用級(jí)聯(lián)，對(duì)于業(yè)務(wù)側(cè)來說，時(shí)間成本主要在應(yīng)用更改連接和回歸驗(yàn)證。如果從庫無流量，不需要等待業(yè)務(wù)低峰。
- OnlineDDL可通過修改參數(shù)，提高效率，其中雙一參數(shù)會(huì)影響數(shù)據(jù)安全，推薦業(yè)務(wù)低峰期操作。
- Gh-ost 適合變更時(shí)間寬裕的場(chǎng)景，業(yè)務(wù)低峰期操作，可調(diào)整參數(shù)加快進(jìn)度，自定義切換的時(shí)間。
- 以上方式均不推薦多個(gè)DDL同時(shí)進(jìn)行，即并行DDL。
- 大表操作和大數(shù)據(jù)量操作，需要我們貼合場(chǎng)景找到合適的變更方案，不需要最優(yōu)，需要合適。
福利時(shí)間：分享一個(gè)速查表
到此這篇關(guān)于MySQL級(jí)聯(lián)復(fù)制下進(jìn)行大表的字段擴(kuò)容的文章就介紹到這了,更多相關(guān)mysql字段擴(kuò)容內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！
網(wǎng)公網(wǎng)安備