搜索
首页数据库mysql教程MySQL级联复制下怎么进行大表的字段扩容

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 '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'; #看下此表的数据量

既然是大表,我们应该使用什么方式做变更呢?

三、方案选择

下文中的 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;

补充场景: 基于磁盘IO能力的测试

直接在主库上修改,且无流量的情况下:
场景1,磁盘是NVME的物理机,4亿数据大约需要5个小时(磁盘性能1G/s)。
场景2,磁盘是机械盘的虚拟机,此数据量大约需要40个小时(磁盘性能100M/s)。

以上是MySQL级联复制下怎么进行大表的字段扩容的详细内容。更多信息请关注PHP中文网其他相关文章!

声明
本文转载于:亿速云。如有侵权,请联系admin@php.cn删除
解释InnoDB缓冲池及其对性能的重要性。解释InnoDB缓冲池及其对性能的重要性。Apr 19, 2025 am 12:24 AM

InnoDBBufferPool通过缓存数据和索引页来减少磁盘I/O,提升数据库性能。其工作原理包括:1.数据读取:从BufferPool中读取数据;2.数据写入:修改数据后写入BufferPool并定期刷新到磁盘;3.缓存管理:使用LRU算法管理缓存页;4.预读机制:提前加载相邻数据页。通过调整BufferPool大小和使用多个实例,可以优化数据库性能。

MySQL与其他编程语言:一种比较MySQL与其他编程语言:一种比较Apr 19, 2025 am 12:22 AM

MySQL与其他编程语言相比,主要用于存储和管理数据,而其他语言如Python、Java、C 则用于逻辑处理和应用开发。 MySQL以其高性能、可扩展性和跨平台支持着称,适合数据管理需求,而其他语言在各自领域如数据分析、企业应用和系统编程中各有优势。

学习MySQL:新用户的分步指南学习MySQL:新用户的分步指南Apr 19, 2025 am 12:19 AM

MySQL值得学习,因为它是强大的开源数据库管理系统,适用于数据存储、管理和分析。1)MySQL是关系型数据库,使用SQL操作数据,适合结构化数据管理。2)SQL语言是与MySQL交互的关键,支持CRUD操作。3)MySQL的工作原理包括客户端/服务器架构、存储引擎和查询优化器。4)基本用法包括创建数据库和表,高级用法涉及使用JOIN连接表。5)常见错误包括语法错误和权限问题,调试技巧包括检查语法和使用EXPLAIN命令。6)性能优化涉及使用索引、优化SQL语句和定期维护数据库。

MySQL:初学者的基本技能MySQL:初学者的基本技能Apr 18, 2025 am 12:24 AM

MySQL适合初学者学习数据库技能。1.安装MySQL服务器和客户端工具。2.理解基本SQL查询,如SELECT。3.掌握数据操作:创建表、插入、更新、删除数据。4.学习高级技巧:子查询和窗口函数。5.调试和优化:检查语法、使用索引、避免SELECT*,并使用LIMIT。

MySQL:结构化数据和关系数据库MySQL:结构化数据和关系数据库Apr 18, 2025 am 12:22 AM

MySQL通过表结构和SQL查询高效管理结构化数据,并通过外键实现表间关系。1.创建表时定义数据格式和类型。2.使用外键建立表间关系。3.通过索引和查询优化提高性能。4.定期备份和监控数据库确保数据安全和性能优化。

MySQL:解释的关键功能和功能MySQL:解释的关键功能和功能Apr 18, 2025 am 12:17 AM

MySQL是一个开源的关系型数据库管理系统,广泛应用于Web开发。它的关键特性包括:1.支持多种存储引擎,如InnoDB和MyISAM,适用于不同场景;2.提供主从复制功能,利于负载均衡和数据备份;3.通过查询优化和索引使用提高查询效率。

SQL的目的:与MySQL数据库进行交互SQL的目的:与MySQL数据库进行交互Apr 18, 2025 am 12:12 AM

SQL用于与MySQL数据库交互,实现数据的增、删、改、查及数据库设计。1)SQL通过SELECT、INSERT、UPDATE、DELETE语句进行数据操作;2)使用CREATE、ALTER、DROP语句进行数据库设计和管理;3)复杂查询和数据分析通过SQL实现,提升业务决策效率。

初学者的MySQL:开始数据库管理初学者的MySQL:开始数据库管理Apr 18, 2025 am 12:10 AM

MySQL的基本操作包括创建数据库、表格,及使用SQL进行数据的CRUD操作。1.创建数据库:CREATEDATABASEmy_first_db;2.创建表格:CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入数据:INSERTINTObooks(title,author,published_year)VA

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热工具

Dreamweaver Mac版

Dreamweaver Mac版

视觉化网页开发工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

mPDF

mPDF

mPDF是一个PHP库,可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件,并处理不同的语言。与原始脚本如HTML2FPDF相比,它的速度较慢,并且在使用Unicode字体时生成的文件较大,但支持CSS样式等,并进行了大量增强。支持几乎所有语言,包括RTL(阿拉伯语和希伯来语)和CJK(中日韩)。支持嵌套的块级元素(如P、DIV),

安全考试浏览器

安全考试浏览器

Safe Exam Browser是一个安全的浏览器环境,用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问,并防止学生使用未经授权的资源。

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

将Eclipse与SAP NetWeaver应用服务器集成。