您好,登錄后才能下訂單哦!
本篇內(nèi)容介紹了“MySQL級(jí)聯(lián)復(fù)制下怎么進(jìn)行大表的字段擴(kuò)容”的有關(guān)知識(shí),在實(shí)際案例的操作過(guò)程中,不少人都會(huì)遇到這樣的困境,接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧!希望大家仔細(xì)閱讀,能夠?qū)W有所成!
某客戶的業(yè)務(wù)中有一張約4億行的表,因?yàn)闃I(yè)務(wù)擴(kuò)展,表中open_id varchar(50) 需要擴(kuò)容到 varchar(500).
變更期間盡量減少對(duì)主庫(kù)的影響(最好是不要有任何影響->最終爭(zhēng)取了4個(gè)小時(shí)的窗口期)。
環(huán)境:Mysql 8.0.22
1主1從 基于Gtid復(fù)制
1.第一個(gè)問(wèn)題,這是一張大表嗎? 是的,請(qǐng)看
此表的ibd 文件280G + count長(zhǎng)時(shí)間無(wú)返回 + 使用備庫(kù)看了一下確認(rèn)行數(shù)>4億
以下語(yǔ)句也可以查看: 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 表示主庫(kù),S1 為從1 ,S2 為從2
方式 | 優(yōu)點(diǎn) | 缺點(diǎn) | 可行性 |
---|---|---|---|
OnlineDDL | 原生,使用中間臨時(shí)表 | ALGORITHM=COPY時(shí),會(huì)阻塞DML,推薦版本>MySQL5.7 | 5星 |
Gh-ost | 使用binlog+回放線程代替觸發(fā)器 | 第三方工具,根據(jù)不同的參數(shù)導(dǎo)致執(zhí)行時(shí)間較長(zhǎng) | 4星 |
Pt-osc | 版本兼容性好,使用觸發(fā)器保持主副表一致 | 第三方工具,且使用限制較多 | 3星 |
M-S1-S2 | 時(shí)間可預(yù)估 | 級(jí)聯(lián)復(fù)制,人工操作 | 1星 |
為什么我們沒(méi)有選擇前3種方案?
根據(jù)實(shí)際情況評(píng)估,本次業(yè)務(wù)側(cè)的需求是此表24h都有業(yè)務(wù)流量,且不接受超過(guò)4小時(shí)的業(yè)務(wù)不可用時(shí)間
OnlineDDL的方式,ALGORITHM=COPY時(shí),期間會(huì)阻塞DML(只讀),最后主副表rename操作時(shí)(不可讀寫),直到DDL完成(其中需要的時(shí)間不確定)。
Gh-ost的方式,推薦的模式為連接從庫(kù),在主庫(kù)轉(zhuǎn)換,此模式對(duì)主庫(kù)影響最小,可通過(guò)參數(shù)設(shè)置流控。致命的缺點(diǎn)是此工具的變更時(shí)間太長(zhǎng),4億的表,測(cè)試環(huán)境使用了70個(gè)小時(shí)。最后我們還需要下發(fā)切換命令及手動(dòng)刪除中間表*_del。如果是1主2從還是比較推薦這種方式的,因?yàn)檫€有一個(gè)從庫(kù)可以保障數(shù)據(jù)安全。
Pt-osc 和Gh-ost都屬于第三方,Pt-osc 對(duì)大表的操作和OnlineDDL有一個(gè)共同的缺點(diǎn)就是失敗回滾的代價(jià)很大。
如果是低版本如MySQL<5.7可以使用,理論上OnlineDDL是在MySQL5.6.7開(kāi)始支持,剛開(kāi)始支持的不是很好,可適當(dāng)取舍。
最后我們選擇了,DBA最喜愛(ài)(xin ku)的一種方式,在M-S1-S2級(jí)聯(lián)復(fù)制下進(jìn)行。
新建一個(gè)S1的從庫(kù),構(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成為主庫(kù),S1為從庫(kù)(應(yīng)用需要修改前端連接信息)
應(yīng)用進(jìn)行回歸驗(yàn)證
以上內(nèi)容看上去很復(fù)雜,本質(zhì)上就是備份恢復(fù)。讀者可將其做為備選方案。分享一下具體步驟?
環(huán)境裝備:開(kāi)啟Gtid,注意M,S1 binlog保存時(shí)長(zhǎng),磁盤剩余空間大于待變更表的2倍 show global variables like 'binlog_expire_logs_seconds'; # 默認(rèn)604800 set global binlog_expire_logs_seconds=1209600; # 主庫(kù)和級(jí)聯(lián)主庫(kù)都需要設(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)度不一致無(wú)法回放 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ì)比主從Gtid 4.移除S1,建立M-S2的主從關(guān)系 S1 (可選) stop slave; reset slave all; systemctl stop mysql_3306 S2 stop 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/data chown -R mysql. data/ chown -R mysql. binlog/* chown -R mysql. redolog/* systemctl start mysql_3306 set 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版本需要在上面語(yǔ)句中添加 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ù)一致 主庫(kù)停服+可設(shè)置read_only+flush privileges,對(duì)比主從Gtid 7.最終S2成為主庫(kù),S1為從庫(kù) 應(yīng)用更改配置連接新主庫(kù)。 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è)試
直接在主庫(kù)上修改,且無(wú)流量的情況下:
場(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)。
“MySQL級(jí)聯(lián)復(fù)制下怎么進(jìn)行大表的字段擴(kuò)容”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站,小編將為大家輸出更多高質(zhì)量的實(shí)用文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。