Percona XtraBackup 2.4：InnoDB热备份的开源解决方案

5星 · 超过95%的资源需积分: 14 11 浏览量更新于2024-07-20 收藏 784KB PDF 举报

Percona XtraBackup是一款专为MySQL数据库设计的开源热备份工具，其主要目标是实现对InnoDB、XtraDB以及MyISAM表在MySQL 5.1、5.5、5.6和5.7服务器，以及Percona Server（配备XtraDB）环境下的数据备份，而不会锁定数据库，确保在备份过程中服务器的正常运行。此工具特别适用于24/7高负载或低事务量环境，旨在提供无中断的备份流程，确保生产环境中的性能稳定。在Percona XtraBackup 2.4.7版本的文档中，包含了丰富的内容，包括但不限于： 1. **介绍**：简要概述了XtraBackup的功能、优点和适用场景，强调了它是商业备份工具InnoDB Hotbackup的替代方案，特别提及了对InnoDB 5.1内置支持的移除（从2.1版开始）。 2. **安装**：详细指导用户如何安装和配置XtraBackup，可能涉及不同操作系统上的步骤和依赖项。 3. **前置条件**：列出了进行备份操作之前需要满足的技术和环境要求，例如数据库版本兼容性、系统资源等。 4. **备份场景**：这部分会列举出各种常见的备份情况，如全备、增量备份、差异备份，以及如何针对不同类型的数据表和不同数据库状态选择合适的备份策略。 5. **用户手册**：详尽地介绍了如何使用XtraBackup进行备份、恢复和管理任务，包括命令行参数解析、备份过程控制等。 6. **高级功能**：这部分深入探讨了XtraBackup的高级特性，如多线程备份、压缩选项、远程备份、并行备份等，以及这些功能如何提升备份效率。 7. **教程、实例与指南**：提供了实用的教程和示例，帮助用户解决实际操作中的问题，如备份策略优化、故障恢复指导等。 8. **参考资料**：链接到官方文档、社区论坛、技术支持和其他相关资源，便于用户查阅更多信息和获取帮助。 9. **索引与表格**：提供了文档内容的索引，便于读者快速查找特定主题。 Percona XtraBackup作为一款强大的MySQL备份工具，它的核心优势在于在线热备份能力，以及对生产环境的低影响。对于MySQL管理员来说，了解并掌握这个工具的使用方法，对于保证数据安全性和备份效率至关重要。商业支持合同的存在也进一步保障了用户的使用体验和服务质量。

Percona XtraBackup 2.4 Documentation, Release 2.4.7

These options are passed to the mysql child process without alteration, see mysql --help for details.

Note: In case of multiple server instances the correct connection parameters (port, socket, host) must be speciﬁed in

order for xtrabackup to talk to the correct server.

Permissions and Privileges Needed

Once connected to the server, in order to perform a backup you will need READ, WRITE and EXECUTE permissions

at a ﬁlesystem level in the server’s datadir.

The database user needs the following privileges on the tables/databases to be backed up:

• RELOAD and LOCK TABLES (unless the --no-lock option is speciﬁed) in order to FLUSH TABLES WITH

READ LOCK and FLUSH ENGINE LOGS prior to start copying the ﬁles, and LOCK TABLES FOR BACKUP

and LOCK BINLOG FOR BACKUP require this privilege when Backup Locks are used,

• REPLICATION CLIENT in order to obtain the binary log position,

• CREATE TABLESPACE in order to import tables (see Restoring Individual Tables),

• PROCESS in order to run SHOW ENGINE INNODB STATUS (which is mandatory), and optionally to see all

threads which are running on the server (see Improved FLUSH TABLES WITH READ LOCK handling),

• SUPER in order to start/stop the slave threads in a replication environment, use XtraDB Changed Page Tracking

for Incremental Backups and for Improved FLUSH TABLES WITH READ LOCK handling,

• CREATE privilege in order to create the PERCONA_SCHEMA.xtrabackup_history database and table,

• INSERT privilege in order to add history records to the PERCONA_SCHEMA.xtrabackup_history table,

• SELECT privilege in order to use innobackupex --incremental-history-name or

innobackupex --incremental-history-uuid in order for the feature to look up the

innodb_to_lsn values in the PERCONA_SCHEMA.xtrabackup_history table.

The explanation of when these are used can be found in How Percona XtraBackup Works.

An SQL example of creating a database user with the minimum privileges required to full backups would be:

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';

mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON

'bkpuser'@'localhost';

mysql> FLUSH PRIVILEGES;

Conﬁguring xtrabackup

All of the xtrabackup conﬁguration is done through options, which behave exactly like standard MySQL program

options: they can be speciﬁed either at the command-line, or through a ﬁle such as /etc/my.cnf.

The xtrabackup binary reads the [mysqld] and [xtrabackup] sections from any conﬁguration ﬁles, in that

order. That is so that it can read its options from your existing MySQL installation, such as the datadir or some of the

InnoDB options. If you want to override these, just specify them in the [xtrabackup] section, and because it is

read later, it will take precedence.

You don’t need to put any conﬁguration in your my.cnf if you don’t want to. You can simply specify the options

on the command-line. Normally, the only thing you might ﬁnd convenient to place in the [xtrabackup] section

of your my.cnf ﬁle is the target_dir option to default the directory in which the backups will be placed, for

example:

3.2. Conﬁguring xtrabackup 13

CHAPTER

FOUR

BACKUP SCENARIOS

The Backup Cycle - Full Backups

Creating a backup

To create a backup, run xtrabackup with the xtrabackup --backup option. You also need to specify a

xtrabackup --target-dir option, which is where the backup will be stored, if the InnoDB data or log ﬁles

aren’t stored in the same directory, you might need to specify the location of those, too. If the target directory does not

exist, xtrabackup creates it. If the directory does exist and is empty, xtrabackup will succeed. xtrabackup

will not overwrite existing ﬁles, it will fail with operating system error 17, file exists.

To start the backup process run:

$ xtrabackup --backup --target-dir=/data/backups/

This will store the backup at /data/backups/. If you specify a relative path, the target directory will be relative

to the current directory.

During the backup process, you should see a lot of output showing the data ﬁles being copied, as well as the log ﬁle

thread repeatedly scanning the log ﬁles and copying from it. Here is an example that shows the log thread scanning

the log in the background, and a ﬁle copying thread working on the ibdata1 ﬁle:

160906 10:19:17 Finished backing up non-InnoDB tables and files

160906 10:19:17 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (for incremental): '62988944'

xtrabackup: Stopping log copying thread.

.160906 10:19:18 >> log scanned up to (137343534)

160906 10:19:18 Executing UNLOCK TABLES

160906 10:19:18 All tables unlocked

160906 10:19:18 Backup created in directory '/data/backups/'

160906 10:19:18 [00] Writing backup-my.cnf

160906 10:19:18 [00] ...done

160906 10:19:18 [00] Writing xtrabackup_info

160906 10:19:18 [00] ...done

xtrabackup: Transaction log of lsn (26970807) to (137343534) was copied.

160906 10:19:18 completed OK!

The last thing you should see is something like the following, where the value of the <LSN> will be a number that

depends on your system:

xtrabackup: Transaction log of lsn (<SLN>) to (<LSN>) was copied.

Note: Log copying thread checks the transactional log every second to see if there were any new log records written

that need to be copied, but there is a chance that the log copying thread might not be able to keep up with the amount

Percona XtraBackup 2.4 Documentation, Release 2.4.7

of writes that go to the transactional logs, and will hit an error when the log records are overwritten before they could

be read.

After the backup is ﬁnished, the target directory will contain ﬁles such as the following, assuming you have a single

InnoDB table test.tbl1 and you are using MySQL’s innodb_ﬁle_per_table option:

$ ls -lh /data/backups/

total 182M

drwx------ 7 root root 4.0K Sep 6 10:19 .

drwxrwxrwt 11 root root 4.0K Sep 6 11:05 ..

-rw-r----- 1 root root 387 Sep 6 10:19 backup-my.cnf

-rw-r----- 1 root root 76M Sep 6 10:19 ibdata1

drwx------ 2 root root 4.0K Sep 6 10:19 mysql

drwx------ 2 root root 4.0K Sep 6 10:19 performance_schema

drwx------ 2 root root 4.0K Sep 6 10:19 sbtest

drwx------ 2 root root 4.0K Sep 6 10:19 test

drwx------ 2 root root 4.0K Sep 6 10:19 world2

-rw-r----- 1 root root 116 Sep 6 10:19 xtrabackup_checkpoints

-rw-r----- 1 root root 433 Sep 6 10:19 xtrabackup_info

-rw-r----- 1 root root 106M Sep 6 10:19 xtrabackup_logfile

The backup can take a long time, depending on how large the database is. It is safe to cancel at any time, because it

does not modify the database.

The next step is getting your backup ready to be restored.

Preparing a backup

After you made a backup with the xtrabackup --backup option, you’ll ﬁrst need to prepare it in order to restore

it. Data ﬁles are not point-in-time consistent until they’ve been prepared, because they were copied at different times

as the program ran, and they might have been changed while this was happening. If you try to start InnoDB with these

data ﬁles, it will detect corruption and crash itself to prevent you from running on damaged data. The xtrabackup

--prepare step makes the ﬁles perfectly consistent at a single instant in time, so you can run InnoDB on them.

You can run the prepare operation on any machine; it does not need to be on the originating server or the server to

which you intend to restore. You can copy the backup to a utility server and prepare it there.

Note: You can prepare a backup created with older Percona XtraBackup version with a newer one, but not vice versa.

Preparing a backup on an unsupported server version should be done with the latest Percona XtraBackup release

which supports that server version. For example, if one has a backup of MySQL 5.0 created with Percona XtraBackup

1.6, then preparing the backup with Percona XtraBackup 2.3 is not supported, because support for MySQL 5.0 was

removed in Percona XtraBackup 2.1. Instead, the latest release in the 2.0 series should be used.

During the prepare operation, xtrabackup boots up a kind of modiﬁed InnoDB that’s embedded inside it (the li-

braries it was linked against). The modiﬁcations are necessary to disable InnoDB’s standard safety checks, such as

complaining that the log ﬁle isn’t the right size, which aren’t appropriate for working with backups. These modiﬁca-

tions are only for the xtrabackup binary; you don’t need a modiﬁed InnoDB to use xtrabackup for your backups.

The prepare step uses this “embedded InnoDB” to perform crash recovery on the copied data ﬁles, using the copied log

ﬁle. The prepare step is very simple to use: you simply run xtrabackup with the xtrabackup --prepare

option and tell it which directory to prepare, for example, to prepare the previously taken backup run:

$ xtrabackup --prepare --target-dir=/data/backups/

4.1. The Backup Cycle - Full Backups 16

Percona XtraBackup 2.4 Documentation, Release 2.4.7

When this ﬁnishes, you should see an InnoDB shutdown with a message such as the following, where again the

value of LSN will depend on your system:

InnoDB: Shutdown completed; log sequence number 137345046

160906 11:21:01 completed OK!

All following prepares will not change the already prepared data ﬁles, you’ll see that output says:

xtrabackup: This target seems to be already prepared.

xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.

It is not recommended to interrupt xtrabackup process while preparing backup because it may cause data ﬁles corrup-

tion and backup will become unusable. Backup validity is not guaranteed if prepare process was interrupted.

Note: If you intend the backup to be the basis for further incremental backups, you should use the xtrabackup

--apply-log-only option when preparing the backup, or you will not be able to apply incremental backups to it.

See the documentation on preparing incremental backups for more details.

Restoring a Backup

Warning: Backup needs to be prepared before it can be restored.

For convenience xtrabackup binary has an xtrabackup --copy-back option, which will copy the backup to

the server’s datadir:

$ xtrabackup --copy-back --target-dir=/data/backups/

If you don’t want to save your backup, you can use the xtrabackup --move-back option which will move the

backed up data to the datadir.

If you don’t want to use any of the above options, you can additionally use rsync or cp to restore the ﬁles.

Note: The datadir must be empty before restoring the backup. Also it’s important to note that MySQL server needs

to be shut down before restore is performed. You can’t restore to a datadir of a running mysqld instance (except when

importing a partial backup).

Example of the rsync command that can be used to restore the backup can look like this:

$ rsync -avrP /data/backup/ /var/lib/mysql/

You should check that the restored ﬁles have the correct ownership and permissions.

As ﬁles’ attributes will be preserved, in most cases you will need to change the ﬁles’ ownership to mysql before

starting the database server, as they will be owned by the user who created the backup:

$ chown -R mysql:mysql /var/lib/mysql

Data is now restored and you can start the server.

4.1. The Backup Cycle - Full Backups 17

剩余163页未读，继续阅读

QXA66

粉丝: 2
资源: 7

Percona XtraBackup 2.4：InnoDB热备份的开源解决方案

percona-xtrabackup-80-8.0.11-1.el7.x86_64.rpm

Percona-XtraBackup-2.4.9.zip

xtrabackup

percona-xtrabackup

percona-xtrabackup-8.0.35

percona-xtrabackup-2.2.7

percona-xtrabackup-2.0.0

Percona-XtraBackup-8.0.4

percona-xtrabackup2.4

bitnami-docker-percona-xtrabackup

最新资源