一、完全备份。
1、mysql安装路径：/usr/local/mysql3307
2、mysql数据目录路径：/usr/local/mysql3307/data
3、备份文件在存储路径：/home/backupPrepare
4、xtrabackup安装路径：/root/lixia/percona-xtrabackup-2.0.0
5、进入/root/lixia/percona-xtrabackup-2.0.0/bin目录
6、执行完全备份：
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backupPrepare/ --innodb-log-file-size=268435456
注意：因为联机重做日志不是标准的5MB，所以要指定联机重做日志的文件的大小。
7、执行恢复：
./xtrabackup --prepare --target-dir=/home/backupPrepare/
./xtrabackup --prepare --target-dir=/home/backupPrepare/
注意：一定要执行两次。
关闭mysql
cd /home/backupPrepare/db1
cp t1.ibd /usr/local/mysql3307/data/db1/
注意：只能拷贝数据文件，千万不能拷贝备份中的公共表空间文件和日志文件覆盖数据库中的日志文件和公共表空间，一旦覆盖数据库将无法启动。需要注意的是，xtrabackup只备份数据文件，并不备份数据表结构(.frm)，所以使用xtrabackup恢复的时候，你必须有对应表结构文件(.frm)。
二、增量备份
1、执行完全备份：
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backuptest/all/ --innodb-log-file-size=268435456
注意：因为联机重做日志不是标准的512MB，所以要指定联机重做日志的文件的大小。
2、增量备份：
./xtrabackup --backup --target-dir=/home/backuptest/inck1/ --incremental-basedir=/home/backuptest/all/ --datadir=/usr/local/mysql3307/data/ --innodb-log-file-size=268435456
以第一个增量备份作为下个增量备份的基目录：
./xtrabackup --backup --target-dir=/home/backuptest/inck2/ --incremental-basedir=/home/backuptest/inck1/ --datadir=/usr/local/mysql3307/data/ --innodb-log-file-size=268435456
3、数据恢复
3.1 恢复完全备份：
./xtrabackup --prepare --apply-log-only --target-dir=/home/backuptest/all
3.2 恢复第一个增量备份：
./xtrabackup --prepare --apply-log-only --target-dir=/home/backuptest/all/ --incremental-dir=/home/backuptest/inck1
3.3 恢复第二个增量备份：
./xtrabackup --prepare --apply-log-only --target-dir=/home/backuptest/all/ --incremental-dir=/home/backuptest/inck2
注意：一定要按完全备份、第一个增量备份、第二个增量备份顺序恢复，如果在恢复增量备份一前恢复增量备份二，然后再恢复增量一备份，会地址增量备份一无法恢复，破坏备份。
3.4 把恢复好的表空间文件，复制到对应的数据中。
3.5 二进制日志偏移量记录的值，对于mysql-bin.000001日志的偏移量1082
[root@localhost all]# cat xtrabackup_binlog_pos_innodb
./mysql-bin.000001 1082
#bin/mysqlbinlog -vv data/mysql-bin.000001
# at 1055
#120618 16:55:57 server id 20 end_log_pos 1082 Xid = 15
COMMIT/*!*/;
三、按表备份做完全备份(只备份db1库的t1表)
bin/xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/ --innodb-log-file-size=268435456 --tables=db1.t1
四、备份整个db1数据库
bin/xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backupPrepare/ --innodb-log-file-size=268435456 --tables=db1
五、指定备份db1和db2库
bin/xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backupPrepare/ --innodb-log-file-size=268435456 --tables=db1,db2
指定恢复db1
cd /home/backupPrepare/
mkdir /home/backupPrepare/test
cp -R /home/backupPrepare/* /home/backupPrepare/test
./xtrabackup --prepare --target-dir=/home/backupPrepare/test/ --tables=db1
六、指定多库做增量备份及恢复指定库。
6.1 完全备份
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/db1db2/ --innodb-log-file-size=268435456 --tables=db1,db2
6.2 增量备份1
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/db1db2inck1/ --incremental-basedir=/home/backup/db1db2/ --innodb-log-file-size=268435456 --tables=db1,db2
6.3 增量备份2
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/db1db2inck2/ --incremental-basedir=/home/backup/db1db2inck1/ --innodb-log-file-size=268435456 --tables=db1,db2
6.4 恢复完整备份
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/db1db2
6.5 恢复增量备份一中db1
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/db1db2 --incremental-dir=/home/backup/db1db2inck1 --tables=db1
6.6 恢复增量备份二中的db2
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/db1db2 --incremental-dir=/home/backup/db1db2inck2 --tables=db1
6.7 恢复增量备份一中的DB2
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/db1db2 --incremental-dir=/home/backup/db1db2inck1 --tables=db2
提示错误：
xtrabackup: error: This incremental backup seems not to be proper for the target.
xtrabackup: Check 'to_lsn' of the target and 'from_lsn' of the incremental.
七、指定多库做增量备份及恢复指定库(每个库分开做增量备份共用完整备份，该方法不可行)。
备份
7.1 做完全备份
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/201206201141/db1db2/ --innodb-log-file-size=268435456 --tables=db1,db2
7.2 db1做增量备份一
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/201206201141/db1inck1/ --incremental-basedir=/home/backup/201206201141/db1db2/ --innodb-log-file-size=268435456 --tables=db1
7.3 db2做增量备份一
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/201206201141/db2inck1/ --incremental-basedir=/home/backup/201206201141/db1db2/ --innodb-log-file-size=268435456 --tables=db2
7.4 db1做增量备份二
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/201206201141/db1inck2/ --incremental-basedir=/home/backup/201206201141/db1inck1/ --innodb-log-file-size=268435456 --tables=db1
7.5 db2做增量备份二
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/201206201141/db2inck2/ --incremental-basedir=/home/backup/201206201141/db2inck1/ --innodb-log-file-size=268435456 --tables=db2
恢复
7.6 恢复完整备份
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/201206201141/db1db2
7.7 db1恢复增量备份一
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/201206201141/db1db2 --incremental-dir=/home/backup/201206201141/db1inck1 --tables=db1
7.8 db1恢复增量备份二
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/201206201141/db1db2 --incremental-dir=/home/backup/201206201141/db1inck2 --tables=db1
7.9 db2恢复增量备份一(出错)
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/201206201141/db1db2 --incremental-dir=/home/backup/201206201141/db2inck1 --tables=db2
xtrabackup: tables regcomp(db2): Success
./xtrabackup version 2.0.0 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined)
incremental backup from 1618793 is enabled.
xtrabackup: cd to /home/backup/db1db2
xtrabackup: This target seems to be already prepared.
xtrabackup: error: This incremental backup seems not to be proper for the target.
xtrabackup: Check 'to_lsn' of the target and 'from_lsn' of the incremental.
8、指定库做增量备份及恢复指定库 (每个库使用独立的完整和增量备份)
8.1 db1完整备份
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/db1db2/ --innodb-log-file-size=268435456 --tables=db1,db2 --tables=db1
8.2 db2做完整备份
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/db2/ --innodb-log-file-size=268435456 --tables=db1,db2 --tables=db2
8.3 db1增量备份一
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/db1inck1/ --incremental-basedir=/home/backup/db1db2/ --innodb-log-file-size=268435456 --tables=db1
8.4 db2做增量备份一
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/db2inck1/ --incremental-basedir=/home/backup/db2/ --innodb-log-file-size=268435456 --tables=db2
8.5 db1做增量备份二
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/db1inck2/ --incremental-basedir=/home/backup/db1inck1/ --innodb-log-file-size=268435456 --tables=db1
8.6 db2做增量备份二
./xtrabackup --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/backup/db2inck2/ --incremental-basedir=/home/backup/db2inck1/ --innodb-log-file-size=268435456 --tables=db2
8.7 恢复db1
8.7.1 db1恢复完整备份
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/db1db2
8.7.2 db1恢复增量备份一
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/db1db2 --incremental-dir=/home/backup/db1inck1 --tables=db1
8.7.3 db1恢复完整备份二
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/db1db2 --incremental-dir=/home/backup/db1inck2 --tables=db1
把恢复好的表空间文件复制到对应的数据库中。
8.8 恢复db2
8.8.1 db2恢复完整备份
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/db2
8.8.2 db2恢复增量备份一
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/db2 --incremental-dir=/home/backup/db2inck1 --tables=db2
8.8.3 db2恢复增量备份二
./xtrabackup --prepare --apply-log-only --target-dir=/home/backup/db2 --incremental-dir=/home/backup/db2inck2 --tables=db2
把恢复好的表空间文件复制到对应的数据库中。
备注：xtrabackup默认不会备份mysql、test、performance_schema库。
9.xtrabackup对全库备份，进行单表恢复(在表之间没有外键约束时可以使用该方法)
9.1 完整备份
完整备份的数据时的数据：
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa1 |
| 2 | aa2 |
| 3 | aa3 |
+------+------+
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | bb1 |
| 2 | bb2 |
| 3 | bb3 |
+------+------+
3 rows in set (0.00 sec)
完整备份：
[root@localhost bin]# ./xtrabackup --defaults-file=/usr/local/mysql3307/my.cnf --backup --datadir=/usr/local/mysql3307/data/ --target-dir=/home/aa
./xtrabackup version 2.0.0 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql3307/data/
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
>> log scanned up to (1671215)
[01] Copying ./ibdata1 to /home/aa/ibdata1
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
[01] ...done
[01] Copying ./ibdata2 to /home/aa/ibdata2
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
[01] ...done
[01] Copying ./ibdata3 to /home/aa/ibdata3
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
[01] ...done
[01] Copying ./ibdata4 to /home/aa/ibdata4
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
[01] ...done
[01] Copying ./ibdata5 to /home/aa/ibdata5
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
>> log scanned up to (1671215)
[01] ...done
[01] Copying ./db1/t1.ibd to /home/aa/./db1/t1.ibd
[01] ...done
[01] Copying ./db2/t1.ibd to /home/aa/./db2/t1.ibd
[01] ...done
[01] Copying ./db2/t2.ibd to /home/aa/./db2/t2.ibd
[01] ...done
xtrabackup: The latest check point (for incremental): '1671215'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1671215)
xtrabackup: Transaction log of lsn (1671215) to (1671215) was copied.
9.2 增量备份一
做增量备份一时的数据：
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa1 |
| 2 | aa2 |
| 3 | aa3 |
| 4 | aa4 |
| 5 | aa5 |
| 6 | aa6 |
+------+------+
6 rows in set (0.00 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | bb1 |
| 2 | bb2 |
| 3 | bb3 |
| 4 | bb4 |
| 5 | bb5 |
| 6 | bb6 |
+------+------+
6 rows in set (0.00 sec)
增量备份一：
[root@localhost bin]# ./xtrabackup --defaults-file=/usr/local/mysql3307/my.cnf --backup --target-dir=/home/20120710-inck1/ --incremental-basedir=/home/aa/ --datadir=/usr/local/mysql3307/data/ --innodb-log-file-size=268435456
./xtrabackup version 2.0.0 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined)
incremental backup from 1671215 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql3307/data/
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
>> log scanned up to (1672764)
[01] Copying ./ibdata1 to /home/20120710-inck1/ibdata1.delta
>> log scanned up to (1672764)
[01] ...done
[01] Copying ./ibdata2 to /home/20120710-inck1/ibdata2.delta
>> log scanned up to (1672764)
[01] ...done
[01] Copying ./ibdata3 to /home/20120710-inck1/ibdata3.delta
>> log scanned up to (1672764)
[01] ...done
[01] Copying ./ibdata4 to /home/20120710-inck1/ibdata4.delta
>> log scanned up to (1672764)
[01] ...done
[01] Copying ./ibdata5 to /home/20120710-inck1/ibdata5.delta
>> log scanned up to (1672764)
[01] ...done
[01] Copying ./db1/t1.ibd to /home/20120710-inck1/./db1/t1.ibd.delta
[01] ...done
[01] Copying ./db2/t1.ibd to /home/20120710-inck1/./db2/t1.ibd.delta
[01] ...done
[01] Copying ./db2/t2.ibd to /home/20120710-inck1/./db2/t2.ibd.delta
[01] ...done
xtrabackup: The latest check point (for incremental): '1672764'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1672764)
xtrabackup: Transaction log of lsn (1672764) to (1672764) was copied.
做增量备份二时的数据：
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa1 |
| 2 | aa2 |
| 3 | aa3 |
| 4 | aa4 |
| 5 | aa5 |
| 6 | aa6 |
| 7 | aa7 |
| 8 | aa8 |
| 9 | aa9 |
+------+------+
9 rows in set (0.00 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | bb1 |
| 2 | bb2 |
| 3 | bb3 |
| 4 | bb4 |
| 5 | bb5 |
| 6 | bb6 |
| 7 | bb7 |
| 8 | bb8 |
| 9 | bb9 |
+------+------+
9 rows in set (0.00 sec)
9.3 增量备份二
第二次增量备份：
./xtrabackup --defaults-file=/usr/local/mysql3307/my.cnf --backup --target-dir=/home/20120710-inck2/ --incremental-basedir=/home/20120710-inck1/ --datadir=/usr/local/mysql3307/data/ --innodb-log-file-size=268435456
[root@localhost bin]# ./xtrabackup --defaults-file=/usr/local/mysql3307/my.cnf --backup --target-dir=/home/20120710-inck2/ --incremental-basedir=/home/20120710-inck1/ --datadir=/usr/local/mysql3307/data/ --innodb-log-file-size=268435456
./xtrabackup version 2.0.0 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined)
incremental backup from 1672764 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql3307/data/
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
>> log scanned up to (1674313)
[01] Copying ./ibdata1 to /home/20120710-inck2/ibdata1.delta
>> log scanned up to (1674313)
[01] ...done
[01] Copying ./ibdata2 to /home/20120710-inck2/ibdata2.delta
>> log scanned up to (1674313)
[01] ...done
[01] Copying ./ibdata3 to /home/20120710-inck2/ibdata3.delta
>> log scanned up to (1674313)
[01] ...done
[01] Copying ./ibdata4 to /home/20120710-inck2/ibdata4.delta
>> log scanned up to (1674313)
[01] ...done
[01] Copying ./ibdata5 to /home/20120710-inck2/ibdata5.delta
>> log scanned up to (1674313)
[01] ...done
[01] Copying ./db1/t1.ibd to /home/20120710-inck2/./db1/t1.ibd.delta
[01] ...done
[01] Copying ./db2/t1.ibd to /home/20120710-inck2/./db2/t1.ibd.delta
[01] ...done
[01] Copying ./db2/t2.ibd to /home/20120710-inck2/./db2/t2.ibd.delta
[01] ...done
xtrabackup: The latest check point (for incremental): '1674313'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1674313)
xtrabackup: Transaction log of lsn (1674313) to (1674313) was copied.
删除数据：
mysql> delete from t1 where id=4 or id >4;
Query OK, 6 rows affected (0.02 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa1 |
| 2 | aa2 |
| 3 | aa3 |
+------+------+
3 rows in set (0.00 sec)
mysql> delete from t2 where id>6;
Query OK, 3 rows affected (0.01 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | bb1 |
| 2 | bb2 |
| 3 | bb3 |
| 4 | bb4 |
| 5 | bb5 |
| 6 | bb6 |
+------+------+
6 rows in set (0.00 sec)
9.3 恢复t1的数据：
9.3.1、把备份文件复制一份
[root@localhost home]# cp -R /home/aa /home/huifu/
[root@localhost home]# cp -R /home/20120710-inck1 /home/huifu/
[root@localhost home]# cp -R /home/20120710-inck2 /home/huifu/
9.3.2、删除不需要恢复的表和库(最好的办法是在复制是选择性的复制需要恢复的表的相关文件)
rm /home/huifu/aa/db2/t2.ibd
rm -rf /home/huifu/aa/db1
rm -rf /home/huifu/20120710-inck1/db1
rm /home/huifu/20120710-inck1/db2/t2.ibd.delta
rm /home/huifu/20120710-inck1/db2/t2.ibd.meta
rm -rf /home/huifu/20120710-inck2/db1
rm /home/huifu/20120710-inck2/db2/t2.*
9.3.3、在/home/huifu目中进行恢复
恢复完整备份
./xtrabackup --defaults-file=/usr/local/mysql3307/my.cnf --prepare --apply-log-only --target-dir=/home/huifu/aa --tables=db2.t1
[root@localhost bin]# ./xtrabackup --defaults-file=/usr/local/mysql3307/my.cnf --prepare --apply-log-only --target-dir=/home/huifu/aa --tables=db2.t1
xtrabackup: tables regcomp(db2.t1): Success
./xtrabackup version 2.0.0 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined)
xtrabackup: cd to /home/huifu/aa
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1671215)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M
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: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
120710 11:43:50 InnoDB: Initializing buffer pool, size = 100.0M
120710 11:43:50 InnoDB: Completed initialization of buffer pool
120710 11:43:50 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!
120710 11:43:50 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 3312, file name ./mysql-bin.000011
[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 3312, file name ./mysql-bin.000011
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120710 11:43:51 InnoDB: Starting shutdown...
120710 11:43:52 InnoDB: Shutdown completed; log sequence number 1672429
恢复增量备份一：
./xtrabackup --defaults-file=/usr/local/mysql3307/my.cnf --prepare --apply-log-only --target-dir=/home/huifu/aa --incremental-dir=/home/huifu/20120710-inck1 --tables=db2.t1
[root@localhost bin]# ./xtrabackup --defaults-file=/usr/local/mysql3307/my.cnf --prepare --apply-log-only --target-dir=/home/huifu/aa --incremental-dir=/home/huifu/20120710-inck1 --tables=db2.t1
xtrabackup: tables regcomp(db2.t1): Success
./xtrabackup version 2.0.0 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined)
incremental backup from 1671215 is enabled.
xtrabackup: cd to /home/huifu/aa
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1672764)
xtrabackup: page size for /home/huifu/20120710-inck1/ibdata4.delta is 16384 bytes
Applying /home/huifu/20120710-inck1/ibdata4.delta ...
xtrabackup: page size for /home/huifu/20120710-inck1/ibdata3.delta is 16384 bytes
Applying /home/huifu/20120710-inck1/ibdata3.delta ...
xtrabackup: page size for /home/huifu/20120710-inck1/ibdata2.delta is 16384 bytes
Applying /home/huifu/20120710-inck1/ibdata2.delta ...
xtrabackup: page size for /home/huifu/20120710-inck1/ibdata1.delta is 16384 bytes
Applying /home/huifu/20120710-inck1/ibdata1.delta ...
xtrabackup: page size for /home/huifu/20120710-inck1/ibdata5.delta is 16384 bytes
Applying /home/huifu/20120710-inck1/ibdata5.delta ...
xtrabackup: page size for /home/huifu/20120710-inck1/db2/t1.ibd.delta is 16384 bytes
Applying /home/huifu/20120710-inck1/db2/t1.ibd.delta ...
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M
xtrabackup: innodb_log_group_home_dir = /home/huifu/20120710-inck1
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: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
120710 12:07:35 InnoDB: Initializing buffer pool, size = 100.0M
120710 12:07:35 InnoDB: Completed initialization of buffer pool
120710 12:07: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!
120710 12:07: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 3725, file name ./mysql-bin.000011
[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 3725, file name ./mysql-bin.000011
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120710 12:07:35 InnoDB: Starting shutdown...
120710 12:07:35 InnoDB: Shutdown completed; log sequence number 1672764
恢复增量备份二：
./xtrabackup --defaults-file=/usr/local/mysql3307/my.cnf --prepare --apply-log-only --target-dir=/home/huifu/aa --incremental-dir=/home/huifu/20120710-inck2 --tables=db2.t1
[root@localhost bin]# ./xtrabackup --defaults-file=/usr/local/mysql3307/my.cnf --prepare --apply-log-only --target-dir=/home/huifu/aa --incremental-dir=/home/huifu/20120710-inck2 --tables=db2.t1
xtrabackup: tables regcomp(db2.t1): Success
./xtrabackup version 2.0.0 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined)
incremental backup from 1672764 is enabled.
xtrabackup: cd to /home/huifu/aa
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1674313)
xtrabackup: page size for /home/huifu/20120710-inck2/ibdata4.delta is 16384 bytes
Applying /home/huifu/20120710-inck2/ibdata4.delta ...
xtrabackup: page size for /home/huifu/20120710-inck2/ibdata3.delta is 16384 bytes
Applying /home/huifu/20120710-inck2/ibdata3.delta ...
xtrabackup: page size for /home/huifu/20120710-inck2/ibdata2.delta is 16384 bytes
Applying /home/huifu/20120710-inck2/ibdata2.delta ...
xtrabackup: page size for /home/huifu/20120710-inck2/ibdata1.delta is 16384 bytes
Applying /home/huifu/20120710-inck2/ibdata1.delta ...
xtrabackup: page size for /home/huifu/20120710-inck2/ibdata5.delta is 16384 bytes
Applying /home/huifu/20120710-inck2/ibdata5.delta ...
xtrabackup: page size for /home/huifu/20120710-inck2/db2/t1.ibd.delta is 16384 bytes
Applying /home/huifu/20120710-inck2/db2/t1.ibd.delta ...
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M
xtrabackup: innodb_log_group_home_dir = /home/huifu/20120710-inck2
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: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
120710 12:09:45 InnoDB: Initializing buffer pool, size = 100.0M
120710 12:09:45 InnoDB: Completed initialization of buffer pool
120710 12:09:45 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!
120710 12:09:45 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 4137, file name ./mysql-bin.000011
[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 4137, file name ./mysql-bin.000011
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120710 12:09:45 InnoDB: Starting shutdown...
120710 12:09:45 InnoDB: Shutdown completed; log sequence number 1674313
关闭mysql用恢复好表空间文件替换需要恢复的表空间文件。
cp /home/huifu/aa/db2/t1.ibd /usr/local/mysql3307/data/db2/
启动mysql查看恢复后的数据：
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa1 |
| 2 | aa2 |
| 3 | aa3 |
| 4 | aa4 |
| 5 | aa5 |
| 6 | aa6 |
| 7 | aa7 |
| 8 | aa8 |
| 9 | aa9 |
+------+------+
9 rows in set (0.00 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | bb1 |
| 2 | bb2 |
| 3 | bb3 |
| 4 | bb4 |
| 5 | bb5 |
| 6 | bb6 |
+------+------+
6 rows in set (0.00 sec)
恢复前的数据：
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa1 |
| 2 | aa2 |
| 3 | aa3 |
+------+------+
3 rows in set (0.00 sec)
mysql> delete from t2 where id>6;
Query OK, 3 rows affected (0.01 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | bb1 |
| 2 | bb2 |
| 3 | bb3 |
| 4 | bb4 |
| 5 | bb5 |
| 6 | bb6 |
+------+------+
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21582653/viewspace-735080/，如需转载，请注明出处，否则将追究法律责任。
1580
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
