MySql恢复误删的表或数据(不完全恢复）

来源：互联网发布：三国群英传辅助软件编辑：程序博客网时间：2024/05/17 01:52

一：实验目的

利用mysqldump全备及二进制日志恢复误删的表或数据（不完全恢复）

二：实验步骤

2.1：准备测试数据

use test;mysql> show tables;+----------------+| Tables_in_test |+----------------+| t              || t3             || t4             || t5             |+----------------+4 rows in set (0.00 sec)mysql> create table t6(id int);Query OK, 0 rows affected (0.01 sec)mysql> insert into t6 values(1);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> insert into t6 values(2);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> delete    -> from t6    -> where id=1;Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values(3);Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values(4);Query OK, 1 row affected (0.01 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select *    -> from t6;+------+| id   |+------+|    2 ||    3 ||    4 |+------+3 rows in set (0.00 sec)mysql> drop table t6;Query OK, 0 rows affected (0.01 sec)mysql> select *    -> from t6;ERROR 1146 (42S02): Table 'test.t6' doesn't exist

2.2：恢复

--在这里，我想把t6表的值为2,3,4及被删的值为1的数据都恢复回来。

2.2.1：用最近的一个全备恢复

[root@target_pc ~]# mysql -u root -p test< /backup/databasefile/database_test_201410081042.bakEnter password: --查看此时数据库情况：mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| t              || t3             || t4             || t5             |+----------------+4 rows in set (0.00 sec)看到，此时还没还原t6。

2.2.2：用二进制日志进行恢复

mysql> show binary logs;

+----------------+-----------+

| Log_name | File_size |

+----------------+-----------+

| log-bin.000006 | 120 |

| log-bin.000007 | 9609 |

| log-bin.000008 | 120 |

| log-bin.000009 | 4904 |

+----------------+-----------+

4 rows in set (0.00 sec)

全备是在log-bin.000007前进行的，所以这里需要恢复二进制日志文件log-bin.000007,log-bin.000008,log-bin.000009

--表t6信息是在log-bin.000009里

2.2.2.1：先恢复log-bin.000007,log-bin.000008

[root@target_pc ~]# mysqlbinlog /var/lib/mysql/log-bin.000007 | mysql -u root -p

Enter password:

[root@target_pc ~]# mysqlbinlog /var/lib/mysql/log-bin.000008 | mysql -u root -p

Enter password:

2.2.2.2：恢复log-bin.000009

2.2.2.2.1：查看log-bin.000009具体内容

mysql>show binlog events in 'log-bin.000009';

--这里只显示关于t6表的内容：

+----------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+

+----------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+

| log-bin.000009 | 4599 | Query | 1 | 4696 | use `test`; create table t6(id int) |

| log-bin.000009 | 4696 | Query | 1 | 4775 | BEGIN |

| log-bin.000009 | 4775 | Query | 1 | 4873 | use `test`; insert into t6 values(1) |

| log-bin.000009 | 4873 | Xid | 1 | 4904 | COMMIT /* xid=202 */ |

| log-bin.000009 | 4904 | Query | 1 | 4983 | BEGIN |

| log-bin.000009 | 4983 | Query | 1 | 5081 | use `test`; insert into t6 values(2) |

| log-bin.000009 | 5081 | Xid | 1 | 5112 | COMMIT /* xid=210 */ |

| log-bin.000009 | 5112 | Query | 1 | 5191 | BEGIN |

| log-bin.000009 | 5191 | Query | 1 | 5290 | use `test`; delete

from t6

where id=1 |

| log-bin.000009 | 5290 | Xid | 1 | 5321 | COMMIT /* xid=212 */ |

| log-bin.000009 | 5321 | Query | 1 | 5400 | BEGIN |

| log-bin.000009 | 5400 | Query | 1 | 5498 | use `test`; insert into t6 values(3) |

| log-bin.000009 | 5498 | Xid | 1 | 5529 | COMMIT /* xid=213 */ |

| log-bin.000009 | 5529 | Query | 1 | 5608 | BEGIN |

| log-bin.000009 | 5608 | Query | 1 | 5706 | use `test`; insert into t6 values(4) |

| log-bin.000009 | 5706 | Xid | 1 | 5737 | COMMIT /* xid=214 */ |

| log-bin.000009 | 5737 | Query | 1 | 5852 | use `test`; DROP TABLE `t6` /* generated by server */ |

+----------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+

64 rows in set (0.00 sec)

2.2.2.2.2：恢复t6表删除数据前的数据

[root@target_pc ~]# mysqlbinlog /var/lib/mysql/log-bin.000009 --stop-position='5112' | mysql -u root -p

Enter password:

此时，t6有1,2两条数据

mysql> select *

-> from t6;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.00 sec)

2.2.2.2.3：跳过删除语句，恢复剩下的几条插入语句

[root@target_pc ~]# mysqlbinlog /var/lib/mysql/log-bin.000009 --start-position='5400' --stop-position='5737' | mysql -u root -p

Enter password:

此时：

mysql> select *

-> from t6;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.00 sec)

2.2.2.2.4：剩下的是drop table语句，就不恢复了

如果drop table 后有别的数据库DML操作，继续恢复即可。

--本篇文章参考自： http://wenku.baidu.com/link?url=-oWg11CKbEy0jvRec1IxLg4eiR2jRkZRY7cKxkakCSOkGEZqbX5nmG7om4IMZCi-CAE48jah4s_mZNf433roacNu-a61EMxQlFqL5wsfl67

0 0