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

SpringBoot入门教程:数据库恢复(mysqldump和mysqlbinlog)

请添加图片描述

一:什么是bin log

MySQL的所有的数据库改变操作(除了select等)都会记录到一种二进制文件中,这种文件就是bin log。
bin log 用来实现主从复制,也常用来误删数据库找回丢失的记录。

一般恢复从两种纬度来进行恢复:

  1. 一部分从mysqldump中恢复备份到之前的状态。
  2. 另一部分在恢复到备份之前的基础上再从bin log中找出需要恢复的数据。

二:查看bin log配置

在这里插入图片描述

  • log_bin :是否开启bin log,默认值为 ON 表示开启。
  • log_bin_basename :配置bin log文件所在的目录(/usr/local/mysql/data)以及文件名前缀(mysql-bin)。bin log文件名是从mysql-bin.000001开始的。
  • log_bin_index:配置索引对应的bin log文件的绝对路径,文件中记录所有bin log文件名。 /usr/local/mysql/data/mysql-bin.index

三:删除bin log文件

为了测试方便可以先删除之前的bin log文件。

注意:最好是自己本地开发环境来执行以下命令,千万不要在测试环境或者其它共用环境删除bin log。

# 用来查看当前最新的bin log文件及对应的结束位置
show master status;

# 查看所有bin log文件
show binary logs;

# 生成一个新的bin log文件(推荐:自己玩的时候可以先生成一个新的bin log文件,所有新操作都记录在新生成的文件中)
flush binary logs;

# 删除所有binlog日志,新日志编号从头000001开始(建议自己玩的时候可以使用)
reset master;

# 删除mysql-bin.000001之前的所有bin log
purge master logs to 'mysql-bin.000001';

# 删除指定时间之前的数据
purge master logs before '2022-09-01 23:59:59';

四:准备测试数据

执行SQL时不要一下子全部执行,为了后续方便演示基于时间范围的操作,需要一条一条的执行,最好每条命令间隔1秒以上执行。

CREATE DATABASE `db1`;
use db1;
CREATE TABLE `tbl_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL COMMENT '用户名',
  `status` tinyint(4) DEFAULT NULL COMMENT '状态(0:关闭,1:打开)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


insert into tbl_user(username, status) values('monday', 0);
insert into tbl_user(username, status) values('vbirdbest', 1);

update tbl_user set status = 1 where username = 'monday';

delete from tbl_user where username = 'vbirdbest';
drop database db1;

五:查看bin log

bin log 需要关注的字段:

  • Log_name:bin log 文件名。
  • Pos:开始位置点。
  • End_log_pos:结束位置点。
  • Event_type:事件类型(Query:DML语句,Write_rows:insert语句,Update_rows:update语句,Delete_rows:delete语句)。

方式一:show binlog events

# 查看当前最新的bin log文件
mysql> show master status;
# 查看bin文件基本信息, Pos表示位置position,如904位置是insert一条数据
mysql> show binlog events in 'mysql-bin.000001';

在这里插入图片描述

方式二:mysqlbinlog命令

mysqlbinlog方式和show binlog events方式差不多,其中 at 表示开始位置Pos(position),end_log_pos表示结束位置点。

  • bin log中创建数据库create database、创建表create table、删除数据库drop database 都是直接记录的SQL语句,
  • 插入insert、更新update、删除delete 都是编码之后的人类不可读的内容存在。所以需要--base64-output=DECODE-ROWS -vv解码。
  • 每个SQL操作都会记录操作时间#220903 17:08:44
# 如果报权限问题切换root用户 mysqlbinlog: File '/usr/local/mysql/data/mysql-bin.000001' not found (Errcode: 13 - Permission denied)
~ sudo su

# 解码:将insert/update/delete等解码成SQL语句
mysqlbinlog --no-defaults --skip-gtids --base64-output=DECODE-ROWS -vv /usr/local/mysql/data/mysql-bin.000001

在这里插入图片描述

六:恢复数据mysqlbinlog

6.1 mysqlbinlog命令常用参数

  • --no-defaults:如果不报错可以不使用该参数。 mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
  • --skip-gtids=true:gtid是一种全局唯一id。设置为true表示导出的sql中不包含gtid相关的设置。
  • --base64-output=DECODE-ROWS -vv:bin log内容经过编码了,需要解码之后人类才可读。
  • --start-position=801:恢复的开始位置。注意:开始位置一般取BEGIN的位置,取靠下的位置可能导不出来想要的log。
  • --stop-position=1590:恢复的结束位置。注意:是不包含停止位置的。
  • --start-datetime="2022-01-01 00:00:00" :指定开始时间之后的bin log。如果不指定表示从头开始。
  • --stop-datetime="2022-09-01 23:59:59":指定结束时间之前的bin log。如果不指定表示一直到结尾。
  • --database=db1:bin log 文件会记录所有数据库的变更,所以一般我们需要指定数据库对应的bin log。

6.2 查看最新的bin log文件

# 删除所有bin log文件,从mysql-bin.000001开始
mysql> reset master;
mysql> show master status;

在这里插入图片描述

6.3 模拟数据库误删操作

mysql> CREATE DATABASE `db1`;
mysql> use db1;
mysql> CREATE TABLE `tbl_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL COMMENT '用户名',
  `status` tinyint(4) DEFAULT NULL COMMENT '状态(0:关闭,1:打开)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> insert into tbl_user(username, status) values('monday', 0);
mysql> insert into tbl_user(username, status) values('vbirdbest', 1);

# mysql备份 https://blog.csdn.net/zhou920786312/article/details/123354503
# 单事务导出包括存储过程、触发器、事件
~ mysqldump -uroot -p --single-transaction --triggers --routines --events --databases db1 > db1-20220904.dump
mysql> insert into tbl_user(username, status) values('modely', 1);



# 模拟误操作,清空了表
mysql> delete from tbl_user;

6.3 从备份中恢复

~ mysql -uroot -p  < alldb1.dump

在这里插入图片描述

七:从bin log中恢复

总共有3条数据,已经有2条从备份中恢复了,剩下1条我们从bin log中找出来恢复。

7.1 方式一:基于时间范围

7.1.1 先查看一下所有的内容,确认时间范围

~ sudo su
~ /usr/local/mysql/bin/mysqlbinlog --no-defaults \
--base64-output=DECODE-ROWS -vv --skip-gtids=true \
--database=db1 /usr/local/mysql/data/mysql-bin.000001

在这里插入图片描述

7.1.2 根据时间范围确认过滤出来的日志

/usr/local/mysql/bin/mysqlbinlog \
--no-defaults \
--start-datetime="2022-09-04 18:12:17" \
--stop-datetime="2022-09-04 18:12:52" \
--base64-output=DECODE-ROWS -vv \
--skip-gtids=true \
--database=db1 /usr/local/mysql/data/mysql-bin.000001 

在这里插入图片描述

7.1.3 确认好后导出.sql文件

# 导出sql文件
/usr/local/mysql/bin/mysqlbinlog \
--no-defaults \
--start-datetime="2022-09-04 18:12:17" \
--stop-datetime="2022-09-04 18:12:52" \
--base64-output=DECODE-ROWS -vv \
--skip-gtids=true \
--database=db1 /usr/local/mysql/data/mysql-bin.000001 \
> /Users/mengday/binlog.sql 

在这里插入图片描述

7.1.4 从.sql文件中恢复数据

# 2. 方式一:然后登录mysql source这个文件
~ mysql -uroot -p	
mysql> source /Users/mengday/binlog.sql;

# 2. 方式二
mysql -uroot -p < /Users/mengday/binlog.sql;

在这里插入图片描述
通过 source binlog.sql 或者 mysql -uroot -p < binlog.sql 都没有将insert语句插入进去,不知道什么原因。通过另一种形式也没有执行成功!!!

/usr/local/mysql/bin/mysqlbinlog \
--no-defaults \
--start-datetime="2022-09-04 18:12:17" \
--stop-datetime="2022-09-04 18:12:52" \
--base64-output=DECODE-ROWS -vv \
--skip-gtids=true \
--database=db1 /usr/local/mysql/data/mysql-bin.000001 \
| mysql -uroot -p

7.2 方式二:基于位置(推荐)

因为在同一秒中可能操作多条SQL,如果在这一秒中有一部分需要恢复有一部分不需要恢复那么使用居于时间范围的就不准确,使用基于位置的就更加准确。

7.2.1 基于位置过滤日志

/usr/local/mysql/bin/mysqlbinlog \
--no-defaults \
--start-position=841 \
--stop-position=948 \
--base64-output=DECODE-ROWS -vv \
--skip-gtids=true \
--database=db1 /usr/local/mysql/data/mysql-bin.000001 \
> /Users/mengday/binlog2.sql

在这里插入图片描述

7.2.2 恢复数据

source binlog2.sql;

同样不出意外的insert语句并没有执行。

7.3 多文件恢复

# 跨bin log文件
mysqlbinlog --skip-gtids=true \
--start-position=802 mysql-bin.000001 \
--stop-position=726 mysql-bin.000002 \
|mysql -uroot -proot

# 从mysql-bin.000001 802位置开始到mysql-bin.000002结束
mysqlbinlog --skip-gtids=true \
--start-position=802 mysql-bin.000001  mysql-bin.000002 \
|mysql -uroot -proot

注意:如果有多个mysql-bin文件,文件中的位置position不是唯一的,是重复利用的。所以当操作多个文件时要小心。例如 at 4 可能在多个bin log中出现。需要操作多个文件时最好分开一个一个单独操作。

八:binlog2sql 工具

既然通过source不能直接执行bin log sql语句,那么我们就把bin log sql语句转化成正常的sql语句自己手动执行。

Python工具 binlog2sql:https://github.com/danfengcao/binlog2sql

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

# 注意:如果本地python环境冲突的话,最好为当前项目创建一个虚拟环境,否则很容易报ModuleNotFoundError: No module named 'pymysqlreplication'
python3 -m venv env
source env/bin/activate
pip3 install -r requirements.txt

# 查询所有sql语句,从而找到想要恢复是sql语句
python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root123' -ddb1 -ttbl_user --start-file='mysql-bin.000001'

# 通过起始位置进一步确认是否是想要的sql
python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root123' -ddb1 -ttbl_user --start-file='mysql-bin.000001' --start-position=705 --stop-position=948

# -B参数生成对应的回滚sql,如insert回滚sql为delete
python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root123' -ddb1 -ttbl_user --start-file='mysql-bin.000001' --start-position=705 --stop-position=948 > insert.sql | cat

# 拿到这个SQL我直接执行,我就不信恢复不过来
INSERT INTO `db1`.`tbl_user`(`id`, `username`, `status`) VALUES (6, 'modely', 1); #start 705 end 948 time 2022-09-04 18:12:17

在这里插入图片描述

🌹🌹🌹🌹🌹🌹🌹🌹🌹🌹🌹🌹🌹至此葵花宝典已经连成。🌹🌹🌹🌹🌹🌹🌹🌹🌹🌹🌹🌹🌹

在这里插入图片描述

相关文章:

  • 内网开发新项目之流程记录
  • 数据分析-numpy2
  • MATLAB | 全网唯一,双变量及三变量映射图表的MATLAB绘制
  • 分布式 | 从 dble 日志分析到 MySQL 源码学习
  • 天呐,我居然可以隔空作画了
  • 【面试】软件自动化测试岗位面试题和答案
  • 氨基功能化离子液体修饰SBA-15(NH2-IL-SBA)|含有烯丙基的离子液体氯化1-烯丙基-3-甲基咪唑(AMIMCl)
  • 【c ++ primer 笔记】第 14章 重载运算符
  • Nginx+Tomcat负载均衡、动静分离集群
  • Linux入门学习 —— 常用的基本命令(下)
  • 11、Java 变量作用域、构造方法官方教程
  • NumPy的常用函数
  • 字符串常量池与StringBuilder
  • 时间序列中的特征选择:在保持性能的同时加快预测速度
  • 谷粒商城 (九) --------- 前端基础 Vue 篇
  • [ 一起学React系列 -- 8 ] React中的文件上传
  • [译] React v16.8: 含有Hooks的版本
  • 0x05 Python数据分析,Anaconda八斩刀
  • 2018以太坊智能合约编程语言solidity的最佳IDEs
  • canvas绘制圆角头像
  • Gradle 5.0 正式版发布
  • IP路由与转发
  • JavaScript 事件——“事件类型”中“HTML5事件”的注意要点
  • JAVA多线程机制解析-volatilesynchronized
  • Redis在Web项目中的应用与实践
  • Vue组件定义
  • Web标准制定过程
  • 等保2.0 | 几维安全发布等保检测、等保加固专版 加速企业等保合规
  • 机器学习 vs. 深度学习
  • 机器学习中为什么要做归一化normalization
  • 类orAPI - 收藏集 - 掘金
  • 浏览器缓存机制分析
  • 设计模式(12)迭代器模式(讲解+应用)
  • 使用SAX解析XML
  • 我与Jetbrains的这些年
  • 移动端 h5开发相关内容总结(三)
  • 用简单代码看卷积组块发展
  • 3月27日云栖精选夜读 | 从 “城市大脑”实践,瞭望未来城市源起 ...
  • #我与Java虚拟机的故事#连载08:书读百遍其义自见
  • (22)C#传智:复习,多态虚方法抽象类接口,静态类,String与StringBuilder,集合泛型List与Dictionary,文件类,结构与类的区别
  • (Matlab)遗传算法优化的BP神经网络实现回归预测
  • (vue)el-checkbox 实现展示区分 label 和 value(展示值与选中获取值需不同)
  • (ZT)薛涌:谈贫说富
  • (附源码)spring boot建达集团公司平台 毕业设计 141538
  • (过滤器)Filter和(监听器)listener
  • (六)激光线扫描-三维重建
  • (转)Oracle存储过程编写经验和优化措施
  • (转)Unity3DUnity3D在android下调试
  • ****** 二 ******、软设笔记【数据结构】-KMP算法、树、二叉树
  • .mysql secret在哪_MySQL如何使用索引
  • .NET 4.0网络开发入门之旅-- 我在“网” 中央(下)
  • .NET Core WebAPI中使用swagger版本控制,添加注释
  • .net访问oracle数据库性能问题
  • .net通用权限框架B/S (三)--MODEL层(2)
  • .NET正则基础之——正则委托