MySQL 계단식 복제에서 대규모 테이블의 필드를 확장하는 방법

MySQL 캐스케이드 복제 시 대형 테이블의 필드 확장

1. 배경

고객의 비즈니스에는 약 4억 행의 테이블이 있습니다. 비즈니스 확장으로 인해 테이블의 open_id varchar(50)을 varchar(500)로 확장해야 합니다. ) ).
변경하는 동안 메인 라이브러리에 미치는 영향을 최소화하도록 노력하세요(영향을 주지 않는 것이 바람직합니다 -> 결국 4시간의 창구 기간을 위해 노력했습니다).

2. 데이터베이스 테이블 정보

환경: Mysql 8.0.22
1 Gtid 기반의 마스터 1 슬레이브 복사본

1. 첫 번째 질문입니다. 네, 이 테이블의 ibd를 보세요

파일이 280G + 카운트가 오랫동안 반환되지 않았습니다 + 대기 데이터베이스를 사용하여 행 수가 > 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'; #看下此表的数据量

큰 테이블이므로 어떤 방법을 사용하여 만들어야 합니까? 변화?

3. 아래 솔루션 선택

M은 메인 라이브러리를 나타내며, S1은 슬레이브 1, S2는 슬레이브 2

Method	장점	단점	타당성
온라인DDL	네이티브, 중간 임시 테이블	ALGORITHM=COPY 사용 시 DML이 차단됩니다. 권장 버전 >MySQL5.7	별 5개
Gh-ost	트리거 대신 binlog + 재생 스레드를 사용하세요	파티 도구는 다양한 매개변수에 따라 실행 시간이 길어집니다	별 4개
Pt-osc	트리거를 사용하여 기본 테이블과 보조 테이블의 일관성을 유지하는 우수한 버전 호환성	타사 도구 및 추가 사용 제한	별 3개
M-S1-S2	시간 추정 가능	캐스케이드 복제, 수동 조작	별 1개

처음 3개 옵션을 선택하지 않은 이유 ?

실제 상황 평가에 따르면 이번 비즈니스 측 수요는 이 테이블에는 24시간 비즈니스 트래픽이 있으며, 4시간 이상의 비즈니스 비가용 시간은 허용되지 않습니다.

OnlineDDL 방식, ALGORITHM =COPY, 해당 기간은 DDL이 완료될 때까지 기본 및 보조 테이블의 최종 이름 바꾸기 작업 동안(읽기 및 쓰기 불가능) DML(읽기 전용)이 차단됩니다(필요한 시간은 불확실함).

변환을 위해 슬레이브 라이브러리에 Gh-ost 모드로 연결하는 것이 좋습니다. 이 모드는 메인 라이브러리에 미치는 영향이 가장 적으며 매개변수를 통해 흐름 제어를 설정할 수 있습니다. 이 도구의 치명적인 약점은 4억 개의 데이터가 포함된 테이블의 경우 테스트 환경에서 변경 시간이 70시간이 걸린다는 것입니다. 마지막으로 전환 명령을 실행하고 중간 테이블 *_del을 수동으로 삭제해야 합니다. 이 솔루션의 장점은 슬레이브 데이터베이스가 있어 데이터 보안을 보장하므로 1마스터 2슬레이브 아키텍처를 사용하는 경우 더욱 권장됩니다.

Pt-osc와 Gh-ost는 모두 제3자에 속합니다. Pt-osc의 대형 테이블 운영과 OnlineDDL에는 실패 시 롤백 비용이 매우 높다는 공통적인 단점이 있습니다.

MySQL

결국 우리는 M-S1-S2 캐스케이드 복제에서 수행되는 DBA가 선호하는 (xin ku) 방법을 선택했습니다.

4. 작동 방법

• 새로운 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'; # 默认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;

추가 시나리오: 디스크 IO 기능을 기반으로 테스트

메인 라이브러리에서 직접 수정하고 트래픽이 없습니다.
시나리오 1, 디스크는 NVME 물리적 머신, 4억 데이터 약 5시간 정도 소요됩니다(디스크 성능 1G/s).
시나리오 2, 디스크가 기계식 디스크인 가상 머신의 경우 이 데이터 양은 약 40시간이 걸립니다(디스크 성능 100M/s).

위 내용은 MySQL 계단식 복제에서 대규모 테이블의 필드를 확장하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!