背景
- 之前都是使用mysqldump全量备份,后来数据量大了,一个是磁盘空间不足,第二是占用cpu资源严重;
- 使用binlog之后,执行过程都被实时记录到了日志文件中,只需要维护一次全量备份,基于此,再加上执行记录,就可以恢复到自己想要的位置或时间节点;
查询binlog相关
-- 查看当前数据库是否开启binlog
show variables like '%log_bin%';
-- 查看当前数据库binary log的位置
show master status;
-- 查看binlog日志文件有哪些
show binary logs;
-- 查看具体日志文件
show binlog events in 'mysql-bin.000001';
修改配置文件开启log_bin:
[mysqld]
#binlog 设置
log-bin=/var/lib/mysql/mysql-bin
server_id=1
max_binlog_size=100M # 默认1G
# binlog模式的选择,可参考链接:http://blog.itpub.net/20892230/viewspace-2129567/
binlog_format="ROW"
binlog_rows_query_log_events =1
binlog_row_image='minimal' # 默认是full,记录全部字段, minimal表示只记录更改字段的值和where字段的值
修改好后重启msyql
增量备份
生成新的mysql-bin.00000*文件:
# 登陆了msysql直接执行:
flush logs;
# Linux中直接执行
mysqladmin -uroot -p123456 flush-logs
# docker中:
docker exec -it mysql5.7 mysqladmin -uroot -p123456 flush-logs
清空所有binlog日志命令:
-- 日志文件也会被直接清理掉,重新从mysql-bin.000001开始记录日志
reset master;
通过mysqlbinlog 命令查看日志文件
直接查看
mysqlbinlog --no-defaults ./mysql-bin.000002
解码查看sql执行记录:
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
查看该文件的开始记录时间与结束时间:
# 查看开始时间
mysqlbinlog --no-defaults ./mysql-bin.000011 | grep "Start: binlog"
# 查看结束时间
mysqlbinlog --no-defaults ./mysql-bin.000011 | grep "Rotate to "
如何恢复
- 导入全量备份的数据,然后基于这个数据,通过log_bin日志,按生成顺序,一个个日志文件执行一遍即可;
-- 恢复到 mysql-bin.000001 的数据,指定库名 log(也可以不指定数据库名,全部恢复)
mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p123456 -v log;
-- 指定pos结束点恢复(部分恢复)
mysqlbinlog --no-defaults --stop-position=435 --database= log /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p密码 -v log
-- 指定pos开始点与结束点恢复(部分恢复)
mysqlbinlog --no-defaults --start-position=573 --stop-position=718 --database= log /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p密码 -v log
-- 通过时间来恢复,(指定截止时间,或者指定开始与结束时间)
mysqlbinlog --no-defaults --start-datetime="2020-9-25 15:05:08" --stop-datetime="2020-9-25 16:25:00" --database= log /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p密码 -v log
-- 将mysql-bin.000001日志文件内的 log 数据库日志,事件点 718 之前的日志,输出到 log718.sql
-- 而且解析输出到sql之后,也是可以通过 source **.sql 来执行导入的;
mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001 -d log --skip-gtids --stop-position=718 > log718.sql
--当报此错时:mysqlbinlog: mysql: [ERROR] unknown variable 'default-character-set=utf8mb4'
--加上这个参数就好了:--no-defaults
我的备份方案:
1、确认开启binlog配置后,reset master从新开始记录日志;
2、每天定时 flush logs一次;并及时备份好 msql-bin.00**文件;这一步不做也行;
3、每周全量备份一次,注意:先flush logs,然后执行全量备份;
- 注意注意:一定要小心 reset master命令,一旦执行,日志文件将会全部丢失,重新开始记录后面的执行日志了!!!