MySQL 备份方案

news/2024/4/16 15:40:32/文章来源:https://blog.csdn.net/zhengzhaoyang122/article/details/136520252

在这里插入图片描述

优质博文:IT-BLOG-CN

一、为什么要备份

【1】容灾恢复:硬件故障、不经意的 Bug 导致数据损坏,或者服务器及其数据由于某些原因不可获取或无法使用等(例如:机房大楼烧毁,恶意的黑客攻击或 Mysql 的 Bug 等)。
【2】人们改变想法:很多人经常会在删除某些数据后,又想恢复这些数据。
【3】审计:有时需要知道数据或 Schema 在过去的某个时间点的状态和数据,或发现了应用的一个 Bug,想知道在此之前发生了什么等等。
【4】测试:定期使用生产数据来更新测试服务器(备份是为了恢复,这样也可以验证备份的数据是否完整,是否能够正常还原等)。如果备份的方案非常简单,只需要将备份文件还原到测试服务器上即可。

规划备份和恢复策略时,有两个重要的需求:恢复点目标(RPO)定义了可以容忍丢失多少数据,恢复时间目标(RTO)定义了需要等待多久将数据恢复。

二、在线备份还是离线备份

如果可以,关闭 MySQL 做备份是最简单安全的,也是获取一致性最好的方法,而且损坏和不一致的风险最小。根本不用关心 InnoDB 缓冲池中的脏页或其他缓存。也不需要担心数据在尝试备份的过程中被修改,并且因服务器不对应用提供访问,因此会更快的完成备份。

但在高负载和高数据量下关闭和重启 MySQL 可能要花很长一段时间,即使最小化停顿时间,服务器停机的代价也会很昂贵。因此需要在线备份。即便如此,由于一致性的需要,对服务器进行在线备份,仍然会有明显的服务中断。MyISAM 在线备份的一个最大问题是使用 FLUSH TABLES WITH READ LOCK(关闭所有打开的表并使用全局读锁锁定所有数据库的所有表,不会刷新脏块) 操作,这会导致 MySQL 关闭并锁住所有的表,将 MyISAM 的数据文件刷新到磁盘上,并且刷新查询缓存。该操作需要非常长的时间,具体需要多久不可预估。因为如果一个会话中使用表锁(LOCK TABLES tbl_name lock_type)语句对某表加了表锁,在该表锁未释放前,那么另外一个会话如果执行 FLUSH TABLES WITH READ LOCK 也会被堵塞。除非锁被释放,否则不能在服务器上更改任何数据。 FLUSH TABLES WITH READ LOCK 不像关闭服务器代价那么高,因为大部分缓存仍然在内存中,并且服务器一直是“预热”状态,但是它也有非常大的破坏性。避免使用 FLUSH TABLES WITH READ LOCK 的最好办法是只使用 InnoDB 表。

【在规划备份时,有一些与性能相关的因素需要考虑】:
■ 锁时间:需要持有锁多长时间,例如在备份期间持有的全局 FLUSH TABLES WITH READ LOCK ?
■ 备份时间:复制备份到目的地需要多久。
■ 备份负载:在复制备份到目的地时对服务器性能的影响是多少?
■ 恢复时间:把备份镜像从存储位置复制到 MySQL 服务器,重放二进制日志等,需要多久?

最大的权衡是备份时间与备份负载。可以牺牲其一以增强另外一个。例如,可以降低服务器性能,来提高备份的优先级。

三、逻辑备份还是物理备份

逻辑备份(也叫“导出”)和直接复制原始文件的物理备份。逻辑备份将数据包含在一种 MySQL 能够解析的格式中,要么是 SQL,要么是以某个符号分隔的文本。原始文件是指存在硬盘上的文件。

【1】逻辑备份,优点如下:
 ■ 逻辑备份可以用编辑器或像 grep 和 sed 之类的命令查看和操作的普通文件。当需要恢复数据或只想查看数据但不恢复时,这都非常有帮组。
 ■ 恢复非常简单。可以通过管道把他们输入到 mysql,或者使用 mysqlimport。
 ■ 通过网络来备份和恢复,相当于在与 MySQL 主机不同的另外一台机器上操作。
 ■ 可以在类似 Amazon RDS(关系型数据库服务) 这种不能访问底层文件系统的系统中使用。
 ■ 非常灵活,因为 mysqldump(大部分人都喜欢的工具)可以接受许多选项,例如可以用 WHERE 子句来限制需要备份的数据。
 ■ 与存储引擎无关。因为是从 MySQL 服务器中提取数据而生成,所以消除了底层数据存储的不同。因此可以从 InnoDB 表中备份,然后只需要极小的工作量就可以还原到 MYISAM 表中。而原始数据却不能这么做。
 ■ 有助于避免数据损坏。如果磁盘驱动器有故障需要复制原始文件时,将得到一个错误或部分损坏的备份。如果 MySQL 在内存中的数据还没有损坏,可以得到一个可以信赖的逻辑备份。

【逻辑备份的缺点如下】:
 ■ 必须由数据库服务器完成生成逻辑备份工作,因此要使用更多的 CPU 周期。
 ■ 无法保证导出后再还原出来的一定时同样的数据。浮点表示问题、软件 Bug 等都会导致问题,尽管非常少见。
 ■ 从逻辑备份中还原需要 MySQL 加载和解释语句,转化为存储格式,并重建索引,所有这一切都会很慢。

最大的缺点是从 MySQL 中导出数据和通过 SQL 语句将其加载回去的开销。如果使用逻辑备份,测试恢复需要的时间将非常重要。Percona Server 中包含的 mysqldump 在使用 InnoDB 表时能起到帮组作用,因为它会对输出格式化,以便在重新加载时利用 InnoDB 的快速建索引的优点。我们测试显示这样做可以减少 2/3 甚至更多的还原时间。索引越多,好处越明显。

【2】物理备份,优点如下:
 ■ 基于文件的物理备份,只需要将需要的文件复制目标地址即可完成备份。不需要其他额外的工作来生成原始文件。
 ■ 物理备份的恢复可能更简单,取决于存储引擎。对于 MyISAM 只需要简单的复制文件到目的地即可。对于 InnoDB 则需要停止数据库服务,可能还要采取其他一些步骤。
 ■ InnoDB 和 MyISAM 的物理备份非常容易跨平台、操作系统和 MySQL 版本。
 ■ 从物理备份中恢复会更快,因为不需要执行任何 SQL 和构建索引。如果有很大的 InnoDB 表,无法完全缓存再内存中,则物理备份的恢复要快非常多,只是要快一个数量级。事实上,逻辑备份最可怕的地方就是不确定的还原时间。

【物理备份的缺点如下】:
 ■ InnoDB 的原始文件通常比相应的逻辑备份要大的多。InnoDB 的表空间往往包含很多未使用的空间。还有很多空间被用来做存储以外的用途(插入缓冲,回滚段等)。
 ■ 物理备份不总是可以跨平台、操作系统及 MySQL 版本。文件名大小写敏感和浮点格式是可能会遇到的麻烦。很可能因浮点格式不同而不能移动文件到另一个系统。

物理备份简单高效,但也容易出错。尽管如此对于长期保留的备份,还是很适合的。但尽量不要完全依赖物理备份。至少每隔一段时间还是需要做一次逻辑备份。建议混合使用物理和逻辑两种方式来做备份:先使用物理复制,以此数据启动 MySQL 服务器实例并运行 mysqlcheck。然后周期性地使用 mysqldump 执行逻辑备份。这样做可以获得两种方法的优点,不会使生产服务器在导出时过度负担。如果能够利用文件系统的快照,也可以生成一个快照,将该快照复制到另一个服务器上并释放,然后测试原始文件,再执行逻辑备份。

备份文件一定要经过数据恢复测试,特别是物理备份。不要自认为数据备份是正常的,这样容易酿成大错。对 InnoDB 来说,这意味着需要启动一个 MySQL 实例,执行 InnoDB 恢复操作,然后执行 CHECK TABLES。也可以跳过这一操作,仅对文件运行 innochecksum(是一个用于校验 innodb 表空间文件完整性的工具,是一个官方自带的工具),但不建议这样做。

四、增量备份和差异备份

当数据量很庞大时,一个常见的策略是做定期的增量或差异备份。差异备份是对自上次全量备份后所有改变的部分而做的备份,而增量备份则是从任意类型的上次备份后所有修改做的备份。
::: tip
例如,周日做一个全备份,在周一,对周日以来所有的改变做一个差异备份。在周二,就有两个选择:备份自周日以来所有的改变(差异),或只备份自从周一备份后所有的改变(增量)
:::
两者都是部分备份能够减少服务器开销(不一定:例如,Percona XtraBackup 和 MySQL Enterprise Backup,仍会扫描服务器上的所有数据块,因而并不会节约太多时间的开销)、备份时间以及备份空间。
 ■ 使用 Percona XtraBackup 和 MySQL Enterprise Backup 等软件中的增量备份特性。
 ■ 备份二进制日志。可以在每次备份后使用 FLUSH LOGS 来开始一个新的二进制日志,这样就只需要备份新的二进制日志。
 ■ 不要备份没有改变的表。MyISAM 引擎会记录每个表最后修改时间。可以通过查看磁盘上的文件或运行 SHOW TABLE STATUS 来看这个时间。如果使用 InnoDB 可以利用触发器记录修改时间到一个表中。
 ■ 不要备份没有改变的行。如果一个表只是做插入,可以增加一列(时间戳),然后只备份自上次备份后插入的行。
 ■ 备份所有数据,然后发送到一个有去重特性的目的地,例如 ZFS 文件管理程序。

增量备份的缺点: 增加恢复复杂性,额外的风险,以及更长的恢复时间。如果可以全备,考虑到简便性,尽量经常做全备。建议至少一周一次,周内通过增量的方式进行备份。

五、备份什么

最简单的策略是只备份数据和定义表,但这是一个最低的要求。在生产环境中恢复数据库一般需要更多的工作,例如:
【1】非显著数据: 不要忘记那些容易被忽略的数据,例如:二进制日志和 InnoDB 事务日志等。
【2】代码: MySQL 服务器可以存储许多代码,例如触发器和存储过程。如果备份了 MySQL数据库,大部分这类代码也就备份了,但此时如果还原单个业务数据库会比较麻烦。
【3】复制配置: 如果恢复一个设计复制关系的服务器,应该备份所有与复制相关的文件(二进制日志、中继日志、日志索引文件和.info 文件)。至少应该包含 SHOW MASTER STATUS 和 SHOW SLAVE STATUS 的输出。执行 FLUSH LOGS 也非常有好处,可以让 MySQL 从一个新的二进制日志开始。从日志文件的开头做基于故障时间点的恢复要比从中间更容易。
【4】服务器配置: 如果需要从灾难中恢复,这时需要在新数据中心构建服务器,如果此时备份中包含服务器配置会免去很多工作。
【5】选定的操作系统文件: 对于服务器配置来说,备份中对生产服务器至关重要的任务外部配置,都十分重要。在 UNIX 服务器上,这可能包括 cron 任务(实现 linux 定时任务)、用户和组的配置、管理脚本,以及 sudo(是 linux 系统管理指令,是允许系统管理员让普通用户执行一些或者全部的 root 命令的一个工具) 规则。

六、存储引擎和一致性

MySQL 对存储引擎的选择会导致备份明显更复杂。如何对给定的存储引擎后,得到一致的备份。实际上有两类一致性需要考虑:数据一致性和文件一致性。

【1】数据一致性: 当备份时,应该考虑是否需要数据在指定时间点一致。例如,淘宝中的发货单和付款之间的一致性。付款时如果不考虑其发货单,或反过来都会导致麻烦。
如果做在线备份,可能需要所有相关表的一致性备份。InnoDB 的多版本控制功能可以帮助我们。开始一个事务,转储(存储)一组相关的表,然后提交事务。再要在服务器上使用 REPEATABLE READ 事务隔离级别,并且没有任何 DDL,就一定会有完美的一致性,以及基于时间点的快照数据,且在备份过程中不会阻塞任何后续工作。如果不是事务型存储引擎,则只能在备份时用LOCK TABLES 来锁住所有要一起备份的表,备份完后在释放锁。
也可以用 mysqldump 来获得 InnoDB 表的一致性逻辑备份,采用 --single-transaction 选项。但可能会导致一个很长的事务,在某些负载下会导致开销大到不可接受。

【2】文件一致性: 所有备份的文件相互之间应该一致,包括每个文件的内部一致性也非常重要。如果是在不同的时间复制相关文件,他们彼此可能也不一致。MyISAM 的 .MYD 和 .MYI 文件就是个栗子。InnoDB 检测到不一致或损坏,会记录错误日志直到让服务器崩溃。
对于非事务型存储引擎 MyISAM,需要锁表并刷新表。这意味着要么用 LOCK TABLES 和 FLUSH TABLES 结合的方法以使服务器将内存中的变更刷新到磁盘上,要么使用 FLUSH TABLES WITH READ LOCK。一但刷新完成,就可以安全地复制 MyISAM 的原始文件。
对于事务型存储引擎 InnoDB,确保文件的一致性比较困难。即使使用 FLUSH TABLES WITH READ LOCK,InnoDB 依旧在后台运行:插入缓存、日志和写线程继续将变更合并到日志和表空间文件中。这些线程设计上是异步的(这也是InnoDB 取得更高并发性的原因)与 LOCK TABLES 无关。因此,不仅需要确保每个文件内部是一致的,还需要同时复制同一个时间点的日志和表空间。如果在备份时有其他线程在修改文件,或备份表空间与日志文件时间点不同,会在恢复后再次因系统损坏而告终。可以通过如下几个方法规避这个问题:
 ■ 等待直到 InnoDB 清除线程和插入缓冲合并线程完成。可以观察 SHOW INNODB STATUS 的输出,当没有脏缓存或挂起的写时,就可以复制文件。尽管如此,这种方法可能需要很长一段时间;因为 InnoDB 的后台线程涉及太多的干扰而不太安全。不推荐使用。
 ■ 在一个类似 LVM(Logical Volume Manager:逻辑卷管理)的系统中获取数据和日志文件的快照,必须让数据和日志文件在快照中是相互一致;单独取它们的快照是没有意义的。
 ■ 方式一个 STOP 信号给 MySQL,做备份,然后在发送一个 CONT 信号来再次唤醒 MySQL。看起来像是一个很少推荐方式,但如果另一种方法是在备份过程中需要关闭服务器,则这种方法值得考虑。至少这种技术不需要重启服务器后预热。
在复制数据到其他地方后,就可以释放锁以使 MySQL 服务器再次正常运行。同时,也可以从备库中备份,这样的好处是可以不干扰主库,避免在主库上增加额外的负载。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.luyixian.cn/news_show_997066.aspx

如若内容造成侵权/违法违规/事实不符,请联系dt猫网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Leetcode : 147. 对链表进行插入排序

给定单个链表的头 head ,使用 插入排序 对链表进行排序,并返回 排序后链表的头 。 插入排序 算法的步骤: 插入排序是迭代的,每次只移动一个元素,直到所有元素可以形成一个有序的输出列表。 每次迭代中,插入排序只从输…

Java多线程——wait和notify方法作用,线程的状态

目录 引出wait和notify方法作用线程的状态创建线程有几种方式?方式1:继承Thread创建线程方式2:通过Runnable方式3:通过Callable创建线程方式4:通过线程池概述ThreadPoolExecutor API代码实现源码分析工作原理&#xff…

力扣刷题Days7第二题--242.有效的字母异位词(js)

目录 1,题目 2,代码 2.1 我思考完成的-初版--哈希表思想 2.2略改进 2.3排序思想 3,学习与总结 3.1 判断数组元素是否都为0 3.2总结 1,题目 给定两个字符串 s 和 t ,编写一个函数来判断 t 是否是 s 的字母异位词…

okHttp MediaType MIME格式详解

一、介绍 我们在做数据上传时,经常会用到Okhttp的开源库,okhttp开源库也遵循html提交的MIME数据格式。 所以我们经常会看到applicaiton/json这样的格式在传。 但是如果涉及到其他文件等就需要详细的数据格式,否则服务端无法解析 二、okHt…

重学SpringBoot3-@EnableConfigurationProperties注解

重学SpringBoot3-EnableConfigurationProperties注解 1. 引言2. EnableConfigurationProperties 的作用3. 使用示例4. 总结 1. 引言 Spring Boot 提供了一种便捷的方式来管理和校验应用程序的配置,即通过类型安全的配置属性。EnableConfigurationProperties 注解在…

光谱整形1

华为张德江:下一代光传送网将走向400G80波WDM系统_通信世界网 (cww.net.cn) 张德江指出,400G WDM系统具有三大基本特征:支持400G80波,单纤32T超大容量,传输距离与100G相当;支持32维以上的光交叉&#xff1…

搜维尔科技:3D Systems Geomagic Design X 逆向工程软件

产品概述 3D Systems Geomagic Design X 是全面的逆向工程软件 GeomagicoDesign XTM是全面的逆向工程软件,它结合了基于特征的CAD数模与三维扫描数据处理,使您能创建出可编辑、基于特征的CAD数模,并与您现有的CAD软件兼容。 拓展您的设计能…

请说明Vue中的异步组件加载

Vue中的异步组件加载是指当页面需要渲染某个组件时,可以在需要时再去加载这个组件,而不是在页面初始化的时候就将所有组件一次性加载进来。这种方式能够有效降低页面的初始加载时间,提升用户体验。 在Vue中,我们可以使用import函…

springboot实现多线程开发(使用@Async注解,简单易上手)

根据springboot的核心思想便捷开发,使用多线程也变得简单起来,通过一下几个步骤即可实现。 核心注解 EnableAsync将此注解加在启动类上,使项目支持多线程。 Async 使用我们的Async注解在所需要进行多线程的类上即可实现。 配置线程池 …

从第一原理看大语言模型

大模型基础框架 大模型幻觉问题 大模型能力 思维链模式 思维链模式激发的是大模型的推理能力 LLM知识能力RAG

【设计模式】观察者模式及函数式编程的替代C++

本文介绍观察者模式以及使用函数式编程替代简单的策略模式。 观察者模式 观察者模式是一种行为型设计模式,它定义了一种一对多的依赖关系,当一个对象的状态发生改变时,其所有依赖者都会收到通知并自动更新。 当对象间存在一对多关系时&#…

day13_微服务监控Nginx(微服务集成SBA)

文章目录 1 微服务系统监控1.1 监控系统的意义1.2 SBA监控方案1.3 SBA实战1.3.1 创建SBA服务端1.3.2 微服务集成SBA 1.4 微服务集成logback1.5 配置邮件告警 2 Nginx2.1 Nginx简介2.2 下载和安装2.2.1 方式1:window本地安装2.2.1.1 下载2.2.1.2 安装2.2.1.3 目录结构…

【开源物联网平台】FastBee认证方式和MQTT主题设计

🌈 个人主页:帐篷Li 🔥 系列专栏:FastBee物联网开源项目 💪🏻 专注于简单,易用,可拓展,低成本商业化的AIOT物联网解决方案 目录 一、接入步骤 1.1 设备认证 1.2 设备交…

新项目,Linux上一键安装MySQL,Redis,Nacos,Minio

大家好,我是 jonssonyan 分享一个我的一个开源项目,这是一个在 Linux 平台上一键安装各种软件的脚本项目,脚本使用 Shell 语言编写,后续还会增加更多软件的一键安装,代码在 GitHub 上全部开源的,开源地址如…

TypeScript(三)对象,接口,类,泛型

一、 对象 Typescript 中 Object 类型不单是指普通对象类型,它泛指所有的非原始类型,也就是对象,数组还有函数。 普通对象就是键值对的集合,我们可以使用接口来定义对象的结构。interface Person { // Person是接口CHname: strin…

Python算法题集_搜索插入位置

Python算法题集_搜索插入位置 题51:搜索插入位置1. 示例说明2. 题目解析- 题意分解- 优化思路- 测量工具 3. 代码展开1) 标准求解【二分法查找】2) 改进版一【二分法查找终止条件判断】3) 改进版二【第三方模块】 4. 最优算法5. 相关资源 本文为Python算法题集之一的…

基于springboot的智能物流管理系统论文

智能物流管理系统 摘要 随着信息技术在管理上越来越深入而广泛的应用,管理信息系统的实施在技术上已逐步成熟。本文介绍了智能物流管理系统的开发全过程。通过分析智能物流管理系统管理的不足,创建了一个计算机管理智能物流管理系统的方案。文章介绍了智…

OpenCV与AI深度学习 | 基于OpenCV实现模糊检测 / 自动对焦

本文来源公众号“OpenCV与AI深度学习”,仅用于学术分享,侵权删,干货满满。 原文链接:基于OpenCV实现模糊检测 / 自动对焦 导 读 本文主要介绍使用OpenCV实现图像模糊检测/相机自动对焦功能。 前 言 为了检测图片是否对焦&…

深入浅出(二)MVVM

MVVM 1. 简介2. 示例 1. 简介 2. 示例 示例下载地址:https://download.csdn.net/download/qq_43572400/88925141 创建C# WPF应用(.NET Framework)工程,WpfApp1 添加程序集 GalaSoft.MvvmLight 创建ViewModel文件夹,并创建MainWindowV…

抖音视频评论批量采集软件|视频下载工具

《轻松搞定!视频评论批量采集软件,助您高效工作》 在短视频这个充满活力和创意的平台上,了解用户评论是了解市场和观众心声的重要途径之一。为了帮助您快速获取大量视频评论数据,我们推出了一款操作便捷、功能强大的软件&#xff…