recover table from full backup(二)

1、目标要为 XtraDB
2、 innodb_file_per_table=on
3、目标库innodb_expand_import(innodb_import_table_from_xtrabackup较新版本)开启

背景:

root@luoxuan >desc export_test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
 
root@luoxuan >insert into export_test values(1);
Query OK, 1 row affected (0.00 sec)

instance 1:
一、原db备份

$ sudo ./innobackupex --defaults-file=/u01/mysql/my.cnf --user=root /u01/bak2

二、prepare

$ sudo ./xtrabackup --prepare --export --target-dir=/u01/bak2/2013-03-27_11-33-39

三、在instance 2上创建和原表一样的表结构,并discard掉

root@luoxuan >CREATE TABLE export_test (
    ->   a int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
 
root@luoxuan > ALTER TABLE luoxuan.export_test DISCARD TABLESPACE;

四、把prepare后的文件夹中找到export_test.ibd、export_test.exp并cp instance 2的数据目标
五、在instance 2 上import

root@(none) >ALTER TABLE luoxuan.export_test IMPORT TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
 
root@(none) >select * from luoxuan.export_test;
+------+
| a    |
+------+
|    1 |
+------+

六、得到备份时binlog位置

$ more xtrabackup_binlog_info 
mysql-bin.002520        6244

同时在instance 1写入一条记录insert into luoxuan.export_test values(2)

七、解析binlog,当然可以有stop-position。自己开发脚本对binlog中只抽取export_test相关的DML

sudo mysqlbinlog --no-defaults -v -v  /u01/mysql/log/mysql-bin.002604 --start-position=6244 > /tmp/recover.sql

八、在instance 2上使用解析后的binlog,可以恢复到

root@luoxuan 04:16:30>select * from luoxuan.export_test;
+------+
| a    |
+------+
|    2 |
|    1 |
+------+

九、instance 1上的数据

root@luoxuan 04:17:10>select * from luoxuan.export_test;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+

Post a Comment