关注我，获取更多分享。 DDL Online：在线执行DDL语句，也就是在线修改表结构。所谓在线的意思就是保证在执行DDL语句的过程中，表仍然可以提供正常的读写服务，不会中断。 这就像飞机在不降落的情况下，在空中给飞机加油的操作。 你可能会想我执行一个DDL语句很快的呀，不会导致表不能正常读写吧？那是因为你操作的表是一个数据量比较小的表，或者不是一个被频繁访问的业务表，对于这些表，执行DDL语句确实很快，不会对业务造成很大的影响。但是，你如果对一个几百万、甚至上千万的大表进行DDL语句的修改，这个DDL语句执行的过程，可能会持续几十秒、甚至几分钟，此时的这个过程不会像操作小表那么快。 我们在生产环境中，难免要遇到对表结构的修改。而此时线上的表正在为后端提供各种请求访问的服务，如果此时我们直接进行对表结构的更改，可能导致后端的SQL请求被阻塞，不能正常访问，进而导致服务异常或不可用。所以，在我们对在线的表进行DDL操作的时候，要格外的注意。要选择合适的方式、合适的时间进行，尽量避免这些问题的发生。 那么为什么增加给表增加一个字段会导致锁表呢？ 当我们对表增加字段的时候，我们就要获取这个表的MDL元数据锁(meta data lock)写锁(写锁又称为X锁，读锁又称为S锁)，只有获取的这个MDL的X锁之后，我们才可以对这个表的结构进行修改，否则不能修改会被阻塞。 目前为止，MySQL的DDL Online的方式主要有以下几种。 下面我们分别针对其中的几种方式来一一归纳总结一下。 MySQL从5.6之后的版本已经支持了DDL的Online操作。在使用的时候，就是在我们的DDL语句中，显示去指定关键字ALGORITHM和LOCK的参数值。示例如下： 其中的ALGORITHM=INPLACE, LOCK=NONE是关键的地方，这里需要分别为ALGORITHM和LOCK这两个参数指定值。 MySQL自带的DDL Online的参数大纲如下： 图片来源参考：https://blog.csdn.net/finalkof1983/article/details/88355314 MySQL官方虽然已经支持了DDL的Online操作，但是，这个操作比较有局限性，并不是所有的DDL语句都可以真正的Online的去执行。有些DDL语句，虽然我们显示的执行要让其使用online的方式去执行，但是当MySQL真正的去执行的时候，如果它发现不能online的时候，会给出错误提示，让我们修改对应的显示参数去改为offline的方式去执行。这里不同的DDL语句范围很广，例如：修改字段类型、增加主键、删除主键、增加索引、删除索引、更改索引名称、增加字段、删除字段、修改字段长度等等。根据不同类型的DDL语句，参数ALGORITHM和LOCK的取值范围并不是都可以随意组合使用的，这个要视情况而定，具体场景具体分析。具体参考如下图片： 图片来源参考：https://blog.csdn.net/finalkof1983/article/details/88355314 另外，在针对有主从架构的场景时，使用官方自带的这种DDL Online语句，即便是在master主节点可以online的去执行，但是会在从库上会执行同样的DDL语句期间，从库中对应的这个执行DDL语句的表会出现DML语句被阻塞的情况，从而出现主从延迟的现象。大致过程如下： 所以，官方自带的这个在线的DDL方式比较有局限性。我们基本上都不使用这种方式，而是采用第三方的工具来实现在线修改表结构的需求。 pt-online-schema-change是Percona公司提供的众多工具集中的一个，它是工具包Percona Toolkit里面的其中一个命令。而这个工具箱的安装也比较简单，有各种安装方式。如果不想编译安装，可以下载一个编译好的二进制压缩包，开箱即用，解压后进入bin目录既可以使用。下载地址为：https://www.percona.com/downloads/percona-toolkit/LATEST/ pt-online-schema-change可以实现在线的DDL语句。它的原理是基于触发器来实现在线更改表结构。它的实现过程大概如下： 其中的第3步，在pt-osc工具中的实现方式是在原表上创建insert、update、delete触发器，通过这些触发器，把在导入数据到临时表的过程中在对原表的DML操作同步到临时表中去。这种做法比较大的问题就是增加了原表的压力，对原表的一些事物的操作会因为触发器操作临时表的加入而导致事物变长。 pt-online-schema-change使用方式如下所示： pt-online-schema-change命令各个参数解释如下 pt-online-schema-change工具的不足之处在于它是通过在原表上创建各种trigger的方式来完成原表和新创建的临时表之间增量数据同步的需求。这样在临时表上执行来自于触发器的SQL，和原表上面的SQL是属于同一个事物。当原表的DML比较多的时候，会出现严重的性能问题。主要问题有如下： 为了解决这些问题，Github开源的工具gh-ost放弃了基于触发器的原理，把更多的操作压力从数据库层级解放出来，放在gh-ost工具中去做，避免让数据库去做更多的任务。 gh-ost是Github开源的一款自己内部使用的在线修改数据库表结构的工具。它放弃了大多数第三方工具基于trigger触发器的原理来实现增量同步设计思想，改为使用基于binlog日志的方式来做原表和临时表的数据增量的同步。由于是基于binlog来实现数据同步的，所以MySQL主从都需要开启的binlog功能，与此同时，需要使用指定的row格式的binlog(虽然go-ost工具中支持把binlog的日志自己转换为row格式，但是最好还是自己本身就是使用的是row格式的日志)。 它的下载和安装也比较简单，有rpm安装包，也有提供编译好的二进制安装包，解压后直接使用即可。下载安装包的地址为：https://github.com/github/gh-ost/releases/tag/v1.1.0。 gh-ost在Github上面地址为：https://github.com/github/gh-ost，里面介绍了它的特点。主要是：无触发器、轻量级、可暂停、可动态控制、可审计、可测试等。 它的实现过程大致如下： gh-ost支持还支持多种操作方式如下： 参数解释如下： 在MySQL单实例中使用示例： 单实例中需要指定--allow-on-master参数，默认gh-ost是连接到从节点上面的。 在MySQL主从集群中的使用示例： 在执行DDL中，从库会执行一次stop/start slave，要是确定从的binlog是ROW的话可以添加参数：--assume-rbr。如果从库的binlog不是ROW，可以用参数--switch-to-rbr来转换成ROW，此时需要注意的是执行完毕之后，binlog模式不会被转换成原来的值，如果需要，需要自己去手动切换为原来的格式。--assume-rbr和--switch-to-rbr参数不能一起使用。 只在从库中进行测试的使用示例： 参数--test-on-replica：在从库上测试gh-ost，包括在从库上数据迁移(migration)，数据迁移完成后stop slave，原表和ghost表立刻交换而后立刻交换回来。继续保持stop slave，使你可以对比两张表。如果不想stop slave，则可以再添加参数：--test-on-replica-skip-replica-stop 平时我们使用的比较多的就是Percona公司提供的pt-online-schema-change，还有就是Github的gh-ost工具。 对于其他几种方式，大家如果有兴趣，可以自己研究一下，这里不再赘述了。什么是DDL Online
为什么要Online
DDL Online的方法
官方自带DDL Online
/*推荐该参数组合*/
alter table t add column col varchar(32), ALGORITHM=INPLACE, LOCK=NONE;
alter table t add column col varchar(32), ALGORITHM=INPLACE, LOCK=SHARED;
alter table t add column col varchar(32), ALGORITHM=INPLACE, LOCK=DEFAULT;
alter table t add column col varchar(32), ALGORITHM=INPLACE, LOCK=EXCLUSIVE;
pt-online-schema-change工具
pt-online-schema-change --user=root --password=123456 D=test_db,t=test_tab --alter "add column col1 varchar(16) DEFAULT NULL COMMENT '测试字段1' AFTER id, add column col2 varchar(32) DEFAULT NULL COMMENT '测试字段2' AFTER col1" --recursion-method=processlist --check-interval=5s --max-lag=5s --print --charset=utf8 --execute
--user:
-u，连接的用户名
--password：
-p，连接的密码
--database：
-D，连接的数据库
--port
-P，连接数据库的端口
--host:
-h，连接的主机地址
--socket:
-S，连接的套接字文件
--ask-pass
隐式输入连接MySQL的密码
--charset
指定修改的字符集
--defaults-file
-F，读取配置文件
--alter：
结构变更语句，不需要alter table关键字。可以指定多个更改，用逗号分隔。如下场景，需要注意：
不能用RENAME来重命名表。
列不能通过先删除，再添加的方式进行重命名，不会将数据拷贝到新列。
如果加入的列非空而且没有默认值，则工具会失败。即其不会为你设置一个默认值，必须显示指定。
删除外键(drop foreign key constrain_name)时，需要指定名称_constraint_name，而不是原始的constraint_name。
如：CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)，需要指定：--alter "DROP FOREIGN KEY _fk_foo"
--alter-foreign-keys-method
如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上。
该工具有两种方法,可以自动找到子表,并修改约束关系。
auto： 在rebuild_constraints和drop_swap两种处理方式中选择一个。
rebuild_constraints：使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞。
drop_swap： 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险：
1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。
2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除。
none： 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。
--[no]check-alter
默认yes，语法解析。配合--dry-run 和 --print 一起运行，来检查是否有问题（change column，drop primary key）。
--max-lag
默认1s。每个chunk拷贝完成后，会查看所有复制Slave的延迟情况。要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值，使用Seconds_Behind_Master。如果有任何从滞后超过此选项的值，则该工具将睡眠--check-interval指定的时间，再检查。如果从被停止，将会永远等待，直到从开始同步，并且延迟小于该值。如果指定--check-slave-lag，该工具只检查该服务器的延迟，而不是所有服务器。
--check-slave-lag
指定一个从库的DSN连接地址,如果从库超过--max-lag参数设置的值,就会暂停操作。
--recursion-method
默认是show processlist，发现从的方法，也可以是host，但需要在从上指定report_host，通过show slave hosts来找到，可以指定none来不检查Slave。
METHOD USES
=========== ==================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
dsn=DSN DSNs from a table
none Do not find slaves
指定none则表示不在乎从的延迟。
--check-interval
默认是1。--max-lag检查的睡眠时间。
--[no]check-plan
默认yes。检查查询执行计划的安全性。
--[no]check-replication-filters
默认yes。如果工具检测到服务器选项中有任何复制相关的筛选，如指定binlog_ignore_db和replicate_do_db此类。发现有这样的筛选，工具会报错且退出。因为如果更新的表Master上存在，而Slave上不存在，会导致复制的失败。使用–no-check-replication-filters选项来禁用该检查。
--[no]swap-tables
默认yes。交换原始表和新表，除非你禁止--[no]drop-old-table。
--[no]drop-triggers
默认yes，删除原表上的触发器。 --no-drop-triggers 会强制开启 --no-drop-old-table 即：不删除触发器就会强制不删除原表。
--new-table-name
复制创建新表的名称，默认%T_new。
--[no]drop-new-table
默认yes。删除新表，如果复制组织表失败。
--[no]drop-old-table
默认yes。复制数据完成重命名之后，删除原表。如果有错误则会保留原表。
--max-load
默认为Threads_running=25。每个chunk拷贝完后，会检查SHOW GLOBAL STATUS的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。
--critical-load
默认为Threads_running=50。用法基本与--max-load类似，如果不指定MAX_VALUE，那么工具会这只其为当前值的200%。如果超过指定值，则工具直接退出，而不是暂停。
--default-engine
默认情况下，新的表与原始表是相同的存储引擎，所以如果原来的表使用InnoDB的，那么新表将使用InnoDB的。在涉及复制某些情况下，很可能主从的存储引擎不一样。使用该选项会默认使用默认的存储引擎。
--set-vars
设置MySQL变量，多个用逗号分割。默认该工具设置的是： wait_timeout=10000 innodb_lock_wait_timeout=1 lock_wait_timeout=60
--chunk-size-limit
当需要复制的块远大于设置的chunk-size大小,就不复制.默认值是4.0，一个没有主键或唯一索引的表,块大小就是不确定的。
--chunk-time
在chunk-time执行的时间内,动态调整chunk-size的大小,以适应服务器性能的变化，该参数设置为0,或者指定chunk-size,都可以禁止动态调整。
--chunk-size
指定块的大小,默认是1000行,可以添加k,M,G后缀.这个块的大小要尽量与--chunk-time匹配，如果明确指定这个选项,那么每个块就会指定行数的大小.
--[no]check-plan
默认yes。为了安全,检查查询的执行计划.默认情况下,这个工具在执行查询之前会先EXPLAIN,以获取一次少量的数据,如果是不好的EXPLAIN,那么会获取一次大量的数据，这个工具会多次执行EXPALIN,如果EXPLAIN不同的结果,那么就会认为这个查询是不安全的。
--statistics
打印出内部事件的数目，可以看到复制数据插入的数目。
--dry-run
创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节。--dry-run与--execute必须指定一个，二者相互排斥。和--print配合最佳。
--execute
确定修改表，则指定该参数。真正执行。--dry-run与--execute必须指定一个，二者相互排斥。
--print
打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句，和--dry-run配合最佳。
--progress
复制数据的时候打印进度报告，二部分组成：第一部分是百分比，第二部分是时间。
--quiet
-q，不把信息标准输出。
gh-ost工具
Usage of gh-ost:
--aliyun-rds:是否在阿里云数据库上执行。true
--allow-master-master:是否允许gh-ost运行在双主复制架构中，一般与-assume-master-host参数一起使用
--allow-nullable-unique-key:允许gh-ost在数据迁移依赖的唯一键可以为NULL，默认为不允许为NULL的唯一键。如果数据迁移(migrate)依赖的唯一键允许NULL值，则可能造成数据不正确，请谨慎使用。
--allow-on-master:允许gh-ost直接运行在主库上。默认gh-ost连接的从库。
--alter string:DDL语句
--approve-renamed-columns ALTER:如果你修改一个列的名字，gh-ost将会识别到并且需要提供重命名列名的原因，默认情况下gh-ost是不继续执行的，除非提供-approve-renamed-columns ALTER。
--ask-pass:MySQL密码，弹出输入密码的对话框，在命令行中手动输入。
--assume-master-host string:为gh-ost指定一个主库，格式为”ip:port”或者”hostname:port”。在这主主架构里比较有用，或则在gh-ost发现不到主的时候有用。
--assume-rbr:确认gh-ost连接的数据库实例的binlog_format=ROW的情况下，可以指定-assume-rbr，这样可以禁止从库上运行stop slave,start slave,执行gh-ost用户也不需要SUPER权限。
--check-flag
--chunk-size int:在每次迭代中处理的行数量(允许范围：100-100000)，默认值为1000。
--concurrent-rowcount:该参数如果为True(默认值)，则进行row-copy之后，估算统计行数(使用explain select count(*)方式)，并调整ETA时间，否则，gh-ost首先预估统计行数，然后开始row-copy。
--conf string:gh-ost的配置文件路径。
--critical-load string:一系列逗号分隔的status-name=values组成，当MySQL中status超过对应的values，gh-ost将会退出。-critical-load Threads_connected=20,Connections=1500，指的是当MySQL中的状态值Threads_connected>20,Connections>1500的时候，gh-ost将会由于该数据库严重负载而停止并退出。
Comma delimited status-name=threshold, same format as --max-load. When status exceeds threshold, app panics and quits
--critical-load-hibernate-seconds int :负载达到critical-load时，gh-ost在指定的时间内进入休眠状态。 它不会读/写任何来自任何服务器的任何内容。
--critical-load-interval-millis int:当值为0时，当达到-critical-load，gh-ost立即退出。当值不为0时，当达到-critical-load，gh-ost会在-critical-load-interval-millis秒数后，再次进行检查，再次检查依旧达到-critical-load，gh-ost将会退出。
--cut-over string:选择cut-over类型:atomic/two-step，atomic(默认)类型的cut-over是github的算法，two-step采用的是facebook-OSC的算法。
--cut-over-exponential-backoff
--cut-over-lock-timeout-seconds int:gh-ost在cut-over阶段最大的锁等待时间，当锁超时时，gh-ost的cut-over将重试。(默认值：3)
--database string:数据库名称。
--debug:debug模式。
--default-retries int:各种操作在panick前重试次数。(默认为60)
--discard-foreign-keys:该参数针对一个有外键的表，在gh-ost创建ghost表时，并不会为ghost表创建外键。该参数很适合用于删除外键，除此之外，请谨慎使用。
--dml-batch-size int:在单个事务中应用DML事件的批量大小（范围1-100）（默认值为10）
--exact-rowcount:准确统计表行数(使用select count(*)的方式)，得到更准确的预估时间。
--execute:实际执行alter&migrate表，默认为noop，不执行，仅仅做测试并退出，如果想要ALTER TABLE语句真正落实到数据库中去，需要明确指定--execute
--exponential-backoff-max-interval int
--force-named-cut-over:如果为true，则'unpostpone | cut-over'交互式命令必须命名迁移的表
--force-table-names string:在临时表上使用的表名前缀
--heartbeat-interval-millis int:gh-ost心跳频率值，默认为500
--help
--hooks-hint string:任意消息通过GH_OST_HOOKS_HINT注入到钩子
--hooks-path string:hook文件存放目录(默认为empty，即禁用hook)。hook会在这个目录下寻找符合约定命名的hook文件来执行。
--host string :MySQL IP/hostname
--initially-drop-ghost-table:gh-ost操作之前，检查并删除已经存在的ghost表。该参数不建议使用，请手动处理原来存在的ghost表。默认不启用该参数，gh-ost直接退出操作。
--initially-drop-old-table:gh-ost操作之前，检查并删除已经存在的旧表。该参数不建议使用，请手动处理原来存在的ghost表。默认不启用该参数，gh-ost直接退出操作。
--initially-drop-socket-file:gh-ost强制删除已经存在的socket文件。该参数不建议使用，可能会删除一个正在运行的gh-ost程序，导致DDL失败。
--master-password string :MySQL 主密码
--master-user string:MysQL主账号
--max-lag-millis int:主从复制最大延迟时间，当主从复制延迟时间超过该值后，gh-ost将采取节流(throttle)措施，默认值：1500s。
--max-load string:逗号分隔状态名称=阈值，如：'Threads_running=100,Threads_connected=500'. When status exceeds threshold, app throttles writes
--migrate-on-replica:gh-ost的数据迁移(migrate)运行在从库上，而不是主库上。
--nice-ratio float:每次chunk时间段的休眠时间，范围[0.0…100.0]。0：每个chunk时间段不休眠，即一个chunk接着一个chunk执行；1：每row-copy 1毫秒，则另外休眠1毫秒；0.7：每row-copy 10毫秒，则另外休眠7毫秒。
--ok-to-drop-table:gh-ost操作结束后，删除旧表，默认状态是不删除旧表，会存在_tablename_del表。
--panic-flag-file string:当这个文件被创建，gh-ost将会立即退出。
--password string :MySQL密码
--port int ：MySQL端口，最好用从库
--postpone-cut-over-flag-file string：当这个文件存在的时候，gh-ost的cut-over阶段将会被推迟，数据仍然在复制，直到该文件被删除。
--quiet：静默模式。
--replica-server-id uint : gh-ost的server_id
--replication-lag-query string:弃用
--serve-socket-file string：gh-ost的socket文件绝对路径。
--serve-tcp-port int:gh-ost使用端口，默认为关闭端口。
--skip-foreign-key-checks:确定你的表上没有外键时，设置为'true'，并且希望跳过gh-ost验证的时间-skip-renamed-columns ALTER
--skip-renamed-columns ALTER：如果你修改一个列的名字(如change column)，gh-ost将会识别到并且需要提供重命名列名的原因，默认情况下gh-ost是不继续执行的。该参数告诉gh-ost跳该列的数据迁移，让gh-ost把重命名列作为无关紧要的列。该操作很危险，你会损失该列的所有值。
--stack:添加错误堆栈追踪。
--switch-to-rbr:让gh-ost自动将从库的binlog_format转换为ROW格式。
--table string:表名
--test-on-replica：在从库上测试gh-ost，包括在从库上数据迁移(migration)，数据迁移完成后stop slave，原表和ghost表立刻交换而后立刻交换回来。继续保持stop slave，使你可以对比两张表。
--test-on-replica-skip-replica-stop:当-test-on-replica执行时，该参数表示该过程中不用stop slave。
--throttle-additional-flag-file string:当该文件被创建后，gh-ost操作立即停止。该参数可以用在多个gh-ost同时操作的时候，创建一个文件，让所有的gh-ost操作停止，或者删除这个文件，让所有的gh-ost操作恢复。
--throttle-control-replicas string:列出所有需要被检查主从复制延迟的从库。
--throttle-flag-file string:当该文件被创建后，gh-ost操作立即停止。该参数适合控制单个gh-ost操作。-throttle-additional-flag-file string适合控制多个gh-ost操作。
--throttle-http string
--throttle-query string:节流查询。每秒钟执行一次。当返回值=0时不需要节流，当返回值>0时，需要执行节流操作。该查询会在数据迁移(migrated)服务器上操作，所以请确保该查询是轻量级的。
--timestamp-old-table:在旧表名中使用时间戳。 这会使旧表名称具有唯一且无冲突的交叉迁移
--tungsten：告诉gh-ost你正在运行的是一个tungsten-replication拓扑结构。
--user string :MYSQL用户
--verbose
--version
gh-ost \
--user="root" \
--password="root" \
--host=192.168.163.131 \
--database="test" \
--table="t1" \
--alter="ADD COLUMN cc2 varchar(10),add column cc3 int not null default 0 comment 'test'" \
--allow-on-master \
--execute
gh-ost \
--user="root" \
--password="root" \
--host=192.168.163.130 \
--database="test" \
--table="t" \
--initially-drop-old-table \
--alter="ADD COLUMN y1 varchar(10),add column y2 int not null default 0 comment 'test' " \
--execute
gh-ost \
--user="root" \
--password="root" \
--host=192.168.163.130 \
--database="test" \
--table="t" \
--alter="ADD COLUMN x varchar(10),add column y int not null default 0 comment 'test'" \
--test-on-replica \
--switch-to-rbr \
--execute
总结