Table of Contents
Introduction
Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines. It works with MySQL 5.0 and 5.1 versions (InnoDB Plugin and Barracuda format is supported as of release 0.9.5rc) and also can handle MyISAM tables. XtraBackup is distributed under GPL v2 license.
There two main tools:
- xtrabackup - binary, which allows to copy only InnoDB/XtraDB tablespaces
- innobackupex - script, based on innobackup Perl script, distributed by Oracle/InnoDB under GPL license. Script was modified to work with xtrabackup binary and accept new parameters. Script provides functionality to backup whole MySQL database instance with MyISAM, InnoDB, XtraDB tables.
Main features
- Online non-blocking backup for InnoDB and XtraDB tables; read-only for MyISAM tables
- Stream compressed backup to tape or remote box
- Incremental backup
Backup can be run against standard MySQL version 5.0 and 5.1 and any version of InnoDB (5.0, 5.1, 1.0-plugin) or XtraDB. You do not need to have patched MySQL to use backup utility. Patching MySQL is only needed to prepare xtrabackup binary from source code.
Where to get
The source code lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system.
Contacts
For general questions use Pecona-discussions group, and for development question Percona-dev group.
For support, commercial and sponsorship inquiries contact Percona
XtraBackup options
Usage
Usage: [./xtrabackup [DEFAULTS OPTION] –backup | ./xtrabackup [DEFAULTS OPTION] –prepare] [OPTIONS]
Default options are read from the following files in the given order: /etc/my.cnf /opt/mysql-5.0.xx/etc/my.cnf ~/.my.cnf
The following groups are read: mysqld xtrabackup
The following options may be given as the first argument:
(The following 4 options may be given as the first argument)
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
--target-dir=name destination directory
--backup take backup to target-dir
--stats calc statistic of datadir (offline mysqld is recommended)
--prepare prepare a backup for starting mysql server on the backup.
--export create files to import to another database when prepare.
--print-param print parameter of mysqld needed for copyback.
--use-memory=# The value is used instead of buffer_pool_size
--suspend-at-end creates a file 'xtrabackup_suspended' and waits until the
user deletes that file at the end of '--backup'
--throttle=# limit count of IO operations (pairs of read&write) per
second to IOS values (for '--backup')
--log-stream outputs the contents of 'xtrabackup_logfile' to stdout
only until the file 'xtrabackup_suspended' deleted (for
'--backup').
--incremental-lsn=name
(for --backup): copy only .ibd pages newer than specified
LSN 'high:low'. ##ATTENTION##: checkpoint lsn must be
used. anyone can detect your mistake. be carefully!
--incremental-basedir=name
(for --backup): copy only .ibd pages newer than backup at
specified directory.
--incremental-dir=name
(for --prepare): apply .delta files and logfile in the
specified directory.
--tables=name filtering by regexp for table names.
--create-ib-logfile ** not work for now** creates ib_logfile* also after
'--prepare'. ### If you want create ib_logfile*, only
re-execute this command in same options. ###
-h, --datadir=name Path to the database root.
-t, --tmpdir=name Path for temporary files. Several paths may be specified,
separated by a colon (:), in this case they are used in a
round-robin fashion.
throttle - limit count of IO operations per second to IOS values 
: Too small value of throttle may make the backup endless. (If don't catch up with src transaction log speed)
target-dir - destination directory
- datadir must be set as command line option or [mysqld] section of my.cnf.
- We can set all option also at [xtrabackup] section of my.cnf. (If [xtrabackup] is after [mysqld], the parameters are overwritten)
Details
Options for xtrabackup. The All options are read from [mysqld] and [xtrabackup] section of my.cnf.
--datadir
[required]: This option is same for mysqld. And this shuld be same to mysqld. So, you should set datadir at [mysqld] section of my.cnf .
--target-dir
[required]: This option specifies the target directory to put the backup files. If not exist, ”–backup” option creates the directory.
attention: We must use fullpath expression.
--backup
stores the backup data files (*.ibd) and a backup log file (named 'xtrabackup_logfile') in the directory specified in target-dir
--stats
calculates detail of index statistics and outputs as followings. This option uses read-only instance of InnoDB using ”–use-memory=#”.
...
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 ecords 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.
--prepare
prepares a backup for starting mysqld from the backup files stored by ”–backup” option. It applies the log records in 'xtrabackup_logfile' to the data files.
attention: In current version, you should re-execute ”–prepare”, if you want ib_logfile* here. (This is restriction of the alpha version)
--print-param
This option is used by innobackup script. It generates target mysqld information for “innobackup –copy-back” like this.
# 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
--defaults-file=#
This option is same to the other MySQL tools. It can specify the my.cnf only read.
--defaults-extra-file=#
This option is also same to the other MySQL tools. It can specify the my.cnf additionally read.
--use-memory=#
This option is for ”–prepare”. This is not same to the same named option of ibbackup. It is used as innodb_buffer_pool_size of the InnoDB instance of ”–prepare”. The default value is 100M bytes. But 1G or 2G bytes are recommended if you can. This option also affects to ”–stats” option.
--export
This option is for ”–prepare”. It makes to output “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
--suspend-at-end
This option is for ”–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.
--log-stream
This option is for ”–backup”. outputs the contents of 'xtrabackup_logfile' to stdout only, –suspend-at-end is added automatically. It may be useful when you use your own backup tool. (innobackupex –stream mode also uses this option.)
--incremental-lsn=LSN_HIGH:LSN_LOW
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 at 'xtrabackup_checkpoints' file)
--incremental-basedir=name
This option is for ”–backup”. Copy only .ibd pages newer than backup at specified directory. ('xtrabackup_checkpoints' of the directory is read and the LSN is used automatically.)
--incremental-dir=name
This option is for ”–prepare”. Apply the incremental backup files (*.delta & xtrabackup_logfile) in the specified directory.
--tables=REGEXP
This option only backup the file-per-table datafiles whose table name “db_name.table_name” is matching to the regular expression REGEXP. It also affects to ”–stats” option.
--throttle=#
This option is for ”–backup”. limits count of IO operations (pairs of read&write) per second to IOS values. (At ”–incremental” mode, it is based on rather read IO)
--create-ib-logfile
This option is for ”–prepare”. no effect for now
attention: In current version, you should re-execute ”–prepare”, if you want ib_logfile* here.
--tmpdir
It is read from my.cnf. But it is not used by xtrabackup for now.
--innodb_*
InnoDB options for the target mysqld. Ordinarily, they are read from my.cnf and we don't care about their settings.
Making backup
–backup command is performing backup of InnoDB/XTraDB tablespaces. It reads the InnoDB data file and log file information from my.cnf specified in command line or looking into default location (/etc/my.cnf, etc…) , and stores the backup of data files and a backup log file (named 'xtrabackup_logfile') in the directory specified in target-dir.
* current version may not able to treat over 4GB 'xtrabackup_logfile' in –prepare step.
* newest revision can treat over 4GB 'xtrabackup_logfile' in –prepare step at 64-bit architecture.
Restoring from a backup
Applying the log to a backup
–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 current version, you should execute –-prepare second time if you want to create ib_logfile* (This is restriction of the alpha version)
Starting mysqld on a restored backup
Restore the ”–prepare d” files to the original place in manually or by script like 'innobackupex'.
Advanced operations
Incremental backup
(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 ...
Making a partial backup
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
Backup
# 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!
Restore
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!
Restoring a single .ibd file
In this example we will copy a table `actor` from database `sakila` on server A to database `test2` on server B.
1. Take a backup of the table actor
# 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.
2. Do export
# 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
3. Create table actor on the target server
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>
4. Discard a tablespace ''actor''
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`.
5. Copy actor.ibd, actor.exp to /var/lib/mysql/sakila/
# cp actor.ibd actor.exp sakila/ # chown -R mysql sakila
6. Import a table space ''actor''
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.
7. Check if the tabl is correctly imported
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>
Statistics
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%
Point-in-time recovery from a backup
Setting up a new slave from a backup in replication
1. Take a backup
# 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!
2. Copy the backup to the slave server
# scp -r 2009-11-11_11-27-50/ 10.80.206.236:~/
3. On the slave apply log and copy the backup to datadir
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!
4. Configure slave
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>
Restoring a master database in replication
Making a sharp backup of both MyISAM and InnoDB tables
innobackupex script
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
Usage
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.
Stream backup
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.
Compressed backup
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.
Backup to remote host
innobackupex uses ssh/scp simply. You should setup ssh to the remote_host with no password input.
Add ”–remote-host=” option only.
Installing
Known Bugs
Error Codes
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).
License
GPL V2
Document Revision History
Except where otherwise noted, content on this wiki is licensed under the following license:GNU Free Documentation License 1.2
Discussion
–prepare will output position and file name of binlog at end of backup. This is useful for applying binlogs after restore and for setting up replication slaves.
It's really hard to figure out here but does xtrabackup read from the running InnoDB instance or read from disk?
One thing we really want is to read from memory on the source so that a snapshot is MUCH faster.
Reading from disk when the data is already in the buffer pool is a bit of a waste.
According to this reply from Vadim on the discussion list, you CAN NOT just take the binlog position after doing the –prepare. The binlog position can only be guaranteed after doing a “FLUSH TABLES WITH READ LOCK” like innobackupex script does to get the master binlog position.
http://groups.google.com/group/percona-discussion/browse_thread/thread/599fad16becc0ad7
This one confused me for a bit. In my testing these where always the same, but I'm guessing there are some fringe cases where it is not the same.
I downloaded the binary files of xtrabackup0.9 and uncompressed to my local. However when I tried to run the program 'xtrabackup', it gave me an error “bash: can not execute binary file”. What does it mean? Do I have to download a 32bit version binary package for my 32bit os? ( it works fine in our 64bit server.)
I noticed that –defaults-file argument does not get passed to mysql client. I wanted to have a custom defaults file with a [client] section to avoid having username and password on the command line. Maybe there is another way of doing it, but this is the only way I knew. It seems to work fine. Here is a patch to innobackupex-1.5.1 from xtrabackup0.9 package. <source> — innobackupex-1.5.1 2009-11-22 21:23:16.000000000 -0500 +++ innobackupex-1.5.1-gk 2009-11-22 23:56:15.000000000 -0500 @@ -987,13 +987,20 @@ # user to innobackup. # sub get_mysql_options { - my $options = '–unbuffered'; - + my $options = ''; +
+ # defaults-file option must be first + if ($option_defaults_file) { + $options = “$options –defaults-file=$option_defaults_file”; + } + $options = “$options –unbuffered”; +
if ($option_mysql_password) { $options = "$options --password=$option_mysql_password"; } else {- $options = “$options –”; + # gregk looks strange + # $options = “$options –”;
} if ($option_mysql_user) { $options = "$options --user=$option_mysql_user";</source>
Hi, I have tested a partial backup on my server and found its not working ( my server is innodb_file_per_table.
Details: root@adminlinx.blogspot.com:/backup/base> innobackupex-1.5.1 –include=wikidbPrabhat.user_newtalk* /backup/base
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 innobackupex-1.5.1 prints "completed OK!".innobackupex-1.5.1: Using mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1 innobackupex-1.5.1: Using mysql server version Copyright (C) 2000-2008 MySQL AB
innobackupex-1.5.1: Created backup directory /backup/base/2010-01-07_06-36-50 100107 06:36:50 innobackupex-1.5.1: Starting mysql with options: –unbuffered – 100107 06:36:50 innobackupex-1.5.1: Connected to database with mysql child process (pid=30701) 100107 06:36:54 innobackupex-1.5.1: Connection to database server closed
100107 06:36:54 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup –backup –suspend-at-end –target-dir=/backup/base/2010-01-07_06-36-50 –tables='wikidbPrabhat.user_newtalk*' innobackupex-1.5.1: Waiting for ibbackup (pid=30708) to suspend innobackupex-1.5.1: Suspend file '/backup/base/2010-01-07_06-36-50/xtrabackup_suspended'
xtrabackup: tables regcomp(): Success xtrabackup Ver 1.0 Rev 113 for 5.0.84 unknown-linux-gnu (x86_64) 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
Copying ./ibdata1
» log scanned up to (0 2269337110)
100107 06:37:14 innobackupex-1.5.1: Continuing after ibbackup has suspended 100107 06:37:14 innobackupex-1.5.1: Starting mysql with options: –unbuffered – 100107 06:37:14 innobackupex-1.5.1: Connected to database with mysql child process (pid=30719)
100107 06:37:18 innobackupex-1.5.1: Starting to lock all tables…
100107 06:37:28 innobackupex-1.5.1: All tables locked and flushed to disk
100107 06:37:28 innobackupex-1.5.1: Starting to backup .frm, .MRG, .MYD, .MYI, innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ and .opt files in innobackupex-1.5.1: subdirectories of '/var/lib/mysql' innobackupex-1.5.1: Backing up files '/var/lib/mysql/wikidb/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (37 files) innobackupex-1.5.1: Backing up file '/var/lib/mysql/DBPrabhat/example_innodb.frm' innobackupex-1.5.1: Backing up file '/var/lib/mysql/DBPrabhat/TblPrabhat.MYI' innobackupex-1.5.1: Backing up file '/var/lib/mysql/DBPrabhat/db.opt' innobackupex-1.5.1: Backing up file '/var/lib/mysql/DBPrabhat/TblPrabhat.MYD' innobackupex-1.5.1: Backing up file '/var/lib/mysql/DBPrabhat/TblPrabhat.frm' innobackupex-1.5.1: Backing up files '/var/lib/mysql/wikidbPrabhat/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (37 files) innobackupex-1.5.1: Backing up files '/var/lib/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (51 files) 100107 06:37:29 innobackupex-1.5.1: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ and .opt files
innobackupex-1.5.1: Resuming ibbackup
xtrabackup: The latest check point (for incremental): '0:2269337110'
xtrabackup: Stopping log copying thread. xtrabackup: Transaction log of lsn (0 2269337110) to (0 2269337110) was copied. 100107 06:37:31 innobackupex-1.5.1: All tables unlocked 100107 06:37:31 innobackupex-1.5.1: Connection to database server closed
innobackupex-1.5.1: Backup created in directory '/backup/base/2010-01-07_06-36-50' innobackupex-1.5.1: MySQL binlog position: filename '', position 100107 06:37:31 innobackupex-1.5.1: completed OK!
After that I saw in target dir I saw all tables datafiles;
root@adminlinx.blogspot.com:/backup/base/2010-01-07_06-36-50/wikidbPrabhat> pwd /backup/base/2010-01-07_06-36-50/wikidbPrabhat root@adminlinx.blogspot.com:/backup/base/2010-01-07_06-36-50/wikidbPrabhat> ls archive.frm imagelinks.frm objectcache.frm querycachetwo.frm site_stats.frm user_newtalk.frm categorylinks.frm interwiki.frm oldimage.frm recentchanges.frm templatelinks.frm watchlist.frm db.opt ipblocks.frm page.frm redirect.frm text.frm externallinks.frm job.frm pagelinks.frm revision.frm trackbacks.frm filearchive.frm langlinks.frm page_restrictions.frm searchindex.frm transcache.frm hitcounter.frm logging.frm querycache.frm searchindex.MYD user.frm image.frm math.frm querycache_info.frm searchindex.MYI user_groups.frm
Thanks,
I use innobackupex-1.5.1 to backuo database,at the end,wrong info is: innobackupex: Error: mysql child process has died: ERROR 2006 (HY000) at line 19: MySQL server has gone away while waiting for reply to MySQL request: 'UNLOCK TABLES;' at /usr/bin/innobackupex-1.5.1 line 452. Could you tell me how to solutin?Thanks!