前言：在生产环境中，DBA经常遇到开发要求在生产环境增加一个字段，添加一个索引什么的，如果是5.6、5.7那还好一些，因为可以online DDL，但是原生的online DDL也是有一定条件的，我们会在后面提到，更多情况下，我们会选择使用pt-osc工具来执行DDL

一、PT-OSC工具使用

pt-osc是pt-online-schema-change的简写，是percona-toolkit工具集中的重要一员，是MySQL5.5及以前版本重要的维护工具之一，下面我们来看下pt-osc工具的使用方法

  1. [root@Mariadb-04 mysql]# pt-online-schema-change --help
  2. pt-online-schema-change alters a table's structure without blocking reads or
  3. writes.  Specify the database and table in the DSN.  Do not use this tool before
  4. reading its documentation and checking your backups carefully.  For more
  5. details, please use the --help option, or try 'perldoc
  6. /usr/bin/pt-online-schema-change' for complete documentation.
  8. Usage: pt-online-schema-change [OPTIONS] DSN
  10. Options:
  12.   --alter=s                        The schema modification, without the ALTER
  13.                                    TABLE keywords
  14.   --alter-foreign-keys-method=s    How to modify foreign keys so they reference
  15.                                    the new table
  16.   --[no]analyze-before-swap        Execute ANALYZE TABLE on the new table
  17.                                    before swaping with the old one (default yes)
  18.   --ask-pass                       Prompt for a password when connecting to
  19.                                    MySQL
  20.   --charset=s                  -A  Default character set
  21.   --[no]check-alter                Parses the --alter specified and tries to
  22.                                    warn of possible unintended behavior (
  23.                                    default yes)
  24.   --check-interval=m               Sleep time between checks for --max-lag (
  25.                                    default 1).  Optional suffix s=seconds, m=
  26.                                    minutes, h=hours, d=days; if no suffix, s is
  27.                                    used.
  28.   --[no]check-plan                 Check query execution plans for safety (
  29.                                    default yes)
  30.   --[no]check-replication-filters  Abort if any replication filter is set on
  31.                                    any server (default yes)
  32.   --check-slave-lag=s              Pause the data copy until this replica's lag
  33.                                    is less than --max-lag
  34.   --chunk-index=s                  Prefer this index for chunking tables
  35.   --chunk-index-columns=i          Use only this many left-most columns of a --
  36.                                    chunk-index
  37.   --chunk-size=z                   Number of rows to select for each chunk
  38.                                    copied (default 1000)
  39.   --chunk-size-limit=f             Do not copy chunks this much larger than the
  40.                                    desired chunk size (default 4.0)
  41.   --chunk-time=f                   Adjust the chunk size dynamically so each
  42.                                    data-copy query takes this long to execute (
  43.                                    default 0.5)
  44.   --config=A                       Read this comma-separated list of config
  45.                                    files; if specified, this must be the first
  46.                                    option on the command line
  47.   --critical-load=A                Examine SHOW GLOBAL STATUS after every
  48.                                    chunk, and abort if the load is too high (
  49.                                    default Threads_running=50)
  50.   --database=s                 -D  Connect to this database
  51.   --default-engine                 Remove ENGINE from the new table
  52.   --defaults-file=s            -F  Only read mysql options from the given file
  53.   --[no]drop-new-table             Drop the new table if copying the original
  54.                                    table fails (default yes)
  55.   --[no]drop-old-table             Drop the original table after renaming it (
  56.                                    default yes)
  57.   --[no]drop-triggers              Drop triggers on the old table. --no-drop-
  58.                                    triggers forces --no-drop-old-table (default
  59.                                    yes)
  60.   --dry-run                        Create and alter the new table, but do not
  61.                                    create triggers, copy data, or replace the
  62.                                    original table
  63.   --execute                        Indicate that you have read the
  64.                                    documentation and want to alter the table
  65.   --force                          This options bypasses confirmation in case
  66.                                    of using alter-foreign-keys-method = none ,
  67.                                    which might break foreign key constraints
  68.   --help                           Show help and exit
  69.   --host=s                     -h  Connect to host
  70.   --max-flow-ctl=f                 Somewhat similar to --max-lag but for PXC
  71.                                    clusters
  72.   --max-lag=m                      Pause the data copy until all replicas' lag
  73.                                    is less than this value (default 1s).
  74.                                    Optional suffix s=seconds, m=minutes, h=
  75.                                    hours, d=days; if no suffix, s is used.
  76.   --max-load=A                     Examine SHOW GLOBAL STATUS after every
  77.                                    chunk, and pause if any status variables are
  78.                                    higher than their thresholds (default
  79.                                    Threads_running=25)
  80.   --new-table-name=s               New table name before it is swapped. %T is
  81.                                    replaced with the original table name (
  82.                                    default %T_new)
  83.   --password=s                 -p  Password to use when connecting
  84.   --pid=s                          Create the given PID file
  85.   --plugin=s                       Perl module file that defines a
  86.                                    pt_online_schema_change_plugin class
  87.   --port=i                     -P  Port number to use for connection
  88.   --print                          Print SQL statements to STDOUT
  89.   --progress=a                     Print progress reports to STDERR while
  90.                                    copying rows (default time,30)
  91.   --quiet                      -q  Do not print messages to STDOUT (disables --
  92.                                    progress)
  93.   --recurse=i                      Number of levels to recurse in the hierarchy
  94.                                    when discovering replicas
  95.   --recursion-method=a             Preferred recursion method for discovering
  96.                                    replicas (default processlist,hosts)
  97.   --set-vars=A                     Set the MySQL variables in this comma-
  98.                                    separated list of variable=value pairs
  99.   --sleep=f                        How long to sleep (in seconds) after copying
  100.                                    each chunk (default 0)
  101.   --socket=s                   -S  Socket file to use for connection
  102.   --statistics                     Print statistics about internal counters
  103.   --[no]swap-tables                Swap the original table and the new, altered
  104.                                    table (default yes)
  105.   --tries=a                        How many times to try critical operations
  106.   --user=s                     -u  User for login if not current user
  107.   --version                        Show version and exit
  108.   --[no]version-check              Check for the latest version of Percona
  109.                                    Toolkit, MySQL, and other programs (default
  110.                                    yes)
  112. Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time
  114. Rules:
  116.   --dry-run and --execute are mutually exclusive.
  117.   This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.
  119. DSN syntax is key=value[,key=value...]  Allowable DSN keys:
  121.   KEY  COPY  MEANING
  122.   ===  ====  =============================================
  123.   A    yes   Default character set
  124.   D    yes   Database for the old and new table
  125.   F    yes   Only read default options from the given file
  126.   P    yes   Port number to use for connection
  127.   S    yes   Socket file to use for connection
  128.   h    yes   Connect to host
  129.   p    yes   Password to use when connecting
  130.   t    no    Table to alter
  131.   u    yes   User for login if not current user
  133.   If the DSN is a bareword, the word is treated as the 'h' key.
  135. Options and values after processing arguments:
  137.   --alter                          (No value)
  138.   --alter-foreign-keys-method      (No value)
  139.   --analyze-before-swap            TRUE
  140.   --ask-pass                       FALSE
  141.   --charset                        (No value)
  142.   --check-alter                    TRUE
  143.   --check-interval                 1
  144.   --check-plan                     TRUE
  145.   --check-replication-filters      TRUE
  146.   --check-slave-lag                (No value)
  147.   --chunk-index                    (No value)
  148.   --chunk-index-columns            (No value)
  149.   --chunk-size                     1000
  150.   --chunk-size-limit               4.0
  151.   --chunk-time                     0.5
  152.   --config                         /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-online-schema-change.conf,/root/.percona-toolkit.conf,/root/.pt-online-schema-change.conf
  153.   --critical-load                  Threads_running=50
  154.   --database                       (No value)
  155.   --default-engine                 FALSE
  156.   --defaults-file                  (No value)
  157.   --drop-new-table                 TRUE
  158.   --drop-old-table                 TRUE
  159.   --drop-triggers                  TRUE
  160.   --dry-run                        FALSE
  161.   --execute                        FALSE
  162.   --force                          FALSE
  163.   --help                           TRUE
  164.   --host                           (No value)
  165.   --max-flow-ctl                   (No value)
  166.   --max-lag                        1
  167.   --max-load                       Threads_running=25
  168.   --new-table-name                 %T_new
  169.   --password                       (No value)
  170.   --pid                            (No value)
  171.   --plugin                         (No value)
  172.   --port                           (No value)
  173.   --print                          FALSE
  174.   --progress                       time,30
  175.   --quiet                          FALSE
  176.   --recurse                        (No value)
  177.   --recursion-method               processlist,hosts
  178.   --set-vars                       
  179.   --sleep                          0
  180.   --socket                         (No value)
  181.   --statistics                     FALSE
  182.   --swap-tables                    TRUE
  183.   --tries                          (No value)
  184.   --user                           (No value)
  185.   --version                        FALSE
  186.   --version-check                  TRUE

乍一看，参数是不是眼花缭乱？其实要掌握的参数其实也不多：

  • —user 指定执行的用户

  • —ask-pass 提示输入密码

  • —host 指定连接主机

  • —alter 后面跟上需要执行的sql

  • D 指定DBNAME

  • t 指定table name

  • —print 打印输出信息

  • —execute 提示输入密码后立即执行sql

二、实战一把

正好，今天查看慢日志，有个SQL需要优化，看了下SQL以后发现目前的执行计划走的索引只匹配到了一个字段，需要根据条件另外创建一个索引，那么我们可以通过pt-osc工具来添加此索引。

  1. [root@Mariadb-04 mysql]# pt-online-schema-change --user=root --ask-pass --host=localhost  --alter "add key idx_chnlid_docstatus_dockind_docpubtime(chnlid,docstatus,dockind,docpubtime)" D=xx,t=xx --print --execute
  2. Enter MySQL password: 
  3. ....
  4. Operation, tries, wait:
  5.   analyze_table, 10, 1
  6.   copy_rows, 10, 0.25
  7.   create_triggers, 10, 1
  8.   drop_triggers, 10, 1
  9.   swap_tables, 10, 1
  10.   update_foreign_keys, 10, 1
  11. Altering `xx`.`xx`...
  12. Creating new table...
  13. CREATE TABLE `xxxx`.`_xxxx_new` (
  14.   `xxx` int(11) NOT NULL,
  15.   `xx` int(11) NOT NULL DEFAULT '0',
  16.   `xx` datetime DEFAULT NULL,
  17.   `xx` varchar(50) DEFAULT NULL,
  18.   `xx` datetime DEFAULT NULL,
  19.   `xx` int(11) DEFAULT '1',
  20.   `xx` datetime DEFAULT NULL,
  21.   `xx` int(11) DEFAULT NULL,
  22.   `xx` int(11) DEFAULT NULL,
  23.   `xx` int(11) DEFAULT '0',
  24.   `xx` int(11) NOT NULL DEFAULT '0',
  25.   `xxx` int(11) NOT NULL DEFAULT '0',
  26.   `xx` datetime DEFAULT NULL,
  27.   `xx` int(11) DEFAULT '0',
  28.   `xx` varchar(200) DEFAULT NULL,
  29.   `xx` int(2) DEFAULT '0',
  30.   `xx` int(11) DEFAULT NULL,
  31.   PRIMARY KEY (`xx`),
  32.   KEY `xx` (`xxx`,`xxx`) USING BTREE
  33. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  34. Created new table xx._xx_new OK.
  35. Altering new table...
  36. ALTER TABLE `xx`.`_xx_new` add key idx_chnlid_docstatus_dockind_docpubtime(xx,xx,xx,xx)
  37. Altered `xx`.`_xx_new` OK.
  38. 2018-05-16T17:29:51 Creating triggers...
  39. CREATE TRIGGER `pt_osc_xx_xx_del` AFTER DELETE ON `xx`.`xx` FOR EACH ROW DELETE IGNORE FROM `xx`.`_xx_new` WHERE `xx`.`_xx_new`.`recid` <=> OLD.`recid`
  40. CREATE TRIGGER `pt_osc_xx_xx_upd` AFTER UPDATE ON `xx`.`xx` FOR EACH ROW REPLACE INTO `xx`.`_xx_new` (..........)
  41. CREATE TRIGGER `pt_osc_xx_xx_ins` AFTER INSERT ON `xx`.`xx` FOR EACH ROW REPLACE INTO `xx`.`_xx_new` (..........)
  42. 2018-05-16T17:29:51 Created triggers OK.
  43. 2018-05-16T17:29:51 Copying approximately 6788869 rows...
  44. INSERT LOW_PRIORITY IGNORE INTO `xx`.`_xx_new` (............. FROM `xx`.`xx` FORCE INDEX(`PRIMARY`) WHERE ((`recid` >= ?)) AND ((`recid` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 36224 copy nibble*/
  45. SELECT /*!40001 SQL_NO_CACHE */ `recid` FROM `xx`.`xx` FORCE INDEX(`PRIMARY`) WHERE ((`recid` >= ?)) ORDER BY `recid` LIMIT ?, 2 /*next chunk boundary*/
  46. Copying `xx`.`xx`:   5% 08:43 remain
  47. Copying `xx`.`xx`:  11% 07:30 remain
  48. Copying `xx`.`xx`:  14% 09:59 remain
  49. Copying `xx`.`xx`:  18% 09:25 remain
  50. Copying `xx`.`xx`:  22% 10:23 remain
  51. Copying `xx`.`xx`:  23% 12:03 remain
  52. Copying `xx`.`xx`:  27% 12:09 remain
  53. Copying `xx`.`xx`:  32% 10:35 remain
  54. Copying `xx`.`xx`:  32% 11:32 remain
  55. Copying `xx`.`xx`:  33% 12:28 remain
  56. Copying `xx`.`xx`:  35% 12:34 remain
  57. Copying `xx`.`xx`:  41% 10:35 remain
  58. Copying `xx`.`xx`:  46% 09:13 remain
  59. Copying `xx`.`xx`:  48% 09:08 remain
  60. Copying `xx`.`xx`:  53% 08:02 remain
  61. Copying `xx`.`xx`:  54% 08:20 remain
  62. Copying `xx`.`xx`:  57% 07:52 remain
  63. Copying `xx`.`xx`:  60% 07:25 remain
  64. Copying `xx`.`xx`:  62% 07:23 remain
  65. Copying `xx`.`xx`:  66% 06:24 remain
  66. Copying `xx`.`xx`:  71% 05:23 remain
  67. Copying `xx`.`xx`:  74% 04:47 remain
  68. Copying `xx`.`xx`:  75% 04:52 remain
  69. Copying `xx`.`xx`:  76% 04:44 remain
  70. Copying `xx`.`xx`:  80% 03:46 remain
  71. Copying `xx`.`xx`:  82% 03:33 remain
  72. Copying `xx`.`xx`:  86% 02:47 remain
  73. Copying `xx`.`xx`:  86% 02:52 remain
  74. Copying `xx`.`xx`:  87% 02:47 remain
  75. Copying `xx`.`xx`:  90% 02:00 remain
  76. Copying `xx`.`xx`:  94% 01:15 remain
  77. Copying `xx`.`xx`:  95% 01:04 remain
  78. Copying `xx`.`xx`:  97% 00:30 remain
  79. 2018-05-16T17:53:32 Copied rows OK.
  80. 2018-05-16T17:53:32 Analyzing new table...
  81. 2018-05-16T17:53:33 Swapping tables...
  82. RENAME TABLE `xx`.`xx` TO `xx`.`_xx_old`, `xx`.`_xx_new` TO `xx`.`xx`
  83. 2018-05-16T17:53:33 Swapped original and new tables OK.
  84. 2018-05-16T17:53:33 Dropping old table...
  85. DROP TABLE IF EXISTS `xx`.`_xx_old`
  86. 2018-05-16T17:53:37 Dropped old table `xx`.`_xx_old` OK.
  87. 2018-05-16T17:53:37 Dropping triggers...
  88. DROP TRIGGER IF EXISTS `xx`.`pt_osc_xx_xx_del`;
  89. DROP TRIGGER IF EXISTS `xx`.`pt_osc_xx_xx_upd`;
  90. DROP TRIGGER IF EXISTS `xx`.`pt_osc_xx_xx_ins`;
  91. 2018-05-16T17:53:37 Dropped triggers OK.
  92. Successfully altered `xx`.`xx`.

以上就是我在生产环境利用pt-osc做的在线DDL更改，下面我们根据log来分析一下pt-osc的原理

三、原理分析

从上面的log我们可以看出整体流程是这样的

  • 首先根据原表DDL定义创建一张同样表结构的临时表，并且命名规则为_原表名_new

  • 在新建的表上创建需要添加的索引

  • 在原表上创建三个触发器，分别对应曾、改、删

  • 将原表的数据以insert into select …的方式拷贝到新表

  • 数据拷贝完成以后会将原表重命名为_原表名_old，将新表名重命名为原表名

  • 删除表_原表名_old

  • 删除触发器

  • 成功

总的来说，整个过程还是比较清晰的

四、pt-osc使用限制

  • 外键问题，因为涉及到表的重命名，因此如果要修改的表是其他表的外键，那么修改起来是有问题的，幸好pt-osc提供了—alter-foreign-keys-method选项，优先采用rebuild_constrains，也可以指定使用drop_swap

  • 如果表上有触发器，那么我们无法采用pt-osc来做在线ddl，因为MySQL规定同一个表不能存在多个相同定义的触发器

  • 从库延迟，如果需要变更的表数据量比较多的话，会造成从库延迟的问题，生产环境中我们需要注意这个问题

  • 磁盘容量的问题也是我们需要注意的，如果服务器磁盘空间比较紧张，并且需要变更的表又是大表，我们需要提前估算好容量

五、原生online ddl的限制

对于原生的online ddl支持如下

  • MySQL5.6

    • 辅助索引的创建和删除

    • 改变自增长id值

    • 添加或删除外键

    • 列的重命名

  • MySQL5.7

    • 增加支持在线更改索引名

    • 增加在线增加varchar列长度

  • 如果涉及到表空间的改变，那就不支持在线online ddl，是在不确定，可以参考下官方手册