使用xtrabackup恢复被单表数据

mysql版本
mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.22-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| live_koo   |
+------------+
1 row in set (0.00 sec)

物理备份全库：
[root@test11g scripts]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock --defaults-file=/tmp/my.cnf /tol/xtrabackup/full --no-timestamp

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.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

150101 16:57:16  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/tmp/my.cnf;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
150101 16:57:16  innobackupex: Connected to MySQL server
150101 16:57:16  innobackupex: Executing a version check against the server...
150101 16:57:16  innobackupex: Done.
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.6.22-enterprise-commercial-advanced-log

innobackupex: Created backup directory /tol/xtrabackup/full

150101 16:57:16  innobackupex: Starting ibbackup with command: xtrabackup_56  --defaults-file="/tmp/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tol/xtrabackup/full --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=21328) to suspend
innobackupex: Suspend file '/tol/xtrabackup/full/xtrabackup_suspended_2'

xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (x86_64) (revision id: 702)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /tol/mysql/data/
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 = 2
xtrabackup:   innodb_log_file_size = 2097152000
>> log scanned up to (91560373)
InnoDB: Allocated tablespace 4, old maximum was 0
[01] Copying ./ibdata1 to /tol/xtrabackup/full/ibdata1
[01]        ...done
[01] Copying ./mysql/slave_master_info.ibd to /tol/xtrabackup/full/mysql/slave_master_info.ibd
[01]        ...done
[01] Copying ./mysql/slave_worker_info.ibd to /tol/xtrabackup/full/mysql/slave_worker_info.ibd
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /tol/xtrabackup/full/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /tol/xtrabackup/full/mysql/slave_relay_log_info.ibd
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /tol/xtrabackup/full/mysql/innodb_index_stats.ibd
[01]        ...done
[01] Copying ./live_koo/live_room_failing.ibd to /tol/xtrabackup/full/live_koo/live_room_failing.ibd
[01]        ...done
[01] Copying ./live_koo/live_room.ibd to /tol/xtrabackup/full/live_koo/live_room.ibd
[01]        ...done
[01] Copying ./live_koo/live_consumer_koo.ibd to /tol/xtrabackup/full/live_koo/live_consumer_koo.ibd
[01]        ...done
[01] Copying ./live_koo/live_users.ibd to /tol/xtrabackup/full/live_koo/live_users.ibd
[01]        ...done
[01] Copying ./live_koo/live_stats_detail.ibd to /tol/xtrabackup/full/live_koo/live_stats_detail.ibd
>> log scanned up to (91560373)
[01]        ...done
[01] Copying ./live_koo/live_stats_num.ibd to /tol/xtrabackup/full/live_koo/live_stats_num.ibd
[01]        ...done
[01] Copying ./live_koo/live_provider_soooner.ibd to /tol/xtrabackup/full/live_koo/live_provider_soooner.ibd
[01]        ...done
>> log scanned up to (91560373)
xtrabackup: Creating suspend file '/tol/xtrabackup/full/xtrabackup_suspended_2' with pid '21328'


150101 16:57:19  innobackupex: Continuing after ibbackup has suspended
150101 16:57:19  innobackupex: Starting to lock all tables...
150101 16:57:19  innobackupex: All tables locked and flushed to disk

150101 16:57:19  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/tol/mysql/data/'
innobackupex: Backing up files '/tol/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
>> log scanned up to (91560373)
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_room_failing.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_users.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/db.opt'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_stats_num.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_consumer_koo.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_provider_soooner.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_room.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_stats_detail.frm'
innobackupex: Backing up files '/tol/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
150101 16:57:20  innobackupex: Finished backing up non-InnoDB tables and files

150101 16:57:20  innobackupex: Waiting for log copying to finish

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

xtrabackup: Creating suspend file '/tol/xtrabackup/full/xtrabackup_log_copied' with pid '21328'
xtrabackup: Transaction log of lsn (91560373) to (91560373) was copied.
150101 16:57:21  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/tol/xtrabackup/full'
innobackupex: MySQL binlog position: filename 'mysql-bin.000022', position 78332367
150101 16:57:21  innobackupex: Connection to database server closed
150101 16:57:21  innobackupex: completed OK!

测试用表：34258条数据
mysql> select count(*) from live_users;
+----------+
| count(*) |
+----------+
|    34258 |
+----------+
1 row in set (0.02 sec)

mysql> delete from live_users limit 10000;      
Query OK, 10000 rows affected (0.09 sec)

mysql> select count(*) from live_users;         
+----------+
| count(*) |
+----------+
|    24258 |
+----------+
1 row in set (0.01 sec)

apply log 
[root@test11g scripts]# innobackupex --apply-log --defaults-file=/tmp/my.cnf /tol/xtrabackup/full/

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.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

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

150101 16:59:35  innobackupex: Starting ibbackup with command: xtrabackup_56  --defaults-file="/tmp/my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/tol/xtrabackup/full --tmpdir=/tmp

xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (x86_64) (revision id: 702)
xtrabackup: cd to /tol/xtrabackup/full
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(91560373)
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)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 1625987 and 1625987 in ibdata files do not match the log sequence number 91560373 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages 
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 78332237, file name mysql-bin.000022
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
2015-01-01 16:59:35 7f8de9187700  InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2015-01-01 16:59:35 7f8de9187700  InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: 5.6.11 started; log sequence number 91560373

[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 78332237, file name mysql-bin.000022

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 91560383

150101 16:59:36  innobackupex: Restarting xtrabackup with command: xtrabackup_56  --defaults-file="/tmp/my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/tol/xtrabackup/full --tmpdir=/tmp
for creating ib_logfile*

xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (x86_64) (revision id: 702)
xtrabackup: cd to /tol/xtrabackup/full
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 = 2
xtrabackup:   innodb_log_file_size = 2097152000
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 = 2
xtrabackup:   innodb_log_file_size = 2097152000
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 2000 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
InnoDB: Setting log file ./ib_logfile1 size to 2000 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=91560383
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
2015-01-01 16:59:45 7f8fde5ef700  InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2015-01-01 16:59:45 7f8fde5ef700  InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: 5.6.11 started; log sequence number 91560460

[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 78332237, file name mysql-bin.000022

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 91560470
150101 16:59:46  innobackupex: completed OK!

备份现在的ibd文件（可选） 
[root@test11g live_koo]# cp live_users.ibd live_users.ibd_bak

舍弃现在ibd文件
mysql> alter table live_users discard tablespace;
Query OK, 0 rows affected (0.02 sec)

复制备份的ibd文件
[root@test11g live_koo]# cp /tol/xtrabackup/full/live_koo/live_users.ibd ./
[root@test11g live_koo]# chown mysql:mysql live_users.ibd

导入ibd文件
mysql> alter table live_users import tablespace; 
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> select count(*) from live_users;
+----------+
| count(*) |
+----------+
|    34258 |
+----------+

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1386946/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1386946/