Percona XtraBackup is an Open Source online (non-blocking) backup solution for the InnoDB and XtraDB engines. It works with MySQL versions 5.0 and 5.1 (The InnoDB Plugin and Barracuda format is supported as of release 0.9.5rc). It can also backup MyISAM tables. XtraBackup is distributed under the GPL v2 license.

There two main tools:

• xtrabackup - a binary, which copies only InnoDB/XtraDB tablespaces
• innobackupex - a script based on the innobackup Perl script, distributed by Oracle/InnoDB under the GPL license. The script was modified to work with the xtrabackup binary and accept some new parameters. innobackupex provides functionality to backup a whole MySQL database instance with MyISAM, InnoDB, and XtraDB tables.

• Performs online non-blocking backups of InnoDB and XtraDB tables and read-only backups for MyISAM tables
• Streams a compressed backup to disk, tape or a remote box
• Performs incremental backups

Backups can be run against standard MySQL versions 5.0 and 5.1 and any version of InnoDB (5.0, 5.1, 1.0-plugin) or XtraDB. You do not need to patch MySQL to use this utility. You will only need to patch MySQL if you are building the xtrabackup binary from source.

The source code lives on Launchpad : https://launchpad.net/percona-xtrabackup You can report bugs to the Launchpad bug system.

For general questions use the Pecona-discussions group, and for development questions the Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona directly.

Usage: [./xtrabackup [DEFAULTS OPTION] —backup | ./xtrabackup [DEFAULTS OPTION] —prepare] [OPTIONS]

Default options are read from the following files in this order: /etc/my.cnf, /opt/mysql-5.0.xx/etc/my.cnf, ~/.my.cnf

The following groups are read: mysqld xtrabackup

The following arguments may be given as options:

(The defaults options should be given as the first argument)
--print-defaults       	  Prints the program's argument list and exit.
--no-defaults         	  Don't read the default options from any file.
--defaults-file=          Read the default options from this file. 
--defaults-extra-file=    Read this file after the global options files have been read.
  --target-dir=           The destination directory for backups.
  --backup                Make a backup of a mysql instance.
  --stats                 Calculate the statistic of the datadir (it is recommended you take mysqld offline).
  --prepare               Prepare a backup so you can start mysql server with your restore.
  --export                Create files to import to another database after it has been prepared.
  --print-param           Print the parameters of mysqld that you will need for a for copyback.
  --use-memory=           This value is used instead of buffer_pool_size.
  --suspend-at-end        Creates a file called xtrabackup_suspended and waits until the
                          user deletes that file at the end of the backup.
  --throttle=             (use with --backup) Limits the IO operations (pairs of reads and writes) 
			  per second to the values set here.
  --log-stream            outputs the contents of the xtrabackup_logfile to stdout.
  --incremental-lsn=      (use with --backup) Copy only .ibd pages newer than the specified LSN high:low. 
			  ##ATTENTION##: checkpoint lsn *must* be used. Be Careful!
  --incremental-basedir=  (use with --backup) Copy only .ibd pages newer than 
			  the existing backup at the specified directory.
  --incremental-dir=      (use with --prepare) Apply .delta files and logfiles located in the specified directory.
  --tables=name           Regular Expression list of table names to be backed up.
  --create-ib-logfile     (NOT CURRENTLY IMPLEMENTED) will create ib_logfile* after a --prepare. 
			  ### If you want to create ib_logfile* only re-execute this command using the same options. ###
  --datadir=name          Path to the database root.
  --tmpdir=name           Path for temporary files. Several paths may be specified as a colon (:) separated string. 
			  If you specify multiple paths they are used round-robin.

• Too small a throttle value may make the backup endless. (We need to keep up with the source transaction log speed)

• Specifying a link for the default options file will not work, it needs to be an actual file

• datadir must be set as a command line option or in the [mysqld] section of my.cnf.

• We can also set all options in the [xtrabackup] section of the my.cnf. (If [xtrabackup] is after [mysqld], the parameters are overwritten)

Options for xtrabackup. All options are read from the [mysqld] and [xtrabackup] sections of the my.cnf unless they are overridden.

[required]: This option should be the same as the datadir variable in the [mysqld] section in your my.cnf.

[required]: This option specifies the target directory for all the backup files. If the directory doesn't exist when you use the --backup option the directory will be created. You must specify the full path.

Stores the backup data files (*.ibd) and a backup log file named xtrabackup_logfile in the directory specified by --target-dir=

Calculates the details of index statistics and outputs them to stdout. This option uses a read-only instance of InnoDB. It also uses the --use-memory= option.

...
  table: test/t1, index: GEN_CLUST_INDEX, space id: 49, root page 3
  estimated statistics in dictionary:
    key vals: 4, leaf pages 133, size pages 161
  real statistics:
     level 1 pages: pages=1, data=32 bytes, data/pages=0%
        leaf pages: recs=7, pages=2, data=18149 bytes, data/pages=55%
    external pages: pages=131, data=2128384 bytes, data/pages=99%

  table: tpcc/customer, index: PRIMARY, space id: 47, root page 3
  estimated statistics in dictionary:
    key vals: 29948, leaf pages 1146, size pages 1187
  real statistics:
     level 2 pages: pages=1, data=38 bytes, data/pages=0%
     level 1 pages: pages=2, data=21774 bytes, data/pages=66%
        leaf pages: recs=30000, pages=1146, data=17085634 bytes, data/pages=90%

  table: tpcc/customer, index: idx_customer, space id: 47, root page 4
  estimated statistics in dictionary:
    key vals: 27905, leaf pages 105, size pages 161
  real statistics:
     level 1 pages: pages=1, data=4520 bytes, data/pages=27%
        leaf pages: recs=30000, pages=105, data=1168129 bytes, data/pages=67%
...

“estimated statistics in dictionary:” may be used as result of “analyze table” in the innodb internal to be used for the query optimizer.

“real statistics:” is the result of scan.

–

“level X pages:” is root or branch pages of the tree index. (max X is root page)

“leaf pages:” is leaf page which contains the whole of the each record.

“external pages:” is external page to store big size value which cannot be stored in the leaf page.

–

“recs”: real number of all records in leaf pages

“pages”: number of pages

“data”: sum of data in pages [bytes]

“data/pages”: (data / (pages * PAGE_SIZE)) * 100% So, it never reach to 100% because “pages” contains some header/footer.

Prepares a backup so you can start mysqld from the files created using --backup. It applies the log records in the xtrabackup_logfile to the data files.

This option is used by the innobackupex script. It generates target mysqld information for an innobackupex --copy-back operation.

# This MySQL options file was generated by XtraBackup.
[mysqld]
datadir = /opt/mysql-5.0.67/data
innodb_data_home_dir = /opt/mysql-5.0.67/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql-5.0.67/data

This option is same as in the other MySQL tools, it specifies the my.cnf to be read.

This option is same as in the other MySQL tools. It can specify the additional cnf file to be read.

This option is used with --prepare or --stats. This is not the same as the option for ibbackup. It is used as the innodb_buffer_pool_size of the InnoDB instance when you run --prepare. The default value is 100MB, 1 or 2GB is recommended if you have the available memory.

This option is used with --prepare. It outputs clean .ibd files and .exp files for 'ALTER TABLE … IMPORT TABLESPACE' command innodb_expand_import option enabled XtraDB. .exp file is generated at the same place of the each .ibd file

This option is used with --backup. creates a file xtrabackup_suspended and waits until the user deletes that file at the end. It is used in backup scripts for synchronizing the end position of the backup transaction log file to the another backup.

This option is for --backup. outputs the contents of the xtrabackup_logfile to stdout. --suspend-at-end is added automatically. It may be useful when you use your own backup tool. innobackupex --stream mode also uses this option.)

This option is for --backup. Copy only .ibd pages newer than specified LSN 'high:low'.

The checkpoint LSN must be used. (The checkpoint LSN of the previous backup is printed out when --backup, and stored in the xtrabackup_checkpoints file)

This option is for an incremental backup. Copy only .ibd pages newer than backup at specified directory. (xtrabackup_checkpoints of the directory is read and the LSN is used automatically.)

This option is for ”--prepare”. Apply the incremental backup files (*.delta & xtrabackup_logfile) in the specified directory.

This option only backs up the file-per-table datafiles whose table name db_name.table_name matches the regular expression given. It is also used with the --stats option.

This option is used with --backup. It limits count of IO operations (pairs of reads and writes) per second to the value given. (with the --incremental mode, it is based on read IO)

(Not currently implemented) This option is used with --prepare.

attention: In current version, you should re-execute ”–prepare”, if you want ib_logfile* here.

(Not currently implemented) This will override the value from your my.cnf.

InnoDB options for the target mysqld. Ordinarily these are read from your my.cnf.

The --backup command makes a backup of InnoDB/XTraDB tablespaces. It reads the InnoDB data and log file information from the my.cnf specified on the command line (--defaults-file=) or if none is specified it looks in the default locations (/etc/my.cnf, etc…) It stores a backup of data files and a backup log file (named xtrabackup_logfile) in the directory specified using --target-dir=.

* The current version can only handle an xtrabackup_logfile larger than 4GB in the --prepare step in a 64-bit architecture.

--prepare applies the log records in 'xtrabackup_logfile' to the data files, and creates new log files as specified in target-dir). After this prepared directory can be used by MySQL.

In the current version you should execute --prepare a second time if you want to create ib_logfile* (This is restriction of the alpha version)

Restore the prepared files to the original place using innobackupex --copy-back or you can use cp -r.

Make sure to check the permissions of the restored files prior to restarting mysqld

(full backup)
# ./xtrabackup --backup --target-dir=/backup/base
...

(incremental backup)
# ./xtrabackup --backup --target-dir=/backup/delta --incremental-basedir=/backup/base
...



(prepare)
# ./xtrabackup --prepare --target-dir=/backup/base
...

(apply incremental backup)
# ./xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/delta
...

Say you need to backup only one or several tables. It is possible using –include option of innobackupex (which is effectively –tables of xtrabackup).

This feature works only if innodb_file_per_table is set

# innobackupex-1.5.1 --include=sakila.actor* .

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackup
           prints "innobackup completed OK!".

innobackupex: Using mysql  Ver 14.14 Distrib 5.1.39, for unknown-linux-gnu (x86_64) using readline 5.1
innobackupex: Using mysql server version 5.1.39-percona-log

innobackupex: Created backup directory /mnt/backups/2009-11-10_12-55-53
091110 12:55:53  innobackupex: Starting mysql with options: --unbuffered --
091110 12:55:53  innobackupex: Connected to database with mysql child process (pid=30632)
091110 12:55:57  innobackupex: Connection to database server closed

091110 12:55:57  innobackupex: Starting ibbackup with command: xtrabackup --backup --suspend-at-end --target-dir=/mnt/backups/2009-11-10_12-55-53 --tables='sakila.actor*'
innobackupex: Waiting for ibbackup (pid=30639) to suspend
innobackupex: Suspend file '/mnt/backups/2009-11-10_12-55-53/xtrabackup_suspended'

xtrabackup: tables regcomp(): Success
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
>> log scanned up to (0 6776520)

091110 12:55:59  innobackupex: Continuing after ibbackup has suspended
091110 12:55:59  innobackupex: Starting mysql with options: --unbuffered --
091110 12:55:59  innobackupex: Connected to database with mysql child process (pid=30646)
>> log scanned up to (0 6776520)
091110 12:56:03  innobackupex: Starting to lock all tables...
>> log scanned up to (0 6776520)
>> log scanned up to (0 6776520)
091110 12:56:13  innobackupex: All tables locked and flushed to disk

091110 12:56:13  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ and .opt files in
innobackupex: subdirectories of '/var/lib/mysql'
innobackupex: Backing up files '/var/lib/mysql/sakila/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (36 files)
innobackupex: Backing up files '/var/lib/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (65 files)
091110 12:56:13  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ and .opt files

innobackupex: Resuming ibbackup

>> log scanned up to (0 6776520)
xtrabackup  Ver 0.9.5rc Rev 101 for 5.0.84 unknown-linux-gnu (x86_64)
Copying ./ibdata1
     to /mnt/backups/2009-11-10_12-55-53/ibdata1
        ...done
Copying ./sakila/payment.ibd is skipped.
Copying ./sakila/language.ibd is skipped.
Copying ./sakila/film.ibd is skipped.
Copying ./sakila/category.ibd is skipped.
Copying ./sakila/store.ibd is skipped.
Copying ./sakila/inventory.ibd is skipped.
Copying ./sakila/actor.ibd
     to /mnt/backups/2009-11-10_12-55-53/sakila/actor.ibd
        ...done
Copying ./sakila/rental.ibd is skipped.
Copying ./sakila/staff.ibd is skipped.
Copying ./sakila/customer.ibd is skipped.
Copying ./sakila/film_actor.ibd is skipped.
Copying ./sakila/address.ibd is skipped.
Copying ./sakila/country.ibd is skipped.
Copying ./sakila/film_category.ibd is skipped.
Copying ./sakila/city.ibd is skipped.
xtrabackup: The latest check point (for incremental): '0:6776520'
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (0 6776520) to (0 6776520) was copied.
091110 12:56:15  innobackupex: All tables unlocked
091110 12:56:15  innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/mnt/backups/2009-11-10_12-55-53'
innobackupex: MySQL binlog position: filename 'mysql-bin.000005', position 3382489
091110 12:56:15  innobackupex: innobackup completed OK!

First, apply log file. 2009-11-10_12-59-17 is a directory with backup files

# innobackupex-1.5.1 --apply-log 2009-11-10_12-59-17

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackup
           prints "innobackup completed OK!".



091110 13:02:02  innobackupex: Starting ibbackup with command: xtrabackup --prepare --target-dir=/mnt/backups/
2009-11-10_12-59-17

xtrabackup: cd to /mnt/backups/2009-11-10_12-59-17
xtrabackup: This target seems to be not prepared yet.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
091110 13:02:02  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
091110 13:02:02  InnoDB: Error: table 'sakila/address'
InnoDB: in InnoDB data dictionary has tablespace id 2,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/category'
InnoDB: in InnoDB data dictionary has tablespace id 3,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/city'
InnoDB: in InnoDB data dictionary has tablespace id 4,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/country'
InnoDB: in InnoDB data dictionary has tablespace id 5,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/customer'
InnoDB: in InnoDB data dictionary has tablespace id 6,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/film'
InnoDB: in InnoDB data dictionary has tablespace id 7,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/film_actor'
InnoDB: in InnoDB data dictionary has tablespace id 8,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/film_category'
InnoDB: in InnoDB data dictionary has tablespace id 9,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/inventory'
InnoDB: in InnoDB data dictionary has tablespace id 10,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/language'
InnoDB: in InnoDB data dictionary has tablespace id 11,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/payment'
InnoDB: in InnoDB data dictionary has tablespace id 12,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/rental'
InnoDB: in InnoDB data dictionary has tablespace id 13,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/staff'
InnoDB: in InnoDB data dictionary has tablespace id 14,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:02  InnoDB: Error: table 'sakila/store'
InnoDB: in InnoDB data dictionary has tablespace id 15,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
InnoDB: Last MySQL binlog file position 0 3375100, file name ./mysql-bin.000005
091110 13:02:02  InnoDB: Started; log sequence number 0 6776520
InnoDB: Last MySQL binlog file position 0 3375100, file name ./mysql-bin.000005
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
091110 13:02:02  InnoDB: Starting shutdown...
091110 13:02:03  InnoDB: Shutdown completed; log sequence number 0 6776520
xtrabackup  Ver 0.9.5rc Rev 101 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(0 6776520)

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:


091110 13:02:03  innobackupex: Restarting xtrabackup with command: xtrabackup --prepare --target-dir=/mnt/data1/home/akuzminsky/tmp/xtrabac
kup/2009-11-10_12-59-17
for creating ib_logfile*

xtrabackup: cd to /mnt/backups/2009-11-10_12-59-17
xtrabackup: This target seems to be already prepared.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
091110 13:02:03  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
091110 13:02:03  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
091110 13:02:03  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
091110 13:02:03  InnoDB: Error: table 'sakila/address'
InnoDB: in InnoDB data dictionary has tablespace id 2,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/category'
InnoDB: in InnoDB data dictionary has tablespace id 3,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/city'
InnoDB: in InnoDB data dictionary has tablespace id 4,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/country'
InnoDB: in InnoDB data dictionary has tablespace id 5,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/customer'
InnoDB: in InnoDB data dictionary has tablespace id 6,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/film'
InnoDB: in InnoDB data dictionary has tablespace id 7,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/film_actor'
InnoDB: in InnoDB data dictionary has tablespace id 8,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/film_category'
InnoDB: in InnoDB data dictionary has tablespace id 9,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/inventory'
InnoDB: in InnoDB data dictionary has tablespace id 10,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/language'
InnoDB: in InnoDB data dictionary has tablespace id 11,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/payment'
InnoDB: in InnoDB data dictionary has tablespace id 12,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/rental'
InnoDB: in InnoDB data dictionary has tablespace id 13,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/staff'
InnoDB: in InnoDB data dictionary has tablespace id 14,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091110 13:02:03  InnoDB: Error: table 'sakila/store'
InnoDB: in InnoDB data dictionary has tablespace id 15,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
InnoDB: Last MySQL binlog file position 0 3375100, file name ./mysql-bin.000005
091110 13:02:03  InnoDB: Started; log sequence number 0 6776844
InnoDB: Last MySQL binlog file position 0 3375100, file name ./mysql-bin.000005
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
091110 13:02:03  InnoDB: Starting shutdown...
091110 13:02:05  InnoDB: Shutdown completed; log sequence number 0 6776844
xtrabackup  Ver 0.9.5rc Rev 101 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:

091110 13:02:05  innobackupex: innobackup completed OK!

Then, copy the MySQL files back

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackup
           prints "innobackup completed OK!".

innobackupex: Starting to copy MyISAM tables, indexes,
innobackupex: .MRG, .TRG, .TRN, .ARM, .ARZ, .opt, and .frm files
innobackupex: in '/mnt/backups/2009-11-10_12-59-17'
innobackupex: back to original data directory '/var/lib/mysql'
innobackupex: Copying directory '/mnt/backups/2009-11-10_12-59-17/mysql'
innobackupex: Copying directory '/mnt/backups/2009-11-10_12-59-17/sakila'
innobackupex: Copying file '/mnt/backups/2009-11-10_12-59-17/xtrabackup_checkpoints'
innobackupex: Copying directory '/mnt/backups/2009-11-10_12-59-17/test'
innobackupex: Copying file '/mnt/backups/2009-11-10_12-59-17/xtrabackup_binlog_info'

innobackupex: Starting to copy InnoDB tables and indexes
innobackupex: in '/mnt/backups/2009-11-10_12-59-17'
innobackupex: back to original InnoDB data directory '/var/lib/mysql'
innobackupex: Copying file '/mnt/backups/2009-11-10_12-59-17/ibdata1'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/mnt/backups/2009-11-10_12-59-17'
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Copying file '/mnt/backups/2009-11-10_12-59-17/ib_logfile0'
innobackupex: Copying file '/mnt/backups/2009-11-10_12-59-17/ib_logfile1'
innobackupex: Finished copying back files.

091110 13:03:42  innobackupex: innobackup completed OK!

In this example we will copy a table `actor` from database `sakila` on server A to database `test2` on server B.

# xtrabackup  --table=sakila.actor --backup --target-dir=`pwd`/actor/
xtrabackup: tables regcomp(): Success
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
>> log scanned up to (0 6776844)
>> log scanned up to (0 6776844)
xtrabackup  Ver 0.9.5rc Rev 101 for 5.0.84 unknown-linux-gnu (x86_64)
Copying ./ibdata1
     to /home/akuzminsky/tmp/xtrabackup/actor//ibdata1
        ...done
Copying ./sakila/payment.ibd is skipped.
Copying ./sakila/language.ibd is skipped.
Copying ./sakila/film.ibd is skipped.
Copying ./sakila/category.ibd is skipped.
Copying ./sakila/store.ibd is skipped.
Copying ./sakila/inventory.ibd is skipped.
Copying ./sakila/actor.ibd
     to /home/akuzminsky/tmp/xtrabackup/actor//sakila/actor.ibd
        ...done
Copying ./sakila/rental.ibd is skipped.
Copying ./sakila/staff.ibd is skipped.
Copying ./sakila/customer.ibd is skipped.
Copying ./sakila/film_actor.ibd is skipped.
Copying ./sakila/address.ibd is skipped.
Copying ./sakila/country.ibd is skipped.
Copying ./sakila/film_category.ibd is skipped.
Copying ./sakila/city.ibd is skipped.
xtrabackup: The latest check point (for incremental): '0:6776844'
xtrabackup: Stopping log copying thread..
xtrabackup: Transaction log of lsn (0 6776844) to (0 6776844) was copied.

# xtrabackup --prepare --export --target-dir=`pwd`/actor
xtrabackup: cd to /home/akuzminsky/tmp/xtrabackup/actor
xtrabackup: This target seems to be not prepared yet.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/address.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/category.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/city.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/country.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/customer.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/film.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/film_actor.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/film_category.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/inventory.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/language.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/payment.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/rental.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/staff.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/store.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
091111  9:21:39  InnoDB: Started; log sequence number 0 6776844
091111  9:21:39  InnoDB: error: space object of table sakila/film_actor,
InnoDB: space id 8 did not exist in memory. Retrying an open.
091111  9:21:39  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
091111  9:21:39  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './sakila/film_actor.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
InnoDB: Last MySQL binlog file position 0 3375100, file name ./mysql-bin.000005
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
091111  9:21:39  InnoDB: Starting shutdown...
091111  9:21:40  InnoDB: Shutdown completed; log sequence number 0 6776844
xtrabackup  Ver 0.9.5rc Rev 101 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(0 6776844)
xtrabackup: export option is specified.
xtrabackup: export metadata of table 'sakila/actor' to file `./sakila/actor.exp` (2 indexes)
xtrabackup:     name=PRIMARY, id.low=15, page=3
xtrabackup:     name=idx_actor_last_name, id.low=16, page=4

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:

Note: as long as we did the backup of the only table actor, you'll see errors, saying that other tables are not found, ignore them

The server must have innodb_expand_import enabled in my.cnf: innodb_expand_import=1

mysql> use sakila;
Database changed
mysql> CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.02 sec)
mysql>

mysql> alter table actor discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql>

You will see in .err log

InnoDB: We removed now the InnoDB internal data dictionary entry
InnoDB: of table `sakila`.`actor`.

# cp actor.ibd actor.exp sakila/
# chown -R mysql sakila

mysql> use sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table actor import tablespace;
Query OK, 0 rows affected (0.00 sec)

mysql>

Check .err log for lines:

InnoDB: import: extended import of sakila/actor is started.
InnoDB: import: 2 indexes are detected.
InnoDB: Progress in %: 14 28 42 57 71 85 100 done.

mysql> select * from actor limit 5;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-14 23:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-14 23:34:33 |
|        3 | ED         | CHASE        | 2006-02-14 23:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-14 23:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-14 23:34:33 |
+----------+------------+--------------+---------------------+
5 rows in set (0.00 sec)

mysql>

More details on results interprepation

# xtrabackup --stats --tables=sakila.*
xtrabackup: tables regcomp(): Success
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
xtrabackup: Starting 'read-only' InnoDB instance to gather index statistics.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
091111 11:10:55  InnoDB: Started; log sequence number 0 6776844
xtrabackup  Ver 0.9.5rc Rev 101 for 5.0.84 unknown-linux-gnu (x86_64)


<INDEX STATISTICS>
  table: sakila/actor, index: PRIMARY, space id: 1, root page: 3
  estimated statistics in dictionary:
    key vals: 200, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=200, pages=1, data=7507 bytes, data/pages=45%

  table: sakila/actor, index: idx_actor_last_name, space id: 1, root page: 4
  estimated statistics in dictionary:
    key vals: 121, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=200, pages=1, data=2846 bytes, data/pages=17%

  table: sakila/address, index: PRIMARY, space id: 2, root page: 3
  estimated statistics in dictionary:
    key vals: 589, leaf pages: 4, size pages: 5
  real statistics:
     level 1 pages: pages=1, data=48 bytes, data/pages=0%
        leaf pages: recs=603, pages=4, data=46383 bytes, data/pages=70%

  table: sakila/address, index: idx_fk_city_id, space id: 2, root page: 4
  estimated statistics in dictionary:
    key vals: 599, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=603, pages=1, data=5427 bytes, data/pages=33%

  table: sakila/category, index: PRIMARY, space id: 3, root page: 3
  estimated statistics in dictionary:
    key vals: 16, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=16, pages=1, data=487 bytes, data/pages=2%

  table: sakila/city, index: PRIMARY, space id: 4, root page: 3
  estimated statistics in dictionary:
    key vals: 427, leaf pages: 2, size pages: 3
  real statistics:
     level 1 pages: pages=1, data=22 bytes, data/pages=0%
        leaf pages: recs=600, pages=2, data=21218 bytes, data/pages=64%

  table: sakila/city, index: idx_fk_country_id, space id: 4, root page: 4
  estimated statistics in dictionary:
    key vals: 109, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=600, pages=1, data=5400 bytes, data/pages=32%

  table: sakila/country, index: PRIMARY, space id: 5, root page: 3
  estimated statistics in dictionary:
    key vals: 109, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=109, pages=1, data=3699 bytes, data/pages=22%

  table: sakila/customer, index: PRIMARY, space id: 6, root page: 3
  estimated statistics in dictionary:
    key vals: 541, leaf pages: 4, size pages: 5
  real statistics:
     level 1 pages: pages=1, data=48 bytes, data/pages=0%
        leaf pages: recs=599, pages=4, data=50162 bytes, data/pages=76%

  table: sakila/customer, index: idx_fk_store_id, space id: 6, root page: 4
  estimated statistics in dictionary:
    key vals: 2, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=599, pages=1, data=4792 bytes, data/pages=29%

  table: sakila/customer, index: idx_fk_address_id, space id: 6, root page: 5
  estimated statistics in dictionary:
    key vals: 599, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=599, pages=1, data=5391 bytes, data/pages=32%

  table: sakila/customer, index: idx_last_name, space id: 6, root page: 6
  estimated statistics in dictionary:
    key vals: 599, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=599, pages=1, data=8510 bytes, data/pages=51%

  table: sakila/film, index: PRIMARY, space id: 7, root page: 3
  estimated statistics in dictionary:
    key vals: 1022, leaf pages: 11, size pages: 12
  real statistics:
     level 1 pages: pages=1, data=132 bytes, data/pages=0%
        leaf pages: recs=1000, pages=11, data=147081 bytes, data/pages=81%

  table: sakila/film, index: idx_title, space id: 7, root page: 4
  estimated statistics in dictionary:
    key vals: 713, leaf pages: 2, size pages: 3
  real statistics:
     level 1 pages: pages=1, data=54 bytes, data/pages=0%
        leaf pages: recs=1000, pages=2, data=22235 bytes, data/pages=67%

  table: sakila/film, index: idx_fk_language_id, space id: 7, root page: 5
  estimated statistics in dictionary:
    key vals: 1, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=1000, pages=1, data=8000 bytes, data/pages=48%

  table: sakila/film, index: idx_fk_original_language_id, space id: 7, root page: 6
  estimated statistics in dictionary:
    key vals: 1, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=1000, pages=1, data=8000 bytes, data/pages=48%

  table: sakila/film_actor, index: PRIMARY, space id: 8, root page: 3
  estimated statistics in dictionary:
    key vals: 5143, leaf pages: 11, size pages: 12
  real statistics:
     level 1 pages: pages=1, data=143 bytes, data/pages=0%
        leaf pages: recs=5462, pages=11, data=142012 bytes, data/pages=78%

  table: sakila/film_actor, index: idx_fk_film_id, space id: 8, root page: 4
  estimated statistics in dictionary:
    key vals: 985, leaf pages: 4, size pages: 5
  real statistics:
     level 1 pages: pages=1, data=52 bytes, data/pages=0%
        leaf pages: recs=5462, pages=4, data=49158 bytes, data/pages=75%

  table: sakila/film_category, index: PRIMARY, space id: 9, root page: 3
  estimated statistics in dictionary:
    key vals: 316, leaf pages: 3, size pages: 4
  real statistics:
     level 1 pages: pages=1, data=36 bytes, data/pages=0%
        leaf pages: recs=1000, pages=3, data=25000 bytes, data/pages=50%

  table: sakila/film_category, index: fk_film_category_category, space id: 9, root page: 4
  estimated statistics in dictionary:
    key vals: 16, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=1000, pages=1, data=8000 bytes, data/pages=48%

  table: sakila/inventory, index: PRIMARY, space id: 10, root page: 3
  estimated statistics in dictionary:
    key vals: 4673, leaf pages: 10, size pages: 11
  real statistics:
     level 1 pages: pages=1, data=120 bytes, data/pages=0%
        leaf pages: recs=4581, pages=10, data=128268 bytes, data/pages=78%

  table: sakila/inventory, index: idx_fk_film_id, space id: 10, root page: 4
  estimated statistics in dictionary:
    key vals: 981, leaf pages: 4, size pages: 5
  real statistics:
     level 1 pages: pages=1, data=56 bytes, data/pages=0%
        leaf pages: recs=4581, pages=4, data=45810 bytes, data/pages=69%

  table: sakila/inventory, index: idx_store_id_film_id, space id: 10, root page: 5
  estimated statistics in dictionary:
    key vals: 397, leaf pages: 6, size pages: 7
  real statistics:
     level 1 pages: pages=1, data=90 bytes, data/pages=0%
        leaf pages: recs=4581, pages=6, data=50391 bytes, data/pages=51%

  table: sakila/language, index: PRIMARY, space id: 11, root page: 3
  estimated statistics in dictionary:
    key vals: 6, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=6, pages=1, data=264 bytes, data/pages=1%

  table: sakila/payment, index: PRIMARY, space id: 12, root page: 3
  estimated statistics in dictionary:
    key vals: 16451, leaf pages: 47, size pages: 97
  real statistics:
     level 1 pages: pages=1, data=564 bytes, data/pages=3%
        leaf pages: recs=16049, pages=47, data=690087 bytes, data/pages=89%

  table: sakila/payment, index: idx_fk_staff_id, space id: 12, root page: 4
  estimated statistics in dictionary:
    key vals: 3, leaf pages: 10, size pages: 11
  real statistics:
     level 1 pages: pages=1, data=120 bytes, data/pages=0%
        leaf pages: recs=16049, pages=10, data=128392 bytes, data/pages=78%

  table: sakila/payment, index: idx_fk_customer_id, space id: 12, root page: 5
  estimated statistics in dictionary:
    key vals: 549, leaf pages: 10, size pages: 11
  real statistics:
     level 1 pages: pages=1, data=130 bytes, data/pages=0%
        leaf pages: recs=16049, pages=10, data=144441 bytes, data/pages=88%

  table: sakila/payment, index: fk_payment_rental, space id: 12, root page: 6
  estimated statistics in dictionary:
    key vals: 16071, leaf pages: 16, size pages: 17
  real statistics:
     level 1 pages: pages=1, data=252 bytes, data/pages=1%
        leaf pages: recs=16049, pages=16, data=192568 bytes, data/pages=73%

  table: sakila/rental, index: PRIMARY, space id: 13, root page: 3
  estimated statistics in dictionary:
    key vals: 16305, leaf pages: 53, size pages: 97
  real stxtrabackup: starting shutdown with innodb_fast_shutdown = 1
091111 11:10:55  InnoDB: Starting shutdown...
091111 11:10:56  InnoDB: Shutdown completed; log sequence number 0 6776844
atistics:
     level 1 pages: pages=1, data=742 bytes, data/pages=4%
        leaf pages: recs=16044, pages=53, data=784692 bytes, data/pages=90%

  table: sakila/rental, index: rental_date, space id: 13, root page: 4
  estimated statistics in dictionary:
    key vals: 15180, leaf pages: 28, size pages: 29
  real statistics:
     level 1 pages: pages=1, data=728 bytes, data/pages=4%
        leaf pages: recs=16044, pages=28, data=352968 bytes, data/pages=76%

  table: sakila/rental, index: idx_fk_inventory_id, space id: 13, root page: 5
  estimated statistics in dictionary:
    key vals: 4467, leaf pages: 16, size pages: 17
  real statistics:
     level 1 pages: pages=1, data=256 bytes, data/pages=1%
        leaf pages: recs=16044, pages=16, data=192528 bytes, data/pages=73%

  table: sakila/rental, index: idx_fk_customer_id, space id: 13, root page: 6
  estimated statistics in dictionary:
    key vals: 589, leaf pages: 16, size pages: 17
  real statistics:
     level 1 pages: pages=1, data=240 bytes, data/pages=1%
        leaf pages: recs=16044, pages=16, data=176484 bytes, data/pages=67%

  table: sakila/rental, index: idx_fk_staff_id, space id: 13, root page: 7
  estimated statistics in dictionary:
    key vals: 1, leaf pages: 13, size pages: 14
  real statistics:
     level 1 pages: pages=1, data=182 bytes, data/pages=1%
        leaf pages: recs=16044, pages=13, data=160440 bytes, data/pages=75%

  table: sakila/staff, index: PRIMARY, space id: 14, root page: 3
  estimated statistics in dictionary:
    key vals: 1, leaf pages: 3, size pages: 4
  real statistics:
        leaf pages: recs=2, pages=1, data=1021 bytes, data/pages=6%
    external pages: pages=3, data=35597 bytes, data/pages=72%

  table: sakila/staff, index: idx_fk_store_id, space id: 14, root page: 4
  estimated statistics in dictionary:
    key vals: 2, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=2, pages=1, data=14 bytes, data/pages=0%

  table: sakila/staff, index: idx_fk_address_id, space id: 14, root page: 5
  estimated statistics in dictionary:
    key vals: 2, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=2, pages=1, data=16 bytes, data/pages=0%

  table: sakila/store, index: PRIMARY, space id: 15, root page: 3
  estimated statistics in dictionary:
    key vals: 2, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=2, pages=1, data=52 bytes, data/pages=0%

  table: sakila/store, index: idx_unique_manager, space id: 15, root page: 4
  estimated statistics in dictionary:
    key vals: 2, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=2, pages=1, data=14 bytes, data/pages=0%

  table: sakila/store, index: idx_fk_address_id, space id: 15, root page: 5
  estimated statistics in dictionary:
    key vals: 2, leaf pages: 1, size pages: 1
  real statistics:
        leaf pages: recs=2, pages=1, data=16 bytes, data/pages=0%

# innobackupex-1.5.1 .
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackup
           prints "innobackup completed OK!".

innobackupex: Using mysql  Ver 14.14 Distrib 5.1.39, for unknown-linux-gnu (x86_64) using readline 5.1
innobackupex: Using mysql server version 5.1.39-percona-log

innobackupex: Created backup directory /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50
091111 11:27:50  innobackupex: Starting mysql with options: --unbuffered --
091111 11:27:50  innobackupex: Connected to database with mysql child process (pid=6375)
091111 11:27:54  innobackupex: Connection to database server closed

091111 11:27:54  innobackupex: Starting ibbackup with command: xtrabackup --backup --suspend-at-end --target-dir=/mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50
innobackupex: Waiting for ibbackup (pid=6382) to suspend
innobackupex: Suspend file '/mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/xtrabackup_suspended'

xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
>> >> log scanned up to (0 6776844)
>> >> log scanned up to (0 6776844)

091111 11:28:00  innobackupex: Continuing after ibbackup has suspended
091111 11:28:00  innobackupex: Starting mysql with options: --unbuffered --
091111 11:28:00  innobackupex: Connected to database with mysql child process (pid=6389)
091111 11:28:04  innobackupex: Starting to lock all tables...
>> >> log scanned up to (0 6776844)
>> >> log scanned up to (0 6776844)
091111 11:28:14  innobackupex: All tables locked and flushed to disk

091111 11:28:14  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ and .opt files in
innobackupex: subdirectories of '/var/lib/mysql'
innobackupex: Backing up files '/var/lib/mysql/sakila/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (36 files)
innobackupex: Backing up files '/var/lib/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (65 files)
>> >> log scanned up to (0 6776844)
091111 11:28:15  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ and .opt files

innobackupex: Resuming ibbackup

>> >> log scanned up to (0 6776844)
xtrabackup  Ver 0.9.5rc Rev 101 for 5.0.84 unknown-linux-gnu (x86_64)
Copying ./ibdata1 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/ibdata1
        ...done
Copying ./sakila/payment.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/payment.ibd
        ...done
Copying ./sakila/language.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/language.ibd
        ...done
Copying ./sakila/film.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/film.ibd
        ...done
Copying ./sakila/category.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/category.ibd
        ...done
Copying ./sakila/store.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/store.ibd
        ...done
Copying ./sakila/inventory.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/inventory.ibd
        ...done
Copying ./sakila/actor.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/actor.ibd
        ...done
Copying ./sakila/rental.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/rental.ibd
        ...done
Copying ./sakila/staff.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/staff.ibd
        ...done
Copying ./sakila/customer.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/customer.ibd
        ...done
Copying ./sakila/film_actor.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/film_actor.ibd
        ...done
Copying ./sakila/address.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/address.ibd
        ...done
Copying ./sakila/country.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/country.ibd
        ...done
Copying ./sakila/film_category.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/film_category.ibd
        ...done
Copying ./sakila/city.ibd 
     to /mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50/sakila/city.ibd
        ...done
xtrabackup: The latest check point (for incremental): '0:6776844'
xtrabackup: Stopping log copying thread..
xtrabackup: Transaction log of lsn (0 6776844) to (0 6776844) was copied.
091111 11:28:17  innobackupex: All tables unlocked
091111 11:28:17  innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/mnt/data1/home/akuzminsky/tmp/xtrabackup/2009-11-11_11-27-50'
innobackupex: MySQL binlog position: filename 'mysql-bin.000005', position 3382489		
091111 11:28:17  innobackupex: innobackup completed OK!

# scp -r 2009-11-11_11-27-50/ 10.80.206.236:~/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackup
           prints "innobackup completed OK!".



091111 16:07:35  innobackupex: Starting ibbackup with command: xtrabackup --prepare --target-dir=/root/2009-11-11_11-27-50

xtrabackup: cd to /root/2009-11-11_11-27-50
xtrabackup: This target seems to be not prepared yet.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
091111 16:07:35  InnoDB: Started; log sequence number 0 6776844
InnoDB: Last MySQL binlog file position 0 3375100, file name ./mysql-bin.000005
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
091111 16:07:35  InnoDB: Starting shutdown...
091111 16:07:37  InnoDB: Shutdown completed; log sequence number 0 6776844
xtrabackup  Ver 0.9.5rc Rev 101 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(0 6776844)

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:


091111 16:07:37  innobackupex: Restarting xtrabackup with command: xtrabackup --prepare --target-dir=/root/2009-11-11_11-27-50
for creating ib_logfile*

xtrabackup: cd to /root/2009-11-11_11-27-50
xtrabackup: This target seems to be already prepared.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
091111 16:07:37  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
091111 16:07:37  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
091111 16:07:37  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Last MySQL binlog file position 0 3375100, file name ./mysql-bin.000005
091111 16:07:37  InnoDB: Started; log sequence number 0 6777356
InnoDB: Last MySQL binlog file position 0 3375100, file name ./mysql-bin.000005
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
091111 16:07:37  InnoDB: Starting shutdown...
091111 16:07:38  InnoDB: Shutdown completed; log sequence number 0 6777356
xtrabackup  Ver 0.9.5rc Rev 101 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:

091111 16:07:38  innobackupex: innobackup completed OK!

Make sure server-id is set in my.cnf: server-id=2

# innobackupex-1.5.1 --copy-back 2009-11-11_11-27-50/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackup
           prints "innobackup completed OK!".

innobackupex: Starting to copy MyISAM tables, indexes,
innobackupex: .MRG, .TRG, .TRN, .ARM, .ARZ, .opt, and .frm files
innobackupex: in '/root/2009-11-11_11-27-50'
innobackupex: back to original data directory '/var/lib/mysql'
innobackupex: Copying directory '/root/2009-11-11_11-27-50/test'
innobackupex: Copying directory '/root/2009-11-11_11-27-50/mysql'
innobackupex: Copying file '/root/2009-11-11_11-27-50/xtrabackup_checkpoints'
innobackupex: Copying directory '/root/2009-11-11_11-27-50/sakila'
innobackupex: Copying file '/root/2009-11-11_11-27-50/xtrabackup_binlog_info'
innobackupex: Copying directory '/root/2009-11-11_11-27-50/actor'

innobackupex: Starting to copy InnoDB tables and indexes
innobackupex: in '/root/2009-11-11_11-27-50'
innobackupex: back to original InnoDB data directory '/var/lib/mysql'
innobackupex: Copying file '/root/2009-11-11_11-27-50/ibdata1'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/root/2009-11-11_11-27-50'
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Copying file '/root/2009-11-11_11-27-50/ib_logfile1'
innobackupex: Copying file '/root/2009-11-11_11-27-50/ib_logfile0'
innobackupex: Finished copying back files.

091111 16:12:03  innobackupex: innobackup completed OK!

In the backup directory see the file with binlog position.

# cd 2009-11-11_11-27-50/
# cat xtrabackup_binlog_info
mysql-bin.000005        3382489

Use these value for CHANGE MASTER statement

mysql> CHANGE MASTER TO MASTER_HOST='10.80.206.20', 
MASTER_USER='root', 
MASTER_PASSWORD='', 
MASTER_LOG_FILE='mysql-bin.000005', 
MASTER_LOG_POS=3382489;
mysql>

Start the slave

mysql> slave start;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.80.206.20
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 3382821
               Relay_Log_File: Centos-5-relay-bin.000003
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3382821
              Relay_Log_Space: 886
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

mysql>

A Perl script which automates the backup of both MyISAM and InnoDB tables

Innobackupex is modified version of innobackup script http://www.innodb.com/download/, the patch is available in XtraBackup source code.

For main documentation of innobackup you can use http://www.innodb.com/doc/hot_backup/manual.html#innobackup

The usage of innobackupex is almost same, with differences:

• MY.CNF option in the command line is removed. (xtrabackup uses the information automatically)
• some options not supported by xtrabackup cause error, e.g. ([–sleep=MS] [–compress[=LEVEL]] [–include=REGEXP] [–uncompress])
• option [–throttle=IOS] is added
• the default executable binary of –ibbackup is “xtrabackup”.
• One of main differences from innobackup is that innobackupex puts output to stderr instead of stdout, this is done to support –stream output to stdout

Main the usage is:

innobackupex [--throttle=IOS] [--user=NAME] [--incremental **(not implemented yet)**]
           [--remote-host=HOSTNAME] [--stream=tar **(only tar for now)**] [--include=REGEXP]
           [--password=WORD] [--port=PORT] [--socket=SOCKET]
           BACKUP-ROOT-DIR
innobackupex --apply-log [--use-memory=BYTES(xxM,xxG are also OK)] BACKUP-DIR
innobackupex --copy-back BACKUP-DIR

current “xtrabackup –prepare” cannot create proper ib_logfile* in one execution. So, “innobackupex –apply-log” executes xtrabackup twice.

Even if specify tar stream, BACKUP-ROOT-DIR is needed. In this case, The BACKUP-ROOT-DIR is only used to create contents of backup-my.cnf. If you don't use backup-my.cnf, any value may be OK.

innobackupex can produce stream (currently it is tar format) to stdout. This is very useful to get compressed archive of backup on remote box in single step. You do not need additional space on database server! Example

Compress single file:
innobackupex --stream=tar ./ | gzip - > backup.tar.gz

Copy uncompressed stream over network to file
innobackupex --stream=tar ./  | ssh user@host cat ">"  backup.tar

Copy over network without ssh encryption (fast copying)
ssh user@host "( nc -l -p 9210 > backup.tar & )" && innobackupex --stream=tar ./  |  nc host 9210

Produced tar is having special format, to unpack it you should add -i option to tar. e.g.

 tar xfzi backup.tar.gz

otherwise only part of files will be extracted.

innobackupex uses ssh/scp simply. You should setup ssh to the remote_host with no password input.

Add ”–remote-host=” option only.

The return code of the XtraBackup process is 0 if the backup or restore run succeeds. If the run fails for any reason, the return code is 1 (But some other code can be returned about option handling in current version, because option handling is based on libmysys).

* In the near future, it will be aligned to the other mysql tools (in mysql/include/mysys_err.h).

GPL V2