首页 > 数据库 >MySQL gh-ost 工具使用详解

MySQL gh-ost 工具使用详解

时间：2024-10-07 10:12:30浏览次数：7  
标签：DDL ost State mysql MySQL total gh

前言

MySQL 的同步机制比较单纯，主库上执行过的 DML 和 DDL 会在从库上再执行一次，那么主库上需要 10min 才能执行完的 DDL 理论上在从库至少也要花费 10min 才能执行完，这意味着从库的同步会延迟 10min 以上，等 DDL 执行完之后才会继续追同步。

解决方案

从 MySQL 的同步原理来看，主要是 DDL 这个单独的操作会花费太久的时间，导致从库也会被卡主。

那么解决这个问题的办法就很容易想到：“拆解” DDL 的操作，把一个大操作（大事务同理）拆分成多个小操作，减少单次操作的时间。

“拆解” DDL 操作一般会用到 MySQL Online DDL 的工具，比如 pt-osc，facebook-osc，oak-online-alter-table，gh-ost 等。

这些工具的思路都比较类似，创建一个源表的镜像表，先执行完表结构变更，再把源表的全量数据和增量数据都同步过去，因此可以减少单个 DDL 操作引发的同步延迟，但是不能完全避免，因为大批量 insert 数据本身也是可能会产生一些延迟的，只是这部分延迟的时间会比大多数 DDL 要小很多。

工具介绍

本文会介绍 gh-ost，由 Github 维护的 MySQL online DDL 工具，同样使用了镜像表的形式，但是放弃了使用低效的 trigger，而是从 binlog 中提取需要的增量数据来保持镜像表与源表的数据一致性。

整个 Online DDL 操作仅在最终 rename 源表与镜像表时会阻塞几秒钟的读写。

工作原理

go-ost 的操作流程大致如下：

  • 在 Master 中创建镜像表（_tablename_gho）和心跳表（_tablename_ghc）。
  • 向心跳表中写入 Online-DDL 的进度以及时间。
  • 在镜像表上执行 ALTER 操作。
  • 伪装成 slave 连接到 Master 的某个 Slave 实例上获取 binlog 的信息（默认连接 Slave，也可以连 Master）。
  • 在 Master 中完成镜像表的数据同步：
    • 从源表中拷贝数据到镜像表；
    • 依据 Binlog 信息完成增量数据的变更;
  • 在源表上加锁;
  • 确认心跳表中的时间，确保数据是完全同步的；
  • 用镜像表替换源表。
  • Online DDL 完成。
  • 未来考虑会支持的功能或特性：
    • 支持外键。
    • gh-ost 进程意外中断以后，可以新启动一个进程继续进行 Online DDL。

_tablename_ghc 内容如下：

ghc 的内容

使用限制

  • binlog 格式必须使用 row，且binlog_row_image必须是 FULL。
  • 需求的权限为SUPER, REPLICATION CLIENT, REPLICATION SLAVE on *.* and ALL on dbname.*
    • 如果确认 binlog 的格式为 row，那么可以加上 -assume-rbr，则不再需要 super 权限。
    • 由于不支持 REPLICATION 相关的权限，TiDB 无法使用。
  • 不支持外键。
    • 不论源表是主表还是子表，都无法使用。
  • 不支持触发器。
  • 不支持包含 JSON 列的主键。
  • 迁移表需要有显示定义的主键，或者有非空的唯一索引。
  • 迁移工具不区分大小写英文字母，如果存在同名，但是大小写不同的表则无法迁移。
  • 迁移表的主键或者非空唯一索引包含枚举类型时，迁移效率会大幅度降低。

使用注意

  • 如果源表有非常多的数据，尽量分批次删除。
    • delete from table tablename_old limit 5000;
    • 或者在业务空闲时段用truncate table tablename_old清空表数据之后再 drop 表。
  • 单个 MySQL 实例上启动多个 gh-ost 来进行多个表的 Online DDL 操作时要制定-replica-server-id参数
  • 务必注意可用的磁盘空间，尤其是操作大表的时候。
    • gh-ost 的镜像表包含源表的所有数据，会额外占用一倍的磁盘。
    • gh-ost 在操作的过程中会产生大量的 binlog，且binlog_row_image必须为 FULL，会占用比较多的磁盘空间。
  • rename 列的操作可能会有问题，考虑 drop 和 add 的操作结合起来。
  • 默认会用同样的账号名和密码同时连接 master 和 slave，因此方便起见，直接用高权限账号会比较好。

使用示例

github 官网有安装包可以下载，参考 release note。

实际命令可以参考下面这个（已开启了 row 模式）：

gh-ost -max-load=Threads_running=20 \
            -critical-load=Threads_running=100 \
            -chunk-size=2000 -user="temp" -password="test" -host=10.10.1.10 \
            -allow-on-master -database="sbtest" -table="sbtest1" \
            -alter="engine=innodb" -cut-over=default \
            -exact-rowcount -concurrent-rowcount -default-retries=120 \
            -timestamp-old-table -assume-rbr -panic-flag-file=/tmp/ghost.panic.flag \
            -execute

如果存在从库或者只读实例，希望控制 gh-ost 工具不产生太大同步延迟，那么可以添加两个参数：

gh-ost -max-load=Threads_running=20 \
            -critical-load=Threads_running=100 \
            -max-lag-millis=5000 -throttle-control-replicas="10.10.1.100:3306"  \
            -chunk-size=2000 -user="temp" -password="test" -host=10.10.1.10 \
            -allow-on-master -database="sbtest" -table="sbtest1" \
            -alter="engine=innodb" -cut-over=default \
            -exact-rowcount -concurrent-rowcount -default-retries=120 \
            -timestamp-old-table -assume-rbr -panic-flag-file=/tmp/ghost.panic.flag \
            -execute

部分参数说明

以上文的命令内容为准：

max-load=Threads_running=20         超过50个client在执行SQL查询时,暂停Online DDL操作
critical-load=Threads_running=100   超过100个client在执行SQL查询时,中断Online DDL操作
chunk-size=2000                     每一次同步操作处理2000行数据
max-lag-millis=5000                 控制延迟时间，延迟超过阈值会等待延迟进入阈值范围内继续
throttle-control-replicas="ip:port" 指定从库地址，多个地址可以用逗号分隔
allow-on-master                     允许在主库执行Online DDL相关的所有操作
alter                               Online DDL的操作,仅需要部分alter语句(方括号部分)
                                    例：alter table sbtest.sbtest1 [add column t int not NULL]
cut-over=default                    数据同步完成后自动进行镜像表与源表的切换
exact-rowcount                      精确计算行数,提供更准确的进度
timestamp-old-table                 使用时间戳来命名旧表
assume-rbr                          跳过重启slave线程与row format检查,设置后无需super权限
panic-flag-file                     创建该文件后,会强制中断Online DDL操作

除了这些参数以外，gh-ost 还提供了非常多的方式来从外部暂停或者强制中止 Online DDL 的操作，详细的信息可以使用gh-ost --help命令进行查看。

输出结果示例

# Migrating `sbtest`.`sbtest1`; Ghost table is `sbtest`.`_sbtest1_gho`
# Migrating 10.10.1.10:3306; inspecting10.10.1.10:3306; executing on localhost-debian
# Migration started at Thu Jul 30 11:30:17 +0800 2020
# chunk-size: 3000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=50; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.sbtest.sbtest1.sock
Copy: 0/9863066 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000050:31635038; Lag: 0.03s, State: migrating; ETA: N/A
Copy: 0/9863066 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000050:31639503; Lag: 0.03s, State: migrating; ETA: N/A
Copy: 69000/9999998 0.7%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 2s(copy); streamer: mysql-bin.000050:44815698; Lag: 0.03s, State: migrating; ETA: 4m49s
Copy: 135000/9999998 1.4%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 3s(copy); streamer: mysql-bin.000050:57419220; Lag: 0.03s, State: migrating; ETA: 3m39s
Copy: 195000/9999998 2.0%; Applied: 0; Backlog: 0/1000; Time: 4s(total), 4s(copy); streamer: mysql-bin.000050:68877374; Lag: 0.03s, State: migrating; ETA: 3m21s
......（省略）
Copy: 9729000/9999998 97.3%; Applied: 0; Backlog: 0/1000; Time: 3m16s(total), 3m16s(copy); streamer: mysql-bin.000057:8595335; Lag: 0.04s, State: migrating; ETA: 5s
[2020/07/30 11:33:32] [info] binlogsyncer.go:723 rotate to (mysql-bin.000057, 4)
Copy: 9774000/9999998 97.7%; Applied: 0; Backlog: 0/1000; Time: 3m17s(total), 3m17s(copy); streamer: mysql-bin.000057:17190073; Lag: 0.03s, State: migrating; ETA: 4s
[2020/07/30 11:33:32] [info] binlogsyncer.go:723 rotate to (mysql-bin.000057, 4)
Copy: 9822000/9999998 98.2%; Applied: 0; Backlog: 0/1000; Time: 3m18s(total), 3m18s(copy); streamer: mysql-bin.000057:26357495; Lag: 0.04s, State: migrating; ETA: 3s
Copy: 9861000/9999998 98.6%; Applied: 0; Backlog: 0/1000; Time: 3m19s(total), 3m19s(copy); streamer: mysql-bin.000057:33806865; Lag: 0.03s, State: migrating; ETA: 2s
Copy: 9903000/9999998 99.0%; Applied: 0; Backlog: 0/1000; Time: 3m20s(total), 3m20s(copy); streamer: mysql-bin.000057:41828922; Lag: 0.03s, State: migrating; ETA: 1s
Copy: 9951000/9999998 99.5%; Applied: 0; Backlog: 0/1000; Time: 3m21s(total), 3m21s(copy); streamer: mysql-bin.000057:50996347; Lag: 0.03s, State: migrating; ETA: 0s
Copy: 9999998/9999998 100.0%; Applied: 0; Backlog: 0/1000; Time: 3m22s(total), 3m21s(copy); streamer: mysql-bin.000057:60354465; Lag: 0.03s, State: migrating; ETA: due
# Migrating `sbtest`.`sbtest1`; Ghost table is `sbtest`.`_sbtest1_gho`
# Migrating 10.10.1.10:3306; inspecting 10.10.1.10:3306; executing onlocalhost-debian
# Migration started at Thu Jul 30 11:30:17 +0800 2020
# chunk-size: 3000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=50; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.sbtest.sbtest1.sock
Copy: 9999998/9999998 100.0%; Applied: 0; Backlog: 0/1000; Time: 3m23s(total), 3m21s(copy); streamer: mysql-bin.000057:60359997; Lag: 0.03s, State: migrating; ETA: due
[2020/07/30 11:33:41] [info] binlogsyncer.go:164 syncer is closing...
[2020/07/30 11:33:41] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2020/07/30 11:33:41] [info] binlogsyncer.go:179 syncer is closed

可以看到日志内容中输出了详细的进度百分比和迁移的剩余时间，在预估维护结束的时间，查看 DDL 执行进度的时候会非常方便。

总结一下

gh-ost 输出的信息，迁移数据的效率，以及支持的功能都比 pt-osc 等工具要优秀，而 gh-ost 工具的问题（例如磁盘空间）在其他工具也会遇到，因此在 DDL 操作又想避免延迟等问题时，推荐优先考虑 gh-ost。

标签：DDL,ost,State,mysql,MySQL,total,gh
From： https://www.cnblogs.com/gdjgs/p/18449794

相关文章

  • MySQL 大表改列
    前言作为一个MySQLDBA，和大表打交道的次数想必不少，大表上的ALTER操作一般影响都很大，平时会用OnlineDDL工具来辅助操作，但是本文会介绍一种特殊的技巧来应对一部分大表上的ALTER需求。解决方案从标题可以看出来，这次会用到MySQL5.7的新功能：GeneratedColumn，这种虚拟列......
  • mysql数据库连接异常问题(总结)
    针对你提到的多种数据库连接问题，下面进行总结和建议，以避免未来再次遇到相同的问题：1.连接超时(AnattemptbyaclienttocheckoutaConnectionhastimedout)原因：网络不稳定数据源配置参数异常解决方案：优化网络环境：检查网络延迟和丢包率。考虑使用更稳定的网络......
  • 连接Mysql时出现的“no database selected”错误该如何解决?
    遇到“nodatabaseselected”错误通常是因为在尝试执行SQL查询时没有明确指定要使用的数据库。以下是解决这个问题的步骤：确认连接时已选择数据库在连接数据库之后，确保已经选择了具体的数据库。例如，在MySQL中，可以使用 USE<database_name>; 语句来选择一个数据库。USE......
  • MySQL遇到问题及解决方案
    针对MySQL遇到的问题及解决方案，可以按照以下步骤进行排查和解决：查看错误日志查看MySQL的日志文件，特别是error.log文件。命令行下可以通过 tail-f/var/lib/mysqlhostname.err 查看实时日志。检查数据库服务状态确认MySQL服务是否正在运行。bash systemctl......
  • 探索MySQL的InnoDB索引失效
    MySQL8+InnoDB- 序章索引失效，发生在已经建立索引，但是，查询（SELECT）时没有用到建立的（预期会用到）索引的情况下。失效原因有两个方面：1、建立索引的方式错误需要弄清楚 字段的#区分度（极其重要）这个概念。选择区分度高的建立索引。2、某些SELECT语句不支持使用索引注意，......
  • MySQL单表存多大的数据量比较合适
    前言经常使用MySQL数据库的小伙伴都知道，当单表数据量达到一定的规模以后，查询性能就会显著降低。因此，当单表数据量过大时，我们往往要考虑进行分库分表。那么如何计算单表存储多大的数据量合适？当单表数据达到多大的规模时，我们才要进行分库分表呢？MySQL存储方式首先我们要先了解一下......
  • 【2024计算机毕业设计】基于jsp+mysql+Spring+mybatis的SSM药品进货销售仓储信息管理
    运行环境:最好是javajdk1.8，我在这个平台上运行的。其他版本理论上也可以。IDE环境：Eclipse,Myeclipse,IDEA或者SpringToolSuite都可以，如果编译器的版本太低，需要升级下编译器，不要弄太低的版本tomcat服务器环境：Tomcat7.x,8.x,9.x版本均可操作系统环境：WindowsXP/7......
  • [MySQL]为什么大厂选择读已提交
    为什么读已提交的并发性更好在数据库中，锁的时间和范围是影响并发性的重要因素。已提交读（ReadCommitted）隔离级别与可重复读（RepeatableRead）的主要区别就在于它们在读取数据时对锁的使用方式不同。让我们详细看看为什么已提交读的锁的时间和范围更小。1.已提交读（ReadCommitte......
  • CF946G Almost Increasing Array 题解
    题目传送门前置知识最长不下降子序列|权值树状数组及应用解法若将\(\{a\}\)变成严格递增序列，至少需要更改\(n\)减去\(\{a_{i}-i\}\)的最长不下降子序列长度个数。证明对于\(a_{i},a_{j}(i<j)\)若都在最终的严格递增序列里，则有\(a_{i}-a_{j}\lei-j\)，即\(......
  • 帝国cms后台出现Fail to connect ftp host!
    遇到“Failtoconnectftphost!”的问题，通常是因为FTP设置不正确或网络连接问题。以下是一些常见的解决方法：检查FTP设置登录帝国CMS后台。进入“系统设置”->“FTP设置”。确认以下设置是否正确：FTP主机地址FTP端口（通常是 21）FTP用户名和密码FTP目录路......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99