Percona xtrabackup tool

Percona XtraBackup is an 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 benefits:

  • Backups that complete quickly and reliably
  • Uninterrupted transaction processing during backups
  • Savings on disk space and network bandwidth
  • Automatic backup verification
  • 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 backups to your MySQL backup database.

Percona XtraBackup is in Ubuntu 14.04 LTS packages, so to install it run in terminal:

sudo apt-get install percona-xtrabackup

The innobackupex Script

The innobackupex tool is a Perl script that acts as a wrapper for the xtrabackup C program. It is a patched version of the innobackup Perl script that Oracle distributes with the InnoDB Hot Backup tool. It enables more functionality by integrating xtrabackup and other functions such as file copying and streaming, and adds some convenience. It lets you perform point-in-time backups of InnoDB / XtraDB tables together with the schema definitions, MyISAM tables, and other portions of the server.

How to use innobackupex in detail.
Prerequisites

Connection and Privileges Needed

Percona XtraBackup needs to be able to connect to the database server and perform operations on the server and the datadir when creating a backup, when preparing in some scenarios and when restoring it. In order to do so, there are privileges and permission requirements on its execution that must be fulfilled.

Privileges refers to the operations that a system user is permitted to do in the database server. They are set at the database server and only apply to users in the database server.

Permissions are those which permits a user to perform operations on the system, like reading, writing or executing on a certain directory or start/stop a system service. They are set at a system level and only apply to system users.

Whether xtrabackup or innobackupex is used, there are two actors involved: the user invoking the program – a system user – and the user performing action in the database server – a database user. Note that these are different users in different places, even tough they may have the same username.

All the invocations of innobackupex and xtrabackup in this documentation assumes that the system user has the appropriate permissions and you are providing the relevant options for connecting the database server – besides the options for the action to be performed – and the database user has adequate privileges.

Connecting to the server

The database user used to connect to the server and its password are specified by the –user and –password option,

$ innobackupex –user=DBUSER –password=SECRET /path/to/backup/dir/
$ innobackupex –user=LUKE –password=US3TH3F0RC3 –stream=tar ./ | bzip2 –
$ xtrabackup –user=DVADER –password=14MY0URF4TH3R –backup –target-dir=/data/bkps/

If you don’t use the –user option, Percona XtraBackup will assume the database user whose name is the system user executing it.
Other Connection Options

According to your system, you may need to specify one or more of the following options to connect to the server:
Option Description
–port The port to use when connecting to the database server with TCP/IP.
–socket The socket to use when connecting to the local database.
–host The host to use when connecting to the database server with TCP/IP.

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 specified in order for innobackupex 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 filesystem 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 specified) in order to FLUSH TABLES WITH READ LOCK prior to start copying the files and
REPLICATION CLIENT in order to obtain the binary log position,
CREATE TABLESPACE in order to import tables (see Restoring Individual Tables) and
SUPER in order to start/stop the slave threads in a replication environment.

The explanation of when these are used can be found in How innobackupex 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, REPLICATION CLIENT ON *.* TO ‘bkpuser’@’localhost’;
mysql> FLUSH PRIVILEGES;

The Backup Cycle – Full Backups

Creating a Backup with innobackupex
innobackupex is the tool which provides functionality to backup a whole MySQL database instance using the xtrabackup in combination with tools like xbstream and xbcrypt.

To create a full backup, invoke the script with the options needed to connect to the server and only one argument: the path to the directory where the backup will be stored

$ innobackupex user=DBUSER password=DBUSERPASS /path/to/BACKUP-DIR/

and check the last line of the output for a confirmation message:

innobackupex: Backup created in directory ‘/path/to/BACKUP-DIR/2013-03-25_00-00-09’
innobackupex: MySQL binlog position: filename ‘mysql-bin.000003’, position 1946
111225 00:00:53 innobackupex: completed OK!

The backup will be stored within a time stamped directory created in the provided path, /path/to/BACKUP-DIR/2013-03-25_00-00-09 in this particular example.

Under the hood

innobackupex called xtrabackup binary to backup all the data of InnoDB tables (see Creating a Backup for details on this process) and copied all the table definitions in the database (.frm files), data and files related to MyISAM, MERGE (reference to other tables), CSV and ARCHIVE tables, along with triggers and database configuration information to a time stamped directory created in the provided path.

It will also create the following files for convenience on the created directory.

Other options to consider
The –no-timestamp option

This option tells innobackupex not to create a time stamped directory to store the backup:

$ innobackupex user=DBUSER password=DBUSERPASS /path/to/BACKUP-DIR/ –no-timestamp

innobackupex will create the BACKUP-DIR subdirectory (or fail if exists) and store the backup inside of it.

The –defaults-file option

You can provide other configuration file to innobackupex with this option. The only limitation is that it has to be the first option passed:

$ innobackupex –defaults-file=/tmp/other-my.cnf –user=DBUSER –password=DBUSERPASS /path/to/BACKUP-DIR/

Preparing a Full Backup with innobackupex
After creating a backup, the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data files consistent and it is the purpose of the prepare stage. Once this has been done, the data is ready to be used.

To prepare a backup with innobackupex you have to use the –apply-log and the path to the backup directory as an argument:

$ innobackupex –apply-log /path/to/BACKUP-DIR

and check the last line of the output for a confirmation on the process:

111225 1:01:57 InnoDB: Shutdown completed; log sequence number 1609228
111225 01:01:57 innobackupex: completed OK!

If it succeeded, innobackupex performed all operations needed, leaving the data ready to use immediately.

Under the hood

reading the configuration from the files in the backup directory,

innobackupex replayed the committed transactions in the log files (some transactions could have been done while the backup was being done) and rolled back the uncommitted ones. Once this is done, all the information lay in the tablespace (the InnoDB files), and the log files are re-created.

This implies calling xtrabackup –prepare twice with the right binary (determined through the xtrabackup_binary or by connecting the server). More details of this process are shown in the xtrabackup section.

Note that this preparation is not suited for incremental backups. If you perform it on the base of an incremental backup, you will not be able to “add” the increments. See Incremental Backups with innobackupex.

Other options to consider
The –use-memory option

The preparing process can be speed up by using more memory in it. It depends on the free or available RAM on your system, it defaults to 100MB. In general, the more memory available to the process, the better. The amount of memory used in the process can be specified by multiples of bytes:

$ innobackupex –apply-log –use-memory=4G /path/to/BACKUP-DIR

Restoring a Full Backup with innobackupex

For convenience, innobackupex has a –copy-back option, which performs the restoration of a backup to the server’s datadir

$ innobackupex –copy-back /path/to/BACKUP-DIR

It will copy all the data-related files back to the server’s datadir, determined by the server’s my.cnf configuration file. You should check the last line of the output for a success message:

innobackupex: Finished copying back files.

111225 01:08:13 innobackupex: completed OK!

Note

The datadir must be empty; Percona XtraBackup innobackupex –copy-back option will not copy over existing files. 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).

As files’ attributes will be preserved, in most cases you will need to change the files’ 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

Also note that all of these operations will be done as the user calling innobackupex, you will need write permissions on the server’s datadir.

Source: percona.com