目录
一、开启并binlog日志并配置
1、配置文件
2、参数说明
log_bin
log_bin_basename
binlog_error_action
binlog_format
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的日志写入格式。有三个选项可选:
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日志。