MYSQL数据库的备份与恢复

徐阿马 2020-10-06

448

会不会有那么一天，生活可以简单到每天清早踏上一辆载着鲜花的脚踏车，微笑着穿过窄窄的街巷，为爱花的人送去芬芳，为需要知识的你送去帮助。

简介

随着自动化办公与电子商务的不断发展,企业对于信息系统的依赖性越来越高,而数据库在信息系统中担任着非常重要的角色,尤其一些对数据可靠性要求非常高的行业,如银行、证券、电信等,如果发生意外宕机或数据丢失,其损失是非常严重的,为此数据库管理员必须针对具体的业务要求制定详细的数据库备份与灾难恢复的策略,并通过模拟故障对每种可能的情况进行严格的测试从而保证数据的可靠性。

上一章我们已经学习了MySOL数据库的安装与基本操作,本章我们就以MSaL为例来学习数据库备份与恢复的相关知识，并掌握MySOL常见的备份与恢复的方法.

本章重点

掌握MySOL完全备份恢复操作

掌握MySOL增量备份恢复操作

一，MySQL完全备份与恢复

在介绍MSL完全备份之前。先来了解一下数据库备份的相关知识。

1.数据库备份概述

1）数据备份的重要性因此，数据的安全性及数据在企业中数据的价值至关重要，数据保障了企业业务的正常运行。任何数据的丢失都可能对企业产生严重的后果，通常造成数据丢失的原因有如下几种：

程序错误
人为操作错误
运算错误
磁盘故障
灾难（如火灾、地震）和盗窃

2.数据库备份类型

1）从物理与逻辑的角度

数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。

物理备份又可以分为冷备份（脱机备份）.热备份（联机备份）和温备份。

冷备份：在数据库关闭状态下进行备份操作。

热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件。

温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。

逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构（CREATE DATABASE，CREATE TABLE 语句）和内容（NSERT语句或分隔文本文件）的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系结构上重新创建数据。

2）从数据库的备份策略角度

从数据库的备份策略角度，数据库的备份可分为完全备份、差异备份和增量备份。

完全备份：每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份的时间也很长。

差异备份：备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

3.常见的备份方法

MySOL数据库的备份可以采用很多种方式，如直接打包数据库文件（物理冷备份）、专用备份工具（mysqldump）、二进制日志增量备份、第三方工具备份等。

1）物理冷备份

物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。物理冷备份一般用于非核心业务，这类业务一般都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简单的。通常通过直接打包数据库文件夹（本章中的数据库文件夹位于/usr/local/mysal/data）来实现备份。

2）专用备份工具mysqldump或mysqlhotcopy

mysqldump程序和mysqlhotcopy都可以做备份。mysqldump是客户端常用逻辑备份程序，能够产生一组被执行以再现原始数据库对象定义和表数据的SOL语句，它可以转储一个到多个MySOL数据库，对其进行备份或传输到远程SOL服务器，mysqldump更为通用，因为它可以备份各种表。

mysqlhotcopy 是由per语言编写的数据库备份脚本，因为它只能运行在UNIX或者LINUX 上，因此使用范围很小，这里也就不为大家多做介绍了

3）通过启用二进制日志进行备份

mysql数据库的所有操作记录都保存在二进制日志文件中，可以通过查看二进制日志文件进行增量备份和恢复数据。（注意：使用增量备份时，必须开启二进制日志文件）

4）通过第三方工具备份

xtarbackup

innodbackupex

xbstream

我们通常用的最多的只有mysqldump和mysqlbinlog，第三方工具这种一般不怎么使用，也就不为大家一一介绍了

二，数据库完全备份操作

1.物理备份的恢复与操作

物理备份一般用tar命令直接打包数据库文件夹，而在备份之前需要关闭mysqld的服务，mysql数据库文件夹存放在/usr/local/mysql/data下

模拟备份完以后，可以模拟故障将/usr/local/mysql/data/下的数据全部删除，然后使用tar恢复到原来路径下

2.mysqldump备份与恢复

通过mysqldump命令可以将指定的库、表或全部的库导出为SOL脚本，便于该命令在不同版本的MySOL服务器上使用。例如，当需要升级MySOL服务器时，可以先使用mysqldump命令将原有库信息导出，然后直接在升级后的MySOL服务器中导入即可。

1）备份数据库

使用mysqldump命令导出数据时，默认会直接在终端显示，若要保存到文件，还需要结合Shell的“>”重定向输出操作。命令格式如下所示。

格式1：备份指定库中的部分表。

mysqldump [选项] 库名 [表名1] [表名2]… > /备份路径/备份文件名

格式2 备份一个或多个完整的库（包括其中所有的表）。

mysqldump [选项] --databases [库名1] [库名2]… > /备份路径/备份件名

格式3：备份MySOL服务器中所有的库。

mysq1dump [选项] --all-databases > /备份路径/备份文件名

其中，常用的选项包括-u”“-p”，分别用于指定数据库用户名、密码

若需要备份整个MySOL服务器中的所有库，应使用格式3.当导出的数据量较大的时候，可以添加--opt”选项以优化执行速度。

2）恢复数据库

使用mysqldump命令导出的SOL备份脚本，在需要恢复时可以通过mysql命令对其进行导入操作。命令格式如下所示。

mysql [选项] [库名] [表名] < /备份路径/备份文件名

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

三，MySQL增量备份与恢复

使用mysqldump进行完全备份，备份的数据中有重复数据，备份时间与恢复时间过长。而增量备份就是备份自上一次备份之后增加或改变的文件或内容。

1.MySQL增量备份概述

1)增量备份的特点

与完全备份不同，增量备份没有重复数据.备份量不大，时间短；但其恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。

MySOL没有提供直接的增量备份办法，可以通过MySOL提供的二进制日志（binary logs）间接实现增量备份。

2.MySQL二进制日志对备份的意义

二进制日志保存了所有更新或者可能更新数据库的操作。二进制日志在启动MySOL服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接收到flushlogs命令后重新创建新的日志文件，生成二进制文件序列，并及时把这些口志保存到安全的存储位置，即可完成一个时间段的增量备份。使用max_binlog_size配置项可以设置二进制日志文件的最大值，如果二进制文件的大小超过了max_binlog_size，它就会自动创建新的二进制文件。

要进行MySOL的增量备份，首先要开启二进制日志功能，开启MySOL的二进制日志功能的实现方法有很多种，最常用的是在MySOL配置文件的mysqld项下加入“log-bin=/文件路径/文件名”前缀，如log-bin=/usr/local/mysoql/mysql-bin.然后重启MySOL服务，就可以在指定路径下查看二进制日志文件了。默认情况下，二进制日志文件的扩展名是一个六位的数字，如mysql-bin.000001.

四，MySQL增量恢复

在维护数据库时，因为各种各样的原因可能会导致数据丢失，如人为的SOL语句破坏数据库，在进行下一次全备份之前发生系统故障导致数据库数据丢失，在数据库主从架构中主库的数据发生故障等。当出现以上场景时可以使用增量恢复来恢复数据。

常用的增量恢复的方法有三种：一般恢复、基于位置的恢复、基于时间点的恢复。

一般恢复：将所有备份的二进制日志内容全部恢复，命令格式如下所示。

名：

mysqbinlog [--no-defaults] 增量备份文件 l mysq1-u用户名 -p密码

基于位置的恢复：数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准，命令格式如下所示。

格式1：恢复数据到指定位置。

mysqlbinlog --stop-position='操作id，二进制日志 l mysq1-u用户名-p密码

格式2：从指定的位置开始恢复数据。

mysq1bin1og --start-position ='操作id，二进制日志 | mysq1-u用户名 -p密码

基于时间点的恢复：跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可以分成三种情况。

格式1：从日志开头截止到某个时间点的恢复。

mysqlbinlog [--no-defaults] --stop-datetime='年-月-日小时：分钟：秒二进制日志 I mysql -u 用户名 -p密码

格式2：从某个时间点到日志结尾的恢复。

mysqlbinlog [--no-defaults] --start-datetime='年-月-日小时：分钟：秒二进制日志 I mysql -u用户名 -p密码

格式3：从某个时间点到某个时间点的恢复。

mysqlbinlog [--no-defaults] --start-datetime='年-月-日小时：分钟：秒

--stop-datetime=1年-月-日小时：分钟：秒二进制日志 I mysql -u用户名 -p密码

五，制定企业备份策略的思路

在企业中备份策略并不是干篇一律的，而是根据每个企业的实际生产环境与业务需求指定合适的备份策略。无论是选择完全备份，还是选择增量备份，都需考虑它们的优缺点，是否适合当前的生产环境。同时，为了保证恢复的完整性，建议开启二进制日志功能，二进制日志文件给恢复工作也带来了很大的灵活性，可以基于时间点或位置进行恢复。考虑到数据库性能，我们可以将二进制日志文件保存到其他安全的硬盘中。

在进行热备份时，备份操作和应用服务在同时运行，这样就十分消耗系统资源了，导致数据库服务性能下降，这就要求我们选择一个合适的时间（如在应用负担很小的时候）再来进行备份操作。

需要注意的是，不是备份完就万事大吉，最好确认备份是否可用，所以备份之后的恢复测试是非常有必要的。同时备份时间也要灵活调整，如：