MySQL在线DDL语句

• MySQL在线DDL工具pt-online-schema-change

• 原理
  pt-online-schema-change模拟了MySQL内部alter table的方式，但是其操作所更新的是复制表，所以原表不会被锁住。其原理我们通过示例进行解读：

  shell> pt-online-schema-change –nocheck-replication-filters –recursion-method=none –alter “add newcol int” h=192.168.10.118,P=3306,u=sup,p=xxxx,D=dbteamdb,t=user –alter-foreign-keys-method rebuild_constraints –print –statistics –execute
  Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
  Child tables:
  `dbteamdb`.`user_privs` (approx. 1 rows)
  Will use the rebuild_constraints method to update foreign keys.
  Altering `dbteamdb`.`user`…
  Creating new table…

  
  #步骤1，创建空表，其命名规则是_+原表名+_new

  
  CREATE TABLE `dbteamdb`.`_user_new` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `host` varchar(20) COLLATE utf8_bin NOT NULL,
    `eng_name` varchar(20) COLLATE utf8_bin NOT NULL,
    `chn_name` varchar(20) COLLATE utf8_bin NOT NULL,
    `create_time` datetime NOT NULL,
    `update_time` datetime NOT NULL,
    `status` tinyint(3) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user__host` (`host`)
  ) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  Created new table dbteamdb._user_new OK.
  Altering new table…

  
  #步骤2，根据语句更新新表结构
  ALTER TABLE `dbteamdb`.`_user_new` add newcol int
  Altered `dbteamdb`.`_user_new` OK.
  Creating triggers…

  
  #步骤3，在原表上创建触发器，以便后续原表上的操作同步到新表

  
  CREATE TRIGGER `pt_osc_dbteamdb_user_del` AFTER DELETE ON `dbteamdb`.`user` FOR EACH ROW DELETE IGNORE FROM `dbteamdb`.`_user_new` WHERE `dbteamdb`.`_user_new`.`id` <=> OLD.`id`
  CREATE TRIGGER `pt_osc_dbteamdb_user_upd` AFTER UPDATE ON `dbteamdb`.`user` FOR EACH ROW REPLACE INTO `dbteamdb`.`_user_new` (`id`, `host`, `eng_name`, `chn_name`, `create_time`, `update_time`, `status`) VALUES (NEW.`id`, NEW.`host`, NEW.`eng_name`, NEW.`chn_name`, NEW.`create_time`, NEW.`update_time`, NEW.`status`)
  CREATE TRIGGER `pt_osc_dbteamdb_user_ins` AFTER INSERT ON `dbteamdb`.`user` FOR EACH ROW REPLACE INTO `dbteamdb`.`_user_new` (`id`, `host`, `eng_name`, `chn_name`, `create_time`, `update_time`, `status`) VALUES (NEW.`id`, NEW.`host`, NEW.`eng_name`, NEW.`chn_name`, NEW.`create_time`, NEW.`update_time`, NEW.`status`)
  Created triggers OK.
  Copying approximately 44 rows…

  
  #步骤4，拷贝原表数据到新表，数据量大时会根据主键进行分段chunk插入

  
  INSERT LOW_PRIORITY IGNORE INTO `dbteamdb`.`_user_new` (`id`, `host`, `eng_name`, `chn_name`, `create_time`, `update_time`, `status`) SELECT `id`, `host`, `eng_name`, `chn_name`, `create_time`, `update_time`, `status` FROM `dbteamdb`.`user` LOCK IN SHARE MODE /*pt-online-schema-change 9378 copy table*/
  Copied rows OK.
  Swapping tables…

  
  #步骤5，拷贝完成后，移走原表，用新表代替(RENAME TABLE)。其通过一个RENAME TABLE同时处理两个表，实现原子操作。

  
  RENAME TABLE `dbteamdb`.`user` TO `dbteamdb`.`_user_old`, `dbteamdb`.`_user_new` TO `dbteamdb`.`user`
  Swapped original and new tables OK.
  Rebuilding foreign key constraints…

  
  #如果更新的表是被子表外键引用的父表，那么需要相应的更新子表的外键指向。具体见下文”外键的影响”。
  ALTER TABLE `dbteamdb`.`user_privs` DROP FOREIGN KEY `fk_user_privs__user_id`, ADD CONSTRAINT `_fk_user_privs__user_id` FOREIGN KEY (`user_id`) REFERENCES `dbteamdb`.`user` (`id`)
  Rebuilt foreign key constraints OK.

  
  #最后清除临时生成的表、触发器。默认情况下会删除原表（此时是已经重命名过的_user_old）。

  
  Dropping old table…
  DROP TABLE IF EXISTS `dbteamdb`.`_user_old`
  Dropped old table `dbteamdb`.`_user_old` OK.
  Dropping triggers…
  DROP TRIGGER IF EXISTS `dbteamdb`.`pt_osc_dbteamdb_user_del`;
  DROP TRIGGER IF EXISTS `dbteamdb`.`pt_osc_dbteamdb_user_upd`;
  DROP TRIGGER IF EXISTS `dbteamdb`.`pt_osc_dbteamdb_user_ins`;
  Dropped triggers OK.
  # Event              Count
  # ================== =====
  # INSERT                 1
  # rebuilt_constraint     1
  Successfully altered `dbteamdb`.`user`.

  注意：如果原表上已经有触发器，那么将无法使用本工具！

  性能
  数据的拷贝是基于小块(根据chunk-time参数指定)进行的，而且根据主键或者索引进行选择，所以对整体服务器性能影响较小。

  相关选项：

  –chunk-time <float> 默认0.5。根据每秒拷贝的记录数，动态调整每次chunk size，以便压力变化时能够开始适应，使得每次拷贝消耗这里的指定长时间。
  而且，如果检测到服务器压力大，则会暂停或者退出，详见–max-load和–critical-load选项。

  安全
  有诸多措施来保障安全使用该工具：

  只有指定了–execute才会真正执行
  如果工具检测到服务器选项中有任何复制相关的筛选，如指定binlog_ignore_db和replicate_do_db此类。发现有这样的筛选，工具会报错且退出。其原因是，如果更新的表Master上存在，而Slave上不存在，会导致复制的失败。使用–no-check-replication-filters选项来禁用该检查。
  如果复制环境中，有延迟，会暂停数据拷贝动作，详见–max-laq选项。
  默认设置了–lock-wait-timeout为1秒，这样如果有锁等待，尽量自己先退出，不干扰其他事务。详见–lock-wait-timeout
  如果被外键引用，工具不会执行操作，除非指定–alter-foreign-keys-method选项。
  外键的影响(外键相关基本只涉及到InnoDB引擎)
  外键使得以上操作变得更加复杂，如果有外键引用到要变更的表，那么就无法自动RENAME，因为外键还是引用到老表。工具必须更新外键引用到新表。其提供两种方式来实现这个功能(–alter-foreign-keys-method)。

  相关选项：

  –alter-foreign-keys-method “<string>”  设定该表被外键引用时该如何处理。因为涉及到RENAME，而引用它的表会跟随它，而不会自动切换到新表，所以需要将外键切到新表。可选值：
  rebuild_constraints 使用ALTER TABLE将删除重建引用到更新表的子表的相应外键。但是如果子表很大的话，就不合适了。

  
  drop_swap 禁用外键检测(foreign_key_checks=0)，然后在新表替代旧表前，直接drop掉新表，这与默认的方式不同。默认情况下，使用RENAME TABLE操作两张表，是原子操作，对于客户端来说是透明的；而启用这个选项，是先DROP老表，然后RENAME TABLE将新表重命名为老表，这种情况下，由于不是原子操作，在两个操作的间隙表就不存在了，会导致针对该表的操作错误！而且，如果RENAME操作失败，那么也无法回滚！
  auto 自动选择。其倾向于使用rebuild_constraints，如果预估子表的更新时间小于–chunk-time则选择rebuild_constraints，反之则选drop_swap。

  
  none 会对原表RENAME成_TAB_old，然后set foreign_key_checks=0，再drop _TAB_old。这样就会导致子表依赖的父表被删除！这样后续需要DBA参与处理。
  外键还有一个问题，就是新建表的外键名称和旧表的外键名称重复，工具保证最终表与原表有同样的外键和索引，但是名称可能会稍有不同，会添加前缀下划线。

  输出
  本工具会活动信息输出到标准输出STDOUT。在数据拷贝阶段，会打印–progress报告到STDERR。指定–print可以得到额外的信息。如果指定–statistics，会进一步打印出内部事件的数目。

  其他选项说明
  –max-laq <time> 默认为1s。每个chunk拷贝完成后，这个工具会查看所有复制Slave的延迟情况。其方式是连接到Slave，检查其Seconds_Behind_Master。如果有一个Slave延迟超过max-lag，则工具会睡眠–check-interval秒然后再检查。至于如何查询所有Slave，则根据–recursion-method（默认通过show processlist，可以指定none来不检查Slave）。

  
  –max-load <array> 默认为Threads_running=25。每个chunk拷贝完后，会检查SHOW GLOBAL STATUS的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。

  
  –critical-load  <array> 默认为Threads_running=50。用法基本与–max-load类似，如果不指定MAX_VALUE，那么工具会这只其为当前值的200%。如果超过指定值，则工具直接退出，而不是暂停。

  
  –lock-wait-timeout <整型> 默认为1，即会话innodb_lock_wait_timeout的值为1秒。这个选项只针对InnoDB引擎。

  
  –alter <string> 结构变更语句，不需要alter table关键字。可以指定多个更改，用逗号分隔。如下场景，需要注意：
  不能用RENAME来重命名表。        * 列不能通过先删除，再添加的方式进行重命名，不会将数据拷贝到新列。
  如果加入的列非空而且没有默认值，则工具会失败。即其不会为你设置一个默认值，必须显示指定。
  删除外键(drop foreign key constrain_name)时，需要指定名称_constraint_name，而不是原始的constraint_name。
  –dry-run 并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节
  –execute 真正执行。–dry-run与–execute必须指定一个
  –[no]drop-old-table   删除RENAME后的原表。默认是yes。

  
  典型用法

  
  1）添加一列，并不真正执行
  pt-online-schema-change –alter “add column c1 int” D=mydb,t=mytable –dry-run

  2）更新存储引擎为InnoDB，不删除原表
  pt-online-schema-change –alter “ENGINE=InnoDB” –no-drop-old-table –print –statistics –execute D=mydb,t=mytable –execute

  3）复制环境下，忽略日志筛选和Slave复制延迟，删除表字段
  pt-online-schema-change –no-check-replication-filters –recursion-method=none –alter “drop company_type,drop channel_code” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –print –statistics –execute

  4）更新被子表引用到的父表
  pt-online-schema-change –alter “add newcol int” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –alter-foreign-keys-method auto –print –statistics –execute

  5）在我们的双主复制环境中，设定了忽略mysql库的复制，不是很在乎复制的延迟，有时有外键影响，希望尽量保留原表数据，必要时自行删除。
  pt-online-schema-change –no-check-replication-filters –recursion-method=none –alter “drop newcol” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –alter-foreign-keys-method auto –no-drop-old-table –print –statistics –execute

• OSC是DBA比较熟悉的工具之一,也是PT的TOOLKIT里面最重量级的工具.
  在mysql-5.6支持OLDDL以后,大部分人可能觉着这个工具已经没有意义了,其实在一些特殊环境下,这个工具还是很有用的.
  这篇文章除了介绍普通青年如何使用OSC，还会介绍一种文艺青年使用OSC的方法,那就是用来实现master到slave的数据差异恢复。

  +++++++++++++++++++++++++++普通青年分割线+++++++++++++++++++++++++++++++++++++++
  此段内容转自：http://www.dataguru.cn/article-3460-1.html
  目前InnoDB引擎是通过以下步骤来进行DDL的：
  1 按照原始表（original_table）的表结构和DDL语句，新建一个不可见的临时表（tmp_table）
  2 在原表上加write lock，阻塞所有更新操作（insert、delete、update等）
  3 执行insert into tmp_table select * from original_table
  4 rename original_table和tmp_table，最后drop original_table
  5 释放 write lock。
  我们可以看见在InnoDB执行DDL的时候，原表是只能读不能写的。为此 perconal 推出一个工具 pt-online-schema-change ，其特点是修改过程中不会造成读写阻塞。
  工作原理：
  如果表有外键，除非使用 –alter-foreign-keys-method 指定特定的值，否则工具不予执行。
  1 创建一个和你要执行 alter 操作的表一样的空表结构。
  2 执行表结构修改，然后从原表中的数据到copy到 表结构修改后的表，
  3 在原表上创建触发器将 copy 数据的过程中，在原表的更新操作 更新到新表.
  注意:如果表中已经定义了触发器这个工具就不能工作了。
  4 copy 完成以后，用rename table 新表代替原表，默认删除原表。

  用法介绍：
  pt-online-schema-change [OPTIONS] DSN
  options 可以自行查看 help，DNS 为你要操作的数据库和表。这里有两个参数需要介绍一下：
  –dry-run
  这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。
  –execute
  这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出。

  依赖条件
  1操作的表必须有主键否则 报如下错误。

  [root@rac1 bin]#  ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='add column vid int ' --execute D=houyi,t=ga
  Cannot connect to D=houyi,h=127.0.0.1,p=...,u=root
  Cannot chunk the original table `houyi`.`ga`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353.
  测试例子：
  1 添加字段

  [root@rac1 bin]#  ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='add column vid int ' --execute D=houyi,t=ga
  Cannot connect to D=houyi,h=127.0.0.1,p=...,u=root
  Operation, tries, wait:
    copy_rows, 10, 0.25
    create_triggers, 10, 1
    drop_triggers, 10, 1
    swap_tables, 10, 1
    update_foreign_keys, 10, 1
  Altering `houyi`.`ga`...
  Creating new table...
  Created new table houyi._ga_new OK.
  Altering new table...
  Altered `houyi`.`_ga_new` OK.
  Creating triggers...
  Created triggers OK.
  Copying approximately 746279 rows...
  Copied rows OK.
  Swapping tables...
  Swapped original and new tables OK.
  Dropping old table...
  Dropped old table `houyi`.`_ga_old` OK.
  Dropping triggers...
  Dropped triggers OK.
  Successfully altered `houyi`.`ga`.
  2 添加索引

  [root@rac1 bin]# ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='add key indx_vid(vid) ' --execute D=houyi,t=ga
  3 删除字段

  [root@rac1 bin]# ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='drop  column vid ' --execute D=houyi,t=ga
  ++++++++++++++++++++++++++++文艺青年分割线+++++++++++++++++++++++++++
  所谓的文艺用法,就是通过OSC实现slave和master数据差异时候的恢复.有人说,这个是pt-table-sync该干的事情.但是在表数据差异较大的时候,使用OSC可能效率更好,而且更加简单可靠.
  OSC如何实现master到slave的数据差异恢复的?
  由于OSC的原理是新建表和使用触发器.然后把原表的数据insert into select from的方式导入新表.如果这个时候,我们把binlog改成row格式.那么insert into记录的肯定是源表的数据了.触发器在row格式的时候,也是在日志中记录的源表数据.也就是说,通过OSC可以逻辑的,无阻塞的把源表的数据同步到所有slave.

  pt-online-schema-change –set-vars ‘binlog_format=ROW’ –alter ‘engine=INNODB’ D=test,t=tt1,h=localhost –execute
  如果你本来就工作在row格式下,那么–set-vars ‘binlog_format=ROW’就可以不设置了.

  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  详细原理解剖,转自http://hi.baidu.com/ytjwt/item/7ea9c637c29079f6e6bb7a0d
  pt-online-schema-change在线更改表结构的实现核心有如下几个过程：
  （注：在跟改过程中涉及到三个表：原表、tmp_table即作为原表导数据的临时表，old_table在最后rename 原表的结果表）
  1、CREATE TABLE `$db`.`$tmp_tbl` LIKE`$db`.`$tbl`” 新建tmp_table，表结构同原表
  2、在tmp_table上更改表结构为需要的表结构
  3、在原表上建立三个触发器，如下：
  （1）CREATETRIGGER mk_osc_del AFTER DELETE ON $table ” “FOR EACH ROW ”
  “DELETE IGNORE FROM $new_table “”WHERE$new_table.$chunk_column = OLD.$chunk_column”;
  （2）CREATETRIGGER mk_osc_ins AFTER INSERT ON $table ” “FOR EACH ROW ”
  “REPLACEINTO $new_table ($columns) ” “VALUES($new_values)”;
  （3）CREATETRIGGER mk_osc_upd AFTER UPDATE ON $table ” “FOR EACH ROW ”
  “REPLACE INTO $new_table ($columns) “”VALUES ($new_values)”;

  我们可以看到这三个触发器分别对应于INSERT、UPDATE、DELETE三种操作：
  （1）mk_osc_del，DELETE操作，我们注意到DELETEIGNORE，当新有数据时，我们才进行操作，也就是说，当在后续导入过程中，如果删除的这个数据还未导入到新表，那么我们可以不在新表执行操作，因为在以后的导入过程中，原表中改行数据已经被删除，已经没有数据，那么他也就不会导入到新表中；
  （2）mk_osc_ins，INSERT操作，所有的INSERT INTO全部转换为REPLACEINTO，为了确保数据的一致性，当有新数据插入到原表时，如果触发器还未把原表数据未同步到新表，这条数据已经被导入到新表了，那么我们就可以利用replaceinto进行覆盖，这样数据也是一致的
  （3）mk_osc_upd UPDATE操作，所有的UPDATE也转换为REPLACEINTO，因为当跟新的数据的行还未同步到新表时，新表是不存在这条记录的，那么我们就只能插入该条数据，如果已经同步到新表了，那么也可以进行覆盖插入，所有数据与原表也是一致的；
  我们也能看出上述的精髓也就这这几条replaceinto操作，正是因为这几条replaceinto才能保证数据的一致性
  4、拷贝原表数据到临时表中，在脚本中使用如下语句
  INSERT IGNORE INTO $to_table ($columns) ” “SELECT $columns FROM $from_table “”WHERE ($chunks->[$chunkno])”，我们能看到他是通过一些查询（基本为主键、唯一键值）分批把数据导入到新的表中，在导入前，我们能通过参数–chunk-size对每次导入行数进行控制，已减少对原表的锁定时间，并且在导入时，我们能通过—sleep参数控制，在每个chunk导入后与下一次chunk导入开始前sleep一会，sleep时间越长，对于磁盘IO的冲击就越小
  5、Rename 原表到old表中，在把临时表Rename为原表，
  “RENAME TABLE `$db`.`$tmp_tbl`TO `$db`.`$tbl`”; 在rename过程，其实我们还是会导致写入读取堵塞的，所以从严格意思上说，我们的OSC也不是对线上环境没有一点影响，但由于rename操作只是一个修改名字的过程，也只会修改一些表的信息，基本是瞬间结束，故对线上影响不太大
  6、清理以上过程中的不再使用的数据，如OLD表。