死磕数据库系列（十）：MySQL 数据库的备份与恢复

点关注公众号，回复“1024”获取2TB学习资源！

在这之前，我们学习了：死磕数据库系列（八）：MySQL 主从同步详解、死磕数据库系列（九）：MySQL 读写分离详解等内容。其实，在数据库运行开始之时，有一个非常重要点就是：数据库里的数据备份与恢复，数据对于任何一家企业来说，都是重中之重，那么今天，民工哥就给大家详解介绍一下MySQL数据库的数据备份与恢复。

很多人，一看这标题，肯定张口就会答，这不是废话么。不备份故障了怎么办？跑路吗？数据被沙雕开发（不许喷）误删了怎么办？背锅吗？

当然，大家都知道备份的重要性与必要性。1、保证数据安全与完整

企业的数据安全应该来说是企业的命脉，一旦丢失或造成损坏，轻则损失客户与金钱，重则倒闭（已经有前例在）。

备份的目的：为了保证数据在被人为失误、操作不当、蓄意等情况下删除或损坏后，能及时、有效的进行恢复并不会很大程度上影响到业务运行。2、为业务提供不间断服务

实际生产环境对数据库的要求，首先就是具备7×24×365不间断服务的能力，这也是一定要备份数据库的其中原因之一。

一般情况下, 我们需要备份的数据分为以下几种数据二进制日志, InnoDB事务日志代码(存储过程、存储函数、触发器、事件调度器)服务器配置文件

按照备份时数据库的运行状态，可以分为三种。冷备：停库、停服务来备份

即当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线温备：不停库、不停服务来备份，会(锁表)阻止用户的写入

即当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作热备（建议）：不停库、不停服务来备份，也不会(锁表)阻止用户的写入

即当数据库进行备份时, 数据库的读写操作均不是受影响MySQL 中进行不同类型的备份还要考虑存储引擎是否支持

数据库的备份方式

常用的备份方式包括以下：逻辑备份物理备份逻辑备份

逻辑备份其实就是利用MySQL数据库自带的mysqldump命令，或者使用第三方的工具，然后把数据库里的数据以SQL语句的方式导出成文件的形式。在需要恢复数据时，通过使用相关的命令（如：source ）将备份文件里的SQL语句提取出来重新在数据库中执行一遍，从而达到恢复数据的目的。

实例如下：

一般备份时都会进行压缩处理，以节省磁盘空间，如下

恢复操作

上面的操作，都是针对整个数据库的备份与恢复。但是，在实际生产环境中，这种的备份与恢复方式是有缺点时。因此，一般我们会针对某个库、某个表，也就是我们所说的单库、单表进行备份，在需要恢复数据时，我们可以选择性的进行恢复，可以提高恢复效率，减少宕机时间。单库备份

下面我们看看这个备份文件到底是什么内容

由上的文件内容，可以看出，这个备份实际的过程就是将创建数据库、建表、插入数据的sql语句备份出来，也可以说是将sql语句导出。

参数的作用一目了然，就是当我们的数据库丢失时，可以直接用此备份文件进行恢复，无需再重新建库、建表，然后再进行数据恢复的操作。单表备份

分库备份是为了恢复数据库时方便操作，但是同样面临问题，如果是某个库中的某一个表有损坏，但又不有全库进行恢复，所以实际生产中常用的是分库、分表进行备份，这样数据也备份了，恢复时也好操作

因此分表备份同分库备份一样，只需要进行多次单表备份的操作，但是有的小伙伴肯定会提出问题了，如果一个库里几千张表，几万张表，这种备份要备到猴年马月吧？？？？，数据量比较大的备份可以使用专业的备份工具，数据量不大或者表不是很多的情况，可以将备份操作写成脚本 纳入定时任务，定时执行（MySQL 数据库定时备份的几种方式（非常全面）），只需要检查备份是否成功即可。逻辑备份的优点与使用场景优点：简单，易操作，自带工具方便、可靠。使用场景：数据库数据量不大的情况可以使用，数据量比较大（超过20G左右）时备份速度比较慢，一定程度上还会影响数据库本身的性能。物理备份

物理备份就是利用命令（如cp、tar、scp等）直接将数据库的存储数据文件复制一份或多份，分别存放在其它目录，以达到备份的效果。

这种备份方式，由于在备份时数据库还会存在数据写入的情况，一定程度上会造成数据丢失的可能性。在进行数据恢复时，需要注意新安装的数据的目录路径、版本、配置等与原数据要保持高度一致，否则同样也会有问题。

所以，这种物理备份方式，常常需要在停机状态下进行，一般对实际生产中的数据库不太可取。因此，此方式比较适用于数据库物理迁移，这种场景下这种方式比较高效率。物理备份的优点及使用场景优点：速度快，效率高。场景：可用于停机维护及数据库物理迁移场景中。

实际生产环境中，具体使用哪种方式，就需要看需求与应用场景所定。

在介绍完备份方式之后，再来介绍一下，增量与全量备份这两个概念。什么是全量备份？

全量备份：就是将数据库中的所有数据，或者是某一个特定的库里的所有数据，一次全部备份下来。备份数据库中所有数据

备份某个库的数据

什么是增量备份？

增量备份：指的是上一次全量备份之后到下一次全量备份这前这段时间内数据库所更新或者是增加的数据，将其备份下来。注：全量备份是一个文件，而增量备份则是MySQL的binlog日志文件。所以常说的增量备份就是备份binlog日志文件。两者的区别在哪？

全量备份：需要的备份时间长一点，恢复时间会短一点，因为文件数少，维护方便。但是，全量备份的文件大，占用一定的磁盘空间，全理备份时会一定程序上影响数据库的性能（这也就是为什么在0:00点备份的原因），也因文件大的原因，不便于服务器本地保存过多文件，重要业务的全量备份文件可能需要手工下载或迁移到服务器之外的存储空间中。

增量备份：备份简单，恢复时复杂一点，因为文件数量多，需将所有binlog文件解析成SQL语句，如下：

然后，再通过恢复的方式进行恢复

或者如下操作

生产Mysql数据库数据恢复实战过程 这篇文章是很早之前写的，大家可以参考一下。Xtrabackup介绍

Xtrabackup是由percona开源的免费数据库热备份软件，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）；mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合。

Xtrabackup安装完成后有4个可执行文件，其中2个比较重要的备份工具是innobackupex、xtrabackup

Xtrabackup优点备份速度快，物理备份可靠备份过程不会打断正在执行的事务（无需锁表）能够基于压缩等功能节约磁盘空间和流量自动备份校验还原速度快可以流传将备份传输到另外一台机器上在不增加服务器负载的情况备份数据Xtrabackup备份原理

备份开始时首先会开启一个后台检测进程，实时检测mysq redo的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中，之后复制innodb的数据文件一系统表空间文件ibdatax，复制结束后，将执行flush tables with readlock,然后复制.frm MYI MYD等文件，最后执行unlock tables,最终停止xtrabackup_log输出如下提示信息

Xtrabackup增量备份介绍xtrabackup增量备份的原理1)、首先完成一个完全备份，并记录下此时检查点LSN；2)、然后增量备份时，比较表空间中每个页的LSN是否大于上次备份的LSN，若是则备份该页并记录当前检查点的LSN。增量备份优点1)、数据库太大没有足够的空间全量备份，增量备份能有效节省空间，并且效率高；2)、支持热备份，备份过程不锁表（针对InnoDB而言），不阻塞数据库的读写；3)、每日备份只产生少量数据，也可采用远程备份，节省本地空间；4)、备份恢复基于文件操作，降低直接对数据库操作风险；5)、备份效率更高，恢复效率更高。Xtrabackup安装

下载安装xtrabackup

创建测试数据

Xtrabackup全量备份与恢复

从备份过程截图可以看出会创建一个时间的目录

接下来准备恢复误删除数据

Xtrabackup增量备份与恢复

#需要注意的是，增量备份仅能应用于InooDB或XtraDB表，对于MyISAM表，增量与全备相同

创建用于增量备份的数据，用来模拟删除掉了全备后的数据，能否通过增量备份文件来恢复

删除一条数据来测试增量恢复

增量恢复操作过程如下

恢复全部数据

查看恢复的数据完整性以上就是针对 MySQL 数据库的数据备份与恢复的详细介绍，希望对大家有所帮助，点个在看与转发支持一下吧。

推荐阅读 点击标题可跳转

疯狂涨价！甲骨文推出全新定价模式，想钱想疯了

卸载 Notepad++！事实已证明，它更牛逼…

你还在用 Xshell？试试这款跨平台 SSH 终端工具吧！

最牛逼的监控系统，不接受反驳！！

98家央企及下属409家上市企业全名单（ 2023版）

干掉 VPN ！下一代内核级虚拟网络组网神器横空出世

开工第一天！就把 Zabbix 换成了 Prometheus

转发 关注

点亮下方“在看”图标

更多人看到

南京兆柏数据恢复中心 南京兆柏数据恢复中心