百度360必应搜狗淘宝本站头条
tomcat 乱码无法与服务器建立安全连接是什么意思sysv-rc-confdocker映射容器目录到宿主机internet explorer 增强的安全配置已启用
当前位置：网站首页 > 技术文章 > 正文

MySQL(23) 数据恢复之binlog

nanshan 2024-11-27 18:14 23 浏览 0 评论



一、前言

本文将基于mysql5.7版本根据binlog进行数据恢复

1、查看自己的数据库版本

SELECT VERSION();

2、官方文档

mysql5.7之备份和恢复: https://dev.mysql.com/doc/refman/5.7/en/backup-and-recovery.html

3、查看binlog是否开启

show variables like 'log_%';

log_bin值：

  1. OFF -> 未开启；
  2. ON -> 开启；

4、开启binlog

修改my.cnf文件，添加如下内容，重启mysql

# 同一局域网内注意要唯一
server-id=3306
# 开启二进制日志功能 & 日志位置存放位置`/var/lib/mysql`
#log-bin=mysql-bin
log-bin=/var/lib/mysql/mysql-bin
# binlog格式
# 1. STATEMENT：基于SQL语句的模式，binlog 数据量小，但是某些语句和函数在复制过程可能导致数据不一致甚至出错；
# 2. MIXED：混合模式，根据语句来选用是 STATEMENT 还是 ROW 模式；
# 3. ROW：基于行的模式，记录的是行的完整变化。安全，但 binlog 会比其他两种模式大很多；
binlog_format=ROW
# FULL：binlog记录每一行的完整变更 MINIMAL：只记录影响后的行
binlog_row_image=FULL
# 日志文件大小
max_binlog_size=1G

二、数据恢复

温馨小提示:请先备份数据库!

1、全量恢复

此方式只适合对数据有整体备份的情况下做整体恢复，应该更适用于部署新环境时迁移数据时使用 可参考： https://zhengqing.blog.csdn.net/article/details/86481822

# 导出所有数据库数据
mysqldump -h 127.0.0.1 -P 3306  -uroot -proot --all-databases > /tmp/all_20210903.sql

# 导入数据
mysql -uroot -proot
source /tmp/all_20210903.sql

2、通过binlog恢复

注：数据恢复mysqlbinlog相关命令操作在binlog日志文件/var/lib/mysql所在路径下操作！ 不然，binlog日志文件需要写绝对路径！ 主要根据时间点/事件位置进行操作恢复数据

# 进入bin-log日志文件所在目录
cd /var/lib/mysql

# 恢复数据 

# 1、全部恢复（加`--no-defaults`解决`mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'`问题）
# mysqlbinlog mysql-bin.000006 | mysql -uroot -proot
mysqlbinlog --no-defaults mysql-bin.000006 | mysql -uroot -proot

# 2、根据时间点恢复
# 2.1、恢复"2021-09-03 00:00:00"之前的数据
mysqlbinlog --no-defaults --stop-datetime="2021-09-03 00:00:00" mysql-bin.000006 | mysql -uroot -proot
# 2.2、恢复"2021-09-03 00:00:00"之后的数据
mysqlbinlog --no-defaults --start-datetime="2021-09-03 00:00:00" mysql-bin.000006 | mysql -uroot -proot
# 2.3、恢复"2021-09-03 00:00:00"-"2021-09-03 23:59:59"之间的数据
mysqlbinlog --no-defaults --start-datetime="2021-09-03 00:00:00" --stop-datetime="2021-09-03 23:59:59" mysql-bin.000006 | mysql -uroot -proot

# 3、根据事件位置恢复
# 3.1、恢复位置"1000"之后的数据
mysqlbinlog --no-defaults --start-position=1000 mysql-bin.000008 | mysql -uroot -proot
# 3.2、恢复位置"1000"之前的数据
mysqlbinlog --no-defaults --stop-position=1000 mysql-bin.000008 | mysql -uroot -proot
# 3.3、恢复位置"1000"-"2000"之间的数据
mysqlbinlog --no-defaults --start-position=1000 --stop-position=2000 mysql-bin.000008 | mysql -uroot -proot

3、小白操作教学

-- 结束正在写入的日志文件，创建一个新的日志文件进行写入
flush logs;
-- 查看最新正在写入的日志文件
show master status;

小白建库建表造数据

-- 创建数据库-test
create database test;
use test;
-- 创建表t_user
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(20) DEFAULT NULL COMMENT '用户名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='测试表';
-- 插入数据
insert into test.t_user values(1,'test'),(2,'admin');

小白查看日志文件内容

# 查看日志文件大小
du -sh mysql-bin*
# 如果日志文件不大，可以通过此方式直接查看
mysqlbinlog --no-defaults -v mysql-bin.000002
# 如果日志文件大，则选择一页一页的方式查看吧
mysqlbinlog --no-defaults -v mysql-bin.000002 | more

下面为mysql-bin.000002日志文件内容，一个简单的建库，建表，新增数据内容

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210905 16:19:23 server id 3306  end_log_pos 123 CRC32 0x509e9c38       Start: binlog v 4, server v 5.7.26-log created 210905 16:19:23
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
i300YQ/qDAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATicnlA=
'/*!*/;
# at 123
#210905 16:19:23 server id 3306  end_log_pos 154 CRC32 0x1607f568       Previous-GTIDs
# [empty]
# at 154
#210905 16:20:28 server id 3306  end_log_pos 219 CRC32 0x0f215cf5       Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210905 16:20:28 server id 3306  end_log_pos 313 CRC32 0xbd2f9107       Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1630830028/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113664/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database test
/*!*/;
# at 313
#210905 16:20:41 server id 3306  end_log_pos 378 CRC32 0x996a1c5e       Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 378
#210905 16:20:41 server id 3306  end_log_pos 688 CRC32 0xee3bc520       Query   thread_id=3     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1630830041/*!*/;
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(20) DEFAULT NULL COMMENT '用户名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='测试表'
/*!*/;
# at 688
#210905 16:21:14 server id 3306  end_log_pos 753 CRC32 0xf09526cc       Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 753
#210905 16:21:14 server id 3306  end_log_pos 825 CRC32 0x83a30735       Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1630830074/*!*/;
BEGIN
/*!*/;
# at 825
#210905 16:21:14 server id 3306  end_log_pos 877 CRC32 0x0cc0d937       Table_map: `test`.`t_user` mapped to number 112
# at 877
#210905 16:21:14 server id 3306  end_log_pos 933 CRC32 0x9f85ceed       Write_rows: table id 112 flags: STMT_END_F

BINLOG '
+n00YRPqDAAANAAAAG0DAAAAAHAAAAAAAAEABHRlc3QABnRfdXNlcgACAw8CPAACN9nADA==
+n00YR7qDAAAOAAAAKUDAAAAAHAAAAAAAAEAAgAC//wBAAAABHRlc3T8AgAAAAVhZG1pbu3OhZ8=
'/*!*/;
### INSERT INTO `test`.`t_user`
### SET
###   @1=1
###   @2='test'
### INSERT INTO `test`.`t_user`
### SET
###   @1=2
###   @2='admin'
# at 933
#210905 16:21:14 server id 3306  end_log_pos 964 CRC32 0x3e9c8ccb       Xid = 301
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

小白删库

drop database test;

小白恢复数据

-- 结束正在写入的日志文件，创建一个新的日志文件进行写入 => 目的：方便做数据恢复时，所有操作数据写入新的日志中，不然数据恢复时，之前的相关sql操作语句会再次写入到当前日志文件，当然这里根据自己需不需要此操作！
flush logs;
-- 查看最新正在写入的日志文件
show master status;

从binlog可以看出执行删库命令的事件位置在1996，时间点在2021-09-05 16:42:08

# 方式一： 恢复 ”mysql-bin.000002“日志文件中 事件位置”1996“ 之前的数据
mysqlbinlog --no-defaults --stop-position=1996 mysql-bin.000002 | mysql -uroot -proot
# 方式二： 恢复 ”mysql-bin.000002“日志文件中 时间点”2021-09-05 16:42:08“ 之前的数据
mysqlbinlog --no-defaults --stop-datetime='2021-09-05 16:42:08' mysql-bin.000002 | mysql -uroot -proot

之后查看数据已恢复成功^_^

三、其它

1、binlog文件生成策略

  1. mysql重启；
  2. 执行flush logs命令;
  3. binlog文件大小达到设定的日志文件最大值（不是绝对，例如：指定1G，因为某些情况，超过1G后，暂时还不会重新生成新的日志文件进行写入）；

2、查看/删除 binlog

-- 查看binlog格式
show variables like 'binlog_format';
show variables like '%row_im%';

-- 查询 BINLOG 位置
show variables like 'datadir';

-- 查看日志文件
show binary logs;
-- 或 show master logs;

-- 查看正在写入的日志文件
show master status;

-- 查看当前binlog文件内容 (“limit 0,10” => 从第0条数据开始，展示10条数据，切记加limit查看，不然数据量大的时候吓死乖乖！)
show binlog events limit 0,10;
-- 可以格式化输出 
show binlog events limit 0,10 \G;
-- 或指定日志文件查看 
show binlog events in 'mysql-bin.000003' limit 0,10;

-- 注：
-- Log_name:    此条log存在哪个文件中 
-- Pos:         log在bin-log中的开始位置 
-- Event_type:  log的类型信息 
-- Server_id:   可以查看配置中的server_id,表示log是哪个服务器产生 
-- End_log_pos: log在bin-log中的结束位置 
-- Info:        log的一些备注信息，可以直观的看出进行了什么操作 


-- 查看日志 （此方式查看日志，可看见完整sql操作）
mysqlbinlog --no-defaults -v mysql-bin.000008


-- 结束正在写入的日志文件，创建一个新的日志文件进行写入
flush logs;
show master status;

-- 删除所有二进制日志，并重新开始记录（此命令谨慎操作！）
reset master;
show master status;

-- 将'mysql-bin.000003'编号之前的所有日志进行删除
purge master logs to 'mysql-bin.000003';
-- 将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除
purge master logs before '2021-09-03 17:30:00'

3、binlog导出

# 进入bin-log日志文件所在目录
cd /var/lib/mysql

# 导出成sql格式 

# 1、全部导出（加`--no-defaults`解决`mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'`问题）
# mysqlbinlog mysql-bin.000006 > /tmp/mysql-bin06-1.sql 
mysqlbinlog --no-defaults mysql-bin.000006 > /tmp/mysql-bin06-1.sql

# 2、根据时间点导出
# 2.1、导出"2021-09-03 00:00:00"之前的数据
mysqlbinlog --no-defaults --stop-datetime="2021-09-03 00:00:00" mysql-bin.000006 > /tmp/mysql-bin06-1.log
# 2.2、导出"2021-09-03 00:00:00"之后的数据
mysqlbinlog --no-defaults --start-datetime="2021-09-03 00:00:00" mysql-bin.000006 > /tmp/mysql-bin06-2.log
# 2.3、导出"2021-09-03 00:00:00"-"2021-09-03 23:59:59"之间的数据
mysqlbinlog --no-defaults --start-datetime="2021-09-03 00:00:00" --stop-datetime="2021-09-03 23:59:59" mysql-bin.000006 > /tmp/mysql-bin06-3.log

# 3、根据事件位置导出
# 3.1、导出位置"1000"之后的数据
mysqlbinlog --no-defaults --start-position=1000 mysql-bin.000008 > /tmp/mysql-bin08-1.log
# 3.2、导出位置"1000"之前的数据
mysqlbinlog --no-defaults --stop-position=1000 mysql-bin.000008 > /tmp/mysql-bin08-2.log
# 3.3、导出位置"1000"-"2000"之间的数据
mysqlbinlog --no-defaults --start-position=1000  --stop-position=2000 mysql-bin.000008 > /tmp/mysql-bin08-3.log

4、mysql全局只读锁

-- 查看全局锁状态
show variables like '%read_only%';
-- 设置全局只读 （普通权限的用户只读，不能写数据）
set global read_only=1;
-- 解开全局锁
set global read_only=0;

今日分享语句： 成功不是将来才有的，而是从决定去做的那一刻起，持续累积而成。

相关推荐

今晚拿下PHP反序列化的一系列操作

引言在CTF中反序列化类型的题目还是比较常见的，之前有学习过简单的反序列化，以及简单pop链的构造。这次学习内容为php内置的原生类的反序列化以及一点进阶知识。在题目给的的代码中找不到可利用的类时，这...

Win10安装Apache和PHP（apache安装php模块）

说明：虽然PHPStudy之类的软件给我们提供了方便的集成环境，但是其使用的是非线程安全的PHP版本（NotThreadSafe，NTS），某些功能不可以使用。所以，我们还需要自己再安装一个Apa...

腾讯云云函数部署laravel项目（腾讯云函数 selenium）

1、购买函数套餐包在Serverless控制台，选择函数套餐包进行购买2、新建函数服务2.1、模板创建选择函数URL：新建函数URL，启用公网访问：2.1.1、postman访问首页2.1.2、pos...

站长教你搭建属于自己的网站（站长教你搭建属于自己的网站是什么）

每个人都希望可以有自己的网站，因为那样高端大气上档次，低调奢华有内涵，尤其是公司用户，一般会有自己的网站。而个人呢，也可以搭建自己的网站博客，论坛等，但是一般个人都是搭建博客的。好了，那么下面站长教你...

微信公众号开发出现 cURL error 60: SSL certificate problem ssl证书

在phpstudy的环境下如果出现这样的报错cURLerror60:SSLcertificateproblem:unabletogetlocalissuercertificat...

【网络安全】关于PHP Study nginx解析高危漏洞的预警通报

网络安全近日，山石网科安全研究院监测发现PHPStudyWindows最新版本存在nginx解析漏洞，可以造成任意代码执行。一、漏洞情况phpStudy是一个PHP调试环境的程序集成包，该程序包集成...

PHP 环境 搭建教程（php环境搭建教程linux）

PHP是一种编程语言，很多网站都用PHP语言编写，我们有时候需要测试一个网站，就需要PHP环境才能运行，又要安装Apache、又要安装MySQL……真的非常麻烦。其实我们可以使用PHP集成...

黑客搭建钓鱼平台，手把手教你如何钓鱼?

跨站脚本攻击XSS：通过HTML注入篡改了网页，插入了恶意的脚本，从而用户浏览网页时，控制用户浏览器的一种攻击那么，我们搭建一个XSS钓鱼平台吧，注意：这个平台仅用于学习和测试，小伙伴们不要动有坏心思...

php源码网站搭建方法和过程（php网站源码完整）

web网站是我们上网的窗口，而网站是如何搭建的呢？今天我们来做一个介绍，以php代码为例来进行介绍（后续会介绍一下java代码搭建，如果想要我这里涉及的工具或源码请私信我）。1、首先你需要去网上下载你...

使用VS Code调试PhpStudy环境里的代码

最近几个月把所有项目都迁过来VSCode了（除了因为Unity调试问题反而用回了VisualStudio），PHP也就抛弃了最强的PhpStorm。这段时间抽空在帮朋友处理PHP项目，然...

phpstudy搭建PHP+Mysql服务（用phpstudy搭建服务器）

PHP是一种创建动态交互性站点的强有力的服务器端脚本语言。PHP是免费的，并且使用非常广泛。同时，对于像微软ASP这样的竞争者来说，PHP无疑是另一种高效率的选项。（1）PHP环境搭建使用V...

Windows安装phpstudy（Windows安装mysql）

说明：phpstudy是一个PHP+MySQL+Apache的集成环境，可以减少单独部署各个所需软件的麻烦，以及更加方便地切换版本。phpenv、wamp等软件的作用一样。由于环境的不同，安装过程中可...

phpstudy安装及简单使用教程（phpstudy安装教程详解）

phpstudy前不久爆出有后门，我的看法是，去看下是哪个版本有后门，为啥会有后门，怎么解决掉这个后门，而不是听到后门就弃用了。毕竟phpstudy绿色安装，配置简单，多版本融合，真香。前言：关于开发...

如何对dedeCMS的开源程序进行二次开发

二次开发，简单的说就是在现有的软件上进行定制修改，功能的扩展，然后达到自己想要的功能和效果，一般来说都不会改变原有系统的内核。为了让更多人了解二次开发，并更方便的了解DedeCMS的二次开发，下面将会...

mysql基础问题三问(底层逻辑;正在执行;日志观察)

背景：经常面试会遇到且实际工作中也会应用到的三个场景：目录：一.mysql查询时的底层原理是什么？二.如何查看正在执行的mysql语句？三.如何观察mysql运行过程中的日志信息？-----...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表