【MySQL工具】pt-online-schema-change 详细介绍

2023-12-21 00:54:43

下面文章基于 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 并尝试对可能出现的意外情况 行为发出警告。目前,它检查:

  1. 列重命名
  2. 删除主键

--[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

动态调整块大小,以便每个数据复制的查询都在该时间内能完成 。

该工具跟踪复制速率(每秒行数)并在?每次数据复制查询后 动态调整块的大小,以便下一个查询花费这个参数指定的时间(以秒为单位)。

它保持指数衰减的移动平均线 每秒的查询数,因此如果服务器的性能由于以下原因而发生变化 服务器负载发生变化时,该工具可以快速适应。

--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

--quiet

short form: -q

不将消息打印到 STDOUT。 错误和警告仍然打印到 STDERR。

--recurse

type: int

发现副本时在层次结构中递归的级别数。 默认为无限。

--recursion-method

type: array; default: processlist,hosts

--skip-check-slave-lag

type: DSN; repeatable: yes

--slave-user

type: string

置用于连接从副本的用户

--slave-password

type: string

用于连接从副本的密码。与--slave-user一起使用

--set-vars

type: Array

--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

参考

官方文档?

pt-online-schema-change — Percona Toolkit Documentation

重庆八怪

https://www.jianshu.com/p/ecec3d307ec0/

文章来源:https://blog.csdn.net/weixin_48154829/article/details/135092780
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。