Creating a backup with Innobackupex (xtrabackup)

Creating a Backup with innobackupex

innobackupex is the tool that glues xtrabackup and tar4ibd, which are specific tools, plus adding functionality to provide a single interface to backup all the data in your database server.
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/
Remote backups (work in progress)
--remote-host = <ssh connection for location to place backups>
--host= <full host to use when connection to db server with tcp/ip>
and check the last line of the output for a confirmation message:innobackupex: Backup created in directory '/path/to/BACKUP-DIR/2011-12-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/2011-12-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:

• Information related to the backup and the server
  • xtrabackup_checkpoints - The type of the backup (e.g. full or incremental), its state (e.g. prepared) and theLSNrange contained in it. This information is used for incremental backups.

• • xtrabackup_binlog_info - The binary log file used by the server and its position at the moment of the backup. Result of theSHOW MASTER STATUS.

• • xtrabackup_binlog_pos_innodb - The binary log file and its current position forInnoDBorXtraDBtables.

• • xtrabackup_binary - Thextrabackupbinary used in the process.

• • backup-my.cnf - This file contains information to start the mini instance of InnoDB during the--apply-log. This is NOT a backup of originalmy.cnf.

• • xtrabackup_logfile - Contains data needed for running the:-apply-log. The bigger this file is the -apply-logprocess will take longer to finish.
• Information related to the replication environment (if using the --slave-info option):
  • xtrabackup_slave_infoTheCHANGEMASTERstatement needed for setting up a slave.
• The output of mysqld during the backup process:
  • mysql-stderr
  • mysql-stdout

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/