MySQL(23) 数据恢复之binlog
nanshan 2024-11-27 18:14 27 浏览 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值：
- OFF -> 未开启；
- 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文件生成策略
- mysql重启；
- 执行flush logs命令;
- 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;
今日分享语句： 成功不是将来才有的，而是从决定去做的那一刻起，持续累积而成。
相关推荐
- 0722-6.2.0-如何在RedHat7.2使用rpm安装CDH(无CM)
-
文档编写目的在前面的文档中，介绍了在有CM和无CM两种情况下使用rpm方式安装CDH5.10.0，本文档将介绍如何在无CM的情况下使用rpm方式安装CDH6.2.0，与之前安装C5进行对比。环境介绍：...
- ARM64 平台基于 openEuler + iSula 环境部署 Kubernetes
-
为什么要在arm64平台上部署Kubernetes，而且还是鲲鹏920的架构。说来话长。。。此处省略5000字。介绍下系统信息；o架构：鲲鹏920(Kunpeng920)oOS：ope...
- 生产环境starrocks 3.1存算一体集群部署
-
集群规划FE：节点主要负责元数据管理、客户端连接管理、查询计划和查询调度。>3节点。BE:节点负责数据存储和SQL执行。>3节点。CN:无存储功能能的BE。环境准备CPU检查JDK...
- 在CentOS上添加swap虚拟内存并设置优先级
-
现如今很多云服务器都会自己配置好虚拟内存，当然也有很多没有配置虚拟内存的，虚拟内存可以让我们的低配服务器使用更多的内存，可以减少很多硬件成本，比如我们运行很多服务的时候，内存常常会满，当配置了虚拟内存...
- 国产深度(deepin)操作系统优化指南
-
1.升级内核随着deepin版本的更新，会自动升级系统内核，但是我们依旧可以通过命令行手动升级内核，以获取更好的性能和更多的硬件支持。具体操作：-添加PPAs使用以下命令添加PPAs：```...
- postgresql-15.4 多节点主从(读写分离)
-
1、下载软件[root@TX-CN-PostgreSQL01-252software]#wgethttps://ftp.postgresql.org/pub/source/v15.4/postg...
- Docker 容器 Java 服务内存与 GC 优化实施方案
-
一、设置Docker容器内存限制（生产环境建议）1.查看宿主机可用内存bashfree-h#示例输出（假设宿主机剩余16GB可用内存）#Mem:64G...
- 虚拟内存设置、解决linux内存不够问题
-
虚拟内存设置（解决linux内存不够情况）背景介绍 Memory指机器物理内存，读写速度低于CPU一个量级，但是高于磁盘不止一个量级。所以，程序和数据如果在内存的话，会有非常快的读写速度。但是，内存...
- Elasticsearch性能调优(5):服务器配置选择
-
在选择elasticsearch服务器时，要尽可能地选择与当前业务量相匹配的服务器。如果服务器配置太低，则意味着需要更多的节点来满足需求，一个集群的节点太多时会增加集群管理的成本。如果服务器配置太高，...
- Es如何落地
-
一、配置准备节点类型CPU内存硬盘网络机器数操作系统data节点16C64G2000G本地SSD所有es同一可用区3(ecs)Centos7master节点2C8G200G云SSD所有es同一可用区...
- 针对Linux内存管理知识学习总结
-
现在的服务器大部分都是运行在Linux上面的，所以，作为一个程序员有必要简单地了解一下系统是如何运行的。对于内存部分需要知道：地址映射内存管理的方式缺页异常先来看一些基本的知识，在进程看来，内存分为内...
- MySQL进阶之性能优化
-
概述MySQL的性能优化，包括了服务器硬件优化、操作系统的优化、MySQL数据库配置优化、数据库表设计的优化、SQL语句优化等5个方面的优化。在进行优化之前，需要先掌握性能分析的思路和方法，找出问题，...
- Linux Cgroups(Control Groups)原理
-
LinuxCgroups（ControlGroups）是内核提供的资源分配、限制和监控机制，通过层级化进程分组实现资源的精细化控制。以下从核心原理、操作示例和版本演进三方面详细分析：一、核心原理与...
- linux 常用性能优化参数及理解
-
1.优化内核相关参数配置文件/etc/sysctl.conf配置方法直接将参数添加进文件每条一行.sysctl-a可以查看默认配置sysctl-p执行并检测是否有错误例如设置错了参数:[roo...
- 如何在 Linux 中使用 Sysctl 命令?
-
sysctl是一个用于配置和查询Linux内核参数的命令行工具。它通过与/proc/sys虚拟文件系统交互，允许用户在运行时动态修改内核参数。这些参数控制着系统的各种行为，包括网络设置、文件...
欢迎 你 发表评论:
- 一周热门
- 最近发表
- 标签列表
-
- linux 查询端口号 (58)
- docker映射容器目录到宿主机 (66)
- 杀端口 (60)
- yum更换阿里源 (62)
- internet explorer 增强的安全配置已启用 (65)
- linux自动挂载 (56)
- 禁用selinux (55)
- sysv-rc-conf (69)
- ubuntu防火墙状态查看 (64)
- windows server 2022激活密钥 (56)
- 无法与服务器建立安全连接是什么意思 (74)
- 443/80端口被占用怎么解决 (56)
- ping无法访问目标主机怎么解决 (58)
- fdatasync (59)
- 405 not allowed (56)
- 免备案虚拟主机zxhost (55)
- linux根据pid查看进程 (60)
- dhcp工具 (62)
- mysql 1045 (57)
- 宝塔远程工具 (56)
- ssh服务器拒绝了密码 请再试一次 (56)
- ubuntu卸载docker (56)
- linux查看nginx状态 (63)
- tomcat 乱码 (76)
- 2008r2激活序列号 (65)