如何从MySQL全备中恢复Innodb单表

如何从MySQL全备中恢复Innodb单表2014-10-28First, 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/

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！

本栏目更多精彩内容：http://www.bianceng.cn/database/MySQL/