MySQL——通过binlog恢复数据

短命女 2023-09-25 22:22 79阅读 0赞

**目录**

1.binlog基本概念

2.MySQL开启binlog

3.使用binlog日志恢复数据

3.1.恢复前准备工作

3.2.数据恢复

3.2.1.通过mysqlbinlog将binlog转为sql，以方便查询具体位置

3.2.2.查看生成的backuptmp.sql，最终确定需要恢复的起始位置为219，结束位置为982

3.2.3.通过mysqlbinlog执行恢复操作

--------------------

## 1.binlog基本概念 ##

二进制日志(binnary log)以事件形式记录了对MySQL数据库执行更改的所有操作。  
binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE、DROP等）以及表数据修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。

binlog有两个常用的使用场景：

*  主从复制：master节点开启binlog，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的（详见[MySQL实操(二)——MySQL主从同步实战\_醉酒的戈多的博客-CSDN博客][MySQL_MySQL_-CSDN]）。
 *  数据恢复：通过mysqlbinlog工具来恢复数据（本文主要讲解功能）。

## 2.MySQL开启binlog ##

MySQL安装完成后，MySQL5.7版本binlog默认不开启，MySQL8默认开启binlog；登录MySQL后，查看binlog状态sql如下：

show variables like '%log_bin%';

![fc590a6bb532489b85d6f5906e99ff7e.png][]

如未开启binlog日志，则可按以下步骤开启binlog日志

**开启binlog日志**

修改MySQL配置文件，linux中配置文件为my.conf，window下问my.ini，下面以centos为例演示

*  编辑配置文件

vim /etc/my.cnf

*  添加配置项

log-bin=mysql-bin
    server-id=1

*  重启MySQL服务

systemctl restart mysqld

*  进入MySQL查看binlog日志是否开启成功

![ab3b49c1c3824a5b9adef24ffd734c70.png][]

> log\_bin为ON说明这个参数是开启的，就是说系统是记录了bin log的
> 
> log\_bin\_basename配置了bin log的文件路径及文件前缀名
> 
> log\_bin\_index配置了bin log索引文件的路径

*  查看日志列表

show master logs;

![9c169bd7652240d0937ee5b04f02e263.png][]

*  根据log\_bin\_basename的路径查看binlog具体文件

![4e928fd0361a4d008233e809b7bccf9e.png][]

## 3.使用binlog日志恢复数据 ##

> **原理**：当数据库发生变化时，binlog会记录数据库中的所有变化；需要恢复的时候可以根据binlog中的开始位置和结束位置还原本部分操作；结束位置一般是数据被破坏或删除之前的位置。

### 3.1.恢复前准备工作 ###

开启binlog之后，创建测试数据库，在测试数据库中创建测试表，并写入数据：

create database test;
    use test;
    CREATE TABLE `testuser` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      `age` decimal(18,2) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    insert  into `testuser`(`id`,`name`,`age`) values (1,'张三',24.00);

![f15044d88f3043b2a7308b0d52a255c3.png][]

此时假如误删了test数据库，就可以用binlog来进行回复数据库、库中的表及表中的数据；

drop database test;

### 3.2.数据恢复 ###

当需要恢复数据时，为了防止恢复数据后影响最新业务，需要执行flush logs，产生一个新的binlog文件，此时旧的binlog文件不会再有写入；

![620d5625ded949f9a6261a22a0e4e160.png][]

> 下面具体通过mysql-bin.000001来进行数据恢复

恢复时需要在binlog中找到两个位置：

*  数据恢复的起始位置
 *  数据恢复的结束位置

如在数据准备中的drop操作，需要在binlog中找到该位置，并将该位置作为数据恢复的结束位置

#### **3.2.1.通过mysqlbinlog将binlog转为sql，以方便查询具体位置** ####

mysqlbinlog --set-charset=utf-8 /var/lib/mysql/mysql-bin.000001>backuptmp.sql

![a821b50e8bf043dd8a2801262edbad95.png][]

#### **3.2.2.查看生成的backuptmp.sql，最终确定需要恢复的起始位置为219，结束位置为982** ####

![68ebfc3bde214cd296b356b1690f17d3.png][]

![7630f43e149341e09aeb9bc520a18606.png][]

#### **3.2.3.通过mysqlbinlog执行恢复操作** ####

mysqlbinlog -v /var/lib/mysql/mysql-bin.000001 --start-position=219 --stop-position=982 | mysql -uroot -p123456

> /var/lib/mysql/mysql-bin.000001 要操作binlog文件
> 
> \--start-position=219 数据恢复的起始位置
> 
> \--stop-position=982 数据恢复的结束位置
> 
> mysql -uroot -p123456 数据恢复需要登录数据库

![a57c0d27dc6e4b268329ff4d9f86b085.png][]

[MySQL_MySQL_-CSDN]: https://blog.csdn.net/DreamEhome/article/details/128646598
[fc590a6bb532489b85d6f5906e99ff7e.png]: https://img-blog.csdnimg.cn/fc590a6bb532489b85d6f5906e99ff7e.png
[ab3b49c1c3824a5b9adef24ffd734c70.png]: https://img-blog.csdnimg.cn/ab3b49c1c3824a5b9adef24ffd734c70.png
[9c169bd7652240d0937ee5b04f02e263.png]: https://img-blog.csdnimg.cn/9c169bd7652240d0937ee5b04f02e263.png
[4e928fd0361a4d008233e809b7bccf9e.png]: https://img-blog.csdnimg.cn/4e928fd0361a4d008233e809b7bccf9e.png
[f15044d88f3043b2a7308b0d52a255c3.png]: https://img-blog.csdnimg.cn/f15044d88f3043b2a7308b0d52a255c3.png
[620d5625ded949f9a6261a22a0e4e160.png]: https://img-blog.csdnimg.cn/620d5625ded949f9a6261a22a0e4e160.png
[a821b50e8bf043dd8a2801262edbad95.png]: https://img-blog.csdnimg.cn/a821b50e8bf043dd8a2801262edbad95.png
[68ebfc3bde214cd296b356b1690f17d3.png]: https://img-blog.csdnimg.cn/68ebfc3bde214cd296b356b1690f17d3.png
[7630f43e149341e09aeb9bc520a18606.png]: https://img-blog.csdnimg.cn/7630f43e149341e09aeb9bc520a18606.png
[a57c0d27dc6e4b268329ff4d9f86b085.png]: https://img-blog.csdnimg.cn/a57c0d27dc6e4b268329ff4d9f86b085.png