MySQL · 2017-08-10 3

pt-online-schema-change MySQL在线DDL利器

翻译部分:

 

一、简述

1、名称

pt-online-schema-change:修改表结构而不锁表

2、概要

## 用法
pt-online-schema-change [OPTIONS] DSN

pt-online-schema-change更改表结构而不阻止读或写,在DSN中指定数据库和表,使用前先阅读文档并做好备份:

## 向sakila.actor添加列:
pt-online-schema-change –alter “ADD COLUMN c1 INT” D=sakila, t=actor

## 将sakila.actor更改为InnoDB,以非堵塞方式有效执行OPTIMIZE TABLE,因为它已经是InnoDB表:
pt-online-schema-change –alter “ENGINE=InnoDB” D=sakila, t=actor

3、风险

使用前阅读工具文档
查看工具的已知BUGS
先在测试环境中测试
先备份生产的服务器数据,并验证备份

4、说明

pt-online-schema-change 模拟MySQL内部更改表的方式,但它工作在原表的拷贝上,这表示原表不被锁定,客户端还会继续读取和更改其中的数据。

pt-online-schema-change 通过创建要修改表的空拷贝,再根据需要进行修改,然后将数据行从原表复制到新表中,当拷贝完成后,它将移开原表并将其替换为新的表,默认情况下它最后会删除原表。

数据在复制时是以小数据块进行,这些数据会尝试在特定时间内进行修改(see –chunk-time),该过程与其他工具很相似(如pt-table-checksum)。在复制期间,对原表数据的任何修改都将映射在新表中,因为pt-osc会在原始表上创建触发器来更新新表中的相应行。由于触发器的使用,那么如果原表上已经定义了任何触发器,该工具将无法正常工作。

当工具将原表数据复制完到新表中时,它使用原子操作RENAME TABLE来同时重命名原表和新表,完成后再删除原表。

如果原表上有外键会使操作变得复杂,并会出现额外的风险。当有外键引用原表时,重命名原表和新表的原子操作将不起作用,模式改完后pt-osc必须修改外键用来引用新表。它有两种方法实现方式,详情参阅–alter-foreign-keys-method;

外键也会造成一些副作用,如名称可能会有点不同,因为要避免MySQL和InnoDB中名称的冲突,但是最终表将和原表有相同的外键和索引(除非你在ALTER语句中又做了更改)。

为了安全必须指定–execute选项,否则pt-osc不会修改原表,而默认情况下未启用该选项。
同时pt-osc还执行着其他安全检查,以防不必要的加载等其他问题,包括自动检测从机,连接到从机,并进行以下检查:

在多数情况下,除非原表上有PRIMARY KEY 或 UNIQUE INDEX,否则工具将不会执行,详情参阅 –alter

如果检测到复制过滤选项,那么工具将不会执行,详情参阅 –[no]check-replication-filters

如果检测到从机复制延迟,那么工具将暂停copy data,详情参阅 –max-lag

如果检测到系统负载过高,那么工具将暂停或终止操作,详情参阅 –max-load 和 –critical-load

该工具会设置 innodb_lock_wait_timeout=1 和 lock_wait_timeout=60 (MySQL5.5及以上版本);
以避免成为任何锁竞争的受害者,并且一般还不会中断其他事务,若要更改这些值,可以通过选项 –set-vars实现;

除非指定选项 –alter-foreign-keys-method,否则若有外键约束,pt-osc不会更改表;

pt-osc无法更改”Percona XtraDB Cluster”节点上的MyISAM表

5、Percona XtraDB Cluster

pt-online-schema-change与Percona XtraDB Cluster(PXC)5.5.28-23.7及以上版本配合使用,但有两个限制,具体参考文档。

6、输出

工具会将其活动的信息打印到STDOUT,以便你能看到它在做什么。在copy data阶段,它会将进度报告打印到STDERR,你可以通过指定 –print 获取其他信息,如果 –statistics 被指定,最后它会打印各种内部事件计数的报告,如:

# Event Count
====== =====
INSERT 1

 

 

二、选项

–dry-run 和 –execute 这两个选项是相互排斥的;工具还接受其他命令行参数,详情请参阅”概要”和使用信息;

## 一般选项
–host(-h; string)
连接到主机

–port(-P; int)
连接时使用的端口号

–user(-u; string)
连接时要登录的用户

–password(-p; string)
连接时使用的密码(如果密码中有逗号,必须要用反斜杠转义)

–database (-D; string)
连接的数据库

–pid(string)
创建一个给定的PID文件

–socket(-S; string)
连接时的Socket文件

–ask-pass
连接MySQL时提示输入密码

–charset (-A; string)
默认字符集

–config (array)
读取以逗号分隔的配置文件列表,如果指定,则必须要在命令行的第一个选项位置

–defaults-file(-F; string)
从给定的文件中读取MySQL选项,必须使用绝对路径

–dry-run
干跑;创建新表,删除新表,但不创建触发器,拷贝数据,以及替换旧表

–execute
真实运行,必须指定该选项才能更改表,若不指定该选项工具会执行些安全检查就退出

–statistics
打印内部计数器的统计信息,其与INSERT数相比,可知有多少警告

–print
将SQL语句打印到STDOUT,该选项使你能查看到工具执行的大多数语句,建议先用–print和–dry-run进行测试

–version
显示版本并退出

–help
显示帮助并退出

 

## 其他选项

–plugin(string)
Perl模块文件定义了一个pt_online_change_plugin类,你可以编写Perl模块挂接到PT-OSC

 

–force
在使用选项–alter-foreign-key-method=none的情况下,此选项将绕过确认,这可能会破坏外键约束

 

–[no]version-check(default: yes)
检查PT, MySQL和其他程序的最新版

 

–sleep(float; default: 0)
每拷贝一个数据块后多久睡眠(秒),和–max-lag和–max-load配合使用很好(一般设置小于秒级: 0.1, 0.05等)

 

–alter(type: string )
修改模式,不用 ALTER TABLE 关键字。通过逗号指定能对表执行多个修改,ALTER TABLE语法请参考MySQL手册;对于以下情况,可能会导致pt-osc以不可预测的方式失败:
【1】 在几乎所有情况中,表中都要有 PRIMARY KEY 或 UNIQUE INDEX,因为pt-osc会创建一个DELETE触发器,以便在执行过程中更新新表;
只有一个例外是,在ALTER子句中:当从现有列创建一个PRIMARY KEY 或 UNIQUE INDEX,它将使用这些列作为DELETE触发器;

【2】 RENAME子句不能用于重命名表

【3】 当用新名称删除和重新添加时,无法重命名列,这时pt-osc不会将原始列的数据拷贝到新列;

【4】 如果你添加的列即没有默认值又指定它为NOT NULL,这时会失败,因为pt-osc不会为你设置默认值,你必须自己指定默认值;

【5】 DROP FOREIGN KEY constraint_name 需要指定 _constraint_name,而不是实际的 constraint_name;
由于MySQL的限制,在创建新表时,pt-osc会为外键约束名称增加一个下划线前缀,例如要删除此约束:
CONSTRAINT ‘fk_foo’ FOREIGN KEY (‘foo_id’) REFERENCES ‘bar’ (‘foo_id’)
你必须指定 –alter “DROP FOREIGN KEY _fk_foo”;

【6】 该工具不会在MySQL 5.0中使用 LOCK IN SHARE MODE,因为它可能导致复制的slave中断,而产生错误:
Query caused different errors on master and slave.
Error on master: ‘Deadlock found when trying to get lock; try restarting transaction'(1213),
Error on slave: ‘no error'(0), Default database: ‘pt_osc’.
Query: ‘INSERT INTO pt_osc.t (id, c) VALUES (‘730’, ‘new row’)’
将MyISAM表转换为InnoDB时,会发生错误,因为MyISAM是非事务性的,但InnoDB是事务性的;
如果使用MySQL 5.0并从MyISAM转换为InnoDB时,请务必验证新表!

 

–alter-foreign-keys-method(type: string )
如何修改外键使之引用新表,引用原表的外键应小心处理,以确保它们能继续引用新表,当工具重命名原表时,引用原表的外键会随之改为引用新表。
pt-osc支持两种方法来自动查找原表的”子表”:
auto
自动确定哪种方法最好,它有可能使用rebuild_constraints或drop_swap(详情参阅rebuild_constraints)

rebuild_constrains
此方法通过ALTER TABLE先删除原表外键,再添加新表外键(首选方案,除非”子表”太大,需要花费ALTER太多时间)。pt-ost会对比”子表”的行数与copy data row速率,如果预估更改”子表”的时间少于–chunk-time,那就会使用该方案。为了预估更改子表的时间,它会将行复制率乘以–chunk-size-limit,因为的ALTER TABLE一般比复制行快得多。由于MySQL的限制,外键在ALTER的前后不会有相同的名称,pt-osc必须重命名外键,它会在名称前添加一个下划线,某些情况下,MySQL还自动重命名外键所需的索引。

drop_swap
禁用外键检查(FOREIGN_KEY_CHECKS=0),然后重命名新表之前删除原表,这与常规的原子操作RENAME old_table and new_table方法不同。这种方法更快也不会阻塞,但它风险更大:
首先,在删除原表并重命名临时表的短时间内,实际表并不存在,对其查询将出错;
其次,若出现错误,临时表不能重命名为旧表时,那么原表将永远的消失了;
此方法强制–no-swap-tables和–no-drop-old-table。

none
这种方法类似drop_swap,引用原表的任何外键现在都引用一个不存在的表,这会导致在SHOW ENGINE INNODB STATUS中出现外键冲突,类似:
Trying to add to index ‘idx_fk_staff_id’ tuple:
DATA TUPLE: 2 fields;
0: len 1; hex 05; asc ;;
1: len 4; hex 80000001; asc ;;
But the parent table ‘sakila’.’staff_old’
or its.ibd file does not currently exist!
这是因为原表(sakila.staff)被重命名为sakila.staff_old删除;提供该处理外键的方法,以便dba能根据需要禁用该工具的内置功能。

 

–[no]analyze-before-swap(default: yes)
在和旧表交换之前,会在新表上执行ANALYZE TABLE。只有在运行MySQL5.6及以上版本时,才会启动innodb_stats_persistent,但无论MySQL版本和Innodb_stats_persitent如何,都可以明确指定该参数启动或禁用。
这绕过了与InnoDB优化器统计信息有关的问题,如果被通知的表很忙,那么工具会尽快完成,则新表在交换后将不具有优化器统计信息,这可能会导致查询的全表扫描,直到优化器统计信息更新(通常在10后),如果表太大和服务器很忙,也可能会导致中断。

 

–[no]check-alter(default:yes)
解析指定的 –alter,并警告有可能出现的异常行为,目前它检查:
Column renames
在上版本中,使用CHANGE COLUMN name new_name重命名列会导致该列的数据丢失。现在pt-osc会解析alter语句并尝试捕获这些情况,所以重命名的列会与原始列数据相。但执行该操作的pt代码并不是完整的SQL解析器,所以你应先用–dry-run和–print运行,并验证它是否正确检测到重命名的列。

DROP PRIMARY KEY
如果–alter中有DROP PRIMARY KEY,则会先打印警告然后再退出,除非你指定了–dry-run。更改主键是危险的,但pt-osc也能处理,因为pt-osc的触发器(特别的DELETE trigger),喜欢用主键作为触发器;
所以应先运行–dry-run和–print,并验证触发器是否正确。

 

–check-interval(type: time; default: 1 )
间隔–max-lag的时间检查一次

 

–[no]check-plan(default: yes )
检测查询计划的安全性,默认情况下,使用该选项会使工具先运行EXPLAIN sql,然后再运行sql,这意味着只用访问少量数据行即可

 

–[no]check-replication-filters(default: yes )
若在任何服务器上设置任何复制过滤器,则会导致工具终止,该工具会查找有过滤复制选项的服务器(如,binlog_ignore_db和replicate_do_db)。如果它发现任何这样的过滤器,它会终止并报错。

如果从机配置了任何过滤选项,则你应注意不要修改master上而不是从机上的任何数据库或表,因为这可能会导致复制失败,有关复制规则的更多信息,参考http://dev.mysql.com/doc/en/replication-rules.html.

 

–check-slave-lag(type: string )
暂停数据的复制,直到从机的延迟小于–max-lag,该值是从连接选项(–port, –user等)继承的DSN属性;
此选项将覆盖在所有从机上查找并持续监视复制延迟的正常行为,如果你想监控多个从机时,还可以使用–recursion-method的DSN选项而不是此选项;

 

–chunk-index(type: string )
为表的数据块指定索引,默认,该工具为数据块选择最合适的索引,但该选项允许你指定所需的索引,如果索引不存在,则工具将使用默认选择的索引,该工具使用FORCE INDEX子句在SQL语句中添加索引,使用此选项时要注意,如果索引选择不佳可能导致性能下降。

 

–chunk-index-columns(type: int)
只能使用–chunk-index中许多最左边的列,这仅适用于复合索引;
以及在MySQL查询优化器(执行计划)中有bug导致大范围扫描,而不是使用索引来精确定位起点和终点的情况下,此功能很有用。该问题常会发生在有多列的索引上(如4个或更多列),若发生该情况,工具会打印与–[no]check-plan相关的警告,指定仅用索引的前N列是某些情况下的错误解决方法。

 

–chunk-size(type: size; default: 1000 )
为复制的数据块选择行数,允许后缀是k, M, G,该选项能覆盖默认行为,即动态地调整块的大小,以使块正好在–chunk-time秒内运行完,当此选项未明确设置时,其默认值将用作起点,而后会忽略此选项的值。
但是如果显示设置此选项,则会禁用动态调整行为,并尝试将所有块都准确地指定为设置的行数。

应注意,如果块索引不是唯一的,那么块大小可能会更大,例如,表由包含给定值的10000的索引分块,则无法编写仅匹配10000个值的WHERE子句,并且该块将至少为10000行大,由于–chunk-size-limit,这样的大块可能会被跳过。

 

–chunk-size-limit(type: float; default: 4.0 )
设置copy的块最大尺寸

 

–chunk-time(type: float; default: 0.5 )
动态调整块大小,以便每个拷贝的数据块所查询的时间都需要–chunk-time秒才能执行

 

–critical-load(type: array; default: Threads_running=50 )
在copy每个块后检查SHOW GLOBAL STATUS,如果负载过高则终止,该选项接收以逗号分隔的MySQL状态变量和阈值列表,以 选项=MAX_VALUE(或:MAX_VALUE) 的形式跟踪检测每个变量,如果不给出,则工具检查启动的当前值来确定阈值并加倍。

 

–default-engine
从新表中删除ENGINE,默认情况下新表和原始表使用一样的引擎;
当主从复制的从机对同一个表使用不同引擎时,有可能会出问题,通过该选项将导致使用系统默认引擎创建新表。

 

–[no]drop-new-table(default: yes)
如果拷贝旧表失败则删除新表,–no-drop-new-table不能和–alter-foreign-keys-method drop_swap一起使用

 

–[no]drop-old-table(default: yes)
如果新旧表交换之后,未出现任何错误则默认会删除旧表,但是若指定了–no-swap-tables,则不会删除旧表

 

–[no]drop-triggers(default: yes)
删除旧表上的触发器

 

–max-flow-ctl(float)
对于PXC集群来说,有点类似–max-lag,检查集群暂停流量控制所花费的平均时间(适用于PXC 5.6及以上版本)

 

–max-lag(type: time; default: 1s)
暂停数据拷贝,直到所有从机的延迟小于该值;每拷贝一个数据块后都通过Seconds_Behind_Master查看所有从机的延迟状况,若延迟大于该选项的值,则会等待–check-interval秒再次检查所有从机,若指定了–check-slave-lag,则会只检查该服务器的延迟,要准确控制工具所监视的服务器,用DSN来为–recursion-method赋值。
(如果有从机停止了主从复制,该工具会永远等待直到从机起来,在等待时会打印报告,按间隔时间打印)

 

–max-load(type: array; default: Threads_running=25)
在拷贝每个数据块后检查SHOW GLOBAL STATUS,如果任何状态变量高于阈值会暂停,选项接收逗号分隔的MySQL状态变量列表,如果不设置,该工具会在检测到的当前值上再增加20%来确定阈值(方式,”Threads_connected:100” 或 “Threads_connected=110″),该选项主要目的是防止给服务器添加太多负载、导致锁等待以及其他侵扰行为。

 

–new-table-name(type: string; default: %T_new)
在和旧表交换之前新表的名字(%T表示旧表的表名)

 

–null-to-not-null
NULL值的列将转换为定义的默认值(0–int, ”–string)

 

–progress(type: array; default: time,30)
在copying rows时打印进度报告到STDERR,该选项的值由逗号分隔的两部分组成;
第一部分可以是百分比,时间,或迭代;第二部分是更新打印的频率,用百分比,秒,或迭代次数表示。

 

–quiet(-q)
不打印消息到STDOUT(即禁用–progress),只有错误和警告会打印到STDERR

 

–recurse(type: int)
主从复制结构的递归层数,默认是无限的,详情参阅 –recursion-method

 

–recursion-method(type: array; default: processlist, hosts)
查找从机的首选方法。可能用到的方法:
方法 使用
=================================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
dsn=DSN DSNs from a table
none Do not find slaves
processlist方法是默认的,SHOW SLAVE HOSTS不可靠(且需要从机配置report_host, report_port等)。dsn方法指的是:通过读取指定D和t的DSN字符串的表,来进行查找,具体参考文档示例。

 

–slave-user(type: string)
设置连接到从机的用户,该参数允许你通过低权限的用户连接到从机(该用户必须在所有从机上都存在)

 

–slave-password(type: string)
设置连接到从机的用户密码,和参数–slave-user一起使用(密码必须在所有从机上都相同)

 

–set-vars(type: Array)
以variable=value的形式设置MySQL变量,多个变量则以逗号分隔;
默认情况下,pt-osc设置以下变量(若在命令行上又指定了,则会覆盖默认值):
wait_timeout=10000, innodb_lock_wait_timeout=1, lock_wait_timeout=60;

注意,sql_mode变量的设置需要转义逗号(反斜杠)和引号(双反斜杠),如:
–set-vars sql_mode=\’STRICT_ALL_TABLE\\,ALLOW_INVALID_DATES\’

 

–[no]swap-tables(default: yes)
通过交换新旧表,该参数通过交换原表和新表来完成在线模式的更改,除非禁用了–[no]-drop-old-table,否则默认会删除原表(旧表)

 

–tries(type: array)
多次重试关键操作,如果出现某些非致命错误而失败的操作,通过该参数可以等待一段时间并重试多次(时间单位秒)
格式:operation:tries:wait[, operation:tries:wait]
示例:–tries create_triggers:5:0.5, drop_triggers:5:0.5(创建和删除触发器都尝试5次,每次等待0.5秒)

若有以下错误发生,pt-osc会重试每个操作(若丢失或杀死连接,也会自动重连):
Lock wait timeout(innodb_lock_wait_timeout and lock_wait_timeout)
Deadlock found
Query is killed(KILL QUERY <thread_id>)
Connection is killed(KILL CONNECTION <thread_id>)
Lost connection to MySQL

 

 

三、PLUGIN

–plugin指定的文件必须用new()子程序定义一个名为pt_online_schema_change_plugin的类。pt-osc会创建此类的一个实例,并调用其定义的钩子程序,详情参阅文档。

 

 

四、DSN OPTIONS

这些DSN选项用于创建DSN,给出的每个选项,如option=value,这些选项区分大小写,所以P和p不一样
在 “=” 之前或之后不能有空格,如果值包含空格,则必须引用它;DSN选项以逗号分割
详情参阅,percona-toolkit联机帮助页细节

A
dsn: charset; copy: yes
默认字符集

D
dsn: database; cope: yes
默认数据库

F
dsn: mysql_read_default_file; copy: yes
只能从给定文件读取默认选项

h
dsn: host; copy: yes
连接到主机

p
dsn: password; copy: yes
连接时使用的密码,如果密码包含逗号,则必须使用反斜线进行转义;如:”exam,ple”

P
dsn: port; copy: yes
用于连接的端口号

S
dsn: mysql_socket; copy: yes
用于连接的套接字文件

t
存储死锁信息的表

u
dns: user; copy: yes
登录用户,如果不是当前用户

 

 

五、其他

1、环境

环境变量PTDEBUG激活STDERR的详细调试输出;要启动调试并捕获文件的所有输出,请运行以下工具:
PTDEBUG=1 pt_online_schema_change … >FILE 2>&1
注意:调试输出量很大,可能产生几兆字节的输出数据;
2、系统要求

你需要Perl, DBI, DBD::MySQL, 以及新版本的Perl中的一些核心软件包;
pt-osc仅适用于MySQL 5.0.2及以上版本,因为早期版本不支持触发器;
3、BUGS

有关已知错误的列表,请参见: http://www.percona.com/bugs/pt-online-schema-change.
请在 https://bugs.launchpad.net/percona-toolkit.上报告错误,错误报告应包含如下信息:

运行该工具所使用的命令行
工具版本–version
涉及的所有服务器的MySQL版本
包含STDERR在内的输出信息
输入文件(log/dump/config file等)
如果可能,通过使用PTDEBUG运行该工具(包括调试输出)
4、版本
pt-online-schema-change 2.2.19

 

使用部分

1、 示例

建议先用–dry-run和–print测试,再用–execute替换
## 删字段
pt-online-schema-change -h xxx.xxx.xxx.xx –u user_name –p xxxxxx –alter=’DROP COLUMN field_name’ D=db_name,t=tbl_name –print –execute

## 改字段类型
pt-online-schema-change h=xxx.xxx.xxx.xxx,u=user_name,p=xxxxxx,D=db_name,t=tbl_name –alter “MODIFY COLUMN content text” –print –execute

2、总结

1.pt-osc执行顺序:

1. 创建一个和原表有相同表结构的新表(_tblname_new)
2. 先对新表执行表结构修改
3. 在原表上创建三个触发器(若原表中已有触发器则失败,因为一个表只能有一种类型的触发器)
4. 拷贝原表的数据到新表(每次拷贝一个data chunk)
5. 新旧表交换rename tblname TO _tblname_old, _tblname_new TO tblname
6. 删除原表,删除触发器
2. pt-osc注意事项:

1. 外键
如果有外键引用要修改的表,应详细阅读–alter-foreign-keys-method提供的两种处理方法

2. 磁盘空间
注意磁盘大小,在pt-osc执行期间会复制一份要修改的表,以及写入大量binlog,需保留多余的磁盘空间

3. 锁
如果线上并发高,特别写多的情况下,pt-osc可能会出现死锁,可以参考选项–chunk-size等处理方法