MySQL在线DDL工具 gh-ost的原理解析

MySQL在线DDL工具 gh-ost的原理解析

简介

gh-ost是一个基于Percona OSC 原理的 MySQL online DDL 工具,它可以在线帮助MySQL用户快速升级表结构、移动数据,同时避免关闭业务及长时间占用MySQL资源等问题。此外,gh-ost的操作具有可回滚性,可以很方便的撤销变更操作。

原理

基本原理

gh-ost的原理与 Facebook 的 online schema change 工具有很多相似之处,大体上分为两个阶段:迁移阶段和同步阶段。

  • 迁移阶段通过创建一个 gh-ost 的数据表 gh-ost,并通过拷贝原表数据创建一个新表 gh-ost<suffix>suffix由当前时间戳和随机字符串组成),在新表中添加要修改的字段和索引,删除原表中的相应的字段和索引。
  • 同步阶段通过 MySQL 的 Trigger 和 Binlog,将原表中的所有操作记录到 gh-ost 表中,然后通过 DML 语句(增、删、改语句)将这些操作同步到新表。

更多详细原理

gh-ost 的运行依赖于多个 MySQL 参数设置:

  • 首先需要启用 MySQL 的 ROW 模式,以便使用 Trigger。
  • 启用 binlog_row_image = FULL,以便在 DML 中包含所有数据。
  • 设置 MySQL 参数 binlog_checksum=NONE,以避免在 binlog 传输过程中计算校验值,加快同步速度。
  • 设置 max_triggers_per_row = 1000,以便每行数据最多放置 1000 个触发器。
  • 可选设置,如果您需要将 gh-ost 运行到分布式系统上,则需要使用 MySQL Group Replication、MySQL Cluster 或 Galera Cluster。

在触发器的流程中,gh-ost 在原表添加触发器,用于捕获所有表的 DML 操作,然后将捕获到的数据记录下来写入到其记录表中。在触发器的 On Update 和 On Delete 事件中,gh-ost 需要把记录写入到两个表,分别是记录表和新表,On Insert 事件就只需要写入到新表。这样,新表的数据和原表保持同步。

一个大表修改结构批量操作时需要注意:

  • 对于大表,gh-ost 可以通过 chunk-index 参数来控制每个事务处理的数据量,以优化性能。
  • 在表上建立多个(>1)异步索引会影响gh-ost的性能,因此推荐在上一个后删除现有索引,然后添加新的索引。
  • 表上存在外键约束和触发器时,需要先将它们禁用,等结构变更完成后再启用,否则可能会执行失败。

示例

下面我们看两个使用gh-ost更改表结构的示例:

示例1:增加字段

比如原有表结构如下:

-- 表结构
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 插入数据
INSERT INTO `test` (`id`, `name`) VALUES (1, 'xiaoming');

现在我们需要在该表中增加一个收入字段income,可以使用 gh-ost 实现在线修改:

$ gh-ost --user=root --host=127.0.0.1 --database=test \
           --table=test --alter="ADD COLUMN income INT(11)" \
           --concurrency=16 --throttle=200 --approve-renamed-columns=false \
           --chunk-size=1000 --max-load=Threads_running=25

示例2:修改字段名

比如原有表结构如下:

-- 表结构
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 插入数据
INSERT INTO `test` (`id`, `name`) VALUES (1, 'xiaoming');

现在我们需要将该表中原有的字段 name 修改为 username,可以使用 gh-ost 实现在线修改:

$ gh-ost --user=root --host=127.0.0.1 --database=test \
           --table=test --alter="CHANGE COLUMN name username varchar(20) NOT NULL" \
           --concurrency=16 --throttle=200 --approve-renamed-columns=false \
           --chunk-size=1000 --max-load=Threads_running=25

总结

gh-ost 可以帮助 MySQL 用户更加方便和快速地修改数据库表结构,同时自带了可回滚性,非常适合大表在线修改,避免了关闭业务、占用 MySQL 资源等问题。但对于不是特别关注在线迁移的用户,可以使用 Percona Toolkit 的 pt-online-schema-change 工具或者 MySQL 官方的 ALTER TABLE 操作,二者在效率上略逊于 gh-ost,但也可以满足大多数用户的需求。

本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:MySQL在线DDL工具 gh-ost的原理解析 - Python技术站

(0)
上一篇 2023年5月22日
下一篇 2023年5月22日

相关文章

  • Neo4j和Redis的区别

    Neo4j和Redis都是流行的开源非关系型数据库系统,在具体的应用场景下,两者都可以提供不同的优势和特点。下面详细介绍Neo4j和Redis的区别: Neo4j:基于图形的数据库系统 Neo4j是一种基于图形的数据库系统,它的数据结构是通过节点、边和图形表示的。因此,它特别适合于处理复杂的数据关系,例如社交网络、推荐系统、网络拓扑图等。Neo4j使用CQL…

    database 2023年3月27日
    00
  • MySQL 连接查询的原理和应用

    一、MySQL 连接查询的原理 MySQL 连接查询(JOIN)是 SQL 查询中最常用的一种查询方式之一,通过该方式可以实现在多张表中对数据的关联查询。连接查询的主要原理是通过连接条件将两张表中的记录进行匹配,最终返回匹配的结果集。连接条件可以通过指定相同的列进行匹配,也可以通过使用运算符、LIKE 等操作符进行匹配。 JOIN 查询一般分为以下几种类型:…

    database 2023年5月22日
    00
  • DBMS 中的死锁

    DBMS 中的死锁 什么是死锁 死锁是指当两个或多个事务相互请求对方占用的资源时,导致系统中出现无法转化的状态,进而导致事务阻塞,无法继续执行的现象。在产生死锁的情况下,没有一个事务可以完成,系统没有办法正常运行。 如何避免死锁 要避免死锁,我们必须去写一些适当的代码,确保事务按照特定的顺序对资源进行访问。下面是一些常见的避免死锁的方法: 加锁顺序:确保在多…

    database 2023年3月27日
    00
  • mysql8报错:ERROR 1410 (42000): You are not allowed to create a user with GRANT解决办法

    当使用mysql8创建用户并授权时,可能会遇到ERROR 1410 (42000): You are not allowed to create a user with GRANT的报错提示。这是因为mysql8对用户的管理进行了更加严格的权限控制,不是所有用户都可以执行创建授权的操作。以下是解决这个问题的完整攻略: 1. 确认当前登录用户是否具有创建用户的…

    database 2023年5月18日
    00
  • tp框架报“mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead” 警告信息

    警告信息是“mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead……\ThinkPHP\Library\Think\Db\Driver\Mysql.class.php 第 52 行.”   …

    MySQL 2023年4月12日
    00
  • SQL 计算累计乘积

    下面我来详细讲解SQL计算累计乘积的方法,包含两条实例。 什么是SQL计算累计乘积? SQL计算累计乘积,是指在某一列中,计算该列元素之间的乘积,得到一个连续的乘积列。例如,给定一组数据[1, 2, 3, 4, 5],则该数据的累乘结果为[1, 2, 6, 24, 120]。 SQL计算累计乘积的实现方法 方法一:使用乘积自连接 我们可以使用乘积自连接的方法…

    database 2023年3月27日
    00
  • VS2022连接数据库MySQL并进行基本的表的操作指南

    下面是“VS2022连接数据库MySQL并进行基本的表的操作指南”的完整攻略: 准备工作 安装 MySQL 数据库,可以从官网下载安装包:https://dev.mysql.com/downloads/mysql/。 安装 MySQL 数据库的 .NET 驱动程序,可以从官网下载安装包:https://dev.mysql.com/downloads/conn…

    database 2023年5月21日
    00
  • mysql获得60天前unix时间思路及代码

    获取60天前的UNIX时间的主要思路是使用MySQL函数来进行日期计算。具体步骤如下: 使用MySQL函数NOW()获取当前时间,这个函数返回当前系统时间的日期和时间部分。 使用MySQL函数UNIX_TIMESTAMP()将当前时间转换为UNIX时间戳,这个函数返回从1970年1月1日午夜(格林威治标准时间)到当前时间的秒数。 使用MySQL函数INTER…

    database 2023年5月22日
    00
合作推广
合作推广
分享本页
返回顶部