MySQL 采用Xtrabackup对数据库进行全库备份
来源：互联网 发布：人工智能高端人才 编辑：程序博客网 时间：2024/05/07 22:18
1，xtrabackup简介
关于数据库备份以及备份工具，参考：http://blog.itpub.net/26230597/viewspace-1460065/，这里来介绍xtrabackup已经如何使用xtrabackup进行对数据库的全备份已经恢复。
1. 下载：
wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.1.6/binary/Linux/x86_64/percona-xtrabackup-2.1.6-702-Linux-x86_64.tar.gz
2. 解压：
tar percona-xtrabackup-2.1.6-702-Linux-x86_64.tar.gz
3. 进入目录：
cd percona-xtrabackup-2.1.5-Linux-x86_64
4. 复制：
cd bin
cp * /usr/bin
下面就可以启动备份了
4，二大工具组件
xtrabackup：支持innodb存储引擎表，xtradb存储引擎表
innobackupex：支持innodb存储引擎表、xtradb存储引擎表、myisam存储引擎表。
5，一些重要参数
--defaults-file
同xtrabackup的--defaults-file参数
--apply-log
对xtrabackup的--prepare参数的封装
--copy-back
做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir ；
--remote-host=HOSTNAME
通过ssh将备份数据存储到进程服务器上；
--stream=[tar]
备份文件输出格式, tar时使用tar4ibd , 该文件可在XtarBackup binary文件中获得.如果备份时有指定--stream=tar, 则tar4ibd文件所处目录一定要在$PATH中(因为使用的是tar4ibd去压缩, 在XtraBackup的binary包中可获得该文件)。
在使用参数stream=tar备份的时候，你的xtrabackup_logfile可能会临时放在/tmp目录下，如果你备份的时候并发写入较大的话 xtrabackup_logfile可能会很大(5G+)，很可能会撑满你的/tmp目录，可以通过参数--tmpdir指定目录来解决这个问题。
--tmpdir=DIRECTORY
当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir
--redo-only--apply-log组,
强制备份日志时只redo ,跳过rollback。这在做增量备份时非常必要。
--use-memory=#
该参数在prepare的时候使用，控制prepare时innodb实例使用的内存量，用来取代my.cnf中的buffer_pool_size值，如果你是独立服务器，而且内存足够大的话，为了加快备份恢复的效率，你可以调大--use-memory这个参数值
--throttle=IOS
同xtrabackup的--throttle参数
--sleep=是给ibbackup使用的，指定每备份1M数据，过程停止拷贝多少毫秒，也是为了在备份时尽量减小对正常业务的影响，具体可以查看ibbackup的手册；
--compress[=LEVEL]
对备份数据迚行压缩，仅支持ibbackup，xtrabackup还没有实现；
--include=REGEXP
对 xtrabackup参数--tables的封装，也支持ibbackup。备份包含的库表，例如：--include="test.*"，意思是要备份 test库中所有的表。如果需要全备份，则省略这个参数；如果需要备份test库下的2个表：test1和test2,则写成：--include="test.test1|test.test2"。也可以使用通配符，如：--include="test.test*"。
--databases=LIST
列出需要备份的databases，如果没有指定该参数，所有包含MyISAM和InnoDB表的database都会被备份；
--uncompress
解压备份的数据文件，支持ibbackup，xtrabackup还没有实现该功能；
--slave-info
备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0
--socket=SOCKET
指定mysql.sock所在位置，以便备份进程登录mysql.
--safe-slave-backup
则会暂停Slave的SQL线程,等待到没有打开的临时表的时候开始备份.备份结束后SQL线程会自动启动,这样就可以确保一致性的复制状态.
6，xtrabackup全备份
6.1 全备份
只备份不压缩：
xtrabackup --defaults-file=/etc/my.cnf--user=root --password="" --port=3306 --backup--target-dir=/data/backups/mysql/$(date +%Y%m%d)/
备份压缩：
xtrabackup --defaults-file=/etc/my.cnf--user=root --password="" --port=3306 --backup --socket=/usr/local/mysql/mysql.sock--stream=tar --target-dir=/data/backups/mysql/$(date +%Y%m%d)/ |gzip >alldbfullbackup.tar.gz
参考：http://www.percona.com/doc/percona-xtrabackup/2.2/howtos/recipes_ibkx_compressed.html
备份过程如下所示：
[root@data01 mysql]# xtrabackup--defaults-file=/etc/my.cnf --user=root --password="" --port=3306--backup --socket=/usr/local/mysql/mysql.sock --stream=tar--target-dir=/data/backups/mysql/$(date +%Y%m%d)/ |gzip > alldbfullbackup.tar.gz
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 67108864
>> log scanned up to (423916780)
xtrabackup: Generating a list oftablespaces
[01] Streaming ./IBdata1
>> log scanned up to (423916780)
>> log scanned up to (423916780)
>> log scanned up to (423916780)
>> log scanned up to (423916780)
>> log scanned up to (423916780)
[01] ...done
[01] Streaming ./IBdata2
>> log scanned up to (423916780)
>> log scanned up to (423916780)
>> log scanned up to (423916780)
[01] ...done
[01] Streaming./mysql/slave_master_info.ibd
[01] ...done
[01] Streaming./mysql/slave_relay_log_info.ibd
[01] ...done
[01] Streaming./mysql/innodb_index_stats.ibd
[01] ...done
[01] Streaming./mysql/slave_worker_info.ibd
[01] ...done
[01] Streaming./mysql/innodb_table_stats.ibd
[01] ...done
[01] Streaming ./sbtest/sbtest1.ibd
[01] ...done
[01] Streaming ./sbtest/sbtest5.ibd
[01] ...done
[01] Streaming ./sbtest/sbtest8.ibd
[01] ...done
[01] Streaming ./sbtest/sbtest4.ibd
[01] ...done
[01] Streaming ./sbtest/sbtest2.ibd
[01] ...done
[01] Streaming ./sbtest/sbtest6.ibd
[01] ...done
[01] Streaming ./sbtest/sbtest7.ibd
[01] ...done
[01] Streaming ./sbtest/sbtest3.ibd
[01] ...done
[01] Streaming ./test/t1.ibd
[01] ...done
[01] Streaming ./test/t3.ibd
[01] ...done
[01] Streaming ./test/t.ibd
[01] ...done
[01] Streaming ./user_db/t1.ibd
[01] ...done
[01] Streaming ./user_db/test.ibd
[01] ...done
[01] Streaming ./user_db/test2.ibd
[01] ...done
[01] Streaming ./user_db/t3.ibd
>> log scanned up to (423916780)
[01] ...done
[01] Streaming ./user_db/UC_USER.ibd
>> log scanned up to (423916780)
[01] ...done
[01] Streaming ./user_db/t.ibd
[01] ...done
>> log scanned up to (423916780)
xtrabackup: The latest check point (forincremental): '423916780'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423916780)
xtrabackup: Transaction log of lsn(423916780) to (423916780) was copied.
[root@data01 mysql]#
备份结束后，可以看到如下结果，备份目录为null，都压缩到了alldbfullbackup.tar.gz备份压缩包里面，如下所示：
[root@data01 mysql]# du -sh *
4.0K 20150318
5.1M alldbfullbackup.tar.gz
[root@data01 mysql]#
PS1:可以用gzip边备份边压缩，不过推荐是备份完了之后对整个备份目录进行压缩。
6.2 全量恢复
先删除测试表数据：
mysql> select * from t1;
+---+----+
| a | b |
+---+----+
| 5 | aa |
+---+----+
1 row in set (0.03 sec)
mysql> delete from t1;
Query OK, 1 row affected (0.02 sec)
mysql> select * from t1;
Empty set (0.00 sec)
mysql> exit
Bye
进行恢复
xtrabackup --defaults-file=/etc/my.cnf--prepare --target-dir=/data/backups/mysql/20150318
停止msyql数据库
service mysqld5612 stop
复制已经恢复好的文件到mysql数据目录并且赋予mysql账号执行权限
cd /data/backups/mysql/20150318
rsync -rvt --exclude'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./ /home/data/mysql/data
chown -R mysql:mysql /home/data/mysql/data
启动mysql数据库
service mysqld5612 start
恢复完毕，check表t1数据，数据恢复回来了：
mysql> use test;
Database changed
mysql> select * from t1;
+---+----+
| a | b |
+---+----+
| 5 | aa |
+---+----+
1 row in set (0.09 sec)
mysql>
PS2： xtrabackup只备份InnoDB数据文件，表结构是不备份的，所以恢复的时候，你必须有对应表结构文件(.frm)。如下所示，只备份了*.ibd文件：
[root@data01 20150318]# ll
总用量 262168
-rw-r-----. 1 root root 134217728 3月 18 02:31 IBdata1
-rw-r-----. 1 root root 134217728 3月 18 02:31 IBdata2
drwx------. 2 root root 4096 3月 18 02:31 mysql
drwx------. 2 root root 4096 3月 18 02:31 sbtest
drwx------. 2 root root 4096 3月 18 02:31 test
drwx------. 2 root root 4096 3月 18 02:31 user_db
-rw-r-----. 1 root root 93 3月 18 02:31 xtrabackup_checkpoints
-rw-r-----. 1 root root 2560 3月 18 02:31 xtrabackup_logfile
[root@data01 20150318]# cd test/
[root@data01 test]# ll
总用量 288
-rw-r-----. 1 root root 98304 3月 18 02:31 t1.ibd
-rw-r-----. 1 root root 98304 3月 18 02:31 t3.ibd
-rw-r-----. 1 root root 98304 3月 18 02:31 t.ibd
[root@data01 test]#
7 innobackupex全备份
备份过程如下图2.png所示：
7.1 全备份
innobackupex --defaults-file=/etc/my.cnf--user=root --password="" --target-dir=/data/backups/mysql/$(date+%Y%m%d)/ --no-timestamp --parallel=2
备份过程如下：
[root@data01 ~]# innobackupex--defaults-file=/etc/my.cnf --user=root --password=""/data/backups/mysql/$(date +%Y%m%d)/ --no-timestamp --parallel=2
InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.
Get the latest version of PerconaXtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
150318 19:32:40 innobackupex: Connecting to MySQL server withDSN'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup'as 'root' (using password: NO).
150318 19:32:40 innobackupex: Connected to MySQL server
150318 19:32:40 innobackupex: Executing a version checkagainst the server...
150318 19:32:40 innobackupex: Done.
150318 19:32:40 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup runcompletes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using server version 5.6.12-log
innobackupex: Created backup directory/data/backups/mysql/20150318
150318 19:32:40 innobackupex: Starting ibbackup with command:xtrabackup --defaults-file="/etc/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/20150318 --tmpdir=/tmp --extra-lsndir='/tmp'--parallel=2
innobackupex: Waiting for ibbackup(pid=3648) to suspend
innobackupex: Suspend file'/data/backups/mysql/20150318/xtrabackup_suspended_2'
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 67108864
>> log scanned up to (423918562)
xtrabackup: Generating a list oftablespaces
xtrabackup: Starting 2 threads for paralleldata files transfer
[02] Copying ./IBdata1 to/data/backups/mysql/20150318/IBdata1
[01] Copying ./IBdata2 to/data/backups/mysql/20150318/IBdata2
>> log scanned up to (423918562)
>> log scanned up to (423918562)
[02] ...done
[02] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/20150318/mysql/slave_master_info.ibd
[02] ...done
[02] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/20150318/mysql/slave_relay_log_info.ibd
[02] ...done
[02] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/20150318/mysql/innodb_index_stats.ibd
[02] ...done
[02] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/20150318/mysql/slave_worker_info.ibd
[02] ...done
[02] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/20150318/mysql/innodb_table_stats.ibd
[02] ...done
[02] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/20150318/sbtest/sbtest1.ibd
[02] ...done
[02] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/20150318/sbtest/sbtest5.ibd
[02] ...done
[02] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/20150318/sbtest/sbtest8.ibd
[02] ...done
[02] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/20150318/sbtest/sbtest4.ibd
[02] ...done
[02] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/20150318/sbtest/sbtest2.ibd
[02] ...done
[02] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/20150318/sbtest/sbtest6.ibd
[02] ...done
[02] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/20150318/sbtest/sbtest7.ibd
[02] ...done
[02] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/20150318/sbtest/sbtest3.ibd
[02] ...done
[02] Copying ./test/t1.ibd to/data/backups/mysql/20150318/test/t1.ibd
[02] ...done
[02] Copying ./test/t3.ibd to/data/backups/mysql/20150318/test/t3.ibd
[02] ...done
[02] Copying ./test/t.ibd to/data/backups/mysql/20150318/test/t.ibd
[02] ...done
[02] Copying ./user_db/t1.ibd to/data/backups/mysql/20150318/user_db/t1.ibd
[02] ...done
[02] Copying ./user_db/test.ibd to/data/backups/mysql/20150318/user_db/test.ibd
[02] ...done
[02] Copying ./user_db/test2.ibd to/data/backups/mysql/20150318/user_db/test2.ibd
[02] ...done
[01] ...done
[01] Copying ./user_db/t3.ibd to /data/backups/mysql/20150318/user_db/t3.ibd
[02] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/20150318/user_db/UC_USER.ibd
[01] ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/20150318/user_db/t.ibd
>> log scanned up to (423918562)
[01] ...done
[02] ...done
>> log scanned up to (423918562)
xtrabackup: Creating suspend file'/data/backups/mysql/20150318/xtrabackup_suspended_2' with pid '3648'
150318 19:32:45 innobackupex: Continuing after ibbackup hassuspended
150318 19:32:45 innobackupex: Executing FLUSH TABLES WITHREAD LOCK...
150318 19:32:45 innobackupex: All tables locked and flushedto disk
150318 19:32:45 innobackupex: Starting to backup non-InnoDBtables and files
innobackupex: in subdirectories of '/home/data/mysql/data/'
innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files)
>> log scanned up to (423918562)
innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files)
>> log scanned up to (423918562)
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest3.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest7.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm'
>> log scanned up to (423918562)
innobackupex: Backing up file'/home/data/mysql/data//user_db/t.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm'
150318 19:32:48 innobackupex: Finished backing up non-InnoDBtables and files
150318 19:32:48 innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...
150318 19:32:48 innobackupex: Waiting for log copying tofinish
xtrabackup: The latest check point (forincremental): '423918562'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423918562)
xtrabackup: Creating suspend file'/data/backups/mysql/20150318/xtrabackup_log_copied' with pid '3648'
xtrabackup: Transaction log of lsn(423918562) to (423918562) was copied.
150318 19:32:49 innobackupex: All tables unlocked
innobackupex: Backup created in directory'/data/backups/mysql/20150318'
innobackupex: MySQL binlog position:filename 'mysql-bin.000064', position 120
150318 19:32:49 innobackupex: Connection to database serverclosed
150318 19:32:49 innobackupex: completed OK!
[root@data01 ~]#
7.2 恢复
删除数据：
mysql> use test;
Database changed
mysql> select * from t1;
+---+----+
| a | b |
+---+----+
| 5 | aa |
+---+----+
1 row in set (0.09 sec)
mysql> delete from t1;
Query OK, 1 row affected (0.06 sec)
mysql> select * from t1;
Empty set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.08 sec)
mysql>
进行恢复：
innobackupex --defaults-file=/etc/my.cnf --use-memory=4G--apply-log /data/backups/mysql/$(date +%Y%m%d)/
恢复过程记录如下：
[root@data01 ~]# innobackupex--defaults-file=/etc/my.cnf --apply-log /data/backups/mysql/$(date +%Y%m%d)/
InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.
Get the latest version of PerconaXtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
150318 20:09:35 innobackupex: Starting the apply-logoperation
IMPORTANT: Please check that the apply-logrun completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
150318 20:09:35 innobackupex: Starting ibbackup with command:xtrabackup --defaults-file="/data/backups/mysql/20150318/backup-my.cnf" --defaults-group="mysqld" --prepare--target-dir=/data/backups/mysql/20150318
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to/data/backups/mysql/20150318
xtrabackup: This target seems to be notprepared yet.
xtrabackup: xtrabackup_logfile detected:size=2654208, start_lsn=(423918562)
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2654208
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2654208
xtrabackup: Starting InnoDB instance forrecovery.
xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count bufferpool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size =100.0M
InnoDB: Completed initialization of bufferpool
InnoDB: Highest supported file format isBarracuda.
InnoDB: The log sequence numbers 423918522and 423918522 in ibdata files do not match the log sequence number 423918562 inthe ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information fromthe .ibd files...
InnoDB: Restoring possible half-writtendata pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.22 started; log sequence number423918562
[notice (again)]
Ifyou use binary log and don't use any hack of group commit,
thebinary log position seems to be:
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequencenumber 423919216
150318 20:09:41 innobackupex: Restarting xtrabackup withcommand: xtrabackup --defaults-file="/data/backups/mysql/20150318/backup-my.cnf" --defaults-group="mysqld" --prepare--target-dir=/data/backups/mysql/20150318
for creating ib_logfile*
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to/data/backups/mysql/20150318
xtrabackup: This target seems to be alreadyprepared.
xtrabackup: notice: xtrabackup_logfile wasalready used to '--prepare'.
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 67108864
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = IBdata1:128M;IBdata2:128M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 67108864
xtrabackup: Starting InnoDB instance forrecovery.
xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count bufferpool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size =100.0M
InnoDB: Completed initialization of bufferpool
InnoDB: Setting log file ./ib_logfile101size to 64 MB
InnoDB: Setting log file ./ib_logfile1 sizeto 64 MB
InnoDB: Setting log file ./ib_logfile2 sizeto 64 MB
InnoDB: Renaming log file ./ib_logfile101to ./ib_logfile0
InnoDB: New log files created,LSN=423919216
InnoDB: Highest supported file format isBarracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.22 started; log sequence number423919628
[notice (again)]
Ifyou use binary log and don't use any hack of group commit,
thebinary log position seems to be:
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequencenumber 423920158
150318 20:09:46 innobackupex: completed OK!
[root@data01 ~]#
修复：RESTORETHE BACKUP
停止mysql服务
[root@data01 ~]# service mysqld5612 stop
Shutting down MySQL.. SUCCESS!
[root@data01 ~]#
然后修复—copy-back：
[root@data01 ~]# innobackupex--defaults-file=/etc/my.cnf --copy-back /data/backups/mysql/$(date +%Y%m%d)/
InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.
Get the latest version of PerconaXtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
150318 20:18:38 innobackupex: Starting the copy-backoperation
IMPORTANT: Please check that the copy-backrun completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex: Starting to copy files in'/data/backups/mysql/20150318'
innobackupex: back to original datadirectory '/home/data/mysql/data'
innobackupex: Copying'/data/backups/mysql/20150318/xtrabackup_binlog_pos_innodb' to'/home/data/mysql/data/xtrabackup_binlog_pos_innodb'
innobackupex: Copying'/data/backups/mysql/20150318/xtrabackup_info' to'/home/data/mysql/data/xtrabackup_info'
innobackupex: Creating directory'/home/data/mysql/data/performance_schema'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_waits_history.frm' to'/home/data/mysql/data/performance_schema/events_waits_history.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_waits_summary_global_by_event_name.frm'to'/home/data/mysql/data/performance_schema/events_waits_summary_global_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/threads.frm' to'/home/data/mysql/data/performance_schema/threads.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_stages_history_long.frm'to '/home/data/mysql/data/performance_schema/events_stages_history_long.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/file_summary_by_instance.frm'to '/home/data/mysql/data/performance_schema/file_summary_by_instance.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_waits_summary_by_user_by_event_name.frm'to '/home/data/mysql/data/performance_schema/events_waits_summary_by_user_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_statements_summary_by_account_by_event_name.frm'to '/home/data/mysql/data/performance_schema/events_statements_summary_by_account_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_statements_summary_by_user_by_event_name.frm'to'/home/data/mysql/data/performance_schema/events_statements_summary_by_user_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_waits_current.frm' to'/home/data/mysql/data/performance_schema/events_waits_current.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/table_lock_waits_summary_by_table.frm'to'/home/data/mysql/data/performance_schema/table_lock_waits_summary_by_table.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/socket_instances.frm' to'/home/data/mysql/data/performance_schema/socket_instances.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/setup_actors.frm' to'/home/data/mysql/data/performance_schema/setup_actors.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/table_io_waits_summary_by_table.frm'to'/home/data/mysql/data/performance_schema/table_io_waits_summary_by_table.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_stages_summary_by_account_by_event_name.frm'to '/home/data/mysql/data/performance_schema/events_stages_summary_by_account_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/cond_instances.frm' to'/home/data/mysql/data/performance_schema/cond_instances.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/users.frm' to'/home/data/mysql/data/performance_schema/users.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/db.opt' to'/home/data/mysql/data/performance_schema/db.opt'
innobackupex: Copying '/data/backups/mysql/20150318/performance_schema/events_waits_summary_by_instance.frm'to'/home/data/mysql/data/performance_schema/events_waits_summary_by_instance.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_stages_summary_by_thread_by_event_name.frm'to'/home/data/mysql/data/performance_schema/events_stages_summary_by_thread_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_statements_current.frm'to '/home/data/mysql/data/performance_schema/events_statements_current.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/objects_summary_global_by_type.frm'to'/home/data/mysql/data/performance_schema/objects_summary_global_by_type.frm'
innobackupex: Copying '/data/backups/mysql/20150318/performance_schema/events_waits_summary_by_thread_by_event_name.frm'to'/home/data/mysql/data/performance_schema/events_waits_summary_by_thread_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_waits_summary_by_host_by_event_name.frm'to'/home/data/mysql/data/performance_schema/events_waits_summary_by_host_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/socket_summary_by_event_name.frm'to '/home/data/mysql/data/performance_schema/socket_summary_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_stages_history.frm' to'/home/data/mysql/data/performance_schema/events_stages_history.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_statements_history.frm'to '/home/data/mysql/data/performance_schema/events_statements_history.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_statements_summary_by_thread_by_event_name.frm'to'/home/data/mysql/data/performance_schema/events_statements_summary_by_thread_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_stages_summary_global_by_event_name.frm'to'/home/data/mysql/data/performance_schema/events_stages_summary_global_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/setup_timers.frm' to'/home/data/mysql/data/performance_schema/setup_timers.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/hosts.frm' to'/home/data/mysql/data/performance_schema/hosts.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_stages_summary_by_host_by_event_name.frm'to '/home/data/mysql/data/performance_schema/events_stages_summary_by_host_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/host_cache.frm' to'/home/data/mysql/data/performance_schema/host_cache.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/setup_objects.frm' to'/home/data/mysql/data/performance_schema/setup_objects.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/setup_consumers.frm' to'/home/data/mysql/data/performance_schema/setup_consumers.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/session_account_connect_attrs.frm'to '/home/data/mysql/data/performance_schema/session_account_connect_attrs.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_statements_summary_by_host_by_event_name.frm'to'/home/data/mysql/data/performance_schema/events_statements_summary_by_host_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/socket_summary_by_instance.frm'to '/home/data/mysql/data/performance_schema/socket_summary_by_instance.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_statements_summary_global_by_event_name.frm'to'/home/data/mysql/data/performance_schema/events_statements_summary_global_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_stages_summary_by_user_by_event_name.frm'to '/home/data/mysql/data/performance_schema/events_stages_summary_by_user_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/mutex_instances.frm' to'/home/data/mysql/data/performance_schema/mutex_instances.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/setup_instruments.frm' to'/home/data/mysql/data/performance_schema/setup_instruments.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_statements_summary_by_digest.frm'to '/home/data/mysql/data/performance_schema/events_statements_summary_by_digest.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/rwlock_instances.frm' to'/home/data/mysql/data/performance_schema/rwlock_instances.frm'
innobackupex: Copying '/data/backups/mysql/20150318/performance_schema/file_summary_by_event_name.frm'to '/home/data/mysql/data/performance_schema/file_summary_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_waits_summary_by_account_by_event_name.frm'to'/home/data/mysql/data/performance_schema/events_waits_summary_by_account_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_stages_current.frm' to'/home/data/mysql/data/performance_schema/events_stages_current.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/session_connect_attrs.frm' to'/home/data/mysql/data/performance_schema/session_connect_attrs.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/performance_timers.frm' to'/home/data/mysql/data/performance_schema/performance_timers.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_waits_history_long.frm'to '/home/data/mysql/data/performance_schema/events_waits_history_long.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/file_instances.frm' to'/home/data/mysql/data/performance_schema/file_instances.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/events_statements_history_long.frm'to'/home/data/mysql/data/performance_schema/events_statements_history_long.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/table_io_waits_summary_by_index_usage.frm'to '/home/data/mysql/data/performance_schema/table_io_waits_summary_by_index_usage.frm'
innobackupex: Copying'/data/backups/mysql/20150318/performance_schema/accounts.frm' to'/home/data/mysql/data/performance_schema/accounts.frm'
innobackupex: Creating directory'/home/data/mysql/data/mysql'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/event.MYI' to'/home/data/mysql/data/mysql/event.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/columns_priv.frm' to'/home/data/mysql/data/mysql/columns_priv.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/db.MYD' to'/home/data/mysql/data/mysql/db.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/slave_master_info.ibd' to'/home/data/mysql/data/mysql/slave_master_info.ibd'
innobackupex: Copying '/data/backups/mysql/20150318/mysql/user.MYI'to '/home/data/mysql/data/mysql/user.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_transition_type.frm' to'/home/data/mysql/data/mysql/time_zone_transition_type.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/procs_priv.frm' to'/home/data/mysql/data/mysql/procs_priv.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_topic.MYI' to'/home/data/mysql/data/mysql/help_topic.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_name.MYI' to'/home/data/mysql/data/mysql/time_zone_name.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/slave_relay_log_info.ibd' to'/home/data/mysql/data/mysql/slave_relay_log_info.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/innodb_index_stats.ibd' to'/home/data/mysql/data/mysql/innodb_index_stats.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/columns_priv.MYI' to'/home/data/mysql/data/mysql/columns_priv.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/slow_log.CSV' to'/home/data/mysql/data/mysql/slow_log.CSV'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/func.MYI' to'/home/data/mysql/data/mysql/func.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/db.frm' to'/home/data/mysql/data/mysql/db.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/servers.MYD' to'/home/data/mysql/data/mysql/servers.MYD'
innobackupex: Copying '/data/backups/mysql/20150318/mysql/time_zone.MYI'to '/home/data/mysql/data/mysql/time_zone.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/proc.MYI' to'/home/data/mysql/data/mysql/proc.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/tables_priv.MYI' to'/home/data/mysql/data/mysql/tables_priv.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/slave_worker_info.frm' to'/home/data/mysql/data/mysql/slave_worker_info.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone.MYD' to'/home/data/mysql/data/mysql/time_zone.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/ndb_binlog_index.frm' to'/home/data/mysql/data/mysql/ndb_binlog_index.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/slow_log.CSM' to'/home/data/mysql/data/mysql/slow_log.CSM'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/ndb_binlog_index.MYD' to'/home/data/mysql/data/mysql/ndb_binlog_index.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/tables_priv.frm' to'/home/data/mysql/data/mysql/tables_priv.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/event.frm' to'/home/data/mysql/data/mysql/event.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/func.MYD' to '/home/data/mysql/data/mysql/func.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_keyword.MYD' to'/home/data/mysql/data/mysql/help_keyword.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/procs_priv.MYI' to'/home/data/mysql/data/mysql/procs_priv.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/slave_relay_log_info.frm' to'/home/data/mysql/data/mysql/slave_relay_log_info.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_leap_second.MYI' to '/home/data/mysql/data/mysql/time_zone_leap_second.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_transition.MYI' to'/home/data/mysql/data/mysql/time_zone_transition.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/func.frm' to'/home/data/mysql/data/mysql/func.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_keyword.frm' to'/home/data/mysql/data/mysql/help_keyword.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/procs_priv.MYD' to '/home/data/mysql/data/mysql/procs_priv.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_topic.MYD' to'/home/data/mysql/data/mysql/help_topic.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/plugin.MYI' to'/home/data/mysql/data/mysql/plugin.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/innodb_index_stats.frm' to'/home/data/mysql/data/mysql/innodb_index_stats.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_category.frm' to'/home/data/mysql/data/mysql/help_category.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_transition_type.MYD' to'/home/data/mysql/data/mysql/time_zone_transition_type.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/user.frm' to'/home/data/mysql/data/mysql/user.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/general_log.frm' to'/home/data/mysql/data/mysql/general_log.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_category.MYD' to'/home/data/mysql/data/mysql/help_category.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_relation.frm' to'/home/data/mysql/data/mysql/help_relation.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/proxies_priv.MYI' to'/home/data/mysql/data/mysql/proxies_priv.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/tables_priv.MYD' to'/home/data/mysql/data/mysql/tables_priv.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_transition_type.MYI' to'/home/data/mysql/data/mysql/time_zone_transition_type.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_transition.MYD' to'/home/data/mysql/data/mysql/time_zone_transition.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/general_log.CSM' to'/home/data/mysql/data/mysql/general_log.CSM'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/proxies_priv.MYD' to'/home/data/mysql/data/mysql/proxies_priv.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/ndb_binlog_index.MYI' to'/home/data/mysql/data/mysql/ndb_binlog_index.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/slave_master_info.frm' to '/home/data/mysql/data/mysql/slave_master_info.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_relation.MYI' to'/home/data/mysql/data/mysql/help_relation.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/db.MYI' to '/home/data/mysql/data/mysql/db.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_leap_second.MYD' to'/home/data/mysql/data/mysql/time_zone_leap_second.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_leap_second.frm' to '/home/data/mysql/data/mysql/time_zone_leap_second.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_name.MYD' to'/home/data/mysql/data/mysql/time_zone_name.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/proc.frm' to '/home/data/mysql/data/mysql/proc.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_relation.MYD' to'/home/data/mysql/data/mysql/help_relation.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/proxies_priv.frm' to '/home/data/mysql/data/mysql/proxies_priv.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/servers.MYI' to'/home/data/mysql/data/mysql/servers.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_keyword.MYI' to'/home/data/mysql/data/mysql/help_keyword.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/servers.frm' to'/home/data/mysql/data/mysql/servers.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_transition.frm' to'/home/data/mysql/data/mysql/time_zone_transition.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_category.MYI' to'/home/data/mysql/data/mysql/help_category.MYI'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone.frm' to'/home/data/mysql/data/mysql/time_zone.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/slave_worker_info.ibd' to'/home/data/mysql/data/mysql/slave_worker_info.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/innodb_table_stats.frm' to'/home/data/mysql/data/mysql/innodb_table_stats.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/slow_log.frm' to'/home/data/mysql/data/mysql/slow_log.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/user.MYD' to'/home/data/mysql/data/mysql/user.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/time_zone_name.frm' to'/home/data/mysql/data/mysql/time_zone_name.frm'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/plugin.MYD' to'/home/data/mysql/data/mysql/plugin.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/innodb_table_stats.ibd' to'/home/data/mysql/data/mysql/innodb_table_stats.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/columns_priv.MYD' to'/home/data/mysql/data/mysql/columns_priv.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/plugin.frm' to'/home/data/mysql/data/mysql/plugin.frm'
innobackupex: Copying '/data/backups/mysql/20150318/mysql/general_log.CSV'to '/home/data/mysql/data/mysql/general_log.CSV'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/event.MYD' to'/home/data/mysql/data/mysql/event.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/proc.MYD' to'/home/data/mysql/data/mysql/proc.MYD'
innobackupex: Copying'/data/backups/mysql/20150318/mysql/help_topic.frm' to'/home/data/mysql/data/mysql/help_topic.frm'
innobackupex: Creating directory'/home/data/mysql/data/sbtest'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest3.frm' to'/home/data/mysql/data/sbtest/sbtest3.frm'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest4.frm' to'/home/data/mysql/data/sbtest/sbtest4.frm'
innobackupex: Copying '/data/backups/mysql/20150318/sbtest/sbtest6.frm'to '/home/data/mysql/data/sbtest/sbtest6.frm'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest1.ibd' to'/home/data/mysql/data/sbtest/sbtest1.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest8.frm' to'/home/data/mysql/data/sbtest/sbtest8.frm'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest1.frm' to'/home/data/mysql/data/sbtest/sbtest1.frm'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest5.ibd' to '/home/data/mysql/data/sbtest/sbtest5.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/db.opt' to'/home/data/mysql/data/sbtest/db.opt'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest8.ibd' to'/home/data/mysql/data/sbtest/sbtest8.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest4.ibd' to'/home/data/mysql/data/sbtest/sbtest4.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest2.frm' to'/home/data/mysql/data/sbtest/sbtest2.frm'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest2.ibd' to'/home/data/mysql/data/sbtest/sbtest2.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest6.ibd' to'/home/data/mysql/data/sbtest/sbtest6.ibd'
innobackupex: Copying '/data/backups/mysql/20150318/sbtest/sbtest7.ibd'to '/home/data/mysql/data/sbtest/sbtest7.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest7.frm' to'/home/data/mysql/data/sbtest/sbtest7.frm'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest3.ibd' to'/home/data/mysql/data/sbtest/sbtest3.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/sbtest/sbtest5.frm' to'/home/data/mysql/data/sbtest/sbtest5.frm'
innobackupex: Creating directory'/home/data/mysql/data/test'
innobackupex: Copying'/data/backups/mysql/20150318/test/t1.ibd' to'/home/data/mysql/data/test/t1.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/test/t3.frm' to'/home/data/mysql/data/test/t3.frm'
innobackupex: Copying'/data/backups/mysql/20150318/test/t3.ibd' to'/home/data/mysql/data/test/t3.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/test/t.ibd' to '/home/data/mysql/data/test/t.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/test/t1.frm' to'/home/data/mysql/data/test/t1.frm'
innobackupex: Creating directory'/home/data/mysql/data/user_db'
innobackupex: Copying'/data/backups/mysql/20150318/user_db/UC_USER.frm' to'/home/data/mysql/data/user_db/UC_USER.frm'
innobackupex: Copying '/data/backups/mysql/20150318/user_db/t.frm'to '/home/data/mysql/data/user_db/t.frm'
innobackupex: Copying'/data/backups/mysql/20150318/user_db/t1.ibd' to'/home/data/mysql/data/user_db/t1.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/user_db/t3.frm' to'/home/data/mysql/data/user_db/t3.frm'
innobackupex: Copying'/data/backups/mysql/20150318/user_db/test.frm' to'/home/data/mysql/data/user_db/test.frm'
innobackupex: Copying'/data/backups/mysql/20150318/user_db/db.opt' to '/home/data/mysql/data/user_db/db.opt'
innobackupex: Copying'/data/backups/mysql/20150318/user_db/test.ibd' to'/home/data/mysql/data/user_db/test.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/user_db/test2.ibd' to'/home/data/mysql/data/user_db/test2.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/user_db/t3.ibd' to'/home/data/mysql/data/user_db/t3.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/user_db/UC_USER.ibd' to'/home/data/mysql/data/user_db/UC_USER.ibd'
innobackupex: Copying '/data/backups/mysql/20150318/user_db/test2.frm'to '/home/data/mysql/data/user_db/test2.frm'
innobackupex: Copying'/data/backups/mysql/20150318/user_db/t.ibd' to'/home/data/mysql/data/user_db/t.ibd'
innobackupex: Copying'/data/backups/mysql/20150318/user_db/t1.frm' to'/home/data/mysql/data/user_db/t1.frm'
innobackupex: Starting to copy InnoDBsystem tablespace
innobackupex: in'/data/backups/mysql/20150318'
innobackupex: back to original InnoDB datadirectory '/home/data/mysql/data'
innobackupex: Copying '/data/backups/mysql/20150318/IBdata1'to '/home/data/mysql/data/IBdata1'
innobackupex: Copying'/data/backups/mysql/20150318/IBdata2' to '/home/data/mysql/data/IBdata2'
innobackupex: Starting to copy InnoDB undotablespaces
innobackupex: in'/data/backups/mysql/20150318'
innobackupex: back to'/home/data/mysql/data'
innobackupex: Starting to copy InnoDB logfiles
innobackupex: in'/data/backups/mysql/20150318'
innobackupex: back to original InnoDB logdirectory '/home/data/mysql/data'
innobackupex: Copying'/data/backups/mysql/20150318/ib_logfile1' to'/home/data/mysql/data/ib_logfile1'
innobackupex: Copying'/data/backups/mysql/20150318/ib_logfile2' to'/home/data/mysql/data/ib_logfile2'
innobackupex: Copying '/data/backups/mysql/20150318/ib_logfile0'to '/home/data/mysql/data/ib_logfile0'
innobackupex: Finished copying back files.
150318 20:18:50 innobackupex: completed OK!
[root@data01 ~]#
赋予mysql用户访问权限，然后重启mysql数据服务，查看表t1是否已经恢复了，如下所示：
[root@data01 ~]# chown -R mysql.mysql/home/data/mysql/data/
[root@data01 ~]#
[root@data01 ~]# service mysqld5612 start
Starting MySQL..... SUCCESS!
[root@data01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.12-log Sourcedistribution
Copyright (c) 2000, 2013, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql> use test;
Database changed
mysql> select * from t1;
+---+----+
| a | b |
+---+----+
| 5 | aa |
+---+----+
1 row in set (0.08 sec)
PS：这里是比较简单的测试，更多的功能以及参数，大家可以去使用innobackupex --help|more 查看需要自己要的参数，然后自己去尝试，也可以参考官网的例子：http://www.percona.com/doc/percona-xtrabackup/2.2/how-tos.html。
8应用场景总结
xtrabackup备份恢复工具比较适合数据增长型数据库。对于数据增长型的库，由于数据的增长导致数据备份和恢复的空间和时间上的压力较大。而xtrabackup也有增量备份的功能，在短时间内可以通过进行增量备份来保证数据的安全性。而长期来看，仍然需要间断性的进行全库备份。此外，由于xtrabackup对innodb的数据库不进行锁定，因此对要求不影响线上服务的数据备份和恢复较适合。
而对于数据量无明显增长，且更新为主的数据更新型数据库，xtrabackup显得过于复杂。xtrabackup操作反而不如mysqldump的性能高。
----------------------------------------------------------------------------------------------------------------
<版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!>
原博客地址： http://blog.itpub.net/26230597/viewspace-1465772/
原作者：黄杉(mchdba)
----------------------------------------------------------------------------------------------------------------
- MySQL 采用Xtrabackup对数据库进行全库备份
- MySQL 采用Xtrabackup对数据库进行全库备份
- 利用Xtrabackup进行mysql数据库的备份
- 利用Xtrabackup进行mysql数据库的备份
- 使用xtrabackup进行MySQL数据库备份
- 使用 xtrabackup 进行MySQL数据库物理备份
- xtrabackup备份mysql数据库
- Xtrabackup全量备份与恢复mysql数据库
- 使用xtrabackup对MySQL进行备份和恢复
- xtrabackup对MySQL进行备份和恢复的全过程
- 使用Percona XtraBackup对MySQL进行在线热备份
- 使用xtrabackup对MySQL进行备份和恢复
- 使用Xtrabackup进行MySQL数据库备份和恢复测试
- Xtrabackup原理及使用innobackupex进行MySQL数据库备份恢复
- MySQL--使用xtrabackup进行备份还原
- MySQL Study之--MySQL innodb引擎备份工具XtraBackup之二（数据库全备）
- MySQL之Xtrabackup备份恢复数据库
- xtrabackup 备份mysql数据库二 ：innobackupex 命令
- CDN加载jQuery的优势
- 什么是 DNS Prefetch ?
- 第一讲:entityManager的配置详解
- CBO hint:no_unnest,push_subq,push_pred的用法
- 苹果开发 笔记（14）
- MySQL 采用Xtrabackup对数据库进行全库备份
- linux网络通讯程序无提示自动退出——SIGPIPE信号退出
- 面向对象设计原则之合成复用原则
- OC中子类和父类的转化问题
- 向Access中插入数据报“INSERT INTO 语句的语法错误”的解决办法
- Android中的模糊效果
- 疯狂Java之学习笔记（19）-----------包装类和匿名类
- Android学习（5）电话拨号器
- AutoCompletedEditView 搜索 变色
