Bagaimana untuk mengembangkan medan jadual besar di bawah replikasi lata MySQL

Peluasan medan jadual besar di bawah replikasi lata MySQL

1 Latar Belakang

Perniagaan pelanggan mempunyai jadual dengan kira-kira 400 juta baris Disebabkan oleh pengembangan perniagaan, open_id dalam jadual varchar (50) perlu dikembangkan kepada varchar(500
Semasa perubahan, cuba untuk meminimumkan kesan pada perpustakaan utama (sebaik-baiknya tidak mempunyai sebarang kesan -> Akhirnya, kami berusaha untuk mendapatkan 4). -tempoh tetingkap jam).

2. Maklumat jadual pangkalan data

Persekitaran: Mysql 8.0.22
1 salinan master 1 slave berdasarkan Gtid

1 gambar Jadual? Ya, sila lihat

Fail ibd jadual ini adalah 280G + kiraan tidak kembali untuk masa yang lama + gunakan pangkalan data siap sedia untuk menyemak bilangan baris > 400 juta

以下语句也可以查看：
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'; #看下此表的数据量

Memandangkan ia adalah meja yang besar, apakah kaedah yang perlu kita gunakan untuk membuat perubahan?

3. Pilihan penyelesaian

M dalam berikut mewakili perpustakaan utama, S1 ialah hamba 1, S2 ialah hamba 2

方式	优点	缺点	可行性
OnlineDDL	原生，使用中间临时表	ALGORITHM=COPY时，会阻塞DML，推荐版本>MySQL5.7	5星
Gh-ost	使用binlog+回放线程代替触发器	第三方工具，根据不同的参数导致执行时间较长	4星
Pt-osc	版本兼容性好,使用触发器保持主副表一致	第三方工具，且使用限制较多	3星
M-S1-S2	时间可预估	级联复制，人工操作	1星

Mengapa kita tidak memilih 3 pilihan pertama?

Berdasarkan penilaian situasi sebenar, permintaan pada bahagian perniagaan kali ini ialah Jadual ini mempunyai trafik perniagaan 24 jam sehari dan tidak menerima ketidaktersediaan perniagaan selama lebih daripada 4 jam

kaedah onlineddl, apabila algoritma = salinan, DML akan disekat dalam tempoh (baca sahaja). selesai (masa yang diperlukan tidak pasti).

Adalah disyorkan untuk menyambung ke pustaka hamba dalam mod Gh-ost untuk penukaran Mod ini mempunyai kesan paling sedikit pada pustaka utama dan kawalan aliran boleh ditetapkan melalui parameter. Kelemahan maut alat ini ialah perubahan mengambil masa terlalu lama Untuk jadual dengan 400 juta keping data, ia mengambil masa 70 jam dalam persekitaran ujian. Akhir sekali, kami juga perlu mengeluarkan arahan pensuisan dan memadam secara manual jadual perantaraan *_del. Kelebihan penyelesaian ini ialah terdapat pangkalan data hamba untuk memastikan keselamatan data, jadi jika anda menggunakan seni bina 1 master 2 hamba, ia lebih disyorkan.

Pt-osc dan Gh-ost kedua-duanya dimiliki oleh pihak ketiga yang mengendalikan jadual besar Pt-osc dan OnlineDDL mempunyai kelemahan yang sama, iaitu kos pemulangan semula pada kegagalan adalah sangat tinggi.

Jika ia adalah versi yang lebih rendah seperti MySQL

Akhirnya, kami memilih kaedah kegemaran (xin ku) DBA, yang dilakukan di bawah replikasi lata M-S1-S2.

4. Cara mengendalikan

• Buat pangkalan data hamba S1 baharu dan bina replikasi lata M-S1-S2

• Gunakan OnlineDDL untuk melakukan pengembangan medan pada S2 (kelebihannya ialah induk M-S1 tidak pernah terjejas dalam tempoh tersebut)

• Selepas pengembangan selesai, tunggu penyegerakan tertunda M -S1-S2 (kurangkan perbezaan Data S2 dengan M dan lakukan pengesahan data)

• Alih keluar S1 dan wujudkan hubungan tuan-hamba antara M-S2 (membenarkan S2 meneruskan penyegerakan data M)

• Sandarkan S2 dan pulihkan S1, wujudkan replikasi lata M-S2-S1

• Hentikan aplikasi dan tunggu data tuan-hamba untuk konsisten (kelebihannya ialah masa penyegerakan jumlah data yang berbeza Sangat singkat)

• Akhirnya S2 menjadi perpustakaan induk dan S1 ialah perpustakaan hamba (aplikasi perlu mengubah suai bahagian hadapan- tamatkan maklumat sambungan)

• Aplikasi menjalankan pengesahan regresi

Kandungan di atas mungkin kelihatan rumit, tetapi ia pada asasnya adalah sandaran dan pemulihan . Pembaca boleh menganggap ini sebagai alternatif. Kongsi langkah tertentu?

环境装备:开启Gtid,注意M,S1 binlog保存时长,磁盘剩余空间大于待变更表的2倍
show global variables like 'binlog_expire_logs_seconds'; # 默认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='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 
数据同步至主从一致,对比主从Gtid
4.移除S1,建立M-S2的主从关系
S1 (可选)
stop slave;
reset slave all;
systemctl stop mysql_3306
S2
stop 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/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主机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，对比主从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='';
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;

Senario tambahan: Ujian berdasarkan keupayaan IO cakera

Ubah suai terus pada pustaka utama tanpa trafik Kes:
Senario 1, cakera ialah mesin fizikal NVME, dan ia mengambil masa kira-kira 5 jam untuk memproses 400 juta data (prestasi cakera 1G/s).
Senario 2, cakera ialah mesin maya dengan cakera mekanikal Jumlah data ini mengambil masa kira-kira 40 jam (prestasi cakera 100M/s).

Atas ialah kandungan terperinci Bagaimana untuk mengembangkan medan jadual besar di bawah replikasi lata MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!