当前位置: 首页 > news >正文

MySQL使用binlog日志做数据恢复

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

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

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

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

先创建一个数据库一个表,再插入两行数据

mysql> create database mytest;
ERROR 1007 (HY000): Can't create database 'mytest'; database exists
mysql> use mytest;
Database changed
mysql> create table t1(id int ,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values (1,'xiaoming');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values (2,'xiaohong');
Query OK, 1 row affected (0.01 sec)

备份数据库

[mysql@t3-dtpoc-dtpoc-web04 mysql]$ cd /home/mysql/mysql/bin
[mysql@t3-dtpoc-dtpoc-web04 bin]$ ./mysqldump -uroot -p -B -F -R -x --master-data=2 mytest | gzip > /testdata/mysql/backup/bak_$(date +%F).sql.gz
Enter password: 
[mysql@t3-dtpoc-dtpoc-web04 bin]$ cd /testdata/mysql/backup/
[mysql@t3-dtpoc-dtpoc-web04 backup]$ ls
bak_2023-08-22.sql.gz

参数说明:

-B:指定数据库

-F:刷新日志 (执行一下flush logs)

-R:备份存储过程等

-x:锁表

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

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

[mysql@t3-dtpoc-dtpoc-web04 backup]$ gzip -d bak_2023-08-22.sql.gz
[mysql@t3-dtpoc-dtpoc-web04 backup]$ ls
bak_2023-08-22.sql

[mysql@t3-dtpoc-dtpoc-web04 bin]$ ./mysql -uroot -p < /testdata/mysql/backup/bak_2023-08-22.sql
Enter password: 

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

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

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


mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


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

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

mysql> insert into t1 values (3,'xiaoli'); 
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (4,'xiaozhao'); 
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoming |
|    2 | xiaohong |
|    3 | xiaoli   |
|    4 | xiaozhao |
+------+----------+
4 rows in set (0.00 sec)

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

我们来把xiaozhao删除掉
mysql> delete from t1 where id=4;
Query OK, 1 row affected (0.00 sec)

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

show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1776 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


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

我们来查看0003的binlog日志。
mysql> show binlog events in 'mysql-bin.000003';
+------------------+------+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+------+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000003 |    4 | Format_desc    |    123454 |         123 | Server ver: 5.7.26-log, Binlog ver: 4  |
| mysql-bin.000003 |  123 | Previous_gtids |    123454 |         154 |                                        |
| mysql-bin.000003 |  154 | Anonymous_Gtid |    123454 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000003 |  219 | Query          |    123454 |         293 | BEGIN                                  |
| mysql-bin.000003 |  293 | Rows_query     |    123454 |         351 | # insert into t1 values (1,'xiaoli')   |
| mysql-bin.000003 |  351 | Table_map      |    123454 |         401 | table_id: 130 (mytest.t1)              |
| mysql-bin.000003 |  401 | Write_rows     |    123454 |         448 | table_id: 130 flags: STMT_END_F        |
| mysql-bin.000003 |  448 | Xid            |    123454 |         479 | COMMIT /* xid=191 */                   |
| mysql-bin.000003 |  479 | Anonymous_Gtid |    123454 |         544 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000003 |  544 | Query          |    123454 |         618 | BEGIN                                  |
| mysql-bin.000003 |  618 | Rows_query     |    123454 |         676 | # delete from t1 where name='xiaoli'   |
| mysql-bin.000003 |  676 | Table_map      |    123454 |         726 | table_id: 130 (mytest.t1)              |
| mysql-bin.000003 |  726 | Delete_rows    |    123454 |         773 | table_id: 130 flags: STMT_END_F        |
| mysql-bin.000003 |  773 | Xid            |    123454 |         804 | COMMIT /* xid=192 */                   |
| mysql-bin.000003 |  804 | Anonymous_Gtid |    123454 |         869 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000003 |  869 | Query          |    123454 |         943 | BEGIN                                  |
| mysql-bin.000003 |  943 | Rows_query     |    123454 |        1001 | # insert into t1 values (3,'xiaoli')   |
| mysql-bin.000003 | 1001 | Table_map      |    123454 |        1051 | table_id: 130 (mytest.t1)              |
| mysql-bin.000003 | 1051 | Write_rows     |    123454 |        1098 | table_id: 130 flags: STMT_END_F        |
| mysql-bin.000003 | 1098 | Xid            |    123454 |        1129 | COMMIT /* xid=193 */                   |
| mysql-bin.000003 | 1129 | Anonymous_Gtid |    123454 |        1194 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000003 | 1194 | Query          |    123454 |        1268 | BEGIN                                  |
| mysql-bin.000003 | 1268 | Rows_query     |    123454 |        1328 | # insert into t1 values (4,'xiaozhao') |
| mysql-bin.000003 | 1328 | Table_map      |    123454 |        1378 | table_id: 130 (mytest.t1)              |
| mysql-bin.000003 | 1378 | Write_rows     |    123454 |        1427 | table_id: 130 flags: STMT_END_F        |
| mysql-bin.000003 | 1427 | Xid            |    123454 |        1458 | COMMIT /* xid=194 */                   |
| mysql-bin.000003 | 1458 | Anonymous_Gtid |    123454 |        1523 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000003 | 1523 | Query          |    123454 |        1597 | BEGIN                                  |
| mysql-bin.000003 | 1597 | Rows_query     |    123454 |        1646 | # delete from t1 where id=4            |
| mysql-bin.000003 | 1646 | Table_map      |    123454 |        1696 | table_id: 130 (mytest.t1)              |
| mysql-bin.000003 | 1696 | Delete_rows    |    123454 |        1745 | table_id: 130 flags: STMT_END_F        |
| mysql-bin.000003 | 1745 | Xid            |    123454 |        1776 | COMMIT /* xid=196 */                   |
+------------------+------+----------------+-----------+-------------+----------------------------------------+
32 rows in set (0.00 sec)

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

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

比如我们要把所有的操作都恢复(不包括我们的删除,我们知道删除是在1523点上):

[mysql@t3-dtpoc-dtpoc-web04 bin]$ ./mysqlbinlog /testdata/mysql/log/bin/mysql-bin.000003 --start-position 154 --stop-position 1523 | ./mysql -uroot -p mytest
Enter password: 

mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoming |
|    2 | xiaohong |
|    3 | xiaoli   |
|    3 | xiaoli   |
|    4 | xiaozhao |
+------+----------+
5 rows in set (0.00 sec)

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

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

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

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

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

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

相关文章:

MySQL使用binlog日志做数据恢复

MySQL的binlog日志是MySQL日志中非常重要的一种日志&#xff0c;记录了数据库所有的DML操作。通过binlog日志我们可以进行数据库的读写分离、数据增量备份以及服务器宕机时的数据恢复。 定期备份固然可以在服务器发生宕机的时候快速的恢复数据&#xff0c;但传统的全量备份不可…...

USB Type-C端口集成式ESD静电保护方案 安全低成本

Type-C端口是根据USB3.x和USB4协议传输数据的&#xff0c;很容易受到电气过载&#xff08;EOS&#xff09;和静电放电&#xff08;ESD&#xff09;事件的影响。由于Type-C支持随意热插拔功能&#xff0c;其内部高集成度的芯片&#xff0c;更容易受到人体静电放电的伤害和损坏。…...

Shiro学习总结

第一章 入门概述 1.概念 shiro是一个Java安全框架&#xff0c;可以完成&#xff1a;认证、授权、加密、会话管理、与web集成、缓存… 2.优势 ● 易于使用&#xff0c;构建简单 ● 功能全面 ● 灵活&#xff0c;可以在任何应用程序环境中工作&#xff0c;并且不需要依赖它们…...

AS中回退git历史版本并删除历史提交记录

当您想把某个版本后的代码删除&#xff0c;回滚到指定的版本。可以使用一下的方法。 一、打开AS中git历史提交窗口 二、选择需要回滚的版本选项&#xff0c;右键弹出菜单。选择Reset Current Branch to Here... 三、选择 Hard 选项 soft&#xff1a;将合并的更改应用到当前分支…...

线性代数的学习和整理5: 矩阵的加减乘除及其几何意义

目录 1 矩阵加法 1.1 矩阵加法的定义 1.2 加法的属性 1.2.1 只有同类型&#xff0c;相同n*m的矩阵才可以相加 1.2.1 矩阵加法的可交换律&#xff1a; 1.2.2 矩阵加法的可结合律&#xff1a; 1.3矩阵加法的几何意义 2 矩阵的减法 2.1 矩阵减法定义和原理基本同 矩阵的…...

sqlsugar 使用TNS连接oracle

在使用SqlSugar连接Oracle数据库时&#xff0c;可以通过TNS来实现连接。以下是一个示例代码&#xff0c;展示了如何使用TNS连接Oracle数据库&#xff1a; 首先&#xff0c;确保您已经安装了Oracle客户端&#xff0c;并正确配置了TNS的相关信息。 // 引入SqlSugar命名空间 usi…...

用python解压zip文件

因为某个需求&#xff0c;需要用python处理解压文件&#xff0c;这里记录下完成的代码&#xff0c;需要注意的是删除解压出来的文件夹时&#xff0c;很多博客都说直接用removedirs就行&#xff0c;实际我在py3.7上测试会提示“文件夹非空”&#xff0c;而且想想如果直接移除了根…...

代码随想录22| 216.组合总和III, 17.电话号码的字母组合

216.组合总和III 题目链接/文章讲解&#xff1a;链接地址 视频讲解&#xff1a;链接地址 代码思路&#xff1a;回溯三部曲&#xff1a; 1.确定函数参数&#xff1a;n,k,sum,startIndex; 2.结束条件&#xff0c;path k,并且如果sumn 结束递归 3.递归回溯逻辑。 class Solution…...

ITIL4—战略与指导

战略与指导 成功的服务提供&#xff0c;需要朝着商定的目标采取协调一致的行动。本节将探讨服务供应商战略的创建和管理&#xff0c;其目的是首先对战略的本质、范围&#xff0c;以及战略与指导的关系建立基本的理解&#xff0c;然后为与该战略一致的指导活动提供指导。 本节…...

【Spring】Spring循环依赖(超重要!!)

目录 什么是循环依赖问题 循环依赖具体是怎么解决的 具体的解决步骤&#xff1a; 通俗实例&#xff1a; 严谨的循环依赖解决图例 为什么使用的是三级缓存&#xff0c;二级缓存不够用吗&#xff1f; 什么是循环依赖问题 Spring的循环依赖是指在Bean之间存在相互依赖关…...

数据分析之路应该是就此开启了

咱就是说工作以后&#xff0c;就是重新学习的开始啊。 祝自己顺顺利利。 前路漫漫亦灿灿。 数据分析之路&#xff0c;开启吧。 以下是借鉴网上的学习路线。 这个学习路线图主要分为以下几个部分&#xff1a; 基础知识 &#xff1a;包括数学、统计学和编程语言。这是数据分析的…...

win10如何配置jdk环境变量

1.首先要打开系统环境变量配置的页面。具体操作是&#xff1a;打开开始菜单&#xff0c;找到“此电脑”&#xff0c;然后右键“更多”→“属性”。 2.在弹出的页面&#xff0c;选择“高级系统设置” 3.在弹出的页面&#xff0c;选择“环境变量&#xff08;N&#xff09;…”。 …...

pm4py使用指南(非机翻)

目录 1. 日志数据读取及预处理&#xff08;1&#xff09;查看case和event数量&#xff08;2&#xff09;查看起始事件和结束事件&#xff08;3&#xff09;时间戳格式的问题 2. 日志数据过滤3. 流程发现4. 模型转化5. 模型可视化 1. 日志数据读取及预处理 通过 pandas库 读取c…...

ChatGPT帮助提升工作效率和质量:完成时间下降40%,质量评分上升 18%

自ChatGPT去年11月发布以来&#xff0c;人们就开始使用它来协助工作&#xff0c;热心的用户利用它帮助撰写各种内容&#xff0c;从宣传材料到沟通话术再到调研报告。 两名MIT经济学研究生近日在《科学》杂志上发表的一项新研究表明&#xff0c;ChatGPT可能有助于减少员工之…...

第二章 搜索

本篇博文是笔者归纳汇总的AcWing基础课题集&#xff0c;方便读者后期复盘巩固~ PS&#xff1a;本篇文章只给出完整的算法实现&#xff0c;并没有讲解具体的算法思路。如果想看算法思路&#xff0c;可以阅读笔者往期写过的文章&#xff08;或许会有&#xff09;&#xff0c;也可…...

transform_train.json文件解析

transform_train.json 文件内容解析transform_matrix 文件内容解析 {"camera_angle_x": 0.6911112070083618,"frames": [{"file_path": "./train/r_0","rotation": 0.012566370614359171,"transform_matrix": [[…...

Wlan——锐捷零漫游网络解决方案以及相关配置

目录 零漫游介绍 一代零漫游 二代单频率零漫游 二代双频率零漫游 锐捷零漫游方案总结 锐捷零漫游方案的配置 配置无线信号的信道 开启关闭5G零漫游 查看配置 零漫游介绍 普通的漫游和零漫游的区别 普通漫游 漫游是由一个AP到另一个AP或者一个射频卡到另一个射频卡的漫…...

分布式锁系列之zookeeper分布式锁和mysql分布式锁

目录 介绍 下载安装 基本指令​编辑 java集成zookeeper 官方提供版 永久节点 临时节点​编辑 永久序列化节点 判断当前节点是否存在 获取当前节点中的数据内容 获取当前节点的子节点 更新节点内容 删除节点 zookeeper实现分布式锁 Mysql实现分布式锁 总结 介绍 ZooK…...

Ubuntu部署PHP7.4

系统版本&#xff1a;Ubuntu22.04 PHP版本: 7.4 Mysql版本&#xff1a;8.0 Nginx版本: 最新 1. 更新系统 首先&#xff0c;确保系统包是最新的&#xff1a; sudo apt update && sudo apt upgrade -y2. 安装 Nginx Nginx 在默认的 Ubuntu 仓库中&#xff0c;因此安装…...

WPF中的数据转换-StringFormat

WPF中的数据转换-StringFormat 前言 字符串格式化。使用该功能可以通过设置Binding.StringFormat属性对文本形式的数据进行转换——例如包含日期和数字的字符串。对于至少一半的格式化任务&#xff0c;字符串格式化是一种便捷的技术。 使用 当设置Binding.StringFormat属性…...

vscode里如何用git

打开vs终端执行如下&#xff1a; 1 初始化 Git 仓库&#xff08;如果尚未初始化&#xff09; git init 2 添加文件到 Git 仓库 git add . 3 使用 git commit 命令来提交你的更改。确保在提交时加上一个有用的消息。 git commit -m "备注信息" 4 …...

Java 语言特性(面试系列2)

一、SQL 基础 1. 复杂查询 &#xff08;1&#xff09;连接查询&#xff08;JOIN&#xff09; 内连接&#xff08;INNER JOIN&#xff09;&#xff1a;返回两表匹配的记录。 SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id d.dept_id; 左…...

7.4.分块查找

一.分块查找的算法思想&#xff1a; 1.实例&#xff1a; 以上述图片的顺序表为例&#xff0c; 该顺序表的数据元素从整体来看是乱序的&#xff0c;但如果把这些数据元素分成一块一块的小区间&#xff0c; 第一个区间[0,1]索引上的数据元素都是小于等于10的&#xff0c; 第二…...

大话软工笔记—需求分析概述

需求分析&#xff0c;就是要对需求调研收集到的资料信息逐个地进行拆分、研究&#xff0c;从大量的不确定“需求”中确定出哪些需求最终要转换为确定的“功能需求”。 需求分析的作用非常重要&#xff0c;后续设计的依据主要来自于需求分析的成果&#xff0c;包括: 项目的目的…...

ffmpeg(四):滤镜命令

FFmpeg 的滤镜命令是用于音视频处理中的强大工具&#xff0c;可以完成剪裁、缩放、加水印、调色、合成、旋转、模糊、叠加字幕等复杂的操作。其核心语法格式一般如下&#xff1a; ffmpeg -i input.mp4 -vf "滤镜参数" output.mp4或者带音频滤镜&#xff1a; ffmpeg…...

Spring Boot+Neo4j知识图谱实战:3步搭建智能关系网络!

一、引言 在数据驱动的背景下&#xff0c;知识图谱凭借其高效的信息组织能力&#xff0c;正逐步成为各行业应用的关键技术。本文聚焦 Spring Boot与Neo4j图数据库的技术结合&#xff0c;探讨知识图谱开发的实现细节&#xff0c;帮助读者掌握该技术栈在实际项目中的落地方法。 …...

Android Bitmap治理全解析:从加载优化到泄漏防控的全生命周期管理

引言 Bitmap&#xff08;位图&#xff09;是Android应用内存占用的“头号杀手”。一张1080P&#xff08;1920x1080&#xff09;的图片以ARGB_8888格式加载时&#xff0c;内存占用高达8MB&#xff08;192010804字节&#xff09;。据统计&#xff0c;超过60%的应用OOM崩溃与Bitm…...

Xen Server服务器释放磁盘空间

disk.sh #!/bin/bashcd /run/sr-mount/e54f0646-ae11-0457-b64f-eba4673b824c # 全部虚拟机物理磁盘文件存储 a$(ls -l | awk {print $NF} | cut -d. -f1) # 使用中的虚拟机物理磁盘文件 b$(xe vm-disk-list --multiple | grep uuid | awk {print $NF})printf "%s\n"…...

Spring是如何解决Bean的循环依赖:三级缓存机制

1、什么是 Bean 的循环依赖 在 Spring框架中,Bean 的循环依赖是指多个 Bean 之间‌互相持有对方引用‌,形成闭环依赖关系的现象。 多个 Bean 的依赖关系构成环形链路,例如: 双向依赖:Bean A 依赖 Bean B,同时 Bean B 也依赖 Bean A(A↔B)。链条循环: Bean A → Bean…...

JS设计模式(4):观察者模式

JS设计模式(4):观察者模式 一、引入 在开发中&#xff0c;我们经常会遇到这样的场景&#xff1a;一个对象的状态变化需要自动通知其他对象&#xff0c;比如&#xff1a; 电商平台中&#xff0c;商品库存变化时需要通知所有订阅该商品的用户&#xff1b;新闻网站中&#xff0…...