[翻译]pt-online-schema-change文档
原文链接:www.percona.com/doc/percona-toolki...
大部分采用机翻,少部分按自已理解润色,去掉了几节与使用无关的内容。可当手册参考。
名称
pt-online-schema-change - 无锁修改表结构。
概要
使用方法
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
风险
Percona Toolkit是成熟的,在现实世界中得到验证且经过良好测试的,但所有的数据库工具都可能对系统和数据库服务器构成风险。在使用这个工具之前,请务必:
- 阅读工具的文档
- 审查该工具的已知 “BUGS”
- 在非生产服务器上测试该工具
- 备份你的生产服务器并验证备份
描述
pt-online-schema-change模拟了MySQL内部修改表的方式,但它在你想修改的表的副本上工作。这意味着原始表没有被锁定,客户端可以继续读取和改变其中的数据。
pt-online-schema-change的工作原理是创建一个要改变的表的空拷贝,根据需要对其进行修改,然后将原表的行复制到新表中。当复制完成后,它将原表移走,用新表替换。默认情况下,它也会删除原来的表。
数据拷贝过程是以小块数据进行的,这些小块数据是变化的,以试图使它们在特定的时间内执行(见 --chunk-time
)。这个过程非常类似于其他工具,如pt-table-checksum的工作方式。在复制过程中,对原表数据的任何修改都将反映在新表中,因为该工具在原表上创建了触发器来更新新表中的相应行。触发器的使用意味着,如果在表上已经定义了任何触发器,该工具将无法工作。
当工具完成向新表复制数据时,它使用一个原子性的RENAME TABLE操作来同时重命名原始表和新表。在这一过程完成后,该工具放弃了原来的表。
外键使工具的操作复杂化,并引入了额外的风险。当外键指向表的时候,原子化地重命名原始表和新表的技术不起作用。该工具必须在模式改变完成后更新外键以引用新表。该工具支持两种方法来完成这个任务。你可以在 --alter-foreign-keys-method
的文档中阅读更多关于这个问题的内容。
外键也会引起一些副作用。最终的表将具有与原始表相同的外键和索引(除非你在ALTER语句中指定不同的内容),但对象的名称可能会略有改变,以避免MySQL和InnoDB的对象名称冲突。
为了安全起见,该工具不会修改表,除非你指定--execute
执行选项,该选项在默认情况下是不启用的。该工具支持各种其他措施,以防止不必要的负载或其他问题,包括自动检测副本,连接到它们,并使用以下安全检查。
- 在大多数情况下,除非表中存在PRIMARY KEY或UNIQUE INDEX,否则该工具将拒绝操作。详见
--alter
。 - 如果检测到复制过滤器,该工具将拒绝操作。参见
-[no]check-replication-filters
检查复制过滤器以了解详情。 - 如果该工具观察到任何在复制中的延迟,则暂停数据复制操作。详见
--max-lag
。 - 如果该工具检测到服务器上有太多的负载,就会暂停或中止其操作。详情见
--max-load
和--critical-load
。 - 该工具设置innodb_lock_wait_timeout=1和(对于MySQL 5.5和更新的版本)lock_wait_timeout=60,以便它更有可能成为任何锁竞争的受害者,并且不太可能干扰其他事务。这些值可以通过指定
--set-vars
来改变。 - 如果外键约束引用了该表,该工具将拒绝改变该表,除非你指定
--alter-foreign-keys-method
。 - 该工具不能改变 “Percona XtraDB Cluster “节点上的MyISAM表。
Percona XtraDB Cluster
pt-online-schema-change可以在Percona XtraDB Cluster (PXC)5.5.28-23.7和更新版本中使用,但是有两个限制:只有InnoDB表可以被改变,并且wsrep_OSU_method必须被设置为TOI(总顺序隔离)。如果主机是一个集群节点,并且表是MyISAM或者正在转换为MyISAM(ENGINE=MyISAM),或者wsrep_OSU_method不是TOI,该工具就会以错误退出。没有办法停用这些检查。
该工具忽略了MySQL 5.7+的 衍生列
,因为这些列的值是根据用于计算列值的表达式生成的。
输出
该工具将其活动信息打印到STDOUT,这样你就可以看到它在做什么。在数据拷贝阶段,它将 --progress
报告打印到STDERR。你可以通过指定 --print
来获得额外的信息。
如果指定了 --statistics
,在最后会打印各种内部事件统计的报告,比如:
# Event Count
# ====== =====
# INSERT 1
选项
--dry-run
和 --execute
是互斥的。
这个工具接受额外的命令行参数。详情请参考 “SYNOPSIS “和使用信息。
--alter
类型:字符串
模式的修改,没有ALTER TABLE的关键字。你可以通过用逗号指定对表进行多次修改。关于ALTER TABLE的语法,请参考MySQL手册。
以下限制适用,如果试图这样做,将导致工具以不可预测的方式失败:
在几乎所有的情况下,表内都需要有一个PRIMARY KEY或UNIQUE INDEX。这是必要的,因为该工具创建了一个DELETE触发器来保持新表在运行过程中的更新。
一个例外的情况是,当使用ALTER子句在已有的列中创建PRIMARY KEY或UNIQUE INDEX,工具将使用这些列作为DELETE触发器。RENAME 子句不能用于重命名表。
列不能通过删除后用新的名称添加来达到重命名的目的。该工具不会将原列的数据复制到新的列中。
如果你添加了一个没有默认值的列,并使其成为NOT NULL,该工具将失败,因为它不会尝试为你猜测一个默认值;你必须指定默认值。
DROP FOREIGN KEY constraint_name
需要指定_constraint_name
,而不是真正的constraint_name
。由于MySQL的一个限制,pt-online-schema-change在创建新表时,会在外键约束名称上添加一个前导下划线。例如,要删除fk_foo
这个外键:
CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)
你必须指定 --alter "DROP FOREIGN KEY _fk_foo"
。
- 该工具在MySQL 5.0中不使用
LOCK IN SHARE MODE
,因为它可能导致一个破坏复制的从属错误。
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.1和更新的版本能正确处理这种情况,但在MySQL 5.0的测试中,有5%的时间会重现这个错误。
这是一个MySQL错误,类似于bugs.mysql.com/bug.php?id=45694,但在MySQL 5.0中没有修复或解决方法。如果没有 LOCK IN SHARE MODE
,测试100%通过,所以数据丢失或破坏复制的风险应该可以忽略不计。
如果使用MySQL 5.0并从MyISAM转换到InnoDB,请确保验证新的表!
alter-foreign-keys-method
类型:字符串
如何修改外键,使其引用新表。指向被修改表的外键必须要特殊处理,以确保它们继续引用正确的表。当工具重命名原来的表,让新的表取代它的位置时,外键必须被修改为引用新表。
该工具提供两种方法来实现这个要求。它能够自动找到指向被修改表的外键所在的表。
auto(自动)
自动确定哪种方法是最好的。如果可能的话,该工具会使用rebuild_constraints(详见该方法的描述),如果不能,则使用drop_swap。
rebuild_constraints(重建约束)
这种方法使用
ALTER TABLE
来删除和重新添加引用新表的外键约束。这是首选的技术,除非一个或多个 “子 “表(外键所在的表)非常大,以至于ALTER会花费太长时间。该工具通过比较子表的行数和该工具能够从旧表复制行到新表的速度来确定这一点。如果工具估计子表可以在比—chunk-time
更短的时间内被改变,那么它将使用这个技术。为了估计改变子表所需的时间,该工具将行复制率(row-copying)乘以--chunk-size-limit
,因为MySQL的ALTER TABLE通常比外部复制行的过程快得多。
由于MySQL的一个限制,外键在ALTER之后的名字将与之前的不一样。该工具在重新定义外键时必须重命名它,这将在名称中添加一个前导下划线。在某些情况下,MySQL还自动重命名外键所需的索引。
drop_swap(删除并交换)
禁用外键检查(FOREIGN_KEY_CHECKS=0),然后在重命名新表之前删除旧表。这与原来的交换新旧表的方法不同,原来使用原子式的
RENAME
实现客户端无感的交换。
这种方法更快,也不会阻塞,但由于下面的两个原因,它的风险更大。首先,在删除原表和重命名临时表之间的短时间内,对旧表的查询将导致错误。其次,如果出现错误,新表不能被重命名为旧表,那么中止就太晚了,因为旧表已经永久地消失了。
此方法强制使用--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后被删除了。提供这种处理外键约束的方法是为了让数据库管理员在需要时可以禁用该工具的内置功能。
--[no]analyze-before-swap
默认:yes
在与旧表互换之前,在新表上执行ANALYZE TABLE。默认情况下,这只发生在MySQL 5.6及以上的版本,并且 innodb_stats_persistent
被启用时。明确指定该选项以启用或禁用它,而不考虑MySQL版本和 innodb_stats_persistent
。
这规避了一个与InnoDB优化器统计有关的潜在的严重问题。如果被警告的表是繁忙的,而且工具很快就完成了,那么新的表在被交换后将没有优化器的统计数据。这可能会导致快速的、使用索引的查询做全表扫描,直到优化器统计数据被更新(通常在10秒之后)。如果表很大,而且服务器非常繁忙,这可能会导致服务中断。
--ask-pass
在连接到 MySQL 时提示输入密码。
--channel
类型:字符串
使用复制通道连接到服务器时使用的通道名称。假设你有两个主站,master_a在12345端口,master_b在1236端口,一个从站使用通道chan_master_a和chan_master_b连接到两个主站。如果你想运行pt-table-sync来使从站与master_a同步,pt-table-sync将不能确定什么是正确的主站,因为SHOW SLAVE STATUS将返回2行。在这种情况下,你可以使用 -channel=chan_master_a 来指定在 SHOW SLAVE STATUS 命令中使用的通道名称。
--charset
简写:-A,类型:字符串
默认字符集。如果该值为utf8,则将Perl在STDOUT上的binmode设置为utf8,将mysql_enable_utf8选项传递给DBD::mysql,并在连接到MySQL后运行SET NAMES UTF8。任何其他值都会在STDOUT上设置binmode,而不设置utf8层,并在连接到MySQL后运行SET NAMES。
--[no]check-alter
默认:yes
解析指定的 --alter
,并尝试对可能的非预期行为发出警告。目前,它检查的是:
Column renames(重命名的列)
在以前版本的工具中,用
CHANGE COLUMN name new_name
重命名一个列会导致该列的数据丢失。现在,该工具解析了alter语句,并试图捕捉这些情况,因此重命名的列应该与原列的数据相同。然而,这部分功能并不像一个完整的SQL解析器,所以你应该首先用--dry-run
和
DROP PRIMARY KEY
如果
--alter
包含DROP PRIMARY KEY
(不区分大小写),就会打印一个警告,并且工具退出,除非指定--dry-run
。改变主键可能是危险的,但是该工具可以处理它。该工具的触发器,特别是DELETE触发器,受改变主键的影响最大,因为该工具更倾向于使用主键来触发它。你应该首先用--dry-run
和
-- [no]check-foreign-keys
默认:yes
检查自引用外键的情况。目前不完全支持自引用外键,所以,为了防止错误,如果表有自引用外键,这个程序就不会运行。使用这个参数可以禁用自引用外键的检查。
--check-interval
类型:时间;默认1
检查 --max-lag
的时间间隔。
-- [no]check-plan
默认:yes
检查查询执行计划的安全性。默认情况下,该选项使工具在运行访问少量数据的查询之前执行EXPLAIN,但如果MySQL选择了一个错误的执行计划,则可能导致访问许多行。这些包括确定分块边界的查询和分块查询本身。如果看起来MySQL将使用一个糟糕的查询执行计划,该工具将跳过该表的块。
该工具使用几个启发式方法来确定一个执行计划是否是坏的。第一个是EXPLAIN是否报告MySQL打算使用合适的索引来访问这些行。如果MySQL选择了一个不同的索引,该工具认为该查询不安全。
该工具还检查了MySQL报告它将为查询使用多少索引。EXPLAIN输出在key_len列中显示这一点。该工具会记住所看到的最大的key_len,并跳过MySQL报告说它将使用较小的索引前缀的块。这种启发式方法可以理解为跳过执行计划比其他块差的块。
该工具在每张表中第一次由于执行计划不好而跳过一个块时,会打印一个警告。随后的块会被无声地跳过,尽管你可以在工具输出的SKIPPED列中看到跳过的块的数量。
这个选项为每个表和块增加了一些设置工作。尽管这些工作对MySQL来说并不具有干扰性,但它导致了更多的服务器往返,从而消耗了时间。将块做得太小会导致开销相对变大。因此,建议你不要把块做得太小,因为如果你这样做,该工具可能需要很长的时间来完成。
-- [no]check-replication-filters
默认:yes
如果在任何服务器上设置了任何复制过滤器,则中止。该工具寻找过滤复制的服务器选项,例如binlog_ignore_db和replicate_do_db。如果它发现任何这样的过滤器,它会以错误的方式中止。
如果复制区被配置了任何过滤选项,你应该注意不要修改任何存在于主站而不是复制区的数据库或表,因为这可能导致复制失败。关于复制规则的更多信息,见dev.mysql.com/doc/en/replication-ru...。
--check-slave-lag
类型:字符串
暂停数据拷贝,直到这个副本的滞后时间小于 --max-lag
。该值是一个DSN,它继承了连接选项( --port
、 --user
等)的属性。这个选项覆盖了在所有连接的副本上寻找和持续监测复制滞后的正常行为。如果你不想监控所有的复制,但你希望不止一个复制被监控,那么请使用 --recursion-method
选项的DSN选项,而不是这个选项。
--chunk-index
类型:字符串
为分块表优先选择这个索引。默认情况下,该工具会选择最合适的索引进行分块。这个选项允许你指定你喜欢的索引。如果该索引不存在,那么该工具将回到它的默认行为,选择一个索引。该工具将索引添加到SQL语句的 FORCE INDEX
子句中。在使用这个选项的时候要小心,一个糟糕的索引选择可能会导致糟糕的性能。
--chunk-index-columns
类型:整型
只使用 --chunk-index
的最左边的这几列。这只适用于复合索引,在MySQL查询优化器(规划器)的错误导致它扫描大范围的行,而不是使用索引来准确定位起点和终点的情况下,这很有用。这个问题有时会发生在有许多列的索引上,如4列或更多。如果这种情况发生,工具可能会打印一个与 --[no]check-plan
选项有关的警告。在某些情况下,指示工具只使用索引的前N列是解决这个错误的一个办法。
--chunk-size
类型:大小;默认:1000
为每一个复制的分块选择的行数。允许的后缀是k、M、G。
这个选项可以覆盖默认行为,即动态调整块的大小,试图使每块的复制时间正好是 --chunk-time
。当这个选项没有明确设置时,它的默认值被用作一个起点,但在那之后,工具会忽略这个选项的值。然而,如果你明确地设置了这个选项,那么它将禁用动态调整行为,并试图使所有的分块恰好达到指定的行数。
有一个微妙的问题:如果用于分块的索引不是唯一索引,那么有可能分块会比预期的大。例如,如果某列有10000个相同的值,而建立在这列上的索引(非唯一索引)被用来对表进行分块,那么就没有办法写一个只匹配1000个值的WHERE子句,所以这个分块至少会有10000行。由于 --chunk-size-limit
的存在,这样的块可能会被跳过。
--chunk-size-limit
类型:浮点型;默认:4.0
不要复制比( --chunk-size
这个参数)还大的数据块。
当一个表没有唯一索引时,块的大小可能是不准确的。这个选项指定了一个可以容忍的最大的不准确度。该工具使用EXPLAIN
来估计该块中有多少行。如果这个估计值超过了所需的数据块大小乘以这个限制,那么工具就会跳过这个数据块。
这个选项的最小值是1,这意味着任何块都不能大于 --chunk-size
。你可能不想指定1,因为EXPLAIN报告的行数是估计值,这可能与块中的实际行数不同。你可以通过指定一个0的值来禁用超大块的检查。
该工具还使用这个选项来决定如何处理指向要改变的表的外键。详见 --alter-foreign-keys-method
。
--chunk-time
类型:浮点型;默认:0.5
动态调整块的大小,使每个数据拷贝查询需要这么长时间来执行。该工具跟踪拷贝率(每秒行数),并在每次数据拷贝查询后调整块的大小,以便下一次查询需要这么长的时间(秒)来执行。它保持着每秒查询次数的指数衰减移动平均值,这样,如果服务器的性能由于服务器负载的变化而发生变化,该工具就会迅速适应。
如果这个选项被设置为0,块的大小不会自动调整,所以查询时间会变化,但查询块的大小不会变化。另一种方法是明确指定 --chunk-size
的值,而不是将其保留在默认值。
--config
类型:数组
以逗号分隔的配置文件列表;如果指定,必须是命令行上的第一个选项。
--critical-load
类型:数组;默认值:Threads_running=50
在每一个块之后执行 SHOW GLOBAL STATUS
检查负载,如果负载太高就中止运行。该选项接受一个以逗号分隔的MySQL状态变量和阈值列表。每个变量后面可以有一个可选的 =MAX_VALUE
(或 :MAX_VALUE
)。如果没有给出,该工具通过检查启动时的当前值并将其加倍来确定阈值。
更多细节见 --max-load
。这俩选项的工作原理类似,只是--critical-load
这个选项会中止工具的操作,而不是暂停;如果你没有指定阈值,两者默认值的计算方式也不同。--critical-load
这个选项作为一种安全检查,以防原始表上的触发器给服务器增加太多的负载而导致宕机。可能没有一个固定的Threads_running的值对每个服务器都是不能承受的,但是默认值50对大多数服务器来说可能是不可接受的高值,表明应该立即取消该操作。
--database
简写:-D;类型:字符串
连接的数据库
--default-engine
创建新表时移除 ENGINE
选项。
默认情况下,新表创建时使用的选项与原表相同,所以如果原表使用InnoDB,那么新表将使用InnoDB。在某些涉及到复制的情况下,这可能会在对同一表使用不同引擎的复制上引起意外的变化。指定这个选项会使新表用系统的默认引擎创建。
--data-dir
类型:字符串
使用DATA DIRECTORY功能在不同的分区上创建新表。只在5.6以上版本可用。如果与 remove-data-dir
同时使用,这个参数会被忽略。
--remove-data-dir
默认值:no
如果原来的表是用DATA DIRECTORY功能创建的,删除它并在MySQL默认目录下创建新的表,而不创建新的isl文件。
--defaults-file
简写:-F;类型:字符串
只从指定的文件获取mysql的选项,必须提供一个绝对路径。
--[no]drop-new-table
默认:yes
如果复制原表失败,则删除新表。
指定 --no-drop-new-table
和 --no-swap-tables
,会留下新的、经过修改的表的副本,而不修改原来的表。参见 --new-table-name
。--no-drop-new-table
与 alter-foreign-keys-method
中的 drop_swap
不兼容。
-- [no]drop-old-table
默认:yes
重命名后删除原表。在原表成功重命名后,让新表取而代之,如果没有错误,该工具会默认删除原表。如果有任何错误,该工具会将原表留在原处。
如果指定了 --no-swap-tables
,旧表不会被删除。
-- [no]drop-triggers
默认:yes
删除旧表的触发器。 --no-drop-triggers
强制 --no-drop-old-table
。
--dry-run
根据选项创建新的表,但不要创建触发器,复制数据,或替换原来的表。
--execute
表示你已经阅读了文档并想改变该表。你必须指定这个选项来改变表。如果你不这样做,那么这个工具将只执行一些安全检查并退出。这有助于确保你已经阅读了文档并了解如何使用这个工具。如果你没有读过文档,那么就不要指定这个选项。
-- [no]check-unique-key-change
默认:yes
如果为 --alter
指定的语句试图添加一个唯一索引,则避免pt-online-schema-change的运行。因为pt-online-schema-change使用 INSERT IGNORE
来复制行到新的表中,如果被写入的行产生了一个重复的键,它将无声地失败,数据将被丢失。
示例:
CREATE DATABASE test;
USE test;
CREATE TABLE `a` (
`id` int(11) NOT NULL,
`unique_id` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into a values (1, "a");
insert into a values (2, "b");
insert into a values (3, "");
insert into a values (4, "");
insert into a values (5, NULL);
insert into a values (6, NULL);
使用pt-online-schema-change在 unique_id
字段上添加唯一索引,由于使用 INSERT IGNORE
从源表中复制记录,会导致一些记录丢失。由于这个原因,如果pt-online-schema-change检测到 --alter
参数试图添加一个唯一键,那么它将会失败,并且会提供一个查询的例子,供你检测是否有在唯一索引上有重复数据的行。
即使你运行这个查询后没有找到有重复数据的行,也要考虑到在复制全表数据时,重复的数据会由于唯一索一的存在而被丢弃。
--force
在使用 alter-foreign-keys-method = none 的情况下,这个选项可以绕过确认,因为它可能会破坏外键约束。
--help
显示帮助信息后退出。
--host
简写:-h;类型:字符串
连接的主机名称。
--max-flow-ctl
类型:浮点型
有点类似于 --max-lag
,但适用于PXC集群。检查集群为流量控制暂停的平均时间,如果超过选项中指示的百分比,则使工具暂停。如果数值为0,当检测到任何流控制活动时,工具就会暂停。默认值是不进行流量控制检查。该选项适用于PXC 5.6或更高版本。
--max-lag
类型:时间;默认:1s
暂停数据拷贝,直到所有副本的滞后时间小于这个值。在每次数据拷贝查询后(每块),该工具会使用Seconds_Behind_Master查看它所连接的所有副本的复制滞后情况。如果任何复制的滞后程度超过了这个选项的值,那么该工具将睡眠 --check-interval
秒,然后再次检查所有复制。如果你指定 --check-slave-lag
,那么该工具只检查该服务器的滞后情况,而不是所有服务器。如果你想准确地控制该工具监视哪些服务器,请使用 --recursion-method
中的DSN值。
在备份集滞后的过程中该工具会一直等待。如果任何复制被停止,该工具将永远等待,直到该复制被启动。当所有的复制都在运行并且没有太多滞后的时候,数据复制就会继续。
该工具在等待时打印进度报告。如果复制被停止,它立即打印一份进度报告,然后在每个进度报告的间隔时间后再次打印。
--max-load
类型:数组;默认:Threads_running=25
在每个块之后检查SHOW GLOBAL STATUS,如果任何状态变量高于其阈值,则暂停。该选项接受一个以逗号分隔的MySQL状态变量列表。每个变量后面可以有一个可选的 =MAX_VALUE
(或 :MAX_VALUE
)。如果没有给出,该工具通过检查当前值并将其增加20%来确定阈值。
例如,如果你想让工具在Threads_connected过高时暂停,你可以指定 “Threads_connected”,工具将在开始工作时检查当前值,并在该值上增加20%。如果当前值是100,那么当Threads_connected超过120时,工具将暂停工作,并在它再次低于120时恢复工作。如果你想指定一个明确的阈值,如110,你可以使用 “Threads_connected:110 “或 “Threads_connected=110”。
这个选项的目的是为了防止该工具给服务器增加太多的负载。如果数据拷贝查询是侵入性的,或者如果它们导致锁等待,那么服务器上的其他查询将倾向于阻塞和排队。这通常会导致Threads_running增加,工具可以在每个查询结束后立即运行SHOW GLOBAL STATUS来检测。如果你为这个变量指定一个阈值,那么你可以指示工具等待,直到查询再次正常运行。然而,这不会阻止排队,它只会给服务器一个机会从排队中恢复。如果你注意到排队现象,最好是减少分块时间。
--preserve-triggers
保留旧表的触发器。从MySQL 5.7.2开始,可以为一个给定的表定义多个触发器,这些触发器具有相同的触发事件和时间。这允许我们添加pt-online-schema-change所需的触发器,即使该表已经有自己的触发器。如果启用这个选项,pt-online-schema-change将尝试把所有现有的触发器复制到新的表中,然后再开始从原来的表中复制行,以确保改变表后可以应用旧的触发器。
示例:
CREATE TABLE test.t1 (
id INT NOT NULL AUTO_INCREMENT,
f1 INT,
f2 VARCHAR(32),
PRIMARY KEY (id)
);
CREATE TABLE test.log (
ts TIMESTAMP,
msg VARCHAR(255)
);
CREATE TRIGGER test.after_update
AFTER
UPDATE ON test.t1
FOR EACH ROW
INSERT INTO test.log VALUES (NOW(), CONCAT("updated row row with id ", OLD.id, " old f1:", OLD.f1, " new f1: ", NEW.f1 ));
当要删除的列被触发器引用时,在修改表结构时(如”DROP COLUMN f1“)不能使用 --preserve-triggers
选项,因为这将使触发器失效。
在测试完触发器将在新表中工作后,触发器将从新表中删除,直到所有的行都被复制,然后重新添加它们。--preserve-triggers
不能和 --no-drop-triggers
, --no-drop-old-table
和 --no-swap-table
一起使用,因为 --preserve-triggers
意味着旧的触发器应该被删除并在新表中重新创建。因为不能有多个同名的触发器,所以必须删除旧的触发器,以便能够在新表中重新创建它们。
使用 --preserve-triggers
和 --no-swap-tables
将使触发器保持与原表一样的定义。请阅读 --swap-tables
的文档。
如果同时设置了 --no-swap-tables
和 --no-drop-new-table
,触发器将保留在原表上,并在新表上复制(触发器将有一个随机的后缀,因为触发器的名称是唯一的)。
--new-table-name
类型:字符串;默认:%T_new
新表在被重命名前的名称。%T即原来的表名。当使用默认值时,工具会在名称前加上最多10个 _
(下划线)来寻找一个唯一的表名。如果指定了一个表名,工具不会在它前面加上 _
,所以这个表名不能和已有表同名。
--null-to-not-null
允许将一个可为NULL值的列修改为不允许NULL值的列。包含NULL值的行将被转换为定义的默认值(应该是列定义中DEFAULT的值)。如果没有给出明确的默认值,MySQL将根据数据类型分配一个默认值,例如,数字数据类型为0,字符串数据类型为”“。
--only-same-schema-fks
只检查引用表和原表在同一数据库中的外键,这个选项很危险,因为如果你有外键引用其它数据库的表,它们将不会被检查。
--password
简写:-p;类型:字符串
连接时使用的密码。如果密码包含逗号,必须用反斜杠转义:”exam,ple”(这里原文转义的例子应该有问题)
--pause-file
类型:字符串
当该参数指定的文件存在时,执行将被暂停。
--pid
类型:字符串
创建给定的PID文件。如果PID文件已经存在,并且它所包含的PID与当前的PID不同,该工具将不会启动。然而,如果PID文件存在,并且它所包含的PID不再运行,该工具将用当前的PID覆盖该PID文件。当该工具退出时,PID文件将被自动删除。
--plugin
类型:字符串
Perl模块文件定义了一个 pt_online_schema_change_plugin
类。一个插件允许你编写一个Perl模块,它可以 Hook pt-online-schema-change的许多部分。这需要对Perl和Percona工具包的约定有很好的理解,这超出了本文档的范围。如果你有问题或需要帮助,请联系Percona。
有关详细信息,请参阅“插件”。
--port
简写:-P;类型:整型
连接时使用的端口号。
--print
打印SQL语句到STDOUT。指定这个选项可以让你看到工具所执行的大部分语句。例如,你可以把这个选项与 --dry-run
一起使用。
--progress
类型:数组;默认:time,30
在复制行的同时将进度报告打印到STDERR。该值是一个用逗号分隔的列表,有两个部分。第一部分可以是百分比、时间或迭代;第二部分指定了打印更新的频率,以百分比、秒或迭代数为单位。
--quiet
简写:-q
不打印信息到STDOUT(禁用 --progress
)。错误和警告仍然被打印到STDERR。
--recurse
类型:整形
当搜索副本时,在层次结构中递归的级别数。默认为无限。参见 --recursion-method
--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 SLAVE HOSTS是不可靠的。然而,如果服务器使用非标准的端口(不是3306),hosts方法可以更好地工作。该工具通常会做正确的事情,找到所有的副本,但是你可以给出一个首选的方法,它将被首先使用。
hosts方法要求复制者配置report_host、report_port等。
dsn方法很特别:它指定了一个表,其他的DSN字符串都是从这个表中读取的。指定的DSN必须指定一个D和t,或者一个数据库限定的t。 DSN表应该有以下结构。
CREATE TABLE `dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
为了使工具只监控主机10.10.1.16和10.10.1.17的复制滞后,在表中插入 h=10.10.1.16
和 h=10.10.1.17
的值。目前,DSN是按id排序的,但id和parent_id在其他方面被忽略。
你可以在OSC执行时改变主机列表:如果你改变了DSN表的内容,OSC很快就能读取到。
-reverse-triggers 以相反的顺序复制复制过程中添加的触发器。新表中的命令将反映在旧表中。你可以把它作为一个安全功能,这样旧表就可以继续接收更新。这个选项需要 --no-drop-old-table
。
警告! 这个选项在开始复制之前会在新表上创建反向触发器。在新表被重命名为原来的名字后,触发器将继续工作。但是由于表缓存中的重命名元数据版本也会改变,你可能会开始得到 “Prepared statement needs to be re-prepared“的错误。这个问题的解决方法是使用新的prepare statement。如果你不使用服务器端的prepare statement,你的应用程序应该不会受到影响。
--skip-check-slave-lag
类型:DSN;是否可重复:是
在检查从机滞后时要跳过的DSN。它可以被多次使用。例子: -skip-check-slave-lag h=127.0.0.1,P=12345 -skip-check-slave-lag h=127.0.0.1,P=12346。请注意,即使对MySQL驱动来说h=127.1等于h=127.0.0.1,这个参数你还是需要指定完整的IP地址。
--salve-user
类型:字符串
设置用于连接从站的用户。这个参数允许你在从站上有一个权限较小的不同用户,但该用户必须存在于所有从站上。
--salve-password
类型:字符串
设置用于连接从机的密码。它可以与 --slave-user
一起使用,并且用户的密码在所有从站上必须相同。
--set-vars
类型:数组
以 variable=value 的形式设置MySQL的变量,多个以逗号分隔。
工具设置的默认值:
wait_timeout=10000
innodb_lock_wait_timeout=1
lock_wait_timeout=60
在命令行中指定的变量会覆盖这些默认值。例如,指定 --set-vars wait_timeout=500
覆盖了默认值10000。
如果一个变量不能被设置,该工具会打印出一个警告并继续。
注意,设置 sql_mode
变量需要一些棘手的转义,以便能够解析引号和逗号。
示例:
--set-vars sql_mode=\'STRICT_ALL_TABLES\\,ALLOW_INVALID_DATES\'
注意引号用单反斜杠,逗号用双反斜杠转义。
--sleep
类型:浮点型;默认:0
在复制每个分块后要休眠多长时间(秒)。这个选项在无法通过 --max-lag
和 --max-load
节流的情况下很有用。应该使用一个小的、亚秒级的值,比如0.1,否则该工具可能需要很长的时间来复制大表。
--socket
简写:-S,类型:字符串
用来连接的socket文件
--statistics
打印关于内部计数器的统计数据。这对于查看与INSERT的数量相比,有多少警告被抑制是有用的。
-- [no]swap-tables
默认:yes
将原来的表和新的、经过改变的表进行交换。这一步通过用新表替换旧表实现了在线表更新。原来的表变成了 “旧表”,除非你禁用了 --[no]drop-old-table
,否则旧表将被删除。
--tries
类型:数组
关键操作的尝试次数。如果某些操作由于非致命的、可恢复的错误而失败,该工具会等待并再次尝试该操作。下面是默认的可重试的操作,以及它们的尝试次数和重试间隔(秒)。
OPERATION TRIES WAIT
=================== ===== ====
create_triggers 10 1
drop_triggers 10 1
copy_rows 10 0.25
swap_tables 10 1
update_foreign_keys 10 1
analyze_table 10 1
要更改默认值,像下面那样指定新的值:
--tries create_triggers:5:0.5,drop_triggers:5:0.5
这使得该工具仅重试 create_triggers
和 drop_triggers
5次,两次尝试之间等待0.5秒。因此选项的格式是:
operation:tries:wait[,operation:tries:wait]
必须同时指定三个指。
注意,由于元数据锁的原因,大多数操作只在MySQL 5.5和更新的版本中受到 lock_wait_timeout
(见 --set-vars
)的影响。 copy_rows
操作在任何版本的MySQL中都受到 innodb_lock_wait_timeout
的影响。
对于创建和删除触发器,尝试的次数适用于每个触发器的每个 CREATE TRIGGER
和 DROP TRIGGER
语句。对于复制行,尝试的次数适用于每个块,而不是整个表。对于交换表,尝试的次数通常适用于一次,因为通常只有一个RENAME TABLE语句。对于重建外键约束,尝试的次数适用于每个语句(对于 rebuild_constraints
--alter-foreign-keys-method
的 ALTER
语句;对于drop_swap方法的其他语句)。
如果出现以下错误,该工具会重试每个操作:
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
在连接丢失和被断开的情况下,该工具将自动重新连接。
失败和重试会被记录在 --statistics
中。
--user
简写:-u;类型:字符串。
指定登录的用户名。
--version
打印版本号后退出。
-- [no]version-check
默认:yes
检查Percona Toolkit、MySQL和其他程序的最新版本。
这是一个标准的 “自动检查更新 “功能,有两个额外的功能。首先,该工具检查自己的版本,也检查以下软件的版本:操作系统、Percona监控和管理(PMM)、MySQL、Perl、Perl的MySQL驱动(DBD::mysql),以及Percona工具包。第二,它检查并警告有已知问题的版本。例如,MySQL 5.5.25有一个关键的错误,被重新发布为5.5.25a。
为了执行这引起检查,将发起一个与Percona版本检查数据库的安全连接。每个请求都被服务器记录下来,包括软件版本号和被检查系统的唯一ID。这个ID是由Percona工具包的安装脚本或在第一次调用版本检查数据库时产生的。
任何更新或已知问题都会在工具的正常输出之前打印到STDOUT。这个功能不会干扰工具的正常运行。
欲了解更多信息,请访问www.percona.com/doc/percona-toolki...。
插件
由 --plugin
指定的文件必须定义一个名为 pt_online_schema_change_plugin
的类(即一个包),并有一个 new()
子程序。该工具将创建这个类的一个实例,并调用它所定义的任何钩子。钩子不是必须的,但是没有钩子的插件没不会有多大的作用。
如果定义了这些钩子,则按此顺序调用:
init
before_create_new_table
after_create_new_table
before_alter_new_table
after_alter_new_table
before_create_triggers
after_create_triggers
before_copy_rows
after_copy_rows
before_swap_tables
after_swap_tables
before_update_foreign_keys
after_update_foreign_keys
before_drop_old_table
after_drop_old_table
before_drop_triggers
before_exit
get_slave_lag
每个钩子都被传递不同的参数。要看哪些参数被传递给一个钩子,可以在工具的源代码中搜索钩子的名字,比如:
# --plugin hook
if ( $plugin && $plugin->can('init') ) {
$plugin->init(
orig_tbl => $orig_tbl,
child_tables => $child_tables,
renamed_cols => $renamed_cols,
slaves => $slaves,
slave_lag_cxns => $slave_lag_cxns,
);
}
每个钩子调用前都会有注释 # --plugin
。
这是一个包含所有钩子的插件文件模板:
package pt_online_schema_change_plugin;
use strict;
sub new {
my ($class, %args) = @_;
my $self = { %args };
return bless $self, $class;
}
sub init {
my ($self, %args) = @_;
print "PLUGIN init\n";
}
sub before_create_new_table {
my ($self, %args) = @_;
print "PLUGIN before_create_new_table\n";
}
sub after_create_new_table {
my ($self, %args) = @_;
print "PLUGIN after_create_new_table\n";
}
sub before_alter_new_table {
my ($self, %args) = @_;
print "PLUGIN before_alter_new_table\n";
}
sub after_alter_new_table {
my ($self, %args) = @_;
print "PLUGIN after_alter_new_table\n";
}
sub before_create_triggers {
my ($self, %args) = @_;
print "PLUGIN before_create_triggers\n";
}
sub after_create_triggers {
my ($self, %args) = @_;
print "PLUGIN after_create_triggers\n";
}
sub before_copy_rows {
my ($self, %args) = @_;
print "PLUGIN before_copy_rows\n";
}
sub after_copy_rows {
my ($self, %args) = @_;
print "PLUGIN after_copy_rows\n";
}
sub before_swap_tables {
my ($self, %args) = @_;
print "PLUGIN before_swap_tables\n";
}
sub after_swap_tables {
my ($self, %args) = @_;
print "PLUGIN after_swap_tables\n";
}
sub before_update_foreign_keys {
my ($self, %args) = @_;
print "PLUGIN before_update_foreign_keys\n";
}
sub after_update_foreign_keys {
my ($self, %args) = @_;
print "PLUGIN after_update_foreign_keys\n";
}
sub before_drop_old_table {
my ($self, %args) = @_;
print "PLUGIN before_drop_old_table\n";
}
sub after_drop_old_table {
my ($self, %args) = @_;
print "PLUGIN after_drop_old_table\n";
}
sub before_drop_triggers {
my ($self, %args) = @_;
print "PLUGIN before_drop_triggers\n";
}
sub before_exit {
my ($self, %args) = @_;
print "PLUGIN before_exit\n";
}
sub get_slave_lag {
my ($self, %args) = @_;
print "PLUGIN get_slave_lag\n";
return sub { return 0; };
}
1;
注意, get_slave_lag
必须返回一个函数引用;最好是返回实际的slave lag,而不是像例子中那样简单的零。
这是一个更为实际的例子:
package pt_online_schema_change_plugin;
use strict;
sub new {
my ($class, %args) = @_;
my $self = { %args };
return bless $self, $class;
}
sub after_create_new_table {
my ($self, %args) = @_;
my $new_tbl = $args{new_tbl};
my $dbh = $self->{cxn}->dbh;
my $row = $dbh->selectrow_arrayref("SHOW CREATE TABLE $new_tbl->{name}");
warn "after_create_new_table: $row->[1]\n\n";
}
sub after_alter_new_table {
my ($self, %args) = @_;
my $new_tbl = $args{new_tbl};
my $dbh = $self->{cxn}->dbh;
my $row = $dbh->selectrow_arrayref("SHOW CREATE TABLE $new_tbl->{name}");
warn "after_alter_new_table: $row->[1]\n\n";
}
1;
你可以和 --dry-run
一起使用,以检查修改前后表格的差异。
如果你有问题或需要帮助,请联系Percona。
DSN选项
DSN选项用来创建一个DSN。每个选项都以 option=value
的形式指定。这些选项是区分大小写的,所以P和p不是同一个选项。在=之前或之后不能有空格,如果值包含空白,则必须加引号。DSN选项是以逗号分隔的。详细情况见percona-toolkit手册。
A
dsn: charset; copy: yes
默认字符集D
dsn: database; copy: no
旧表和新表所在数据库F
dsn: mysql_read_default_file; copy: yes
仅从指定的文件获取默认选项h
dsn: host; copy: yes
连接的主机。p
dsn: password; copy: yes
连接时使用的密码。如果密码包含逗号,必须使用反斜杠转义:“exam,ple”P
dsn: port; copy: no
连接端口。S
dsn: mysql_socket; copy: yes
连接使用的socket文件。t
dsn: table; copy: no
要修改的表。u
dsn: user; copy: yes
指定登录的用户名。
环境变量
环境变量 PTDEBUG
使冗长的调试信息输出到STDERR。要启用调试并捕获所有的输出到一个文件中,使用如果方式运行工具:
PTDEBUG=1 pt-online-schema-change ... > FILE 2>&1
注意:调试输出量很大,可以产生几MB大小的输出。
退出状态
INVALID_PARAMETERS = 1
UNSUPORTED_MYSQL_VERSION = 2
NO_MINIMUM_REQUIREMENTS = 3
NO_PRIMARY_OR_UNIQUE_KEY = 4
INVALID_PLUGIN_FILE = 5
INVALID_ALTER_FK_METHOD = 6
INVALID_KEY_SIZE = 7
CANNOT_DETERMINE_KEY_SIZE = 9
NOT_SAFE_TO_ASCEND = 9
ERROR_CREATING_NEW_TABLE = 10
ERROR_ALTERING_TABLE = 11
ERROR_CREATING_TRIGGERS = 12
ERROR_RESTORING_TRIGGERS = 13
ERROR_SWAPPING_TABLES = 14
ERROR_UPDATING_FKS = 15
ERROR_DROPPING_OLD_TABLE = 16
UNSUPORTED_OPERATION = 17
MYSQL_CONNECTION_ERROR = 18
LOST_MYSQL_CONNECTION = 19
ERROR_CREATING_REVERSE_TRIGGERS = 20
系统要求
你需要Perl、DBI、DBD::mysql和一些核心软件包,这些软件包应该安装在任何合理的新版本Perl中。
这个工具只适用于MySQL 5.0.2和更新的版本,因为早期版本不支持触发器。另外,为了使pt-online-schema-change按预期操作,应该在MySQL上设置一些权限。PROCESS、SUPER、REPLICATION SLAVE全局权限,以及SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER和TRIGGER表的权限应该被授予服务器。从机只需要 REPLICATION SLAVE 和 REPLICATION CLIENT 权限。
BUGS
关于已知bug的列表,见www.percona.com/bugs/pt-online-sche...。
请在 bugs.launchpad.net/percona-toolkit 上报告错误。在你的错误报告中包括以下信息:
- 运行工具的完整命令行
--version
的输出- 所有相关服务器的MySQL版本
- 工具的输出,包括STDERR
- 输入文件(log/dump/config文件等)
工具下载
访问www.percona.com/software/percona-to...,下载最新版本的Percona Toolkit。或者从命令行获取最新版本。
wget percona.com/get/percona-toolkit.tar.gz
wget percona.com/get/percona-toolkit.rpm
wget percona.com/get/percona-toolkit.deb
您还可以从最新版本中单独获取所需的工具:
wget percona.com/get/TOOL
将 TOOL 替换为任何工具的名称。
版本
pt-online-schema-change 3.3.1
本作品采用《CC 协议》,转载必须注明作者和本文链接