Percona-XtraBackup.pdf-资料库

u014151871-9819546-16359647593925798471.pdf-第1页.png

第1页 / 共164页

u014151871-9819546-16359647593925798471.pdf-第2页.png

第2页 / 共164页

u014151871-9819546-16359647593925798471.pdf-第3页.png

第3页 / 共164页

u014151871-9819546-16359647593925798471.pdf-第4页.png

第4页 / 共164页

u014151871-9819546-16359647593925798471.pdf-第5页.png

第5页 / 共164页

u014151871-9819546-16359647593925798471.pdf-第6页.png

第6页 / 共164页

u014151871-9819546-16359647593925798471.pdf-第7页.png

第7页 / 共164页

u014151871-9819546-16359647593925798471.pdf-第8页.png

第8页 / 共164页

Percona XtraBackup 2.4 Documentation Release 2.4.7 Percona LLC and/or its afﬁliates April 18, 2017

1 Introduction 2 Installation 3 Prerequisites 4 Backup Scenarios 5 User’s Manual 6 Advanced Features 7 Tutorials, Recipes, How-tos 8 References 9 Indices and tables Index CONTENTS 2 6 12 15 25 75 80 100 155 156 i

Percona XtraBackup 2.4 Documentation, Release 2.4.7 Percona XtraBackup is an open-source hot backup utility for MySQL - based servers that doesn’t lock your database during the backup. It can back up data from InnoDB, XtraDB, and MyISAM tables on MySQL 5.1 1, 5.5, 5.6 and 5.7 servers, as well as Percona Server with XtraDB. For a high-level overview of many of its advanced features, including a feature comparison, please see About Percona XtraBackup. Whether it is a 24x7 highly loaded server or a low-transaction-volume environment, Percona XtraBackup is designed to make backups a seamless procedure without disrupting the performance of the server in a production environment. Commercial support contracts are available. 1 Support for InnoDB 5.1 builtin has been removed in Percona XtraBackup 2.1 CONTENTS 1

CHAPTER ONE INTRODUCTION About Percona XtraBackup Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following beneﬁts: • Backups that complete quickly and reliably • Uninterrupted transaction processing during backups • Savings on disk space and network bandwidth • Automatic backup veriﬁcation • Higher uptime due to faster restore time Percona XtraBackup makes MySQL hot backups for all versions of Percona Server, MySQL, and MariaDB. It performs streaming, compressed, and incremental MySQL backups. Percona XtraBackup works with MySQL, MariaDB, and Percona Server. It supports completely non-blocking backups of InnoDB, XtraDB, and HailDB storage engines. In addition, it can back up the following storage engines by brieﬂy pausing writes at the end of the backup: MyISAM, Merge, and Archive, including partitioned tables, triggers, and database options. Percona’s enterprise-grade commercial MySQL Support contracts include support for Percona XtraBackup. We rec- ommend support for critical production deployments. MySQL Backup Tool Feature Comparison Features License Price Percona XtraBackup GPL Free Streaming and encryption formats Supported MySQL ﬂavors Supported operating systems Open source MySQL, Percona Server, MariaDB, Percona XtraDB Cluster, MariaDB Galera Cluster Linux Non-blocking InnoDB backups 1 Blocking MyISAM backups Incremental backups Yes Yes Yes MySQL Enterprise backup Proprietary Included in subscription at $5000 per Server Proprietary MySQL Linux, Solaris, Windows, OSX, FreeBSD. Yes Yes Yes Continued on next page 2

Percona XtraBackup 2.4 Documentation, Release 2.4.7 Table 1.1 – continued from previous page Features Full compressed backups Incremental compressed backups Fast incremental backups 2 Incremental backups with archived logs feature in Percona Server Incremental backups with REDO log only Backup locks 8 Encrypted backups Streaming backups Parallel local backups Parallel compression Parallel encryption Parallel apply-log Parallel copy-back Partial backups Partial backups of individual parti- tions Throttling 4 Backup image validation Point-in-time recovery support Safe slave backups Compact backups 5 Buffer pool state backups Individual tables export Individual partitions export Restoring tables server 7 Data & index ﬁle statistics InnoDB secondary indexes defrag- mentation rsync support to minimize lock time Improved FTWRL handling Backup history table Backup progress table Ofﬂine backups Backup to tape media managers Cloud backups support External graphical user interfaces to backup/recovery to a different Percona XtraBackup Yes Yes Yes Yes MySQL Enterprise backup Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Zmanda Recovery Manager MySQL for Yes Yes 3 Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes 6 Yes Yes Yes Yes Yes Amazon S3 MySQL Workbench, MySQL En- terprise Monitor 1.1. About Percona XtraBackup 3

Percona XtraBackup 2.4 Documentation, Release 2.4.7 What are the features of Percona XtraBackup? Here is a short list of Percona XtraBackup features. See the documentation for more. • Create hot InnoDB backups without pausing your database • Make incremental backups of MySQL • Stream compressed MySQL backups to another server • Move tables between MySQL servers on-line • Create new MySQL replication slaves easily • Backup MySQL without adding load to the server How Percona XtraBackup Works Percona XtraBackup is based on InnoDB‘s crash-recovery functionality. It copies your InnoDB data ﬁles, which results in data that is internally inconsistent; but then it performs crash recovery on the ﬁles to make them a consistent, usable database again. This works because InnoDB maintains a redo log, also called the transaction log. This contains a record of every change to InnoDB data. When InnoDB starts, it inspects the data ﬁles and the transaction log, and performs two steps. It applies committed transaction log entries to the data ﬁles, and it performs an undo operation on any transactions that modiﬁed data but did not commit. Percona XtraBackup works by remembering the log sequence number (LSN) when it starts, and then copying away the data ﬁles. It takes some time to do this, so if the ﬁles are changing, then they reﬂect the state of the database at different points in time. At the same time, Percona XtraBackup runs a background process that watches the transaction log ﬁles, and copies changes from it. Percona XtraBackup needs to do this continually because the transaction logs are written in a round-robin fashion, and can be reused after a while. Percona XtraBackup needs the transaction log records for every change to the data ﬁles since it began execution. Percona XtraBackup will use Backup locks where available as a lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is available in Percona Server 5.6+. Percona XtraBackup uses this automatically to copy non-InnoDB data to avoid blocking DML queries that modify InnoDB tables. When backup locks are supported by the server, xtrabackup will ﬁrst copy InnoDB data, run the LOCK TABLES FOR BACKUP and copy the MyISAM tables and .frm ﬁles. Once this is done, the backup of the ﬁles will begin. It will backup .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .par, and .opt ﬁles. Note: Locking is done only for MyISAM and other non-InnoDB tables, and only after Percona XtraBackup is ﬁnished backing up all InnoDB/XtraDB data and logs. Percona XtraBackup will use Backup locks where available as a lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is available in Percona Server 5.6+. 1InnoDB tables are still locked while copying non-InnoDB data. 2Fast incremental backups are supported for Percona Server with XtraDB changed page tracking enabled. 8Backup locks is a lightweight alternative to FLUSH TABLES WITH READ LOCK available in Percona Server 5.6+. Percona XtraBackup uses them automatically to copy non-InnoDB data to avoid blocking DML queries that modify InnoDB tables. 3Percona XtraBackup supports encryption with any kinds of backups. MySQL Enterprise Backup only supports encryption for single-ﬁle backups. sleep time between operations. 4Percona XtraBackup performs throttling based on the number of IO operations per second. MySQL Enterprise Backup supports a conﬁgurable 5Percona XtraBackup skips secondary index pages and recreates them when a compact backup is prepared. MySQL Enterprise Backup skips unused pages and reinserts on the prepare stage. 6Percona XtraBackup can export individual tables even from a full backup, regardless of the InnoDB version. MySQL Enterprise Backup uses InnoDB 5.6 transportable tablespaces only when performing a partial backup. 7Tables exported with Percona XtraBackup can be imported into Percona Server 5.1, 5.5 or 5.6+, or MySQL 5.6+. Transportable tablespaces created with MySQL Enterprise Backup can only be imported to Percona Server 5.6+, MySQL 5.6+ or MariaDB 10.0+. 1.2. How Percona XtraBackup Works 4

Percona XtraBackup 2.4 Documentation, Release 2.4.7 Percona XtraBackup uses this automatically to copy non-InnoDB data to avoid blocking DML queries that modify InnoDB tables. After that xtrabackup will use LOCK BINLOG FOR BACKUP to block all operations that might change either binary log position or Exec_Master_Log_Pos or Exec_Gtid_Set (i.e. master binary log coordinates corre- sponding to the current SQL thread state on a replication slave) as reported by SHOW MASTER/SLAVE STATUS. xtrabackup will then ﬁnish copying the REDO log ﬁles and fetch the binary log coordinates. After this is completed xtrabackup will unlock the binary log and tables. Finally, the binary log position will be printed to STDERR and xtrabackup will exit returning 0 if all went OK. Note that the STDERR of xtrabackup is not written in any ﬁle. You will have to redirect it to a ﬁle, e.g., xtrabackup OPTIONS 2> backupout.log. It will also create the following ﬁles in the directory of the backup. During the prepare phase, Percona XtraBackup performs crash recovery against the copied data ﬁles, using the copied transaction log ﬁle. After this is done, the database is ready to restore and use. The backed-up MyISAM and InnoDB tables will be eventually consistent with each other, because after the prepare (recovery) process, InnoDB‘s data is rolled forward to the point at which the backup completed, not rolled back to the point at which it started. This point in time matches where the FLUSH TABLES WITH READ LOCK was taken, so the MyISAM data and the prepared InnoDB data are in sync. The xtrabackup and innobackupex tools both offer many features not mentioned in the preceding explanation. Each tool’s functionality is explained in more detail further in the manual. In brief, though, the tools permit you to do operations such as streaming and incremental backups with various combinations of copying the data ﬁles, copying the log ﬁles, and applying the logs to the data. Restoring a backup To restore a backup with xtrabackup you can use the xtrabackup --copy-back or xtrabackup --move-back options. xtrabackup will read from the my.cnf the variables datadir, innodb_data_home_dir, innodb_data_ﬁle_path, innodb_log_group_home_dir and check that the directories exist. It will copy the MyISAM tables, indexes, etc. (.frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, par and .opt ﬁles) ﬁrst, InnoDB tables and indexes next and the log ﬁles at last. It will preserve ﬁle’s attributes when copying them, you may have to change the ﬁles’ ownership to mysql before starting the database server, as they will be owned by the user who created the backup. Alternatively, the xtrabackup --move-back option may be used to restore a backup. This option is similar to xtrabackup --copy-back with the only difference that instead of copying ﬁles it moves them to their target locations. As this option removes backup ﬁles, it must be used with caution. It is useful in cases when there is not enough free disk space to hold both data ﬁles and their backup copies. 1.2. How Percona XtraBackup Works 5

资料库

Percona-XtraBackup.pdf

相关推荐

数据库

热门标签

最新资料