Percona Toolkit：MySQL管理与备份的高级工具指南

需积分: 0 60 浏览量更新于2024-07-19 收藏 1.35MB PDF 举报

本资源是一份关于MySQL管理工具的详细指南，特别是针对Percona Toolkit的使用文档，适用于版本3.0.6。发布于2017年，由Percona LLC及其关联公司提供，更新日期为2018年1月3日。这份文档涵盖了四个主要部分：获取Percona Toolkit、工具介绍、配置管理和杂项内容。 **重要知识点：** 1. **Percona Toolkit简介**： Percona Toolkit是Percona开发的一套高级命令行工具集，专为MySQL、MongoDB以及系统任务设计，旨在解决那些难以或不适合手动完成的复杂操作。它作为专业的替代品，具备正式测试和完整文档支持，便于安装且无需额外库依赖，确保了可靠性和易用性。 2. **历史与起源**：这些工具源于Maatkit和Aspersa，两个广受欢迎的MySQL服务器管理工具包。Percona Toolkit在这些基础上发展而来，并由Percona团队持续开发和维护。访问Percona的官方网站（<http://www.percona.com/software/>）可以获取更多关于Percona和其他免费开源软件的信息。 3. **获取Percona Toolkit**：第一部分详细介绍了如何安装Percona Toolkit，包括可能的安装方法和步骤，对于初次使用者来说这是关键的章节，因为正确的工具安装是后续使用的基石。 4. **工具集**： II部分列出了套装中的各种工具，每个工具都有其独特的功能，例如数据备份、性能监控、故障排查等。用户可以根据具体需求选择并学习如何利用这些工具进行高效管理。 5. **配置管理**： III部分涉及工具的配置选项和最佳实践，这对于调整工具以适应特定环境和优化性能至关重要。这可能包括配置参数调整、设置备份策略等。 6. **杂项内容**： IV部分可能包含了一些附加信息，如工具的升级指南、常见问题解答、版本更新记录等，帮助用户更好地理解和使用该工具集。通过阅读这份文档，管理员可以提升MySQL服务器的管理效率，确保系统的稳定运行，并了解如何利用Percona Toolkit进行高效的数据备份、性能分析和日常运维工作。这份文档的实用性对于任何希望优化MySQL数据库管理的用户来说都是宝贵的资源。

Percona Toolkit Documentation, Release 3.0.6

12 Chapter 2. pt-align

Percona Toolkit Documentation, Release 3.0.6

DESCRIPTION

pt-archiver is the tool I use to archive tables as described in http://tinyurl.com/mysql-archiving. The goal is a

low-impact, forward-only job to nibble old data out of the table without impacting OLTP queries much. You can insert

the data into another table, which need not be on the same server. You can also write it to a ﬁle in a format suitable for

LOAD DATA INFILE. Or you can do neither, in which case it’s just an incremental DELETE.

pt-archiver is extensible via a plugin mechanism. You can inject your own code to add advanced archiving logic

that could be useful for archiving dependent data, applying complex business rules, or building a data warehouse

during the archiving process.

You need to choose values carefully for some options. The most important are --limit, --retries, and

--txn-size.

The strategy is to ﬁnd the ﬁrst row(s), then scan some index forward-only to ﬁnd more rows efﬁciently. Each subse-

quent query should not scan the entire table; it should seek into the index, then scan until it ﬁnds more archivable rows.

Specifying the index with the ‘i’ part of the --source argument can be crucial for this; use --dry-run to examine

the generated queries and be sure to EXPLAIN them to see if they are efﬁcient (most of the time you probably want

to scan the PRIMARY key, which is the default). Even better, examine the difference in the Handler status counters

before and after running the query, and make sure it is not scanning the whole table every query.

You can disable the seek-then-scan optimizations partially or wholly with --no-ascend and --ascend-first.

Sometimes this may be more efﬁcient for multi-column keys. Be aware that pt-archiver is built to start at the

beginning of the index it chooses and scan it forward-only. This might result in long table scans if you’re trying to

nibble from the end of the table by an index other than the one it prefers. See --source and read the documentation

on the i part if this applies to you.

Percona XtraDB Cluster

pt-archiver works with Percona XtraDB Cluster (PXC) 5.5.28-23.7 and newer, but there are three limitations you

should consider before archiving on a cluster:

Error on commit

pt-archiver does not check for error when it commits transactions. Commits on PXC can fail, but

the tool does not yet check for or retry the transaction when this happens. If it happens, the tool will die.

MyISAM tables

Archiving MyISAM tables works, but MyISAM support in PXC is still experimental at the time of

this release. There are several known bugs with PXC, MyISAM tables, and AUTO_INCREMENT

columns. Therefore, you must ensure that archiving will not directly or indirectly result in the use of

default AUTO_INCREMENT values for a MyISAM table. For example, this happens with --dest if

--columns is used and the AUTO_INCREMENT column is not included. The tool does not check for

this!

Non-cluster options

Certain options may or may not work. For example, if a cluster node is not also a slave, then

--check-slave-lag does not work. And since PXC tables are usually InnoDB, but InnoDB doesn’t

support INSERT DELAYED, then --delayed-insert does not work. Other options may also not

work, but the tool does not check them, therefore you should test archiving on a test cluster before archiv-

ing on your real cluster.

14 Chapter 3. pt-archiver

Percona Toolkit Documentation, Release 3.0.6

OUTPUT

If you specify --progress, the output is a header row, plus status output at intervals. Each row in the status output

lists the current date and time, how many seconds pt-archiver has been running, and how many rows it has

archived.

If you specify --statistics, pt-archiver outputs timing and other information to help you identify which

part of your archiving process takes the most time.

ERROR-HANDLING

pt-archiver tries to catch signals and exit gracefully; for example, if you send it SIGTERM (Ctrl-C on UNIX-

ish systems), it will catch the signal, print a message about the signal, and exit fairly normally. It will not execute

--analyze or --optimize, because these may take a long time to ﬁnish. It will run all other code normally,

including calling after_ﬁnish() on any plugins (see “EXTENDING”).

In other words, a signal, if caught, will break out of the main archiving loop and skip optimize/analyze.

OPTIONS

Specify at least one of --dest, --file, or --purge.

--ignore and --replace are mutually exclusive.

--txn-size and --commit-each are mutually exclusive.

--low-priority-insert and --delayed-insert are mutually exclusive.

--share-lock and --for-update are mutually exclusive.

--analyze and --optimize are mutually exclusive.

--no-ascend and --no-delete are mutually exclusive.

DSN values in --dest default to values from --source if COPY is yes.

--analyze

type: string

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

Runs ANALYZE TABLE after ﬁnishing. The argument is an arbitrary string. If it contains the letter ‘s’, the

source will be analyzed. If it contains ‘d’, the destination will be analyzed. You can specify either or both. For

example, the following will analyze both:

--analyze=ds

See http://dev.mysql.com/doc/en/analyze-table.html for details on ANALYZE TABLE.

--ascend-first

Ascend only ﬁrst column of index.

If you do want to use the ascending index optimization (see --no-ascend), but do not want to incur the

overhead of ascending a large multi-column index, you can use this option to tell pt-archiver to ascend

only the leftmost column of the index. This can provide a signiﬁcant performance boost over not ascending the

index at all, while avoiding the cost of ascending the whole index.

See “EXTENDING” for a discussion of how this interacts with plugins.

3.6. OUTPUT 15

Percona Toolkit Documentation, Release 3.0.6

--ask-pass

Prompt for a password when connecting to MySQL.

--buffer

Buffer output to --file and ﬂush at commit.

Disables autoﬂushing to --file and ﬂushes --file to disk only when a transaction commits. This typically

means the ﬁle is block-ﬂushed by the operating system, so there may be some implicit ﬂushes to disk between

commits as well. The default is to ﬂush --file to disk after every row.

The danger is that a crash might cause lost data.

The performance increase I have seen from using --buffer is around 5 to 15 percent. Your mileage may vary.

--bulk-delete

Delete each chunk with a single statement (implies --commit-each).

Delete each chunk of rows in bulk with a single DELETE statement. The statement deletes every row between

the ﬁrst and last row of the chunk, inclusive. It implies --commit-each, since it would be a bad idea to

INSERT rows one at a time and commit them before the bulk DELETE.

The normal method is to delete every row by its primary key. Bulk deletes might be a lot faster. They also

might not be faster if you have a complex WHERE clause.

This option completely defers all DELETE processing until the chunk of rows is ﬁnished. If you have a plugin

on the source, its before_delete method will not be called. Instead, its before_bulk_delete method

is called later.

WARNING: if you have a plugin on the source that sometimes doesn’t return true from is_archivable(),

you should use this option only if you understand what it does. If the plugin instructs pt-archiver not to

archive a row, it will still be deleted by the bulk delete!

--[no]bulk-delete-limit

default: yes

Add --limit to --bulk-delete statement.

This is an advanced option and you should not disable it unless you know what you are doing and why! By

default, --bulk-delete appends a --limit clause to the bulk delete SQL statement. In certain cases, this

clause can be omitted by specifying --no-bulk-delete-limit. --limit must still be speciﬁed.

--bulk-insert

Insert each chunk with LOAD DATA INFILE (implies --bulk-delete --commit-each).

Insert each chunk of rows with LOAD DATA LOCAL INFILE. This may be much faster than inserting a row

at a time with INSERT statements. It is implemented by creating a temporary ﬁle for each chunk of rows, and

writing the rows to this ﬁle instead of inserting them. When the chunk is ﬁnished, it uploads the rows.

To protect the safety of your data, this option forces bulk deletes to be used. It would be unsafe to delete each

row as it is found, before inserting the rows into the destination ﬁrst. Forcing bulk deletes guarantees that the

deletion waits until the insertion is successful.

The --low-priority-insert, --replace, and --ignore options work with this option, but

--delayed-insert does not.

If LOAD DATA LOCAL INFILE throws an error in the lines of The used command is not

allowed with this MySQL version, refer to the documentation for the L DSN option.

--charset

short form: -A; type: string

16 Chapter 3. pt-archiver

剩余487页未读，继续阅读

香蕉花生

粉丝: 0
资源: 1

Percona Toolkit：MySQL管理与备份的高级工具指南

MySql Backup Release 自動備份All MySQL 附說明文件檔

MTK-MySQLBackup.zip

Python-mysqlbackup提供一种简单方法来备份和恢复运行在容器中的MySQL数据库

mysqlbackup

mySQLbackup.zip_mysql delphi_mysqlbackup_mysql备份 delphi

mysqlbackup.rar_delphi mysql_mysql 备份_mysqlbackup_备份 delphi

mysqlbackup:MySQL备份

MySQLBackupFTP

mysqlbackup.sh

MySQLBackup-开源

最新资源