mysql开启binlog并配置定期删除以及文件大小，删库跑路之数据恢复

Promise_J_Z

已于 2023-07-18 16:24:20 修改

阅读量1.4k

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

于 2023-07-17 23:58:11 首次发布

本文链接：https://blog.csdn.net/Promise_J_Z/article/details/131774760

版权

Mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

binlog_do_db和binlog_ignore_db

sync_binlog

二、通过binglog日志还原

三、数据库备份并自动刷新binlog日志

一、开启并binlog日志并配置

1、配置文件

[mysqld]
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=C:\\JJS\\software\\mysql-5.7.41-winx64\\mysql-5.7.41-winx64
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 选择binlog日志模式
binlog_format = mixed
# 开启binlog日志
log_bin =C:\\JJS\\software\\mysql-5.7.41-winx64\\jjs_binlog
# 日志最长保存七天
expire_logs_days = 7
# binglog日志单个文件最大512m
max_binlog_size = 512m
# 缓冲区内存大小
binlog_cache_size = 4m
# 最大缓冲区内存大小
max_binlog_cache_size = 1024m
# 不指定定义binlog日志同步到硬盘的规则。取值范围为0~N
sync_binlog=0
# binlog遇到错误的情况处理方式
binlog_error_action=ABORT_SERVER

server-id=1

2、参数说明

log_bin

log_bin=ON # 是否激活二进制日志

log_bin_basename

指定了binlog的基础命名和存储路径也可以通过–log-bin(如上面贴的配置)设置。也就是说像上面的配置binlog文件会存在C:\\JJS\\software\\mysql-5.7.41-winx64目录下，文件命名方式为C:\\JJS\\software\\mysql-5.7.41-winx64\\jjs_binlog.0000xx

binlog_error_action

当发生不可以写、刷或同步binary log的时候，可能会导致数据一致性遭到破坏或者复制操作停止，这时候服务器要做什么操作。有两个选项ABORT_SERVER 服务关闭（默认值）, IGNORE_ERROR 忽略错误。

binlog_format

定义binlog的日志写入格式。有三个选项可选：

STATEMENT
每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行。
优点：不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高新能；缺点：容易出现主从复制不一致
ROW
日志中会记录每一行数据被修改后的情况，然后在slave端对相同的数据进行修改。
优点：能清楚的记录每一行数据修改的细节；
缺点：数据量太大
MIXED
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
这种模式会导致binglog可读性变差。

binlog_do_db和binlog_ignore_db

binlog_do_db = db1 # 此参数表示只记录指定数据库的二进制日志，默认全部记录
binlog_ignore_db = db2# 此参数表示不记录指定的数据库的二进制日志

sync_binlog

定义binlog日志同步到硬盘的规则。取值范围为0~N

0
当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让操作系统的Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。这个是性能最好的
1或者N
当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

二、通过binglog日志还原

前言，binlog日志是以事件的形式写入二进制文件的，所以在还原数据库之前应该要正确找到开始pos和结束的pos

先介绍几个相关指令（均为mysql指令）

查看第一个日志文件： show binlog events;

查看最后一个日志和结束点： show master status;

查看指定的日志文件： show binlog events in 'xxx';

1、数据创建并查找起止位置：

通过inser语句插入几十条记录

此时binlog日志已经生成了记录

我们可以使用指令查看日志事件内容

show binlog events in 'jjs_binlog.000002'

可以看到开始位置是 4

结束位置是7924

为了演示多个binlog还原数据库的情景，这里手动刷新了一次binlog日志，输入指令

Flush logs

会立即看到产生了一个新的binlog日志

继续使用insert语句插入多条数据

使用指令查询时间起止位置

show binlog events in 'jjs_binlog.000003'

可以看到开始位置是4

结束位置是5770

这时候使用delete语句，一下清空所有的数据

在使用语句查询终止位置

show binlog events in 'jjs_binlog.000003'

可以看红框一的终止位置5770是最后一条插入语句的终止位置，而从5770往后就是delete语句，可想而知，我们本次需要执行binlog文件的范围是jjs_binlog.000002 的4 至jjs_binlog.000003 的5770

2、开始还原

方式一：挨个binlog日志还原（注意，以下的语句不是sql，不是在数据库内执行，而是cmd指令）

mysqlbinlog --start-position=4 --stop-position=7924 --database=textdb  jjs_binlog.000002  | mysql -uroot -proot

mysqlbinlog --start-position=4 --stop-position=5770 --database=textdb  jjs_binlog.000003  | mysql -uroot -proot

执行完毕之后可以看到数据库恢复到之前的记录

方式二：一次还原多个文件

mysqlbinlog --no-defaults --start-position=4 --database=textdb jjs_binlog.000002 | mysql -uroot -proot -f
mysqlbinlog --no-defaults --stop-position=5770 --database=textdb jjs_binlog.000003 | mysql -uroot -proot -f

如果设置到2个以上的binlog日志，还可以开头只需要写开始位置，中间不需要表明位置，表明整个binlog日志，然后结束的时候标明结束位置

mysqlbinlog --no-defaults --start-position=4 --database=textdb jjs_binlog.000002 | mysql -uroot -proot -f

mysqlbinlog --no-defaults --database=textdb jjs_binlog.000003 | mysql -uroot -proot -f

mysqlbinlog --no-defaults --stop-position=5770 --database=textdb jjs_binlog.000004 | mysql -uroot -proot -f

注意：在不能精确把握开始位置的情况下，可以适当将开始位置往前调，因为如果是遇到update和delete语句，并不会影响整体的数据结果，如果遇到的是insert语句，则会报错，但是不会终止数据还原，一直到我们需要还原的位置即可正常开始还原数据

三、数据库备份并自动刷新binlog日志

要想完整的还原数据库，那么数据库备份和binlog日志必须要协调起来。在数据库备份时就要刷新一下binlog日志，这样子以后还原数据库的时候可以先用备份还原数据库，然后通过备份时间找到binlog日志，在通过binlog日志还原从备份到现在丢失的数据。

下面这段代码为数据库备份的bat指令，只需要配合定时任务即可实现定时备份数据库，

rem ******Start backing up the database. After the database backup is completed, the window will automatically close******
@echo off

::删除一周前的备份数据

forfiles /p "D:\mysql-5.7.33-winx64" /m backup_*.sql -d -7 /c "cmd /c del /f @path"

::设置时间变量

set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%"

::进入mysql安装目录的bin目录下

cd D:\mysql-5.7.33-winx64\bin

::执行备份操作

mysqldump --opt --single-transaction=TRUE --user=root --password=root --flush-logs --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events "textdb" > D:\mysql-5.7.33-winx64\bin\backup_%Ymd%.sql

@echo on

rem ******Database backup completed******

mysqldump --opt --single-transaction=TRUE --user=root --password=root --flush-logs --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events "sdey" > D:\mysql-5.7.33-winx64\bin\backup_%Ymd%.sql

--flush-logs的作用就是在备份数据库时刷新binlog日志。