Mysql使用binlog增量备份与恢复

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: 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命令,一旦执行,日志文件将会全部丢失,重新开始记录后面的执行日志了!!!
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 关系型数据库 MySQL
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
62 4
|
2月前
|
canal 消息中间件 关系型数据库
Canal作为一款高效、可靠的数据同步工具,凭借其基于MySQL binlog的增量同步机制,在数据同步领域展现了强大的应用价值
【9月更文挑战第1天】Canal作为一款高效、可靠的数据同步工具,凭借其基于MySQL binlog的增量同步机制,在数据同步领域展现了强大的应用价值
481 4
|
24天前
|
安全 关系型数据库 MySQL
MySQL用户备份
【10月更文挑战第2天】MySQL用户备份
46 3
|
24天前
|
存储 关系型数据库 MySQL
MySQL如何备份?
【10月更文挑战第2天】MySQL如何备份?
54 3
|
2月前
|
消息中间件 canal 关系型数据库
Maxwell:binlog 解析器,轻松同步 MySQL 数据
Maxwell:binlog 解析器,轻松同步 MySQL 数据
209 11
|
9天前
|
存储 关系型数据库 MySQL
MySQL中的Redo Log、Undo Log和Binlog:深入解析
【10月更文挑战第21天】在数据库管理系统中,日志是保障数据一致性和完整性的关键机制。MySQL作为一种广泛使用的关系型数据库管理系统,提供了多种日志类型来满足不同的需求。本文将详细介绍MySQL中的Redo Log、Undo Log和Binlog,从背景、业务场景、功能、底层实现原理、使用措施等方面进行详细分析,并通过Java代码示例展示如何与这些日志进行交互。
11 0
|
3月前
|
SQL 关系型数据库 MySQL
【揭秘】MySQL binlog日志与GTID:如何让数据库备份恢复变得轻松简单?
【8月更文挑战第22天】MySQL的binlog日志记录数据变更,用于恢复、复制和点恢复;GTID为每笔事务分配唯一ID,简化复制和恢复流程。开启binlog和GTID后,可通过`mysqldump`进行逻辑备份,包含binlog位置信息,或用`xtrabackup`做物理备份。恢复时,使用`mysql`命令执行备份文件,或通过`innobackupex`恢复物理备份。GTID模式下的主从复制配置更简便。
285 2
|
3月前
|
SQL 关系型数据库 MySQL
【MySQL】根据binlog日志获取回滚sql的一个开发思路
【MySQL】根据binlog日志获取回滚sql的一个开发思路
|
3月前
|
关系型数据库 MySQL Shell
MySQL回滚脚本: 误操作delete binlog回滚shell脚本
MySQL回滚脚本: 误操作delete binlog回滚shell脚本
|
3月前
|
关系型数据库 MySQL 数据库
MySQL回滚工具:binlog 闪回工具 MyFlash工具
MySQL回滚工具:binlog 闪回工具 MyFlash工具