MySQL高级管理工具Percona Toolkit之2—pt-align/pt-archiver

MySQL高级管理工具Percona Toolkit之2——pt-align

一、pt-algin

1、概念

将其他工具的输出与列对齐。

2、描述

pt-align读取行并将其拆分为单词。它计算每行有多少单词，如果有一个数字占主导地位，它假设这就是每行中的单词数量。

然后，它丢弃所有没有那么多单词的行，并查看有单词的第二行。它假设这是第一个非标题行。

根据每个单词看起来是否为数字，它决定列对齐方式。最后，它会遍历并决定每列的宽度，然后打印出来。

3、语法

pt-align [FILES]

4、案例

二、pt-archiver

1、概念

将MySQL表中的行归档到另一个表或文件中。

2、描述

清理线上过期数据； 
导出线上数据，到线下数据作处理； 
清理过期数据，并把数据归档到本地归档表中，或者远端归档服务器。

3、语法

pt-archiver [OPTIONS] --source DSN --where WHERE

4、参数

[root@mysql155 test]# pt-archiver --help
pt-archiver nibbles records from a MySQL table. The --source and --dest
arguments use DSN syntax; if COPY is yes, --dest defaults to the key's value
from --source. For more details, please use the --help option, or try 'perldoc /usr/bin/pt-archiver' for complete documentation.

Usage: pt-archiver [OPTIONS] --source DSN --where WHERE

Options:

--analyze=s           Run ANALYZE TABLE afterwards on --source and/or --dest
--ascend-first        Ascend only first column of index
--ask-pass            Prompt for a password when connecting to MySQL
--buffer              Buffer output to --file and flush at commit
--bulk-delete         Delete each chunk with a single statement (implies --commit-each)
--[no]bulk-delete-limit Add --limit to --bulk-delete statement (defaultyes)
--bulk-insert         Insert each chunk with LOAD DATA INFILE (implies --bulk-delete --commit-each)
--channel=s           Channel name used when connected to a server using replication channels
--charset=s -A        Default character set
--[no]check-charset   Ensure connection and table character sets are the same (default yes)
--[no]check-columns   Ensure --source and --dest have same columns (default yes)
--check-interval=m    If --check-slave-lag is given, this defines how long the tool pauses each time it discovers that a slave is lagging (default 1s). Optional suffix s=seconds, m=minutes, h=hours, d=days; if no suffix,s is used.
--check-slave-lag=s   Pause archiving until the specified DSN's slave lag is less than --max-lag
--columns=a -c        Comma-separated list of columns to archive 
--commit-each         Commit each set of fetched and archived rows ( disables --txn-size)
--config=A            Read this comma-separated list of config files; if specified, this must be the first option on the command line
--database=s -D       Connect to this database
--delayed-insert      Add the DELAYED modifier to INSERT statements
--dest=d              DSN specifying the table to archive to
--dry-run             Print queries and exit without doing anything
--file=s              File to archive to, with DATE_FORMAT()-like formatting
--for-update          Adds the FOR UPDATE modifier to SELECT statements
--header              Print column header at top of --file
--help                Show help and exit 
--high-priority-select Adds the HIGH_PRIORITY modifier to SELECT statements
--host=s -h             Connect to host
--ignore                Use IGNORE for INSERT statements
--limit=i               Number of rows to fetch and archive per statement (default 1)
--local                 Do not write OPTIMIZE or ANALYZE queries to binlog
--low-priority-delete   Adds the LOW_PRIORITY modifier to DELETE statements
--low-priority-insert   Adds the LOW_PRIORITY modifier to INSERT or REPLACE statements
--max-flow-ctl=f        Somewhat similar to --max-lag but for PXC clusters
--max-lag=m             Pause archiving if the slave given by --check-slave-lag lags (default 1s). Optional suffix s=seconds, m=minutes, h=hours, d=days; if no suffix,s is used.
--no-ascend             Do not use ascending index optimization
--no-delete             Do not delete archived rows
--optimize=s            Run OPTIMIZE TABLE afterwards on --source and/or --dest
--output-format=s       Used with --file to specify the output format
--password=s -p         Password to use when connecting
--pid=s                 Create the given PID file
--plugin=s             Perl module name to use as a generic plugin
--port=i -P            Port number to use for connection
--primary-key-only     Primary key columns only
--progress=i           Print progress information every X rows
--purge                Purge instead of archiving; allows omitting --file and --dest
--quick-delete         Adds the QUICK modifier to DELETE statements
--quiet -q             Do not print any output, such as for --statistics
--replace              Causes INSERTs into --dest to be written as REPLACE
--retries=i            Number of retries per timeout or deadlock (default 1)
--run-time=m           Time to run before exiting. Optional suffix s=seconds, m=minutes, h=hours, d=days; if no suffix,s is used.
--[no]safe-auto-increment   Do not archive row with max AUTO_INCREMENT (default yes)
--sentinel=s                Exit if this file exists (default /tmp/pt-archiver-sentinel)
--set-vars=A                Set the MySQL variables in this comma-separated list of variable=value pairs
--share-lock                Adds the LOCK IN SHARE MODE modifier to SELECT statements
--skip-foreign-key-checks   Disables foreign key checks with SET FOREIGN_KEY_CHECKS=0
--slave-password=s          Sets the password to be used to connect to the slaves
--slave-user=s              Sets the user to be used to connect to the slaves
--sleep=i                   Sleep time between fetches
--sleep-coef=f              Calculate --sleep as a multiple of the last SELECT time
--socket=s -S               Socket file to use for connection
--source=d                  DSN specifying the table to archive from (required)
--statistics                Collect and print timing statistics
--stop                      Stop running instances by creating the sentinel file
--txn-size=i                Number of rows per transaction (default 1)
--unstop                    Remove sentinel file
--user=s -u                 User for login if not current user
--version                   Show version and exit
--[no]version-check         Check for the latest version of Percona Toolkit,MySQL, and other programs (default yes)
--where=s                   WHERE clause to limit which rows to archive (required)
--why-quit                  Print reason for exiting unless rows exhausted

Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time

Rules:

Specify at least one of --dest, --file, or --purge.
--ignore and --replace are mutually exclusive.                       --ignore和--replace是互斥的。
--txn-size and --commit-each are mutually exclusive.                 --txn-size和--commit-each是互斥的。
--low-priority-insert and --delayed-insert are mutually exclusive.   --low-priority-insert和--delayed-insert是互斥的。
--share-lock and --for-update are mutually exclusive.                --share-lock和--for-update是互斥的。
--analyze and --optimize are mutually exclusive.                     --analyze和--optimize是互斥的。
--no-ascend and --no-delete are mutually exclusive.                  --no-ascend和--no-delete是互斥的。
DSN values in --dest default to values from --source if COPY is yes. --如果COPY为yes，DSN值--dest默认为--source；  

DSN syntax is key=value[,key=value...] Allowable DSN keys:

KEY COPY   MEANING
=== ====   =============================================
A   yes    Default character set
D   yes    Database that contains the table
F   yes    Only read default options from the given file
L   yes    Explicitly enable LOAD DATA LOCAL INFILE
P   yes    Port number to use for connection
S   yes    Socket file to use for connection
a   no     Database to USE when executing queries
b   no     If true, disable binlog with SQL_LOG_BIN
h   yes    Connect to host
i   yes    Index to use
m   no     Plugin module name
p   yes    Password to use when connecting
t   yes    Table to archive from/to
u   yes    User for login if not current user

5、原表数据

6、迁移案例1-全表归档，不删除原表数据，归档到文件

主机为192.168.21.155，数据库为testdb，表为test；

（1）迁移

前提：表中必须要有索引，否则报错：Cannot find an ascendable index in table at /usr/bin/pt-archiver line 3280.）

pt-archiver --source h=192.168.21.155,P=3306,u=root,p='rootroot',D=testdb,t=test2 --no-check-charset \
--file=/mysql/test/archive.save --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 \
--statistics --no-safe-auto-increment --no-delete

（2）迁移日志

at /usr/bin/pt-archiver line 5094.
TIME ELAPSED COUNT
2024-03-25T08:33:29 0 0
2024-03-25T08:33:29 0 10
Started at 2024-03-25T08:33:29, ended at 2024-03-25T08:33:29
Source: D=testdb,P=3306,h=192.168.21.155,p=...,t=test2,u=root
SELECT 10
INSERT 0
DELETE 0
Action Count Time Pct
select 2 0.0012 48.39
commit 1 0.0004 14.23
print_file 10 0.0000 0.78
other 0 0.0009 36.60

（3）查看迁移到archive的内容

（4）加载文件到数据库

mysql> load data local infile "/mysql/test/archive.save" into table testdb.test3;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0

7、迁移案例2-id<6，不删除原表数据，归档到文件

（1）数据迁移

pt-archiver --source h=192.168.21.151,P=3306,u=root,p='rootroot',D=testdb,t=test2 --no-check-charset  --file=/mysql/test/archive-test3.save --where 'id<6' --progress 10000 --limit=10000 --txn-size 10000  --statistics --no-safe-auto-increment --no-delete

（2）查看迁出的数据

8、常用参数

--where 'id<6'        设置操作条件
--limit 1000          每次取1000行数据给pt-archive处理
--txn-size 1000       设置1000行为一个事务提交一次
--progress 5000       每处理5000行输出一次处理信息
--statistics          结束的时候给出统计信息：开始的时间点，结束的时间点，查询的行数，归档的行数，删除的行数，以及各个阶段消耗的总的时间和比例，便于以此进行优化。只要不加上--quiet，默认情况下pt-archive都会输出执行过程的
--charset=UTF8        指定字符集为UTF8
--no-delete           表示不删除原来的数据，注意：如果不指定此参数，所有处理完成后，都会清理原表中的数据
--bulk-delete         批量删除source上的旧数据
--bulk-insert         批量插入数据到dest主机 (看dest的general log发现它是通过在dest主机上LOAD DATA LOCAL INFILE插入数据的)
--purge               删除source数据库的相关匹配记录
--local               不把optimize或analyze操作写入到binlog里面（防止造成主从延迟巨大）
--analyze=ds          操作结束后，优化表空间（d表示dest，s表示source）

默认情况下，pt-archiver操作结束后，不会对source、dest表执行analyze或optimize操作，因为这种操作费时间，并且需要你提前预估有足够的磁盘空间用于拷贝表。
一般建议在pt-archiver操作结束后，在业务非高峰期手动执行analyze table用以回收表空间

三、pt-config-diff

1、概念

pt-config-diff 是 Percona Toolkit 工具集中的一个工具，用于比较两个 MySQL 配置文件之间的差异。

通过 pt-config-diff 工具，可以方便地比较两个配置文件的设置，找出它们之间的差异，从而帮助您了解配置文件的变化情况。

2、描述

pt-config-diff 工具可用于比较两个 MySQL 配置文件，包括 my.cnf、my.ini 或其他自定义配置文件。

它会逐行比较两个配置文件的设置，并显示出它们之间的差异，包括新增、修改或删除的配置项。

可以快速了解配置文件之间的差异，做出必要的调整和优化。

3、语法

pt-config-diff [OPTIONS] CONFIG_FILE1 CONFIG_FILE2

4、参数

-h, --help：显示帮助信息
--defaults-file=FILE：指定用于连接数据库的默认配置文件
--defaults-group=GRP：在默认配置文件中指定的组
--config, --cnf=FILE：指定使用的配置文件路径
--ignore-globals=REGEX：忽略全局配置项
--set-vars=VAR=VAL：为诊断设置变量
--no-quote-names：不要在配置项名字周围使用引号
--quiet：减少输出

5、案例

（1）比较两个本地配置文件

[root@mysql151 mysql]# pt-config-diff my.cnf my.cnf.linux.MySQL8.0

（2）比较两个服务器参数

pt-config-diff h=192.168.21.151 --user='root' --password='rootroot' h=192.168.21.155 --user='root' --password='rootroot'

四、pt-deadlock-logger

1、概念：

pt-deadlock-logger 是 Percona Toolkit 工具集中的一个工具，用于监控和记录 MySQL 数据库中发生的死锁情况。

通过 pt-deadlock-logger 工具，您可以定期检查数据库中的死锁情况，并将死锁事件记录到日志中，以便后续分析和优化。

2、描述：

pt-deadlock-logger 工具会定期查询 MySQL 数据库的错误日志，检测其中是否包含有关死锁的信息。

一旦检测到死锁事件，pt-deadlock-logger 会记录相关信息到指定的日志文件中，并可选择性地发送通知邮件给管理员，以便及时关注和解决死锁问题。

3、语法：

pt-deadlock-logger [OPTIONS]

4、参数：

- -h,                   --help：显示帮助信息
--run-time=N：          运行时间，即监控间隔时间（以秒为单位，默认为 1 秒）
--check-interval=N：    检查数据库错误日志的间隔时间（以秒为单位，默认为 1 秒）
--create-table：        在监控数据库之前创建必要的表
--run-once：            仅运行一次并退出
--log：                 指定记录死锁事件的日志文件路径
--notify：              在发现死锁时是否发送通知邮件
--notify-method=EMAIL： 通知方法，默认为 EMAIL
--notify-condition：    自定义通知条件
--mail-to：             邮件接收者
--mail-from：           邮件发送者
--mail-subject：        邮件主题

5、案例：

pt-deadlock-logger --run-time=3600 --check-interval=5 --log=/mysql/deadlock.log --notify --mail-to=admin@example.com

在上述示例中，pt-deadlock-logger 会每隔 5 秒检查一次数据库错误日志中的死锁情况，运行时间为 1 小时（3600 秒），并将死锁事件记录到指定的日志文件中。同时，它会在发现死锁时发送通知邮件给管理员（admin@example.com）。

希望以上介绍对您有所帮助。如果您需要进一步了解或有其他问题，请随时提问，我会尽力解答。