MySQL 增量备份与恢复

article/2025/8/21 11:38:03

目录

  • 引言
  • 一、MySQL 增量备份
    • 1. 增量备份特点
    • 2. 示例
  • 二、MySQL 增量恢复
    • 1. 增量恢复的场景
    • 2. 丢失完全备份之后更改的数据的恢复
    • 3. 完全备份之后丢失所有数据
    • 4. 基于时间点与位置的恢复
      • 4.1 基于时间点的恢复
      • 4.2 基于位置的恢复
    • 5. 指定企业备份策略的思路
  • 总结

引言

完全备份是把所有的数据内容进行备份，备份数据中有大量的重复数据，并且完全备份的时间与恢复的时间很长。解决完全备份存在的问题就是使用增量备份的方式，增量备份就是备份自上一次备份之后增加或改变的文件或内容。

一、MySQL 增量备份

1. 增量备份特点

  • 增量备份的优点是没有重复数据，备份量不大，时间短；缺点也很明显，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且对所有增量备份进行逐个反推恢复，操作较为繁锁

  • MySQL 没有提供直接的增量备份方法，但是可以通过 MySQL 的二进制日志（binarylogs) 间接实现增量备份

  • 二进制日志对备份的意义如下：

① 二进制日志保存了所有更新或者可能更新数据库的操作
② 二进制日志在启动 MySQL 服务器后开始记录，并在文件达到max_binlog_size 所设置的大小或者接收到 flush logs 命令后重新创建新的日志文件
③ 只需要定时执行 flushlogs 方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

2. 示例

  • ① 开启二进制日志功能
[root@mysql ~]#vim /etc/my.cnflog-bin=mysql-bin									#开启二进制日志文件，以mysql-bin命名								
binlog_format = MIXED								#指定二进制日志(binlog)的记录格式为 MIXED[root@mysql ~]#systemctl restart mysqld.service 
#查看二进制日志文件
[root@mysql /usr/local/mysql/data]#ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 154 10月 26 14:53 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql  19 10月 26 14:53 /usr/local/mysql/data/mysql-bin.index

二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT
(1) STATEMENT（基于SQL语句）:
每一条涉及到被修改的sql 都会记录在binlog中
缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题
总结：增删改查通过sql语句来实现记录，如果用高并发可能会出错，可能时间差异或者延迟，可能不是我们想想的恢复可能你先删除或者在修改，可能会倒过来。准确率低

(2) ROW(基于行)
只记录变动的记录，不记录sql的上下文环境
缺点:如果遇到update…set…where true 那么binlog的数据量会越来越大
总结：update、delete以多行数据起作用，来用行记录下来，只记录变动的记录，不记录sql的上下文环境，比如sql语句记录一行，但是ROW就可能记录10行，但是准确性高，高并发的时候由于操作量，性能变低比较大所以记录都记下来

(3) MIXED 推荐使用一般的语句使用statement，函数使用ROW方式存储。

② 每周选择服务器负载较轻的时间段，或者用户访问较少的时间段进行备份

#对表进行完全备份
mysqldump -uroot -p123123 SCHOOL CLASS01 > /opt/SCHOOL_CLASS01_$(date +%F).sql#对库进行完全备份
mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql#用计划性任务执行
crontab -e
30 1 * * 3 mysqldump -uroot -p123123 SCHOOL CLASS01 > /opt/SCHOOL_CLASS01_$(date +%F).sql
30 1 * * 3 mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql
#每周三的凌晨 1:30 对数据库和表进行完全备份

③ 可每天进行增量备份操作，生成新的二进制日志文件

ls /usr/local/mysql/data
mysqladmin -uroot -p123456 flush-logs

④ 插入新的数据，以模拟数据的增加或变更

mysql> insert into IT_member values('运维工程师','张三',22,'8','本科',7,20000);
Query OK, 1 row affected (0.01 sec)mysql> select * from IT_member;
+--------------------+-----------+--------+----------+--------+--------+----------+
| 岗位               | 姓名      | 年龄   | 员工ID   | 学历   | 年限   | 薪资     |
+--------------------+-----------+--------+----------+--------+--------+----------+
| 云计算工程师       | 顾磊      |     18 |        1 | 本科   |      6 | 18888.00 |
| 云计算工程师       | 崔配文    |     19 |        2 | 本科   |      7 | 19999.00 |
| 运维工程师         | 张三      |     22 |        8 | 本科   |      7 | 20000.00 |
+--------------------+-----------+--------+----------+--------+--------+----------+
3 rows in set (0.00 sec)

⑤ 生成新的二进制文件并查看其内容

cd /usr/local/mysql/data/
mysqladmin -uroot -p123456 flush-logs

在这里插入图片描述

注意：之前的步骤④的数据库操作会保存到mysql-bin.000002文件中，之后我们测试删除库的操作会保存在mysql-bin.000003文件中 （以免当我们基于mysql-bin.000002日志进行恢复时，依然会删除库）

#将记录变更的二进制文件02复制至/opt目录下
cp mysql-bin.000002 /opt/cd /opt/
#使用64位编码机制去解码，按行读取详细内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

在这里插入图片描述

二、MySQL 增量恢复

1. 增量恢复的场景

当数据发送错误时，应根据实际情况选择使用完全备份恢复，还是增量备份恢复

增量备份的场景：
① 人为的 SQL 语句破坏了数据库
② 在进行下一次全备之前发送系统故障导致数据库数据丢失
③ 在主从架构中，主库数据发送了故障

根据数据丢失的情况可以分为两类：
① 只丢失了完全备份之后更改的数据
② 完全备份之后丢失所有的数据

2. 丢失完全备份之后更改的数据的恢复

#添加表内容
mysql> insert into gl values ('wangliu',4);
mysql> insert into gl values ('wangw',5);#截取日志文件
mysqladmin -u root -p123456 flush-logs
[root@mysql /usr/local/mysql/data]#ls							#生成03，添加数据的操作保存在02中
mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  ......#删除插入的两条数据，模拟完全备份后数据丢失的故障
mysql> delete from gl where id=4;
mysql> delete from gl where id=5;#使用二进制文件进行恢复操作
mysqlbinlog --no-defaults mysql-bin.000002 | mysql -uroot -p

在这里插入图片描述

3. 完全备份之后丢失所有数据

#创建新的数据库和数据表
mysql> create database class;
mysql> use class;
mysql> create table gl(name char(8),ID int not null,primary key (ID));
mysql> insert into gl values('zhangsan','1');
......
mysql> insert into gl values('laal','6');
mysql> select * from gl where id;
+----------+----+
| name     | ID |
+----------+----+
| zhangsan |  1 |
| wangwu   |  2 |
| wangliu  |  3 |
| lisi     |  4 |
| wb       |  5 |
| laal     |  6 |
+----------+----+#完全备份
mysqldump -uroot -p123456 class gl > /opt/class_gl_$(date +%F).sql#刷新日志
mysqladmin -u root -p flush-logs#继续在数据库表中添加信息
insert into gl values('ssa',7);
insert into gl values('sa',8);
mysql> select * from gl;
+----------+----+
| name     | ID |
+----------+----+
| zhangsan |  1 |
| wangwu   |  2 |
| wangliu  |  3 |
| lisi     |  4 |
| wb       |  5 |
| laal     |  6 |
| ssa      |  7 |
| sa       |  8 |
+----------+----+#删除库class，模拟故障
mysql> drop database class;#基于000001的全量备份进行还原数据，依次还原000002、000003，并验证结果
mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p
mysqlbinlog --no-defaults mysql-bin.000002 | mysql -uroot -p
mysqlbinlog --no-defaults mysql-bin.000003 | mysql -uroot -p

在这里插入图片描述

4. 基于时间点与位置的恢复

利用二进制日志可实现基于时间点与位置的恢复，例如由于误操作删除了一张表，这时完全恢复是没有用的，因为日志里还有误操作的语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作的语句，再恢复后面操作的语句

4.1 基于时间点的恢复

基于时间点的恢复，就是将某个起始时间的二进制文件导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复
使用 mysqlbinlog 加上–stop-datetime 选项，表示在哪个时间点结束，后面误操作的语句不执行
–start-datetime 选项表示执行后面的语句
结合使用它们就可以跳过误操作的语句，完成恢复工作
需要注意的是，二进制文件中保存的日期格式需要调整为用“-”分割

#恢复用户“lier”的数据
mysql -uroot -p123456 -e "truncate table class.gl;"				#清空表数据
mysql -uroot -p123123 -e "select * from class.gl;"mysqlbinlog --no-defaults --stop-datetime='2021-10-27 0:39:20' mysql-bin.000002 |mysql -uroot -p123456mysql -uroot -p123456 -e "select * from class.gl;"

在这里插入图片描述

在这里插入图片描述

4.2 基于位置的恢复

基于位置的恢复，就是使用基于时间点的恢复。可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，基于位置是一种更为精确的恢复方式。

#使用64位编码机制解码并按行读取二进制文件02（增量备份）的详细内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001

在这里插入图片描述

#仅恢复“778”之前的数据，也就是不恢复“lier”的数据
mysql -uroot -p123456 -e "select * from class.gl;"
mysql -uroot -p123456 -e "truncate table class.gl;"
mysql -uroot -p123456 -e "select * from class.gl;"
mysqlbinlog --no-defaults --stop-position='778' mysql-bin.000001 | mysql -uroot -p123456
mysql -uroot -p123456 -e "select * from class.gl;"

#仅恢复“lier”的数据
mysql -uroot -p123456 -e "select * from class.gl;"
mysqlbinlog --no-defaults --start-position='955' mysql-bin.000001 | mysql -uroot -p123456
mysql -uroot -p123456-e "select * from class.gl;"

5. 指定企业备份策略的思路

  • 指定企业备份策略要根据企业数据库的实际读写的频繁性与数据的重要性进行
  • 数据更新频繁，则应该进行较为频繁的备份
  • 数据较为重要，则在有适当更新时进行备份
  • 在数据库压力小的时段进行全备，如一周一次，然后每天增备
  • 根据公司的规模，中小公司可一天一次全备，大公司可每周一次全备，每天进行一次增备，并且尽量为企业实现主从复制架构

总结

  • 使用分割日志的方式进行增量备份
  • 增量备份需要根据日志文件的时间先后逐个执行
  • 使用基于时间和位置的方式进行恢复可以更精确的恢复数据

http://chatgpt.dhexx.cn/article/Ng2SX3xD.shtml

相关文章

增量备份恢复

增量备份恢复

** 增量备份恢复案例 **1、配置mysql，设置日志文件 [rootlocalhost bak]# vim /etc/my.cnf 添加： log-binmysql-bin2、重启mysqld服务 systemctl restart mysqld查看日志文件： mysqlbinlog /usr/local/mysql/data/mysql-bin.0000013、创…
阅读更多...
mysql实现增量备份

mysql实现增量备份

有点要注意 如果你误删了表 想通过这个恢复 必须恢复日志里面有创建表的日志 不然的话是无法回复的 就是必须是从你开始创建表的时候就已经记录日志了 恢复到哪个位置 就按照哪个位置来计算 mysql 5.0不支持增量备份 增量备份定义 mysql数据库会以二进制形式 自动把用户对my…
阅读更多...
什么是全量备份,增量备份,差异备份?

什么是全量备份,增量备份,差异备份?

背景 今天我司服务器工程大牛看我在备份数据,冷不丁提到了差异备份;但是才疏学浅的我却不知何为差异备份,故而以此为引,开始了对全量备份,增量备份,差异备份这三者的研习;经过一番寻觅,最终找到了他们.呵呵 希望大家有所受益! 问题 1. 什么是全量备份？ 2. 什么是增量…
阅读更多...
MySQL完全备份和增量备份

MySQL完全备份和增量备份

MySQL日治管理、数据库备份与恢复 备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。而备份、恢复中，日志起到了很重要的作用 一、日志 MySQL的日志默认保存位置为 /usr/local/mysql/data 1.日志类型与作用 ①re…
阅读更多...
MySQL 数据库备份（增量备份与恢复）

MySQL 数据库备份（增量备份与恢复）

目录 一、MySQL 增量备份 1.增量备份的概念 1.1 为什么使用增量备份 1.2 增量备份的特点 2.增量备份示例 二、MySQL 增量恢复 1.增量恢复的场景 2.丢失完全备份之后更改的数据的恢复步骤 3.完全备份之后丢失所有数据的恢复步骤 4. 基于时间点与位置的恢复 4.1 基于时间…
阅读更多...
mysql数据的备份与恢复

mysql数据的备份与恢复

mysql数据的备份与恢复 日志的类型与作用：开启日志方式查看日志是否开启 数据备份的重要性：备份类型：如何选择逻辑备份策略或频率？常见的备份方法：MysQL完全备份优缺点：数据库完全备份分类： 实验…
阅读更多...
完全备份 、差异备份、增量备份的区别和特点

完全备份 、差异备份、增量备份的区别和特点

数据备份的方式有完全备份、差异备份以及增量备份，那么这三种备份方式有什么区别，在具体应用中又该如何选择呢？本文就这些问题作以介绍。 一、备份方式简介 1、完全备份（Full Backup） 备份全部选中的文件夹&#xf…
阅读更多...
MySQL 数据库备份（二）（增量备份与恢复）

MySQL 数据库备份（二）（增量备份与恢复）

文章目录 一、MySQL 增量备份1.增量备份的概念1.1 为什么使用增量备份1.2 增量备份的特点 2.增量备份示例 二、MySQL 增量恢复1.增量恢复的场景2.丢失完全备份之后更改的数据的恢复步骤3.完全备份之后丢失所有数据的恢复步骤4. 基于时间点与位置的恢复4.1 基于时间点的恢复4.1 …
阅读更多...
增量备份

增量备份

增量备份，是指在完整备份的基础上，每次只是备份自上次备份以来被修改过的数据块。增量备份策略不能用于映像备份，因为映像备份总是对于所有的数据文件。增量备份可以是数据库、数据文件和表空间三个级别。 一、增量备份的种类 第一、累积增…
阅读更多...
PC端微信打不开小程序解决

PC端微信打不开小程序解决

PC端微信点击小程序之后没有啥反应，可以使用下面的方法解决：右键桌面的微信快捷方式，属性-兼容性，勾选上以兼容模式运行这个程序即可
阅读更多...
微信外部链接无法使用浏览器打开 微信链接打不开的解决方案

微信外部链接无法使用浏览器打开 微信链接打不开的解决方案

域名在微信中打不开，有诸多问题 1、硬件原因，比如手机网络、手机缓存的手机硬件设备等 这里就不讨论硬件方便的问题 2、其他原因 目前的APP基本都支持二维码扫描下载，二维码下载也成为了大家用起来很顺手的一种方式。由于微信的用户基本占据…
阅读更多...
微信中已停止访问该网页、据用户投诉及腾讯手机管家云网址检测、该网页包含不安全内容。为维护绿色上网环境，已停止访问的解决方案

微信中已停止访问该网页、据用户投诉及腾讯手机管家云网址检测、该网页包含不安全内容。为维护绿色上网环境，已停止访问的解决方案

不管是网站的首页，还是产品的页面地址，以及在线支付的地址，都有可能会被微信提示：已停止访问该网页，据用户投诉及腾讯网址安全中心检测，该网页包含违法或违规内容。为维护绿色上网环境,已停止访问。有的页面…
阅读更多...
微信内置浏览器打不开链接怎么办，实现微信跳转链接

微信内置浏览器打不开链接怎么办，实现微信跳转链接

在我们使用微信营销的时候，很容易碰到推广连接在微信内无法打开或无法下载app的情况。通常这种情况微信会给个提示 “已停止访问该网址” ，那么导致这个情况的因素有哪些呢，主要有以下三点 1、网页链接被举报次数过多。2、网页含违规内容，含敏感词。3、被腾讯检测系统判断为…
阅读更多...
微信小程序打开外部链接

微信小程序打开外部链接

如果是个人类型的小程序就没法打开外部链接了。 首先要了解下微信提供的容器web-view。 web-view | 微信开放文档 (qq.com) 具体的操作步骤这样，首先在WXML文件里某个需要触发点击事件的标签里面加上点击事件。记得带上参数data-url"{{item.url}}" 在J…
阅读更多...
浏览器突然无法打开微信链接解决办法

浏览器突然无法打开微信链接解决办法

win10更新版本后，不知道为什么用微信的时候不能直接在chrome里打开公众号文章了，然后在chrome里点击prt scn（截屏键）也没有反应（原本是要弹出个框框让选择截图方式的），重启电脑无效，…
阅读更多...
企业微信PC端打不开h5页面

企业微信PC端打不开h5页面

这个问题是个大坑，h5页面在手机上能打开，但是在pc端不行。从https到http，甚至怀疑是vue的问题，然而都没解决。最终，尝试了文档中的这句话： 我之前引入的版本是1.3.2，换成1.2.0，立即…
阅读更多...
网页版微信为什么打不开连接服务器失败,电脑能上微信，但是打不开网页怎么回事？...

网页版微信为什么打不开连接服务器失败,电脑能上微信，但是打不开网页怎么回事？...

打不开网页的原因： 路由器的问题； DNS错误； 域名服务器不正确； 路由器资源不够； 网络出现丢包导致； 手机网络连接不稳定； 软件版本没有更新； 应用程序管理器出现问题； 数…
阅读更多...
微信打不开MP4文件 （记录编码问题）

微信打不开MP4文件 （记录编码问题）

文章目录 起因是一大早上我兄弟给我打电话说家里的粮食被偷了，但是监控视频格式只能qq打开微信打不开，需要把视频发给警察。 一、编码错误二、解决2.1、简单的就是拿格式工厂走一下就可以了2.2、找到一个优秀的老哥写的转码的代码 参考文章 起因是一大早…
阅读更多...
js实现微信打开下载链接

js实现微信打开下载链接

微信如何唤起外部浏览器打开指定链接 需求分析 将打包好的apk/ios文件部署到服务器，把下载页面的URL通过二维码编辑器或根据URL代码生成一个二维码，然后通过二维码进行微信推广已经成为很多用户惯用的方式。但微信会对含apk/ios文件的链接进行了屏蔽&…
阅读更多...
微信打不开链接怎么办，详谈微信跳转外部浏览器的实现原理

微信打不开链接怎么办，详谈微信跳转外部浏览器的实现原理

很多朋友并不知道微信浏览器会屏蔽拦截第三方分享网页，所以经常都是直接在微信内转发分享，最终的结果就是导致原链接被微信屏蔽，通常这种情况微信会给个提示 “已停止访问该网址” ，出现这个提示的一般会在以下几种情况下发生 情况1：分享转发H5网页，但会因为他人举报或微…
阅读更多...
推荐文章

Copyright @ 2022~2023