怎么对MySQL数据库进行备份与恢复

MySQL的备份与恢复

前言：前面我们已经了解了 MySQL 数据库的安装及使用，但随着自动化办公与电子商务的不断发展，企业对信息系统的依赖性越来越高，而数据库在信息系统中担任着非常重要的角色。尤其是一些对数据可靠性要求高的行业，如果发生数据丢失，其损失是非常严重的。因此，我们对数据库指定详细的数据库备份与灾难恢复的策略是完全有必要的。

文档大纲：

一、数据库备份的一些概念

1.造成数据丢失的原因

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

2.数据备份的类型

1）从物理与逻辑的角度分类

物理备份可分为：

• 冷备份（脱机备份）：在数据库关闭状态下进行备份操作
• 热备份（联机备份）：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件
• 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

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

逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，这种类型的备份使用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系结构上重新创建数据。

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

从备份策略可分为：

• 完全备份：每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便，但数据存在大量的重复，并且会占用大量的磁盘空间，备份时间也很长。
• 差异备份：备份那些从上一次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完整备份与最近一次的差异备份。
• 增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量一次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

如图所示：

3.常见的备份方法

1）物理冷备份

需要在数据库关闭状态下进行，能够较好的保证数据库的完整性。一般用于非核心业务，这类业务一般都允许终端，物理冷备份的特点就是速度快，恢复操作简单。

通常通过直接打包数据库文件夹（如：/usr/local/mysql/data）来实现

2）专用备份工具mysqldump或mysqlhotcopy

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

Mysqlhotcopy是由Tim Bunce最初编写和贡献的 Perl 脚本。Mysqlhotcopy仅用于备份MyISAM和ARCHIVE表。它只能运行在UNIX或 linux 上，因为使用范围很小，在这里就不细说了。

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

MySQL支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份，需要刷新二进制日志。

4）通过第三方 工具 备份

Percona XtraBackup是一个免费的MySQL热备份软件，支持在线热备份Innodb和XtraDB，也可以支持MySQL表备份，不过MyISAM表的备份要在表锁的情况进行。由于内容太多，这里就不详细讲解了，有兴趣的话可以查阅该软件的详细介绍了解一下。

二、MySQL的完全备份与恢复

1.物理冷备份与恢复

物理冷备份一般用tar命令直接打包数据库文件夹，而在进行备份之前需要使用先关闭mysql服务。

1）备份数据库

创建一个/backup目录作为备份数据存储路径，使用tar创建备份文件。整个数据库文件夹备份属于完全备份。如下图：

2）恢复数据库

模拟故障，将/usr/local/mysql/data移动到/bak目录下。如下图：

从备份文件恢复数据。如下图：

2.mysqldump备份与恢复

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

1）备份数据库

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

常用的选项：-u：指定用户名；-p：指定密码

示例：将mysql库中的user表导出为mysql-user.sql，将整个auth库导出为auth.sql，将所有库导出为all-data.sql文件。如下图：

2）查看备份文件

通过mysqldump工具导出的SQL脚本是文本文件，其中"/*...*/"部分或以"--"开头的行为注释信息，使用grep、less、cat等文本工具可以查看脚本内容。如下图（查看auth.sql文件中的内容）：

3）恢复数据库

使用mysqldump命令导出的SQL备份脚本，在恢复时通过mysql命令对其进行导入操作。命令格式为："mysql [选项] [库名] [表名] < /备份路径/备份文件名"。

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

示例：从备份文件mysql-user.sql中将表导入test库。如下图：

若备份文件中已经包括完整的库信息，则导入操作时不需要指定库名。如下图：

三、MySQL的增量备份与恢复

1.增量备份的概述

1）增量恢复的特点

与完全备份不同，增量备份没有重复数据，备份量不大，时间短；但其恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复，MySQL没有提供直接的增量备份办法，可以通过MySQL提供的二进制日志（binary logs）间接实现增量备份。

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

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

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

2.增量恢复

在维护数据库时，因为各种各样的原因可能会导致数据丢失，如：认为的SQL语句破坏、在进行下一次全备份之前发生系统故障、数据库主从架构中主库的数据发生故障等。所以常用的增量恢复的方法有三种： 一般恢复、基于位置的恢复、基于时间点的恢复

1）一般恢复：将所有备份的二进制日志内容全部恢复

命令格式：

"mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p"

2）基于位置的恢复：数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准

命令格式：

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

"mysqlbinlog --stop-position='操作id' 二进制日志 | mysql -u 用户名 -p 密码"

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

"mysqlbinlog --start-position='操作id' 二进制日志 | mysql -u 用户名 -p 密码"

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

A.从日志开头截止到某个时间点的恢复

命令格式：

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

B.从某个时间点到日志结尾的恢复

命令格式：

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

C.从某个时间点到某个时间点的恢复

命令格式：

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