从MySQL全备中恢复Innodb单表

First, you must meet certain prerequisites to be able to restore a ibd tablespace:

1.The ibd file must be from a consistent backup with all insert buffer entries merged  and have no uncommitted transactions in order to not be dependent of the shared 2.tablespace ibdata. That is, shutting down with innodb_fast_shutdown=0. We’ll use XtraBackup to avoid the server shutdown.
3.You must not drop, truncate or alter the schema of the table after the backup has been taken.
The variable innodb_file_per_table must be enabled.

[root@test bin]# ./innobackupex --defaults-file=/service/mysql5.5/my.cnf  --export /backup/5.5/


InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.


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


131125 19:50:23  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/service/mysql5.5/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).
131125 19:50:23  innobackupex: Connected to MySQL server
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".


innobackupex: Using mysql server version 5.5.25-log


innobackupex: Created backup directory /backup/5.5/2013-11-25_19-50-23


131125 19:50:23  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/service/mysql5.5/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/backup/5.5/2013-11-25_19-50-23 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=13361) to suspend
innobackupex: Suspend file '/backup/5.5/2013-11-25_19-50-23/xtrabackup_suspended_2'


xtrabackup_55 version 2.1.5 for Percona Server 5.5.31 Linux (x86_64) (revision id: 680)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql5.5
xtrabackup: using the following InnoDB configuration:
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 = 3
xtrabackup:   innodb_log_file_size = 5242880
>> log scanned up to (1600610)
[01] Copying ./ibdata1 to /backup/5.5/2013-11-25_19-50-23/ibdata1
[01]        ...done
[01] Copying ./test/t2.ibd to /backup/5.5/2013-11-25_19-50-23/test/t2.ibd
[01]        ...done
>> log scanned up to (1600610)
xtrabackup: Creating suspend file '/backup/5.5/2013-11-25_19-50-23/xtrabackup_suspended_2' with pid '13361'


131125 19:50:25  innobackupex: Continuing after ibbackup has suspended
131125 19:50:25  innobackupex: Starting to lock all tables...
131125 19:50:25  innobackupex: All tables locked and flushed to disk


131125 19:50:25  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/data/mysql5.5'
innobackupex: Backing up file '/data/mysql5.5/test/t2.frm'
innobackupex: Backing up files '/data/mysql5.5/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
>> log scanned up to (1600610)
innobackupex: Backing up files '/data/mysql5.5/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
131125 19:50:26  innobackupex: Finished backing up non-InnoDB tables and files


131125 19:50:26  innobackupex: Waiting for log copying to finish


xtrabackup: The latest check point (for incremental): '1600610'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1600610)


xtrabackup: Creating suspend file '/backup/5.5/2013-11-25_19-50-23/xtrabackup_log_copied' with pid '13361'
xtrabackup: Transaction log of lsn (1600610) to (1600610) was copied.
131125 19:50:27  innobackupex: All tables unlocked


innobackupex: Backup created in directory '/backup/5.5/2013-11-25_19-50-23'
innobackupex: MySQL binlog position: filename 'ZWC-TEST.000003', position 420
131125 19:50:27  innobackupex: Connection to database server closed
131125 19:50:27  innobackupex: completed OK!

Truncate t2 table

root@test 07:52:19>show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@test 07:52:28>select count(*) from t2;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

root@test 07:52:36>alter table t2 discard tablespace;
Query OK, 0 rows affected (0.00 sec)

root@test 07:52:46>select count(*) from t2;
ERROR 1030 (HY000): Got error -1 from storage engine

Begin recovery t2

Then apply the logs to get a consistent backup.

[root@test bin]# ./innobackupex --defaults-file=/service/mysql5.5/my.cnf --apply-log  --export /backup/5.5/2013-11-25_19-50-23/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  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 innobackupex
           prints "completed OK!".



131125 19:53:30  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/service/mysql5.5/my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/backup/5.5/2013-11-25_19-50-23 --export --tmpdir=/tmp

xtrabackup_55 version 2.1.5 for Percona Server 5.5.31 Linux (x86_64) (revision id: 680)
xtrabackup: cd to /backup/5.5/2013-11-25_19-50-23
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1600610)
xtrabackup: using the following InnoDB configuration for recovery:
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: using the following InnoDB configuration for recovery:
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)
131125 19:53:30 InnoDB: The InnoDB memory heap is disabled
131125 19:53:30 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131125 19:53:30 InnoDB: Compressed tables use zlib 1.2.3
131125 19:53:30 InnoDB: Initializing buffer pool, size = 100.0M
131125 19:53:30 InnoDB: Completed initialization of buffer pool
131125 19:53:30 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131125 19:53:30  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 420, file name ./ZWC-TEST.000003
131125 19:53:30  InnoDB: Waiting for the background threads to start
131125 19:53:31 Percona XtraDB (http://www.percona.com) 5.5.31-29.3 started; log sequence number 1600610
xtrabackup: export option is specified.
xtrabackup: export metadata of table 'test/t2' to file `./test/t2.exp` (1 indexes)
xtrabackup:     name=PRIMARY, id.low=15, page=3

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 420, file name ./ZWC-TEST.000003

xtrabackup: starting shutdown with innodb_fast_shutdown = 0
131125 19:53:31  InnoDB: Starting shutdown...
131125 19:53:35  InnoDB: Shutdown completed; log sequence number 1601824

131125 19:53:35  innobackupex: Restarting xtrabackup with command: xtrabackup_55  --defaults-file="/service/mysql5.5/my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/backup/5.5/2013-11-25_19-50-23 --export --tmpdir=/tmp
for creating ib_logfile*

xtrabackup_55 version 2.1.5 for Percona Server 5.5.31 Linux (x86_64) (revision id: 680)
xtrabackup: cd to /backup/5.5/2013-11-25_19-50-23
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
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 = 3
xtrabackup:   innodb_log_file_size = 5242880
xtrabackup: using the following InnoDB configuration for recovery:
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 = 3
xtrabackup:   innodb_log_file_size = 5242880
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
131125 19:53:35 InnoDB: The InnoDB memory heap is disabled
131125 19:53:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131125 19:53:35 InnoDB: Compressed tables use zlib 1.2.3
131125 19:53:35 InnoDB: Initializing buffer pool, size = 100.0M
131125 19:53:35 InnoDB: Completed initialization of buffer pool
131125 19:53:35  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...
131125 19:53:35  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...
131125 19:53:35  InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 5 MB
InnoDB: Database physically writes the file full: wait...
131125 19:53:35 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131125 19:53:35  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 420, file name ./ZWC-TEST.000003
131125 19:53:35  InnoDB: Waiting for the background threads to start
131125 19:53:36 Percona XtraDB (http://www.percona.com) 5.5.31-29.3 started; log sequence number 1602060
xtrabackup: export option is specified.
xtrabackup: export metadata of table 'test/t2' to file `./test/t2.exp` (1 indexes)
xtrabackup:     name=PRIMARY, id.low=15, page=3

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 420, file name ./ZWC-TEST.000003

xtrabackup: starting shutdown with innodb_fast_shutdown = 0
131125 19:53:36  InnoDB: Starting shutdown...
131125 19:53:40  InnoDB: Shutdown completed; log sequence number 1602060
131125 19:53:40  innobackupex: completed OK!

Copy the t2.ibd files from the backup to the database data directory

[root@test bin]# cp /backup/5.5/2013-11-25_19-50-23/test/t2.ibd /data/mysql5.5/test/
[root@test bin]# chown mysql.mysql /data/mysql5.5/test/t2.ibd 

Import tablespace.

root@test 07:55:55>select count(*) from t2;
ERROR 1030 (HY000): Got error -1 from storage engine
root@test 07:55:56>
root@test 07:55:56>
root@test 07:55:57>alter table t2 import tablespace;
Query OK, 0 rows affected (0.00 sec)

root@test 07:56:04>select count(*) from t2;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

root@test 07:56:08>select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
+----+------+
2 rows in set (0.00 sec)

root@test 07:56:13>