Percona Toolkit权威指南：全面掌握MySQL运维命令 - CSDN文库

需积分: 10 50 浏览量更新于2024-07-18 收藏 1.31MB PDF 举报

Percona Toolkit 是一套由 Percona 公司开发并维护的全面的MySQL数据库运维工具集，主要用于管理和优化MySQL服务器性能、监控、诊断和数据恢复。该工具组在Release 2.2.20版本的文档中详细列出了23个实用工具，涵盖了MySQL数据库的各个方面： 1. **安装**：文档首先介绍了如何获取和安装Percona Toolkit，这对于新用户来说是至关重要的入门步骤。 2. **工具概览**： - **pt-align**：用于调整表的空间分配，确保数据一致性。 - **pt-archiver**：备份工具，支持增量和压缩备份，提高数据迁移效率。 - **pt-conﬁg-diff**：比较和检测MySQL配置文件的变化，有助于识别潜在问题。 - **pt-deadlock-logger**：跟踪和分析死锁事件，提升并发性能。 - **pt-diskstats**：提供磁盘I/O统计，帮助了解存储性能瓶颈。 - **pt-duplicate-key-checker**：检测重复键，有助于数据完整性检查。 - **pt-ﬁfo-split**：将大型FIFO（先进先出）文件拆分，便于处理大文件。 - **pt-find**：搜索MySQL数据文件，支持复杂的查询和过滤条件。 - **pt-fingerprint**：生成数据库的指纹报告，用于快速识别和匹配。 - **pt-fk-error-logger**：记录外键错误，帮助解决数据一致性问题。 - **pt-heartbeat**：实时监控MySQL服务器的心跳，保持连接状态。 - **pt-index-usage**：分析索引使用情况，优化查询性能。 - **pt-ioprofile**：详细记录MySQL的I/O操作，诊断性能瓶颈。 - **pt-kill**：用于优雅地停止MySQL进程或客户端连接。 - **pt-mext**：管理MySQL扩展存储引擎的工具。 - **pt-mysql-summary**：生成简洁的MySQL性能报告。 - **pt-online-schema-change**：在不中断服务的情况下进行表结构修改。 - **pt-pmp**：Percona Monitoring Plugin，用于实时监控系统性能。 - **pt-query-digest**：简化复杂查询，生成易于理解的执行计划。 - **pt-show-grants**：查看和管理用户的权限。 - **pt-sift**：数据清洗工具，处理大量数据集。 - **pt-slave-delay**：监控复制延迟，确保数据同步的一致性。 - **pt-slave-find**：定位丢失的复制日志文件，辅助复制故障排查。 - **pt-slave-restart**：重启MySQL复制服务。 - **pt-stalk**：持续监控MySQL实例，发送警报或执行操作。 - **pt-summary**：汇总关键性能指标，提供简明视图。 - **pt-table-checksum**：检查表的数据完整性。 - **pt-table-sync**：同步多个表的行级数据。 - **pt-table-usage**：分析表的使用情况，包括行数和空间占用。 - **pt-upgrade**：升级Percona Toolkit的指南。 - **pt-variable-advisor**：建议优化MySQL变量设置。 - **pt-visual-explain**：图形化解释查询执行计划，便于理解。 3. **配置管理**：文档还涵盖配置文件管理、数据源名称（DSN）规格和环境变量设置等内容，确保工具能正确连接和操作MySQL服务器。这些工具提供了强大的功能，无论是日常运维还是应对突发问题，都能极大地提升MySQL数据库的管理和优化效率。对于任何从事MySQL数据库运维的DBA来说，Percona Toolkit是一份不可或缺的参考资料。

Percona Toolkit Documentation, Release 2.2.20

If --check-slave-lag is given, this deﬁnes how long the tool pauses each time it discovers that a slave

is lagging. This check is performed every 100 rows.

-check-slave-lag

type: string; repeatable: yes

Pause archiving until the speciﬁed DSN’s slave lag is less than --max-lag. This option can be speciﬁed

multiple times for checking more than one slave.

-columns

short form: -c; type: array

Comma-separated list of columns to archive.

Specify a comma-separated list of columns to fetch, write to the ﬁle, and insert into the destination table. If

speciﬁed, pt-archiver ignores other columns unless it needs to add them to the SELECT statement for ascending

an index or deleting rows. It fetches and uses these extra columns internally, but does not write them to the ﬁle

or to the destination table. It does pass them to plugins.

See also --primary-key-only.

-commit-each

Commit each set of fetched and archived rows (disables --txn-size).

Commits transactions and ﬂushes --file after each set of rows has been archived, before fetching the next set

of rows, and before sleeping if --sleep is speciﬁed. Disables --txn-size; use --limit to control the

transaction size with --commit-each.

This option is useful as a shortcut to make --limit and --txn-size the same value, but more importantly

it avoids transactions being held open while searching for more rows. For example, imagine you are archiving

old rows from the beginning of a very large table, with --limit 1000 and --txn-size 1000. After some

period of ﬁnding and archiving 1000 rows at a time, pt-archiver ﬁnds the last 999 rows and archives them,

then executes the next SELECT to ﬁnd more rows. This scans the rest of the table, but never ﬁnds any more

rows. It has held open a transaction for a very long time, only to determine it is ﬁnished anyway. You can use

--commit-each to avoid this.

-config

type: Array

Read this comma-separated list of conﬁg ﬁles; if speciﬁed, this must be the ﬁrst option on the command line.

-database

short form: -D; type: string

Connect to this database.

-delayed-insert

Add the DELAYED modiﬁer to INSERT statements.

Adds the DELAYED modiﬁer to INSERT or REPLACE statements. See

http://dev.mysql.com/doc/en/insert.html for details.

-dest

type: DSN

DSN specifying the table to archive to.

This item speciﬁes a table into which pt-archiver will insert rows archived from --source. It uses the same

key=val argument format as --source. Most missing values default to the same values as --source, so

you don’t have to repeat options that are the same in --source and --dest. Use the --help option to see

which values are copied from --source.

12 Chapter 2. Tools

Percona Toolkit Documentation, Release 2.2.20

WARNING: Using a default options ﬁle (F) DSN option that deﬁnes a socket for --source causes pt-

archiver to connect to --dest using that socket unless another socket for --dest is speciﬁed. This means

that pt-archiver may incorrectly connect to --source when it connects to --dest. For example:

--source F=host1.cnf,D=db,t=tbl --dest h=host2

When pt-archiver connects to --dest, host2, it will connect via the --source, host1, socket deﬁned in

host1.cnf.

-dry-run

Print queries and exit without doing anything.

Causes pt-archiver to exit after printing the ﬁlename and SQL statements it will use.

-file

type: string

File to archive to, with DATE_FORMAT()-like formatting.

Filename to write archived rows to. A subset of MySQL’s DATE_FORMAT() formatting codes are allowed in

the ﬁlename, as follows:

%d Day of the month, numeric (01..31)

%H Hour (00..23)

%i Minutes, numeric (00..59)

%m Month, numeric (01..12)

%s Seconds (00..59)

%Y Year, numeric, four digits

You can use the following extra format codes too:

%D Database name

%t Table name

Example:

--file ’/var/log/archive/%Y-%m-%d-%D.%t’

The ﬁle’s contents are in the same format used by SELECT INTO OUTFILE, as documented in the MySQL

manual: rows terminated by newlines, columns terminated by tabs, NULL characters are represented by N, and

special characters are escaped by . This lets you reload a ﬁle with LOAD DATA INFILE’s default settings.

If you want a column header at the top of the ﬁle, see --header. The ﬁle is auto-ﬂushed by default; see

--buffer.

-for-update

Adds the FOR UPDATE modiﬁer to SELECT statements.

For details, see http://dev.mysql.com/doc/en/innodb-locking-reads.html.

-header

Print column header at top of --file.

Writes column names as the ﬁrst line in the ﬁle given by --file. If the ﬁle exists, does not write headers; this

keeps the ﬁle loadable with LOAD DATA INFILE in case you append more output to it.

-help

Show help and exit.

-high-priority-select

Adds the HIGH_PRIORITY modiﬁer to SELECT statements.

See http://dev.mysql.com/doc/en/select.html for details.

2.2. pt-archiver 13

Percona Toolkit Documentation, Release 2.2.20

-host

short form: -h; type: string

Connect to host.

-ignore

Use IGNORE for INSERT statements.

Causes INSERTs into --dest to be INSERT IGNORE.

-limit

type: int; default: 1

Number of rows to fetch and archive per statement.

Limits the number of rows returned by the SELECT statements that retrieve rows to archive. Default is one

row. It may be more efﬁcient to increase the limit, but be careful if you are archiving sparsely, skipping over

many rows; this can potentially cause more contention with other queries, depending on the storage engine,

transaction isolation level, and options such as --for-update.

-local

Do not write OPTIMIZE or ANALYZE queries to binlog.

Adds the NO_WRITE_TO_BINLOG modiﬁer to ANALYZE and OPTIMIZE queries. See --analyze for

details.

-low-priority-delete

Adds the LOW_PRIORITY modiﬁer to DELETE statements.

See http://dev.mysql.com/doc/en/delete.html for details.

-low-priority-insert

Adds the LOW_PRIORITY modiﬁer to INSERT or REPLACE statements.

See http://dev.mysql.com/doc/en/insert.html for details.

-max-flow-ctl

type: ﬂoat

Somewhat similar to –max-lag but for PXC clusters. Check average time cluster spent pausing for Flow Control

and make tool pause if it goes over the percentage indicated in the option. Default is no Flow Control checking.

This option is available for PXC versions 5.6 or higher.

-max-lag

type: time; default: 1s

Pause archiving if the slave given by --check-slave-lag lags.

This option causes pt-archiver to look at the slave every time it’s about to fetch another row. If the slave’s lag

is greater than the option’s value, or if the slave isn’t running (so its lag is NULL), pt-table-checksum sleeps

for --check-interval seconds and then looks at the lag again. It repeats until the slave is caught up, then

proceeds to fetch and archive the row.

This option may eliminate the need for --sleep or --sleep-coef.

-no-ascend

Do not use ascending index optimization.

The default ascending-index optimization causes pt-archiver to optimize repeated SELECT queries so they

seek into the index where the previous query ended, then scan along it, rather than scanning from the beginning

of the table every time. This is enabled by default because it is generally a good strategy for repeated accesses.

14 Chapter 2. Tools

Percona Toolkit Documentation, Release 2.2.20

Large, multiple-column indexes may cause the WHERE clause to be complex enough that this could actually

be less efﬁcient. Consider for example a four-column PRIMARY KEY on (a, b, c, d). The WHERE clause to

start where the last query ended is as follows:

WHERE (a > ?)

OR (a = ? AND b > ?)

OR (a = ? AND b = ? AND c > ?)

OR (a = ? AND b = ? AND c = ? AND d >= ?)

Populating the placeholders with values uses memory and CPU, adds network trafﬁc and parsing overhead, and

may make the query harder for MySQL to optimize. A four-column key isn’t a big deal, but a ten-column key

in which every column allows NULL might be.

Ascending the index might not be necessary if you know you are simply removing rows from the beginning

of the table in chunks, but not leaving any holes, so starting at the beginning of the table is actually the most

efﬁcient thing to do.

See also --ascend-first. See “EXTENDING” for a discussion of how this interacts with plugins.

-no-delete

Do not delete archived rows.

Causes pt-archiver not to delete rows after processing them. This disallows --no-ascend, because enabling

them both would cause an inﬁnite loop.

If there is a plugin on the source DSN, its before_delete method is called anyway, even though pt-archiver

will not execute the delete. See “EXTENDING” for more on plugins.

-optimize

type: string

Run OPTIMIZE TABLE afterwards on --source and/or --dest.

Runs OPTIMIZE TABLE after ﬁnishing. See --analyze for the option syntax and

http://dev.mysql.com/doc/en/optimize-table.html for details on OPTIMIZE TABLE.

-password

short form: -p; type: string

Password to use when connecting. If password contains commas they must be escaped with a backslash:

“exam,ple”

-pid

type: string

Create the given PID ﬁle. The tool won’t start if the PID ﬁle already exists and the PID it contains is different

than the current PID. However, if the PID ﬁle exists and the PID it contains is no longer running, the tool will

overwrite the PID ﬁle with the current PID. The PID ﬁle is removed automatically when the tool exits.

-plugin

type: string

Perl module name to use as a generic plugin.

Specify the Perl module name of a general-purpose plugin. It is currently used only for statistics (see

--statistics) and must have new() and a statistics() method.

The new( src = $src, dst => $dst, opts => $o )> method gets the source and destination DSNs, and their

database connections, just like the connection-speciﬁc plugins do. It also gets an OptionParser object ($o) for

accessing command-line options (example: ‘‘$o-‘‘get(‘purge’);>).

The statistics(\%stats, $time) method gets a hashref of the statistics collected by the archiving

job, and the time the whole job started.

2.2. pt-archiver 15

Percona Toolkit Documentation, Release 2.2.20

-port

short form: -P; type: int

Port number to use for connection.

-primary-key-only

Primary key columns only.

A shortcut for specifying --columns with the primary key columns. This is an efﬁciency if you just want

to purge rows; it avoids fetching the entire row, when only the primary key columns are needed for DELETE

statements. See also --purge.

-progress

type: int

Print progress information every X rows.

Prints current time, elapsed time, and rows archived every X rows.

-purge

Purge instead of archiving; allows omitting --file and --dest.

Allows archiving without a --file or --dest argument, which is effectively a purge since the rows are just

deleted.

If you just want to purge rows, consider specifying the table’s primary key columns with

--primary-key-only. This will prevent fetching all columns from the server for no reason.

-quick-delete

Adds the QUICK modiﬁer to DELETE statements.

See http://dev.mysql.com/doc/en/delete.html for details. As stated in the documentation, in some cases it may

be faster to use DELETE QUICK followed by OPTIMIZE TABLE. You can use --optimize for this.

-quiet

short form: -q

Do not print any output, such as for --statistics.

Suppresses normal output, including the output of --statistics, but doesn’t suppress the output from

--why-quit.

-replace

Causes INSERTs into --dest to be written as REPLACE.

-retries

type: int; default: 1

Number of retries per timeout or deadlock.

Speciﬁes the number of times pt-archiver should retry when there is an InnoDB lock wait timeout or deadlock.

When retries are exhausted, pt-archiver will exit with an error.

Consider carefully what you want to happen when you are archiving between a mixture of transactional and

non-transactional storage engines. The INSERT to --dest and DELETE from --source are on separate

connections, so they do not actually participate in the same transaction even if they’re on the same server.

However, pt-archiver implements simple distributed transactions in code, so commits and rollbacks should

happen as desired across the two connections.

At this time I have not written any code to handle errors with transactional storage engines other than InnoDB.

Request that feature if you need it.

-run-time

type: time

16 Chapter 2. Tools

剩余448页未读，继续阅读

yurongga

粉丝: 2
资源: 16

最新资源