MySQL使用binlog日志做数据恢复

系统管理员 2022-06-13 14:37 221阅读 0赞

MySQL的binlog日志是MySQL日志中非常重要的一种日志，记录了数据库所有的DML操作。通过binlog日志我们可以进行数据库的读写分离、数据增量备份以及服务器宕机时的数据恢复。

定期备份固然可以在服务器发生宕机的时候快速的恢复数据，但传统的全量备份不可能做到实时，所以在发生宕机的时候，也会损伤一部分数据，如果这个时候开启了binlog日志，那么可以通过binlog来对没有做备份的这一阶段损失的数据进行恢复。如果了解Redis的朋友，可能想到了，Redis有两种持久化方式，分别是AOF和RDB。RDB就类似于MySQL的全量复制，AOF就类似于MySQL的binlog。

关于数据的恢复这里在说一点，既然binlog这么好，是不是开启了binlog就不需要定期做备份了呢，不要这样做。为什么呢，因为binlog的数据量非常大，另外就是使用binlog做数据的恢复性能会非常低。因为binlog是对操作的记录，比如某一时刻，我先插入了一条数据，然后将这条数据删除了，本身数据是没了，但有两条操作。如果是全量备份，肯定没有这条数据，如果使用binlog需要执行一条插入和一条删除操作，因此性能和文件大小都是比较大的。

啰嗦了一堆，下面说关于数据恢复的问题：

在正式开始之前，先来说一说mysql完整备份数据库，以及恢复数据库的方法

**备份数据库：**

首先我们来创建一个数据库，mytest

create database mytest;

**接着我们来创建一张表**

use mytest;

create table t1(id int ,name varchar(20));

**然后我们插入两条数据**

insert into t1 values (1,'xiaoming');

insert into t1 values (2,'xiaohong');

**下面我们对mytest数据库进行备份，备份到/root/bakup/**

mysqldump -uroot -p -B -F -R -x --master-data=2 mytest | gzip > /root/backup/bak\_$(date +%F).sql.gz

![Center][]

**参数说明：**

\-B：指定数据库

\-F：刷新日志

\-R：备份存储过程等

\-x：锁表

\--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

**查看备份文件**

![Center 1][]

这样呢，我们就把数据做了一个完整的备份。下面来删除数据库，然后通过备份数据进行恢复数据库。

gzip -d bakup\_xxx.gz

mysql -uroot -p < bakup\_xxx.sql

这样我们就把数据导入到库里了。

继续上面的操做，我们新增xiaoli和xiaozhao这两条数据，并把xiaozhao这条记录删除掉。

在删除之前，我们先来刷新binlog日志，生成一个新的日志，那么我们之后所要操做的内容都会被记录到新的日志文件中。（通过前面binlog日志的详细说明我们知道，每次刷新和服务重启的时候，都会生成一个binlog日志文件。）

flush logs;

show master status;

![Center 2][]

我们注意，binlog的文件是0009，位置是在154，这两个信息很重要

下面我们来做插入和删除操作

![Center 3][]  
这个时候我们应该是来查看一下binlog日志的状态，以便与我们一会来进行恢复到此状态，但是，真正的环境中我们并不知道这个状态，因此这里也就不去查看这个状态了，这个状态的值可以通过后面查看binlog日志文件来进行分析。下面我们开始误操作：

我们来把xiaozhao删除掉

![Center 4][]

这样数据就删除掉了，下面我们再来查看binlog的状态

show master status;

![Center 5][]

这个时候我们发现我删除操作是个错误的操作，要进行恢复，那么该如何恢复呢？这个时候我们就可以通过binlog的position来进行恢复。  
**在进行其他的处理之前，我们建议，马上再执行一次flush logs，也就是让出错的部分就集中在这么一个binlog日志文件中。**

我们来查看0009的binlog日志。

![Center 6][]

我们看到delete\_rows 结束点是928这个点，起始点是在755这个点，我们可以把操作的这些数据删除到我们上次备份的内容，然后通过执行binlog来进行恢复，当然恢复到755这个点之前。  
比如我上次备份的是整个数据库，我就可以把整个数据库删除，然后通过备份文件恢复，然后再通过binlog做增量恢复。这样数据就回来了。这里就不再进行删库了，我们**直接演示使用binlog日志恢复数据的方法**

语法如下

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

比如我们要把所有的操作都恢复（不包括我们的删除，我们知道删除是在755点上）：

mysqlbinlog mysql-bin.000009 --start-position 154 --stop-position 755 | mysql -uroot -p mytest

![Center 7][]

再来查看表

![Center 8][]

我们发现xiaozhao又回来了，当然了，这里多了一个xiali，是因为我之前并没有删除备份前的数据。当然了，我们在恢复的过程中可以选择只恢复xiaozhao这么一块内容

下面是binlog日志恢复中一些常用的参数

**--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间**

**--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样**

**--start-position：从二进制日志中读取指定position 事件位置作为开始。**

**--stop-position：从二进制日志中读取指定position 事件位置作为事件截至**

[Center]: /images/20220613/0b83065a76904be9b64d9c13a7b864f2.png
[Center 1]: /images/20220613/1eb73b3f17504615b6b9987401ed805f.png
[Center 2]: /images/20220613/fa37a757c9414e6287a7add628f187fe.png
[Center 3]: /images/20220613/7ab4631184424c7ea8a593fddf9f54cb.png
[Center 4]: /images/20220613/1722719cf27d4cd9baf020348ffd10b3.png
[Center 5]: /images/20220613/1e9245bf8e64427fa7798a3a3380883f.png
[Center 6]: /images/20220613/a8d988b1dd4f4727b54722f09c20aa09.png
[Center 7]: /images/20220613/42e86d62fc3a4bdd81b5411a89d13457.png
[Center 8]: /images/20220613/2dad5c2247f84acba55974c5aee45ad7.png