recover table from full backup(一)

前提:
1、innodb_fast_shutdown = 0
2、You must not drop, truncate or alter the schema of the table after the backup has been taken.
3、The variable innodb_file_per_table must be enabled.

背景:

root@luoxuan 05:14:25>select * from lijie;
+------+
| b    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
 
root@luoxuan 05:14:29>select * from test_recover;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+

一、获得一致性备份:

session 1: sudo ./innobackupex --defaults-file=/u01/mysql/my.cnf --user=root /u01/bak2 --slave-info --export 2> /tmp/xtrabackup.log

session 2:备份过程进行数据写入

root@luoxuan 05:17:23>insert into lijie values(3);
Query OK, 1 row affected (0.00 sec)
 
root@luoxuan 05:17:27>insert into test_recover values(4);     
Query OK, 1 row affected (0.00 sec)

session 1:

sudo ./innobackupex --defaults-file=/u01/mysql/my.cnf --apply-log --export /u01/bak2/2013-03-26_17-16-55

session 2:获得一致性过程中写入数据

root@luoxuan 05:17:36>insert into lijie values(4);
Query OK, 1 row affected (0.61 sec)
 
root@luoxuan 05:20:43>insert into test_recover values(5);
Query OK, 1 row affected (0.00 sec)

二、删除需单独恢复表的数据

root@luoxuan 05:20:57>select * from lijie;
+------+
| b    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)
 
root@luoxuan 05:21:40>select * from test_recover;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)
 
root@luoxuan 05:21:45>delete from test_recover where a = 1;
Query OK, 1 row affected (0.00 sec)

三、
discard需要恢复表

mysql> set FOREIGN_KEY_CHECKS=0;
mysql> ALTER TABLE test_recover DISCARD TABLESPACE;

四、把apply-log后的数据文件cp回/u01/mysql/data/库名/

$ sudo cp ./luoxuan/test_recover.ibd /u01/mysql/data/luoxuan

记得修改权限

五、import需要恢复的表

mysql> set FOREIGN_KEY_CHECKS=0;
mysql> ALTER TABLE test_recover IMPORT TABLESPACE;
mysql> set FOREIGN_KEY_CHECKS=1;
 
root@luoxuan 05:25:24>select * from test_recover;(正确)
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)
 
root@luoxuan 05:25:45>select * from lijie;  记录应该(1,2,3,4,5)
+------+
| b    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

六、拿到备份时的binlog file及position

$ more xtrabackup_binlog_info 
mysql-bin.002603        12634

七、把binlog解析后发现,postion位置也不对

http://www.mysqlperformanceblog.com/2012/01/25/how-to-recover-a-single-innodb-table-from-a-full-backup/
这篇文章只针对在恢复过程没有写入情况下,恢复出来数据正确,能单独恢复一个表


Post a Comment