代码改变世界

Percona-Toolkit 之 pt-online-schema-change 总结

2019-03-27 10:14  H_Johnny  阅读(3903)  评论(0)  编辑  收藏  举报

pt-online-schema-change - ALTER tables without locking them.

pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.

pt-online-schema-change是Percona-Toolkit工具集中的一个组件，很多DBA在使用Percona-Toolkit时第一个使用的工具就是它，同时也是使用最频繁的一个工具。它可以做到在修改表结构的同时（即进行DDL操作）不阻塞数据库表DML的进行，这样降低了对生产环境数据库的影响。在MySQL5.6之前是不支持Online DDL特性的，即使在添加二级索引的时候有FIC特性，但是在修改表字段的时候还是会有锁表并阻止表的DML操作，这样对于DBA来说是非常痛苦的，好在有pt-online-schema-change工具在没有Online DDL时解决了这一问题，关于Online DDL可以参考另一篇博文：MySQL InnoDB Online DDL学习。

本文是关于之前有关pt-online-schema-change工具使用的学习笔记进行重新整理，使用最新版本的工具同时也进行原理知识的梳理。

安装

  • 获取

可以到官网：https://www.percona.com/downloads/percona-toolkit/LATEST/，选择适合的操作系统版本和工具版本进行下载：

# wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-1.el7.x86_64.rpm

本文使用的安装包是：percona-toolkit-3.0.13-1.el7.x86_64.rpm

  • 安装

因为下载的是RPM安装包，所以采用本地安装的方式进行：

-- 如果只使用本地rpm安装的话，需要安装以下依赖的perl模块
# rpm -ivh percona-toolkit-3.0.13-1.el7.x86_64.rpm
warning: percona-toolkit-3.0.13-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
error: Failed dependencies:
        perl(DBI) >= 1.13 is needed by percona-toolkit-3.0.13-1.el7.x86_64
        perl(DBD::mysql) >= 1.0 is needed by percona-toolkit-3.0.13-1.el7.x86_64
        perl(IO::Socket::SSL) is needed by percona-toolkit-3.0.13-1.el7.x86_64
        perl(Digest::MD5) is needed by percona-toolkit-3.0.13-1.el7.x86_64
        perl(Term::ReadKey) is needed by percona-toolkit-3.0.13-1.el7.x86_64

-- 如果配置了在线的yum源，则使用yum安装
# yum localinstall percona-toolkit-3.0.13-1.el7.x86_64.rpm

基本说明

pt-online-schema-change [OPTIONS] DSN

常用选项(OPTIONS)

--alter
变更结构选项，不需要ALTER TABLE关键字，如果表有多个变更可以使用逗号分隔。

限制：
1.在绝大部分情况下表都需要有主键或者是唯一索引。因为这个工具会在运行的时候创建一个DELETE触发器，这是为了保证在变更中新表能够与旧表保持更新一致性。值得注意的是，如果在需要变更的列上创建主键或是唯一索引时，则会以这些列创建触发器；
2.不能使用RENAME子句为表进行重命名；
3.字段不能通过删除再重添加的方式进行重命名，这种方式是不会拷贝原字段的数到新字段上；
4.如果新增NOT NULL的列并且没有指定default值，工具就会执行失败，它并不会指定默认值；
5.涉及到删除外键时，需要指定_constraint_name，工具会在新表上创建一个前面加了下划线的外键名称，这个外键名称与原致。如需要删除外键fk_foo，则指定'--alter "DROP FOREIGN KEY _fk_foo"'。

--alter-foreign-keys-method
采用何种方式修改外键以便关联到新表上。有外键约束的表需要被特殊处理，为了确保外键依然能够关联到正确的表上。当工具重命名外键关联的父表时，确保外键也必须关联到重命名后的父表。
主要有以下几种方式：

auto：让工具自动选择使用。优先选择rebuild_constraints，如果不成功，则选择drop_swap；
rebuild_constraints：这种方式使用ALTER TABLE先删除然后重建外键关联到新父表。这是首选的方式，如果一张或多张子表过大会导致ALTER需要很长时间，子表会被阻塞；

drop_swap：禁用外键约束(FOREIGN_KEY_CHECKS=0)，在进行重命名新父表之前删除原父表，这与常规转换旧表与新表的方式不同，这个RENAME操作是原子性的并且对应用客户端无感知。
这种方式更快速并且不会阻塞，但是也有隐患：
1.删除原父表以及重命名新表这段时间很短，如果这段时间更改子表有可能会报错；
2.如果重命名新表发生失败，而原父表已经永久删除了，这时就需要人工进行干预了。
这种方式强制使用选项'--no-swap-tables'和'--no-drop-old-table'。

none：这种方式类似于drop_swap，不同在于不进行swap原父表。子表有任何外键关联父表都将变成关联一张不存在的表，这会使得子表的外键约束失效，可以通过SHOW ENGINE INNODB STATUS查看。

--[no]analyze-before-swap
默认值：yes
在新表与旧表完成转换之前对新表执行ANALYZE TABLE操作，默认会在MySQL5.6及之后版本并且开启innodb_stats_persistent的情况下执行。

--ask-pass
命令行提示密码输入，保护密码安全，前提需安装模块perl-TermReadKey。

--[no]check-alter
默认值：yes
解析变更选项的内容，发出表变更警告，主要警告项为：
1.字段重命名
在工具的早期版本中，通过指定CHANGE COLUMN name new_name进行字段重命名会导致数据库的丢失，现在的版本已经通过代码解决了数据一致性问题。但这段代码并不能保证能够确保数据的不丢失。所以当涉及到字段名变更时应通过添加选项'--dry-run'和'--print'查看变更是否可以正确执行。
2.删除主键
如果'--alter'选项中包含DROP PRIMARY KEY删除主键的操作，除非指定选项'--dry-run'，否则工具将退出。变更表的主键是十分危险的，工具变更时建立的触发器，尤其是DELETE触发器，是基于主键的，在做主键变更前先添加选项'--dry-run'和'--print'验证触发器是可用的。

--[no]check-replication-filters
默认值：yes
如果服务器指定了任何主从复制过滤选项，该工具会查询是否有复制过滤选项，一旦发现，工具都会中止并报错。

--check-slave-lag
指定暂停旧表与新表的数据拷贝直到主从复制小于选项'--max-lag'指定的值。

--skip-check-slave-lag
DSN类型，可重复使用
指定DSN连接从库时跳过主从延迟检查，可以指定多个从库检查。

--check-interval
默认值：1s
指定因为选项'--max-lag'检查之间休眠时间。

--chunk-index
指定使用哪个索引对表进行chunk分块操作。默认情况下会选择最优的索引，工具会在SQL语句中添加FORCE INDEX子句。

--chunk-index-columns
指定使用选项'--chunk-index'的索引使用最左前缀几个索引字段，只适用于复合索引。

--chunk-size
默认值：1000
指定表分块的chunk大小，每个chunk需要拷贝的表行数，允许的后缀单位为k、M、G。
当指定了这个选项会覆盖工具默认动态调整chunk块大小以便在选项'--chunk-time'指定时间内完成行拷贝的行为。

--chunk-time
默认值：0.5
动态调整每个chunk的大小使相应的表行数都在指定的时间内完成拷贝查询。如果该选项值设置为0，则不会动态调整chunk的大小，就有可能造成每次拷贝查询的时间不同，但每个chunk大小还是一致的。

--host，-h
指定连接的数据库IP地址。

--port，-P
指定连接的数据库Port端口。

--user，-u
指定连接的数据库用户。

--password，-p
指定连接的数据库用户密码。

--database，-D
指定连接的数据库。

--charset，-A
指定连接字符集。

--max-lag
默认值：1s
指定允许主从复制延迟时长的最大值，单位秒。如果在每次拷贝查询之后主从延迟超过指定的值，则操作将暂停执行，暂停休眠时间为选项'--check-interval'指定的值。待休眠时间结束之后再次检查主从延迟时长，检查方法是通过从库查询的'Seconds_Behind_Master'值来确定。如果主从复制延迟一直大于该参数指定值或者从库停止复制，则操作将一直等待直到从库重新启动并且延迟小于该参数指定值。

--max-load
数组类型，默认值：Threads_running = 25
在变更拷贝完每个chunk数据之后，运行SHOW GLOBAL STATUS检查所指定变量值高于该参数指定变量的阈值时将暂停操作。如果有多个变量阈值，可以用','(逗号)进行分隔，参数指定型式可以为变量名=MAX_VALUE或变量名:MAX_VALUE。
如果只是指定变量名，没有为其指定阈值，则检查当前值并增加20%作为阈值。如：

    --max-load=Threads_running：没有指定具体值，以当前查询值增加20%作为阈值，如当前为100，阈值为120；
    --max-load=Threads_running:10：以当前指定值为阈值。

--critical-load
数组类型，默认值：Threads_running = 50
指定需中止操作的状态变量阈值。用法可以参考选项'--max-load'。

--preserve-triggers
指定保留旧表的触发器。
从MySQL5.7.2起开始支持在同一张给定的表上定义具有相同触发事件和触发时间的多个触发器。这意味着如果表原来已有触发器，那么工具所需的触发器也可以创建成功。如果指定了该选项，则工具将旧表上所有的触发器复制到新表上，然后再进行表数据行的拷贝操作。

限制：
1.如果旧表上的触发器引用了将被工具删除的字段，则触发器失效；
2.该选项不能与选项'--no-drop-triggers'、'--no-drop-old-table'和'--no-swap-tables'一起使用，因为该选项需要删除旧表的触发器并在新表上重新创建，因为表不可能有多个同名的触发器。

--null-to-not-null
指定可以将允许NULL的字段转换为NOT NULL字段。其中如有包含NULL行的字段值转换为字段默认值，如果没有字段值，则根字段类型来分配默认值。如：字符串类型为''(空字符串)，数值类型为0。

--new-table-name
字符串类型，默认值：%T_new
指定旧表和新表交换之前新表的名称。%T会替换为旧表名称。

--[no]drop-new-table
默认值：yes
指定如果拷贝旧表数据到新表时失败，则删除新表。
如果指定选项'--no-drop-new-table'以及'--no-swap-tables'将保留一份变更后的副本，但不会对旧表进行修改。

限制：
当选项'--alter-foreign-keys-method'指定的方式为drop_swap时，选项'--no-drop-new-table'不生效。

--[no]drop-old-table
默认值：yes
指定在完成旧表与新表交换重命名之后删除旧表。如果之间发生了错误，则会保留旧表。指定选项'--no-swap-tables'同样不会删除旧表。

--[no]drop-triggers
默认值：yes
指定旧表上删除触发器。如果指定了选项'--no-drop-triggers'就会强制指定'--no-drop-old-table'。

--[no]swap-tables
默认值：yes
指定变更交换旧表和新表。
如果指定选项'--no-swap-tables'也会运行整个过程，只是最后不进行旧表与新表的交换，并且删除新表。

--dry-run
指定创建和变更新表，但是不创建触发器，也不拷贝数据和变更原始表。

--execute
指定需要执行真正的变更操作。当确定要执行变更操作时必须指定该选项，如果不指定该选项，则工具会进行安全检查之后退出。

--[no]check-unique-key-change
默认值：yes
当工具要进行添加唯一索引的变更时停止运行。因为工具使用语句INSERT IGNORE从旧表进行数据拷贝插入新表，如果插入的值违返唯一性约束，数据插入不会明确提示失败但这样会造成数据丢失。

--recursion-method
默认值：processlist，hosts
指定获取从库的方式。
METHOD       USES
===========  =============================================
processlist  SHOW PROCESSLIST   
hosts        SHOW SLAVE HOSTS   
dsn=DSN      DSNs from a table
none         Do not find slaves
==========================================================
processlist：通过SHOW PROCESSLIST方式找到slave，为默认方式，当SHOW SLAVE HOSTS不可用时。一旦实例运行在非3306端口上时，hosts方式就会变为默认方式；
hosts：通过SHOW SLAVE HOSTS方式找到slave，hosts方式要求从库配置'--report_host'和'--report_port'这两个参数；
dsn：通过读取表中从库的DSN信息进行连接。

--recurse
指定搜寻从库的层级，默认无限级。

--set-vars
默认：
    wait_timeout=10000
    innodb_lock_wait_timeout=1
    lock_wait_timeout=60
运行检查时指定参数值，如有多个用','(逗号)分隔。如`--set-vars=wait_timeout=5000`。

--sleep
默认值：0s
指定表变更拷贝数据时的间隔时间。

--print
打印工具执行过程中的语句到STDOUT。可以结合'--dry-run'一起使用。
 
--progress
打印工具执行过程的进度提示到STDERR。选项值有两部分组成，用逗号进行分隔，第一部分为百分比，时间和迭代。第二部分为根据第一部分数据更新频率，也分为百分比，时间和迭代。

--quiet，-q
不打印工具执行过程的信息到STDOUT(禁用'--progress')。但错误和警告还是打印到STDERR。

--statistics
打印内部计数的统计信息。

--version
显示工具的版本并退出。

--[no]version-check
默认值：yes
检查Percona Toolkit、MySQL和其他程序的最新版本。

DSN选项(DSN)

可以使用DSN方式来连接数据库，DSN选项为key=value方式，在等号的两侧不能有空格出现，并且区分大小写，多个选项之前以','(逗号)隔开，主要选项如下：

  • A
    指定字符集
  • D
    指定变更表所在数据库
  • t
    指定需要变更的表
  • h
    指定要连接的HOST
  • P
    指定要连接的PORT
  • S
    指定连接所使用的SOCKET文件(Unix systems)
  • u
    指定连接的用户名
  • p
    指定连接的用户名密码

示例：
h=192.168.58.3,P=3306,D=employees,t=employees

使用限制

  1. 要求需要执行变更的表有主键(Primary key)或唯一索引(Unique index)，否则工具会执行失败，参考选项--alter说明；
  2. 如果检测到表有外键约束(Foreign key)，工具除非选项--alter-foreign-keys-method，否则不会执行变更；
  3. 如果检测到主从复制中存在过滤，则工具不会执行，参考选项--[no]check-replication-filters说明；
  4. 如果检测到主从复制有延迟，则工具有可能会暂停数据拷贝，参考选项--max-lag说明；
  5. 如果检测到连接当前服务器负载过高，则工具有可能暂停执行或中止退出，参考选项--max-load各--critical-load说明。

用法示例

  • 测试数据准备

本文基于MySQL官方示例数据库employee：Example Databases进行测试。

-- employees：
mysql root@localhost:employees> show create table employees\G;
***************************[ 1. row ]***************************
Table        | employees
Create Table | CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `idx_first_last` (`first_name`,`last_name`),
  KEY `idx_birth_hire` (`birth_date`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.008s

-- dept_emp：
mysql root@localhost:employees> show create table dept_emp\G;
***************************[ 1. row ]***************************
Table        | dept_emp
Create Table | CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.010s

-- departments：
mysql root@localhost:employees> show create table departments\G;
***************************[ 1. row ]***************************
Table        | departments
Create Table | CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.012s

mysql root@localhost:employees> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024   |
+----------+
1 row in set
Time: 0.342s
mysql root@localhost:employees> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
| 331603   |
+----------+
1 row in set
Time: 0.306s
mysql root@localhost:employees> select count(*) from departments;
+----------+
| count(*) |
+----------+
| 9        |
+----------+
1 row in set
Time: 0.050s
  • 添加字段
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add comment varchar(50) not null default 'pt-osc'" --charset=utf8

因为employees表中的emp_no字段被其他表外建关联，以下命令执行时会报如下错误：

You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully.

根据报错信息的提示，加入选项--alter-foreign-keys-method重新执行并通过选项--dry-run查看执行过程主要信息：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add comment varchar(50) not null default 'pt-osc'" --alter-foreign-keys-method=auto --charset=utf8 --dry-run
Enter MySQL password:

Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `employees`.`dept_emp` (approx. 331143 rows)
  `employees`.`dept_manager` (approx. 24 rows)
Will automatically choose the method to update foreign keys.
Starting a dry run.  `employees`.`employees` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table employees._employees_new OK.
Altering new table...
Altered `employees`.`_employees_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not determining the method to update foreign keys because this is a dry run.
Not swapping tables because this is a dry run.
Not updating foreign key constraints because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2019-03-25T13:30:05 Dropping new table...
2019-03-25T13:30:05 Dropped new table OK.
Dry run complete.  `employees`.`employees` was not altered.

-- 确保信息无误之后可以真正执行变更操作
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add comment varchar(50) not null default 'pt-osc'" --alter-foreign-keys-method=auto --charset=utf8 --execute

……省略……
Will automatically choose the method to update foreign keys.
Altering `employees`.`employees`...
Creating new table...
Created new table employees._employees_new OK.
Altering new table...
Altered `employees`.`_employees_new` OK.
2019-03-25T13:35:25 Creating triggers...
2019-03-25T13:35:25 Created triggers OK.
2019-03-25T13:35:25 Copying approximately 299512 rows...
2019-03-25T13:35:31 Copied rows OK.
2019-03-25T13:35:31 Max rows for the rebuild_constraints method: 99266
Determining the method to update foreign keys...
2019-03-25T13:35:31   `employees`.`dept_emp`: too many rows: 331143; must use drop_swap
2019-03-25T13:35:31 Drop-swapping tables...
2019-03-25T13:35:31 Analyzing new table...
2019-03-25T13:35:31 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2019-03-25T13:35:31 Dropping triggers...
2019-03-25T13:35:31 Dropped triggers OK.
Successfully altered `employees`.`employees`.
  • 修改字段

将表employees的comment字段的字符集修改为utf8mb4：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "modify column comment varchar(50) character set utf8mb4" --alter-foreign-keys-method=auto --charset=utf8 --execute
  • 删除字段
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "drop column comment" --alter-foreign-keys-method=auto --charset=utf8 --execute
  • 添加索引

为表dept_emp的字段from_date和to_date创建复合索引idx_fr_to_date：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add index idx_fr_to_date(from_date,to_date)" --alter-foreign-keys-method=auto --charset=utf8 --execute
  • 删除索引
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "drop index idx_fr_to_date" --alter-foreign-keys-method=auto --charset=utf8 --execute
  • 修改字段允许NULL

将表dept_emp的字段to_date指定为允许NULL：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "modify column to_date date null" --alter-foreign-keys-method=auto --charset=utf8 --execute
  • 修改字段不允许NULL(NOT NULL)

为表employees添加字段ptosc_num并允许NULL，字段类型为int，没有指定默认值。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add ptosc_num int null" --alter-foreign-keys-method=auto --charset=utf8 --execute

修改字段ptosc_num为不允许NULL(NOT NULL)，需要通过指定选项--null-to-not-null，否则会报错。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "modify column ptosc_num int not null" --alter-foreign-keys-method=auto --null-to-not-null --charset=utf8 --execute

-- 因为字段ptosc_num没有指定默认值，字段类型为int，所以默认值为0
mysql root@localhost:employees> select * from employees limit 5;
+--------+------------+------------+-----------+--------+------------+-----------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | ptosc_num |
+--------+------------+------------+-----------+--------+------------+-----------+
| 10001  | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 | 0         |
| 10002  | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 | 0         |
| 10003  | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 | 0         |
| 10004  | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 | 0         |
| 10005  | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 | 0         |
+--------+------------+------------+-----------+--------+------------+-----------+
5 rows in set
Time: 0.022s
  • 删除外键

需要为外键指定名称为_forigen_key，因为在创建新表时候默认为新表上的外键创建这样的名称，如果没这样指定则无法删除。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "drop foreign key _dept_emp_ibfk_1" --alter-foreign-keys-method=auto --charset=utf8 --execute
  • 重建表
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "engine=InnoDB" --alter-foreign-keys-method=auto --charset=utf8 --execute
  • 变更后保留旧表

如果是涉及外键关联的父表进行变更，则建议选项--alter-foreign-keys-method=rebuild_constraints，这样在子表中会重命名外键约束名，如果选项--alter-foreign-keys-method有可能取值drop_swap时，则会强制使用选项--no-swap-tables和--no-drop-old-table，其中--no-swap-tables并不会有旧表的产生，就不存在保留之说了。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add comment varchar(50) notnull default 'pt-osc'" --no-drop-old-table --charset=utf8 --execute

以上语句执行完成后会在数据库中生成名为_dept_emp_old的表，即变更之前的旧表。

  • 变更后保留新表

顾名思义，就是先做一次完整的表变更操作，但是不进行旧表与新表的交换，也不删除变更之后的新表，通过指定选项--no-drop-new-table和--no-swap-tables实现，可以通过选项--new-table-name指定新表名，当选项--alter-foreign-keys-method=drop_swap时，--no-drop-new-table不生效，与保留旧表的情形一致。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add comment varchar(50) notnull default 'pt-osc'" --no-drop-new-table --no-swap-tables --new-table-name='dept_emp_bak' --charset=utf8 --execute

以上语句执行完成后会在数据库中生成名为dept_emp_bak的表，即变更之后的新表，但对旧表不会做任何修改。

  • 添加主键

如果是InnoDB表没有主键，真的不敢想像啊，但还是要进行测式下。这里测试基于employees表创建employees_ptosc表：

mysql root@localhost:employees> create table employees_ptosc as select * from employees;
Query OK, 300024 rows affected
Time: 2.010s
mysql root@localhost:employees> show create table employees_ptosc;
+-----------------+--------------------------------------+
| Table           | Create Table                         |
+-----------------+--------------------------------------+
| employees_ptosc | CREATE TABLE `employees_ptosc` (     |
|                 |   `emp_no` int(11) NOT NULL,         |
|                 |   `birth_date` date NOT NULL,        |
|                 |   `first_name` varchar(14) NOT NULL, |
|                 |   `last_name` varchar(16) NOT NULL,  |
|                 |   `gender` enum('M','F') NOT NULL,   |
|                 |   `hire_date` date NOT NULL          |
|                 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------+--------------------------------------+
1 row in set
Time: 0.022s

对employees_ptosc表添加主键：

-- 如果employees_ptosc表没有任何索引和约束会报如下信息，工具执行失败
Cannot chunk the original table `employees`.`employees_ptosc`: There is no good index and the table is oversized. at /usr/bin/pt-online-schema-change line 5882.

-- 先为employees_ptosc表创建基于first_name的索引idx_first_name，再次执行添加主键
mysql root@localhost:employees> create index idx_first_name on employees_ptosc(first_name);
Query OK, 0 rows affected
Time: 1.175s

-- 如果没有加选项--no-check-unique-key-change会报如下信息
……省略……
Altering `employees`.`employees_ptosc`...
`employees`.`employees_ptosc` was not altered.
You are trying to add an unique key. This can result in data loss if the data is not unique.
Please read the documentation for the --check-unique-key-change parameter.
You can check if the column(s) contain duplicate content by running this/these query/queries:

SELECT IF(COUNT(DISTINCT emp_no) = COUNT(*),
       'Yes, the desired unique index currently contains only unique values',
       'No, the desired unique index contains duplicated values. There will be data loss'
) AS IsThereUniqueness FROM `employees`.`employees_ptosc`;

Keep in mind that these queries could take a long time and consume a lot of resources

大致意思就是工具无法确定需要创建主键基于的字段值是否唯一，一旦有重复值出现，在数据拷贝的时候容易出现数据丢失，并给出了检查的语句。

mysql root@localhost:employees> SELECT IF(COUNT(DISTINCT emp_no) = COUNT(*),
                                       'Yes, the desired unique index currently contains only unique values',
                                       'No, the desired unique index contains duplicated values. There will be data loss'
                                ) AS IsThereUniqueness FROM `employees`.`employees_ptosc`;

+---------------------------------------------------------------------+
| IsThereUniqueness                                                   |
+---------------------------------------------------------------------+
| Yes, the desired unique index currently contains only unique values |
+---------------------------------------------------------------------+
1 row in set
Time: 0.274s

使用选项--no-check-unique-key-change再次执行添加主键操作：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees_ptosc --user=admin --ask-pass --alter "add primary key(emp_no)" --charset=utf8 --no-check-unique-key-change --charset=utf8 --execute

工作流程

为了了解pt-online-schema-change工具是如何做到不阻塞DML的，还是通过general log来了解。

以添加字段的执行语句获得的general log为例说明：

-- 初始的一些检查数据库参数、负载信息这里不再细说。
…………省略…………

-- 查看需要执行变更的表状态
200 Query	SHOW TABLES FROM `employees` LIKE 'employees'
200 Query	SELECT VERSION()

-- 查看表是否存在触发器
200 Query	SHOW TRIGGERS FROM `employees` LIKE 'employees'

-- 查看表的建表语句
200 Query	/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
200 Query	USE `employees`
200 Query	SHOW CREATE TABLE `employees`.`employees`
200 Query	/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

-- 查询表的执行计划，确定表是否有外键关联
200 Query	EXPLAIN SELECT * FROM `employees`.`employees` WHERE 1=1
200 Query	SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='employees' AND referenced_table_name='employees'
200 Query	EXPLAIN SELECT * FROM `employees`.`dept_emp` WHERE 1=1
200 Query	EXPLAIN SELECT * FROM `employees`.`dept_manager` WHERE 1=1
200 Query	SHOW VARIABLES LIKE 'wsrep_on'
200 Query	/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

-- 创建'_'(下划线)开头相同表结构的新表，并先在新表上执行变更操作
200 Query	USE `employees`
200 Query	SHOW CREATE TABLE `employees`.`employees`
200 Query	/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
200 Query	CREATE TABLE `employees`.`_employees_new` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `idx_first_last` (`first_name`,`last_name`),
  KEY `idx_birth_hire` (`birth_date`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
200 Query	ALTER TABLE `employees`.`_employees_new` add comment varchar(50) not null default 'pt-osc'

-- 在原表上分别创建DELETE、UPDATE、INSERT三个触发器
200 Query	/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
200 Query	USE `employees`
200 Query	SHOW CREATE TABLE `employees`.`_employees_new`
200 Query	/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

……省略……

200 Query	CREATE TRIGGER `pt_osc_employees_employees_del` AFTER DELETE ON `employees`.`employees` FOR EACH ROW DELETE IGNORE FROM `employees`.`_employees_new` WHERE `employees`.`_employees_new`.`emp_no` <=> OLD.`emp_no`
200 Query	CREATE TRIGGER `pt_osc_employees_employees_upd` AFTER UPDATE ON `employees`.`employees` FOR EACH ROW BEGIN DELETE IGNORE FROM `employees`.`_employees_new` WHERE !(OLD.`emp_no` <=> NEW.`emp_no`) AND `employees`.`_employees_new`.`emp_no` <=> OLD.`emp_no`;REPLACE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (NEW.`emp_no`, NEW.`birth_date`, NEW.`first_name`, NEW.`last_name`, NEW.`gender`, NEW.`hire_date`);END
200 Query	CREATE TRIGGER `pt_osc_employees_employees_ins` AFTER INSERT ON `employees`.`employees` FOR EACH ROW REPLACE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (NEW.`emp_no`, NEW.`birth_date`, NEW.`first_name`, NEW.`last_name`, NEW.`gender`, NEW.`hire_date`)

-- 根据执行计划判断chunk包含的行数，以chunk数为单位拷贝数据，为在拷贝过程中为这些行加共享读锁
200 Query	EXPLAIN SELECT * FROM `employees`.`employees` WHERE 1=1
200 Query	SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) ORDER BY `emp_no` LIMIT 1 /*first lower boundary*/
200 Query	SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX (`PRIMARY`) WHERE `emp_no` IS NOT NULL ORDER BY `emp_no` LIMIT 1 /*key_len*/
200 Query	EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `employees`.`employees` FORCE INDEX (`PRIMARY`) WHERE `emp_no` >= '10001' /*key_len*/
200 Query	EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) ORDER BY `emp_no` LIMIT 999, 2 /*next chunk boundary*/
200 Query	SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) ORDER BY `emp_no` LIMIT 999, 2 /*next chunk boundary*/
200 Query	EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query	INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/

-- 每次拷贝完chunk中数据后，查看是否有警告，查看服务器的负载情况，这是在每个chunk拷贝完成后进行的
200 Query	SHOW WARNINGS
200 Query	SHOW GLOBAL STATUS LIKE 'Threads_running'
200 Query	EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) ORDER BY `emp_no` LIMIT 12909, 2 /*next chunk boundary*/
200 Query	SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) ORDER BY `emp_no` LIMIT 12909, 2 /*next chunk boundary*/
200 Query	EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) AND ((`emp_no` <= '23910')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query	INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) AND ((`emp_no` <= '23910')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/
200 Query	SHOW WARNINGS
200 Query	SHOW GLOBAL STATUS LIKE 'Threads_running'
200 Query	EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) ORDER BY `emp_no` LIMIT 19857, 2 /*next chunk boundary*/
200 Query	SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) ORDER BY `emp_no` LIMIT 19857, 2 /*next chunk boundary*/
200 Query	EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) AND ((`emp_no` <= '43768')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query	INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) AND ((`emp_no` <= '43768')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/
200 Query	SHOW WARNINGS
200 Query	SHOW GLOBAL STATUS LIKE 'Threads_running'

……省略……

200 Query	EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) ORDER BY `emp_no` LIMIT 26664, 2 /*next chunk boundary*/
200 Query	SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) ORDER BY `emp_no` LIMIT 26664, 2 /*next chunk boundary*/
200 Query	SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) ORDER BY `emp_no` DESC LIMIT 1 /*last upper boundary*/
200 Query	EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) AND ((`emp_no` <= '499999')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query	INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) AND ((`emp_no` <= '499999')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/
200 Query	SHOW WARNINGS
200 Query	SHOW GLOBAL STATUS LIKE 'Threads_running'

-- 当拷贝数据完成之后，及时分析表进行统计信息的收集
200 Query	EXPLAIN SELECT * FROM `employees`.`dept_emp` WHERE 1=1
200 Query	SHOW VARIABLES LIKE 'version%'
200 Query	SHOW ENGINES
200 Query	SHOW VARIABLES LIKE 'innodb_version'
200 Query	ANALYZE TABLE `employees`.`_employees_new` /* pt-online-schema-change */

-- 完成旧表与新表的交换，主要受选项--alter-foreign-keys-method取值不同来进行
'
当--alter-foreign-keys-method=drop_swap时，先禁用外键约束检查，删除旧表，将临时表重命名为原旧表名，完成变更
'
200 Query	SET foreign_key_checks=0
200 Query	DROP TABLE IF EXISTS `employees`.`employees`
200 Query	RENAME TABLE `employees`.`_employees_new` TO `employees`.`employees`

'
当--alter-foreign-keys-method=rebuild_constraints时，做一个原子性的交换重命名表的操作，删除旧表的操作在删除触发器时一并操作
'
203 Query     ANALYZE TABLE `employees`.`_employees_new` /* pt-online-schema-change */
203 Query     RENAME TABLE `employees`.`employees` TO `employees`.`_employees_old`, `employees`.`_employees_new` TO `employees`.`employees`

-- 删除3个触发器
'
当--alter-foreign-keys-method=drop_swap时，直接删除。
'
200 Query	DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_del`
200 Query	DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_upd`
200 Query	DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_ins`
200 Query	SHOW TABLES FROM `employees` LIKE '\_employees\_new'
201 Quit	
200 Quit

'
当--alter-foreign-keys-method=rebuild_constraints时，对于关联的外键表执行重建外键操作，删除旧表，完成变更。
'
203 Query     USE `employees`
203 Query     SHOW CREATE TABLE `employees`.`dept_emp`
203 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
203 Query     ALTER TABLE `employees`.`dept_emp` DROP FOREIGN KEY `_dept_emp_ibfk_1`, ADD CONSTRAINT `__dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees`.`employees` (`emp_no`) ON DELETE CASCADE
203 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

203 Query     USE `employees`
203 Query     SHOW CREATE TABLE `employees`.`dept_manager`
203 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
203 Query     ALTER TABLE `employees`.`dept_manager` DROP FOREIGN KEY `__dept_manager_ibfk_1`, ADD CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees`.`employees` (`emp_no`) ON DELETE CASCADE

203 Query     DROP TABLE IF EXISTS `employees`.`_employees_old`
203 Query     DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_del`
203 Query     DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_upd`
203 Query     DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_ins`
203 Query     SHOW TABLES FROM `employees` LIKE '\_employees\_new'
204 Quit
203 Quit

工作流程总结：

  1. 查询当前数据库服务器信息，包括参数设置，负载信息等，判断表是否有存在触发器，是否有外键关联；
  2. 创建一张与旧表结构相同的新表，表名为_旧表名；
  3. 在新创建的表上做变更操作；
  4. 旧表上创建DELETE、UPDATE、INSERT3个触发器；
  5. 拷贝旧表数据到新表上，以chunk为单位进行，拷贝期间涉及的行会持有共享读锁；
  6. 拷贝期间如果旧表如有DML操作，则通过触发器更新同步到新表上；
  7. 当拷贝数据完成之后旧表与新表进行重命名；
  8. 如果有涉及到外键，根据工具指定选项进行外键处理；
  9. 删除旧表；
  10. 删除旧表上触发器。

总结

pt-online-schema-change工具对于任意的DDL语句都是通过创新表拷贝数据来进行，期间都支持DML，而Online DDL根据DDL类型的来区分是否需要对表进行COPY TABLE操作，有点类似于工具的创建临时表进行变更，而不需要COPY TABLE操作的DDL语句在执行期间支持DML。

关于在对表进行DDL时使用MySQL原生的Online DDL特性还是使用pt-online-schema-change工具，通过以上对工具使用的说明与用法测试可以总结如下：

  • 如果MySQL版本不支持Online DDL特性，比如早于5.6版本的MySQL，则使用pt-online-schema-change工具；
  • 如果MySQL版本支持Online DDL特性，则优先考虑使用Online DDL，因为毕竟原生的支持较好，同时不容易产生不可预知的错误；
  • 如果DDL语句在使用Online DDL时需要进行COPY TABLE操作，建议使用pt-online-schema-change工具，因为期间支持DML操作。
  • 如果表存在触发器的情况下，优先使用Online DDL，对于MySQL5.7.2之后版本则可以pt-online-schema-change工具并通过指定选项--preserve-triggers；
  • 如果涉及外键关联的表，优先考虑使用Online DDL。

关于pt-online-schema-change更多的说明可以参考官方文档：https://www.percona.com/downloads/percona-toolkit/LATEST/

参考

https://www.percona.com/downloads/percona-toolkit/LATEST/
https://dev.mysql.com/doc/index-other.html

☆〖本人水平有限，文中如有错误还请留言批评指正！〗☆