未加星标

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

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏
翻译部分: 一、简述 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等处理方法

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQL服务器InnoDB数据Perl删除RYRIMUT
分页:12
转载请注明
本文标题:pt-online-schema-change MySQL在线DDL利器
本站链接:http://www.codesec.net/view/561343.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(78)