mysql在线DDL工具--pt-online-schema-change 详细介绍
pt-online-schema-change 详细介绍
简介
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
端口
--print
输出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 slaves
processlist方法是默认的，因为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
要更改默认值，请指定新值，例如：
这使得该工具尝试 create_triggers 和 drop_triggers 5 次 两次尝试之间有 0.5 秒的等待时间。所以格式是：
--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
原文链接：https://blog.csdn.net/weixin_48154829/article/details/135092780
