基于binlog二进制日志的MySQL恢复笔记

5643

基于binlog二进制日志的MySQL恢复笔记

刚好复习到这里，顺手做个小实验，记录下。


总的操作流程：

step0、关掉数据库的对外访问【防止用户操作继续写入这个库】

step1、mysqlbinlog 导出相关时间段数据库的二进制日志

step2、编辑today.sql找到误操作的那几条数据，删除并保存。

step3、执行全备份恢复 mysql -e 'source /root/backup.sql;'

step4、用二进制日志恢复今天的修改  mysql -e 'source /root/today.sql;'

step5、登录mysql，验证数据是否回来了。


原始数据库某表的内容如下：

MariaDB [hellodb]> select * from students;

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

| StuID | Name          | Age | Gender | ClassID | TeacherID |

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

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |

|     6 | Shi Qing      |  46 | M      |       5 |      NULL |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |

|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |

|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |

|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |

|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |

|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |

|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |

|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |

|    15 | Duan Yu       |  19 | M      |       4 |      NULL |

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

备份了下数据，备份为backup.sql，模拟当做前一天的全备份文件。


然后随便进行了一些操作，如下：
1
   
INSERT INTO students VALUES(30,'lee',21,'M',2,1);
DELETE FROM students where `StuID`=10;
update students set age=10 where `StuID`=1;


执行后效果如下：

MariaDB [hellodb]> select * from students;

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

| StuID | Name          | Age | Gender | ClassID | TeacherID |

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

|     1 | Shi Zhongyu   |  10 | M      |       2 |         3 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |

|     6 | Shi Qing      |  46 | M      |       5 |      NULL |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |

|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |

|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |

|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |

|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |

|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |

|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |

|    15 | Duan Yu       |  19 | M      |       4 |      NULL |

|    30 | lee           |  21 | M      |       2 |         1 |

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


假如突然DBA发现刚才的DELETE不应该执行，必需要恢复这条数据。


step0、这时候，首先，我们要关掉数据库对外访问的权限，防止有用户写入数据，干扰恢复操作。

(可以修改前端的web连接数据库的文件，将其改到其它从节点，虽然用户无法写，但最起码比网站无法访问要强些)


step1、先提取出误操作这段时间的二进制日志，取名为today.sql，如下：
1
   
mysqlbinlog --start-datetime='2016-07-12 23:51:48' --stop-datetime='2016-07-12 23:53:00' \
/usr/local/mariadb/var/mysql-bin.000033 > /root/today.sql



step2、编辑这个today.sql，找到刚才的那条DELETE操作，剔除DELETE语句，保存退出。


step3、用全备份backup.sql恢复数据：
1
   
mysql -e 'source backup.sql;'

恢复完的效果如下：

MariaDB [hellodb]> select * from students;

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

| StuID | Name          | Age | Gender | ClassID | TeacherID |

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

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |

|     6 | Shi Qing      |  46 | M      |       5 |      NULL |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |

|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |

|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |

|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |

|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |

|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |

|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |

|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |

|    15 | Duan Yu       |  19 | M      |       4 |      NULL |

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

15 rows in set (0.00 sec)

可以看到第一天数据又恢复了最原始的状态，第10条数据又恢复回来了，但是INSERT的那条数据却没有了，因此我们还要使用二进制日志继续恢复。



step4、继续用二进制日志恢复：
1
   
mysql -e 'source today.sql;'


step5、查看恢复后的结果：

恢复完的效果如下：

MariaDB [hellodb]> select * from students;

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

| StuID | Name          | Age | Gender | ClassID | TeacherID |

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

|     1 | Shi Zhongyu   |  10 | M      |       2 |         3 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |

|     6 | Shi Qing      |  46 | M      |       5 |      NULL |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |

|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |

|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |

|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |

|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |

|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |

|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |

|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |

|    15 | Duan Yu       |  19 | M      |       4 |      NULL |

|    30 | lee           |  21 | M      |       2 |         1 |

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

16 rows in set (0.00 sec)


可以看到，第一条数据的Age已经改为我们要求的值了，INSERT的最后一条数据也恢复回来了， 误删除的StuID为10的数据也恢复了。


至此，我们的恢复就完成了。