开发者

SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

开发者 https://www.devze.com 2024-01-24 10:22 出处：网络 作者： 风流 少年
目录一：什么是bin log二：查看bin log配置三：删除bin log文件四：准备测试数据五：查看bin log方式一：show binlog events方式二：mysqlbinlog命令六：恢复数据mysqlbinlog6.1 mysqlbinlog命令常用参数6.2 查看最新
相关专题：SpringBoot mysqlbinlogSpringBoot mysqldumpSpringBoot数据库恢复
目录
  • 一：什么是bin log
  • 二：查看bin log配置
  • 三：删除bin log文件
  • 四：准备测试数据
  • 五：查看bin log
    • 方式一：show binlog events
    • 方式二：mysqlbinlog命令
  • 六：恢复数据mysqlbinlog
    • 6.1 mysqlbinlog命令常用参数
    • 6.2 查看最新的bin log文件
    • 6.3 模拟数据库误删操作
    • 6.4 从备份中恢复
  • 七：从bin log中恢复
    • 7.1 方式一：基于时间范围
    • 7.2 方式二：基于位置(推荐)
    • 7.3 多文件恢复
  • 八：binlog2sql 工具

    一：什么是bin log

    MySQL的所有的数据库改变操作（除了select等）都会记录到一种二进制文件中，这种文件就是bin log。

    bin log 用来实现主从复制，也常用来误删数据库找回丢失的记录。

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

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

    二：查看bin log配置

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    • 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;

# 生成一python个新的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';

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    方式二：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

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    六：恢复数据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;

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    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_uspythoner(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.4 从备份中恢复

    ~ mysql -uroot -p  < alldb1.dump

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    七：从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

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    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

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    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 \
> /Us编程客栈ers/mengday/binlog.sql

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    7.1.4 从.sql文件中恢复数据

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

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

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    通过 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

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    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 binl编程og2sql/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

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    至此葵花宝典已经连成。

    SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog

    到此这篇关于SpringBoot数据库恢复的两种方法mysqldump和mysqlbinlog的文章就介绍到这了,更多相关SpringBoot数据库恢复内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！ 

    0

    上一篇:

    :下一篇

    更多 开发 相关资讯：

    精彩评论

    暂无评论...
    登录 注册
    Qedev.com
    验证码 换一张
    取 消

      关注公众号

      热门标签

      图文推荐

      Qt两种定时器使用实现方式

      Qt两种定时器使用实现方式
      基于C#制作一个颜色拾取器

      基于C#制作一个颜色拾取器
      Java中private关键字详细用法实例以及解释

      Java中private关键字详细用法实例以及解释
      Spring结合WebSocket实现实时通信的教程详解

      Spring结合WebSocket实现实时通信的教程详解
      Java线程中synchronized和volatile关键字的区别详解

      Java线程中synchronized和volatile关键字的区别详解
      SpringBoot整合ELK做日志超完整详细教程

      SpringBoot整合ELK做日志超完整详细教程
      SpringBoot导出Excel的四种实现方式

      SpringBoot导出Excel的四种实现方式
      一文详解如何更改电脑使用的JDK版本

      一文详解如何更改电脑使用的JDK版本

      开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

      法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

      Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9