Mysql使用binlog增量备份与恢复

背景

• 之前都是使用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命令，一旦执行，日志文件将会全部丢失，重新开始记录后面的执行日志了！！！