下面文章基于 pt-online-schema-change 3.5.5 版本
简介
pt-online-schema-change - 无锁表修改表结构工具，这里无锁表也不是绝对的，在交互原表与中间表 表名的时候也会有元数据锁，只不过时间很短
语法
pt-online-schema-change [OPTIONS] DSN
# 给表actor.sakila 添加一个列
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor
# 修改表sakila.actor 的存储引擎为InnoDB。
# 如果该表原本就是innoDB的存储引擎，该语句还能用来非阻塞方式的 OPTIMIZE TABLE
pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor
pt-online-schema-change 可以在线修改表结构而不阻塞该表的读 和 写。
工作原理
pt-online-schema-change 使用了MySQL内部变更表的方式，但是 是对 需要变更表的中间表（很多人也叫幽灵表） 使用MySQL内部变更的方式。这意味着原表不会被锁表，客户端可以继续对该表进行读写。
1、检查更改表是否有主键或唯一索引，是否有触发器
2、检查修改表的表结构，创建一个中间表，在中间表上执行ALTER TABLE语句
3、在源表上创建三个触发器分别对于INSERT UPDATE DELETE操作
4、从源表拷贝数据到临时表，在拷贝过程中，对源表的更新操作会写入到新建表中
5、将临时表和源表rename（需要元数据修改锁，需要短时间锁表）
6、删除源表和触发器，完成表结构的修改。
限制（安全措施）
该工具不会修改表结构 ，除非执行了--execute选项
- 如果表中不存在主键和唯一索引，该工具不会运行 。
- 如果从副本中设置了复制过滤规则 ，该工具不会运行 。与参数 --[no]check-replication-filters有关
- 如果外键约束引用该表，该工具将拒绝更改该表，除非指定 --alter-foreign-keys-method
- 如果发现从副本中的延迟超过了参数--max-lag 设置的值，该工具将暂停执行。
- 如果检测到负载过大，则该工具暂停或终止操作。与参数--max-load和--critical-load有关。
- 该工具会设置参数
innodb_lock_wait_timeout=1 和(5.5版本或者更新版本)lock_wait_timeout=60，因此更有可能成为锁争用过程中的受害者，并且不太可能扰乱其他事务，这些参数可以通过--set-vars 来设置 - 该工具无法更改“Percona XtraDB Cluster”节点上的 MyISAM 表。
输出内容
该工具的日志输出到STDOUT ,可以方便查看具体操作。
在数据拷贝阶段，使用 --progress 会打印报告到 STDERR
可以通过使用 --print 来获取额外的日志信息。
通过执行 --statistics ，会将事件计数打印在最后 。
参数解读
--alter string
通过参参数执行表的变更信息，参数中不用使用ALTER TABLE关键字。可以对一张表进行多次变更，语句之间使用逗号分开即可。
注意以下情况可能导致工具执行失败：
- 表中需要至少存在一个主键或者唯一索引，因为该工具会在DELETE的触发器中来保证中间表更新。
- 如果 在该表已经存在的字段上添加主键或者唯一索引，这个列将会被用在DELETE触发器中
- RENAM语法不能用来重命名表
- 不能通过使用 删除字段后再新增字段的方式来 重命名 列名。因为该工具不会拷贝原表字段的数据到新列上。
- 如果你在添加字段的时候没有使用默认值，但是该字段设置为NOT NULL。该工具会失败，工具不会去猜测默认值。
- DROP FOREIGN KEY constraint_name; 需要指定_constraint_name 而不是真实的 constraint_name。 由于MySQL的限制，在创建新表时 ，在新表中创建外键约束的时候需要添加前导下划线。
- 该工具不会在 MySQL5.0 使用
LOCK IN SHARE MODE，因为这个会造成从副本 复制错误。
--alter-foreign-keys-method
type: string
如何修改外键以便它们引用中间表。外键 引用要更改的表必须进行特殊处理，以确保它们 继续参考正确的表。
当该工具重命名原始文件时 为了让新表取代它的位置，外键“跟随”重命名的表 表，并且必须更改为引用新表。
- auto: 自动决定使用哪种方式最好。该工具优先使用rebuild_constraints。如果不行，使用drop_swap
- rebuild_constraints
- drop_swap
- none
--[no]analyze-before-swap
default :yes
在与旧表交换之前对新表执行 ANALYZE TABLE。 默认情况下，仅在运行 MySQL 5.6 及更高版本时才会发生这种情况，并且 innodb_stats_persistent 已启用。无论 MySQL 版本如何，innodb_stats_persistent是否开启，需要明确指定该参数是开启或禁用。
这避免了与 InnoDB 优化器相关的潜在严重问题 统计数据。如果变更的表很繁忙并且该工具很快完成， 交换后的新表将不会有优化器统计信息。这个可以 导致原来使用索引的查询执行全表扫描，直到优化器 统计数据会更新（通常在 10 秒后）。如果变很大并且非常繁忙，这可能会导致中断。
--ask-pass
当连接到MySQL时 提示输入密码
--channel string
type: string
使用复制通道连接到服务器时使用的通道名称。 假设你有两个master，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 sting
短选项 ： -A
默认字符集，如果不指定字符集，有可能导致表中 中文的注释乱码，在生产中接触到过。
--[no]check-alter
default: yes
解析--alter 并尝试对可能出现的意外情况 行为发出警告。目前，它检查：
- 列重命名
- 删除主键
--[no]check-foreign-keys
default: yes
检查自引用外键。目前自引用的 FK 是 不完全支持，因此，为了防止错误，如果表 具有自引用外键。使用此参数禁用自引用 FK 检查。
--check-interval
type: time; default: 1
检查--max-lag 间隔时间
--[no]check-plan
default: yes
检查查询执行计划的安全性。默认情况下是开启的，在运行要查询原表数据之前运行 EXPLAIN ，如果选择正确的执行计划 该工具每次查询少量数据，但如果 MySQL 选择错误执行计划，则可以访问许多行 。其中包括确定块边界的查询和 块查询本身。如果 MySQL 将使用错误的查询 执行计划时，该工具将停止复制原表数据行并退出。
该工具使用多种启发式方法来确定执行计划是否错误。 第一个是 EXPLAIN 是否报告 MySQL 打算使用所需的索引 访问行。如果 MySQL 选择不同的索引，该工具会考虑 查询不安全。
该工具还检查 MySQL 报告将使用多少索引 查询。 EXPLAIN 输出在 key_len 列中显示了这一点。工具 记住所看到的最大的 key_len，如果 MySQL 报告它则终止 将使用较小的索引前缀。
此选项为每个表和块添加了一些设置工作。虽然工作是 对 MySQL 来说不是侵入性的，它会导致与服务器的更多往返，消耗时间。使块太小会导致开销变得 相对较大。因此建议您不要设置 块 太小，因为如果您这样做，该工具可能需要很长时间才能完成。
--[no]check-replication-filters
default: yes
如果在任何服务器上设置了任何复制过滤规则，则中止。该工具检查所有从副本 复制过滤的规则 ，例如 binlog_ignore_db 和 repl_do_db。如果发现任何此类过滤，则会终止。
--check-slave-lag
type: string
检查副本的延迟如果大于 --max-lag ，则暂停数据拷贝。
--chunk-index
type: string
使用该索引对表进行分块。默认情况下，工具选择合适的索引进行分块。所以使用该选项，则可以自己指定分块的索引。如果指定的索引不存在，则回退到默认的选择方式。
工具会在查询SQL中使用FORCE INDEX 语句。谨慎使用该选项，可能会有不好性能表现。
--chunk-index-columns
type: int
只使用--chunk-index 指定的索引中最左边的字段， 在--chunk-index 指定的复合索引时才有用。
--chunk-size
type: size; default: 1000
每个数据复制的块的行数 ，允许后缀 k ,M,G。默认1000行。
如果你设置了这个选项，会禁用动态调整行为，每个块都是你指定的行数。
--chunk-size-limit
type: float; default: 4.0
当表没有唯一索引时，块大小可能不准确。这个选项 指定不准确度的最大容忍限度。该工具使用此选项的最小值为 1，这意味着没有任何块可以比--chunk-size 更大 。您可能不想指定 1，因为 rows EXPLAIN 报告的是估计值，可能与实际数字不同 块中的行数。您可以通过指定禁用超大块检查 值为 0。
--chunk-time
type: float; default: 0.5
动态调整块大小，以便每个数据复制的查询都在该时间内能完成 。
该工具跟踪复制速率（每秒行数）并在 每次数据复制查询后 动态调整块的大小，以便下一个查询花费这个参数指定的时间（以秒为单位）。
它保持指数衰减的移动平均线 每秒的查询数，因此如果服务器的性能由于以下原因而发生变化 服务器负载发生变化时，该工具可以快速适应。
如果此选项设置为0，则块大小不会自动调整，因此查询 时间会有所不同，但查询块大小不会。另一种方法可以做到同样的事情 事情是显式指定--chunk-size 的值，而不是使用默认值。
--config
type: Array
以逗号分隔的配置文件列表；
如果指定，则必须是 命令行上的第一个选项。
--critical-load
type: Array; default: Threads_running=50
和参数 --max-load参数用法类似，不同的点达到阈值后不是暂停而是中止操作。用法参考 --max-load
--database
short form: -D; type: string
数据库名称
--default-engine
使用该值替换新表的存储引擎
--data-dir
type: string
使用DATA DIRECTORY新功能在不同的分区上创建新表。 仅适用于 5.6+。如果同时使用该参数remove-data-dir 则被忽略
--remove-data-dir
default: no
如果原始表是使用 DATA DIRECTORY 功能创建的，请将其删除并创建 MySQL默认目录中的新表，而不创建新的isl文件。
--defaults-file
short form: -F; type: string
只从该文件中读取 mysql 选项。必须是绝对路径的文件。
--[no]drop-new-table
default: 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
default: yes
原始表 与 中间表交换表名后，删除原始表。
如果使用了参数–no-swap-tables,就没有原始表需要删除
--[no]drop-triggers
default: yes
在原始表上删除触发器，--no-drop-triggers 优先级高于 --no-drop-old-table.
--dry-run
创建并更改新表，但不创建触发器、复制数据或 替换原来的表。
--execute
真正执行所有环节
--[no]check-unique-key-change
default: yes
检查变更语句是否是添加唯一索引。
工具尝试给变更表添加唯一索引，自从该工具使用 INSERT IGNORE 去拷贝数据到中间表，如果该行数据是重复的值，该数据会丢失
--force
在使用 alter-foreign-keys-method = none 的情况下，此选项会绕过确认，这可能会破坏外键约束。
--help
显示帮助
--host
short form: -h; type: string
需要连接的主机
--max-flow-ctl
type: float
与 –max-lag 有点相似，但适用于 PXC 集群。 检查集群花费在流量控制暂停上的平均时间，并在以下情况下暂停工具： 它超过了选项中指示的百分比。 值 0 将使工具在任何流量控制活动发生时暂停 检测到。 默认情况下不进行流量控制检查。 此选项适用于 PXC 版本 5.6 或更高版本。
--max-lag
type: time; default: 1s
暂停复制如果从副本的延迟大于该值，在每次数据拷贝查询之后，工具将检查从副本的延迟，使用Seconds_Behind_Master。如果任何一个从副本的延迟大于该值，则工具将会sleep --check-intervale 秒，然后重新检查。如果指定 --check-slave-lag ,该工具只会检查特定的从副本，不会检查所有的从副本。
该工具会一直循环等待副本没有延迟。如果有任何副本 停止后，该工具将一直等待，直到副本启动。当所有副本都在运行并且没有滞后太多时继续。
该工具在等待时打印进度报告。如果副本停止，它 立即打印进度报告，然后在每次进度报告时再次打印 间隔
--max-load
type: Array; default: Threads_running=25
在每次数据分块拷贝之后， 检查SHOW GLOBAL STATUS ，如果其中任何一个状态量 高于 我们设置的阀值，则暂停数据拷贝。
该选项可以接受多个值，格式为 ：optional1 =MAX_VALUE, optional2 =MAX_VALUE, 用逗号分割，如果只最大值没有给定，工具会把当前至增加20% 当做最大的阈值。
举例
如果您希望该工具在 Threads_connected 变得太高时暂停， 您可以指定“Threads_connected”，该工具将检查当前值 当它开始工作时，将该值添加 20%。如果当前值为 100， 那么当 Threads_connected 超过 120 时该工具将暂停，并恢复工作 当它再次低于120时。如果你想指定一个明确的阈值，比如 例如 110，您可以使用“Threads_connected:110”或“Threads_connected=110”。
目的
该选项的目的是防止工具增加过多的服务器负载 。如果data-copy 的查询是对负载有影响，或者它们导致锁等待， 那么服务器上的其他查询将会阻塞和排队。 通常会导致 Threads_running 增加，该工具可以通过以下方式检测到这一点： 每个查询完成后立即运行 SHOW GLOBAL STATUS。如果你 指定该变量的阈值，然后您可以指示工具等待 直到查询再次正常运行。这不会妨碍排队， 然而;它只会给服务器一个从队列中恢复的机会。如果 如果您注意到排队，最好减少 chunk time.
--preserve-triggers
保留原来的触发器
--new-table-name
type: string; default: %T_new
中间表的名称，默认值中 ，%T是原表的名称，如果自己指定中间表表名，则该表名不能存
--null-to-not-null
将允许为null的字段 改为 不允许为null。该字段变更之前原来的数据 会被该类型的默认值替换，例如字符串类型为''，整数类型为 0.
--only-same-schema-fks
仅检查与原始表具有相同架构的表上的外键。 这个选项很危险，因为如果你在其他地方有 FK 引用表 模式，它们将不会被检测到。
--password
short form: -p; type: string
连接时使用的密码。 如果密码包含逗号，则必须使用反斜杠进行转义：“exam,ple”
--pause-file
type: string
当此参数指定的文件存在时，执行将暂停。也支持暂停了。
--pid
type: string
创建给定的 PID 文件。如果 PID 文件已经存在，该工具将不会启动 存在且其包含的 PID 与当前 PID 不同。然而， 如果 PID 文件存在并且它包含的 PID 不再运行，则 工具将用当前 PID 覆盖 PID 文件。 PID文件是 工具退出时自动删除。
--plugin
type: string
--port
short form: -P; type: int
端口
输出SQL语句到STDOUT。使用该参数你可以看到绝大部分该工具执行的SQL。
例如 你可以和 --dry-run配合使用
--progress
type: array; default: time,30
复制行时将进度报告打印到 STDERR。该值 包含两部分，以逗号分隔. 可选值 percentage, time, or iterations ，第二列是数值，默认是 (time,30)，每30秒打印一次变更的进度。
--progress=time,30 按时间打印进度 ，每30秒打印一次进度
--progress=percentage,5 按百分比打印进度 ，每5%进度进行一次打印。
--progress=iterations,5 按循环迭次数打印进度
--quiet
short form: -q
不将消息打印到 STDOUT。 错误和警告仍然打印到 STDERR。
--recurse
type: int
发现副本时 级联复制的层次数。 默认为无限。
--recursion-method
type: array; default: processlist,hosts
发现副本的方法 。可能的方法有
METHOD USES
=========== ==================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
dsn=DSN DSNs from a table
none Do not find slavesprocesslist方法是默认的，因为SHOW SLAVE HOSTS是不可靠的。但是，如果服务器使用非标准端口（非 3306) 则hosts方法可以更好地工作 。该工具通常会做正确的事情并且 查找所有副本，但您可以给出首选方法，它将被使用 第一的。
Hosts方法要求从副本配置report_host， report_port等
dsn 方法很特殊：它创建一个表，从中读取DNS字符串， 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 很快就会读取它。
--skip-check-slave-lag
type: DSN; repeatable: yes
检查从副本延迟时要跳过的 DSN。它可以多次使用。 示例： –skip-check-slave-lag h=127.0.0.1,P=12345 –skip-check-slave-lag h=127.0.0.1,P=12346
--slave-user
type: string
置用于连接从副本的用户
--slave-password
type: string
用于连接从副本的密码。与--slave-user一起使用
--set-vars
type: Array
设置 MySQL 变量，以逗号分割的 variable=value 形式。
默认情况下 ，该工具
wait_timeout=10000
innodb_lock_wait_timeout=1
lock_wait_timeout=60在命令行上指定的变量会覆盖这些默认值。例如，指定--set-vars wait_timeout=50010000 会覆盖默认值 的值。
如果无法设置变量，该工具会打印警告并继续。
请注意，设置 sql_mode 变量需要一些转义来解析引号和逗号。
例子：
--set-vars sql_mode=\'STRICT_ALL_TABLES\\,ALLOW_INVALID_DATES\'--sleep
type: float; default: 0
复制每个块后休眠多长时间（以秒为单位）。 当无法通过 --max-lag 和 --max-load 进行节流时，这个选项很有用。 应使用较小的秒值，例如 0.1，否则该工具复制大表需可能会要很长时间。
--socket
short form: -S; type: string
用于连接的套接字文件。
--statistics
打印有关内部计数的统计信息。
--[no]swap-tables
default: yes
交换原始表与中间表的表名。
使用 --no-swap-tables 将运行整个过程，它将创建新的 表，它将复制所有行，但最后它将删除中间表。这是旨在进行更真实的试运行。
--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
operation:tries:wait[,operation:tries:wait]
必须指定所有三个值。
失败和重启的记录 如果使用了--statistics
--user
short form: -u; type: string
连接数据库用户
--version
显示版本并推出
--[no]version-check
default: yes
版本检查
--[no]fail-on-stopped-replication
default: yes
如果复制停止，则失败并显示错误（退出状态 128），而不是等待 直到重新启动复制。
DSN 选项
这些 DSN 选项用于创建 DSN。每个选项都给出如下 option=value。选项区分大小写，因此 P 和 p 不是 相同的选项。 = 之前或之后不能有空格 如果该值包含空格，则必须用引号引起来。 DSN 选项有 以逗号分隔。有关完整详细信息，请参阅 percona-toolkit 联机帮助页。
-
A
dsn: charset; copy: yes
默认字符集。
-
D
dsn: charset; copy: 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: no
用于连接的端口号。
-
S
dsn: mysql_socket; copy: yes
用于连接的套接字文件。
-
t
dsn: table; copy: no
要更改的表。
-
u
dsn: user; copy: yes
如果不是当前用户，则用于登录的用户。
程序退出状态
INVALID_PARAMETERS = 1
UNSUPPORTED_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
UNSUPPORTED_OPERATION = 17
MYSQL_CONNECTION_ERROR = 18
LOST_MYSQL_CONNECTION = 19
ERROR_CREATING_REVERSE_TRIGGERS = 20系统要求
安装软件所需的依赖 ：Perl, DBI, DBD::mysql, and some core packages
MySQL版本 ：MySQL 5.0.2 and newer versions，原来的版本不支持触发器
用户权限：
- 连接主库账号 ：PROCESS, SUPER, REPLICATION SLAVE global privileges, as well as SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, and TRIGGER table privileges should be granted on server.
- 连接从库的账号 ： REPLICATION SLAVE and REPLICATION CLIENT privileges.
下载
下载最新版本
wget percona.com/get/percona-toolkit.tar.gz
wget percona.com/get/percona-toolkit.rpm
wget percona.com/get/percona-toolkit.deb参考
官方文档
96
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
