MySQL 备份与恢复 ——介绍

约定不等于承诺〃 2022-12-12 04:41 177阅读 0赞

## MySQL 备份与恢复 ##

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

### 逻辑备份与恢复 ###

**什么是数据库逻辑备份？**  
大家都知道，数据库返回数据时都是按照最初设计的具有一定逻辑关联格式的形式逐条展现的，具有一定的商业逻辑属性。而在物理存储层面上的数据库软件却是按照数据库软件所设计的某种特定格式经过一定处理后存放的。

数据库逻辑备份就是备份软件按照最初设计的逻辑关系，以数据库的逻辑结构对象为单位，将数据库中的数据按照预定义的逻辑关联格式一条一条生成相关的文本文件，以达到备份的目的。

常用的逻辑备份  
逻辑备份可以说是最简单，也是目前中小型系统最常使用的备份方式。在MySQL中常用的逻辑备份主要就是两种，一种是将数据生成为可以完全重现当前数据库中数据的INSERT语句，另外一种就是将数据通过逻辑备份软件，将数据库表的数据以特定分隔符进行分隔后记录在文本文件中。

生成INSERT语句备份

两种逻辑备份方式各有优劣，所针对的使用场景也会稍有差别，先来看生成INSERT语句的逻辑备份。

在MySQL数据库中，一般通过MySQL数据库软件自带工具程序中的mysqldump来生成INSERT语句的逻辑备份文件。其使用方法基本如下：

由于mysqldump的使用方法比较简单，大部分需要的信息都可以通过运行“mysqldump --help”获得。这里只想结合MySQL数据库的一些概念原理和大家探讨一下当使用mysqldump做数据库逻辑备份时有哪些技巧及注意事项。

我们都知道，对于大多数使用数据库的软件或者网站来说，都希望自己的数据库能够提供尽可能高的可用性，而不是时不时地就须要停机。因为一旦数据库无法提供服务，系统就无法再通过存取数据来提供一些动态功能。所以对于大多数系统来说，如果要让每次备份都停机可能是不可接受的，可是mysqldump程序的实现原理是通过我们给的参数信息加上数据库中的系统表信息来逐个表获取数据，然后生成INSERT语句再写入备份文件中的。这样就出现了一个问题，系统正常运行过程中，很可能会不断有数据变更的请求正在执行，这样就可能造成在mysqldump备份出来的数据不一致。也就是说备份数据很可能不是同一个时间点的数据，而且甚至可能都没办法满足完整性约束。这样的备份集对于有些系统来说也许并没有太大问题，但是对于那些对数据的一致性和完整性要求比较严格的系统来说问题就大了，它就是一个完全无效的备份集。

对于如此场景，该如何做？我们知道，想让数据库中的数据一致，只有在两种情况下可以做到。

第一，同一时刻取出所有数据；  
第二，数据库中的数据处于静止状态。  
对于第一种情况，大家肯定会想，这可能吗？只要有两个以上的表，不管怎样写程序，都不可能获得完全一致的取数时间点啊。是的，我们确实无法通过常规方法让取数的时间点完全一致，但是大家不要忘记，在同一个事务中，数据库是可以做到所读取的数据处于同一个时间点的。所以，对于事务支持的存储引擎，如InnoDB或BDB等，就可以通过将整个备份过程控制在同一个事务中，来达到备份数据的一致性和完整性，而且mysqldump程序也提供了相关的参数选项来支持该功能，就是通过“–single-transaction”选项，可以不影响数据库的任何正常服务。

对于第二种情况我想大家首先想到的肯定是将须要备份的表锁定，只允许读取而不允许写入。是的，确实只能这么做。我们只能通过一个折中的处理方式，让数据库在备份过程中仅提供数据的查询服务，锁定写入的服务，以使数据暂时处于一致的不会被修改的状态，等mysqldump完成备份后再取消写入锁定，重新开始提供完整的服务。mysqldump程序也提供了相关选项，如“–lock-tables”和“–lock-all-tables”，它们在执行之前会锁定表，执行结束后自动释放锁定。这里有一点须要注意的就是，“–lock-tables”并不是一次性将须要导出（dump）的所有表锁定，而是每次仅锁定一个数据库的表，如果你须要导出（dump）的表分别在多个不同的数据库中，一定要使用“–lock-all-tables”才能确保数据的一致完整性。

当通过mysqldump生成INSERT语句的逻辑备份文件的时候，有一个非常有用的选项供我们使用，那就是“–master-data\[=value\]”。当添加了“–master-data=1”时，mysqldump会将当前MySQL使用的binlog日志的名称和位置记录到dump文件中，并且是以CHANGE\_MASTER语句的形式记录，如果仅仅是使用“–master-data”或“–master-data＝2”，CHANGE\_MASTER语句会以注释的形式存在。这个选项在实施slave的在线搭建时非常有用，即使不是进行在线搭建slave，也可以在某些恢复的过程中通过备份的binlog做进一步恢复操作。

在某些场景下，可能只须要将某些特殊的数据导出到其他数据库中，但不希望通过新建临时表的方式来实现，那么可以在通过mysqldump程序的“—where=‘where-condition’”来实现，但只能在仅导出（dump）一个表的情况下使用。

其实除了以上一些使用诀窍之外，mysqldump还提供了其他很多有用的选项供大家在不同的场景下使用，如通过“–no-data”仅仅dump数据库结构创建脚本，通过“–no-create-info”去掉dump文件中创建表结构的命令等，

逻辑备份恢复方法  
仅仅有了备份还不够，得知道如何去使用这些备份，现在就看看上面所做的逻辑备份的恢复方法：

由于所有的备份数据都是以最初数据库结构的设计相关形式存储的，所以逻辑备份的恢复也相对比较简单。当然，针对两种不同的逻辑备份形式，恢复方法也稍有区别。下面就分别针对这两种逻辑备份文件的恢复方法做一个简单的介绍。

INSERT语句文件的恢复

对于INSERT语句形式的备份文件的恢复是最简单的，仅仅须要运行该备份文件中的所有（或者部分）Query命令。如果须要做完全恢复，那么可以通过使用“mysql < backup.sql”直接调用备份文件执行其中的所有命令，将数据完全恢复到备份时候的状态。如果已经使用mysql连接上了MySQL，那么也可以通过在mysql中执行“source pathbackup.sql”或“. pathbackup.sql”来进行恢复。

纯数据文本备份的恢复

如果是上面第二种形式的逻辑备份，恢复起来会稍微麻烦一点，需要一个表一个表通过相关命令来进行恢复，当然如果通过脚本来实现自动多表恢复也是比较方便的。恢复方法也有两个，一是通过MySQL的“LOAD DATA INFILE”命令来实现，另一种方法就是通过MySQL提供的使用工具mysqlimport来进行恢复。

在清楚了如何使用逻辑备份进行相应的恢复之后，须要知道可以利用这些逻辑备份做些什么。

1.通过逻辑备份，可以执行相关Query或命令把数据库中的相关数据完全恢复到备份时候所处的状态，而不影响不相关的数据；  
2.通过全库的逻辑备份，可以在新的MySQL环境下完全重建一个与备份时候完全一样的数据库，并且不受MySQL所处的平台类型限制；  
3.通过特定条件的逻辑备份，可以将某些特定数据轻松迁移（或者同步）到其他的MySQL或另外的数据库环境；  
4.通过逻辑备份，可以仅仅恢复备份集中的部分数据而不须要全部恢复。  
在知道了逻辑备份能做什么之后，还必须清楚它不能做什么，这样才能清楚备份能否满足自己的预期，是否确实是自己想要的。

1.逻辑备份无法让数据恢复到备份时刻以外的任何一个时刻；  
2.逻辑备份无法进行快速的恢复。

## 物理备份与恢复 ##

#### 什么是数据库物理备份 ####

在了解MySQL的物理备份之前，我们须要先了解，什么是数据库物理备份。既然是物理备份，那么肯定和数据库的物理对象相对应。就如同逻辑备份是根据数据库逻辑对象所做的备份一样，数据库的物理备份就是对数据库的物理对象所做的备份。

数据库的物理对象主要由数据库的物理数据文件、日志文件及配置文件等组成。在MySQL数据库中，除了MySQL系统共有的一些日志文件和系统表的数据文件之外，每一种存储引擎还会有不太一样的物理对象，下面将详细列出几种常用的存储引擎各自所对应的物理对象（物理文件），以便大家清楚地知道各种存储引擎在做物理备份时到底有哪些文件需要备份，哪些不需要。

MySQL物理备份所需文件  
MyISAM存储引擎

MyISAM存储引擎的所有数据都存放在MySQL配置设定的“datadir”目录下。实际上不管使用的是MyISAM存储引擎还是其他任何存储引擎，每一个数据库都会在“datadir”目录下有一个文件夹（包括系统信息的数据库mysql也是一样）。在各个数据库中每一个MyISAM存储引擎表都会有三个文件存在，分别为记录表结构元数据的“.frm”文件、存储表数据的“.MYD”文件，以及存储索引数据的“.MYI”文件。由于MyISAM属于非事务性存储引擎，所以它没有自己的日志文件。所以MyISAM存储引擎在做物理备份时，除了备份MySQL系统共有的物理文件之外，就只须备份上面的三种文件。

**InnoDB存储引擎**

InnoDB存储引擎属于事务性存储引擎，而且存放数据的位置也可能与MyISAM存储引擎有所不同，这主要取决于我们对InnoDB的相关配置。决定InnoDB存放数据位置的配置为“innodb\_data\_home\_dir”、“innodb\_data\_file\_path”和“innodb\_log\_group\_home\_dir”这三个目录位置指定的参数，以及另外一个决定InnoDB的表空间存储方式的参数“innodb\_file\_per\_table”。前面三个参数指定了数据和日志文件的存放位置，最后一个参数决定InnoDB是以共享表空间存放数据还是以独享表空间方式存储数据。

如果我们使用了共享表空间的存储方式，那么InnoDB须要备份“innodb\_data\_home\_dir”和“innodb\_data\_file\_path”参数所设定的所有数据文件，以及“datadir”中相应数据库目录下的所有InnoDB存储引擎表的“.frm”文件。

如果我们使用了独享表空间，那么除了备份上面共享表空间方式须要备份的所有文件之外，还须要备份“datadir”中相应数据库目录下的所有“.idb”文件，该文件中存放的才是独享表空间方式下InnoDB存储引擎表的数据。这里可能有人会问，既然是使用独享表空间，那为什么还要备份共享表空间“才使用到”的数据文件呢？其实这是很多人的一个共性误区，大家以为使用独享表空间的时候InnoDB的所有信息就都存放在“datadir”所设定数据库目录下的“.ibd”文件中，实际上并不是这样的。“.ibd”文件中所存放的仅仅只是表数据、索引数据以及UNDO信息，大家都很清楚，InnoDB是事务性存储引擎，它是需要REDO信息的而不管InnoDB使用的是共享还是独享表空间的方式来存储数据，各个表的元数据信息都是存放在“innodb\_data\_home\_dir”和“innodb\_data\_file\_path”这两个参数所设定的数据文件中的。所以要想InnoDB的物理备份有效，“innodb\_data\_home\_dir”和“innodb\_data\_file\_path”参数所设定的数据文件不管在什么情况下都必须备份。

此外，除了上面所说的数据文件之外，InnoDB还存放REDO信息和相关事务信息的日志文件在“innodb\_log\_group\_home\_dir”参数所设定的位置。所以要想InnoDB物理备份能够有效使用，还要备份“innodb\_log\_group\_home\_dir”参数设定位置的所有日志文件。

**NDB Cluster存储引擎**

NDB Cluster存储引擎（其实也可以说是MySQL Cluster）的物理备份文件主要有以下三类：

*  元数据（Metadata）：包含所有的数据库及表的定义信息；
 *  表数据（Table Records）：保存实际数据的文件；
 *  事务日志数据（Transaction Log）：维持事务的一致性和完整性，以及恢复过程中需要的事务信息

**备份的主要目的是灾难恢复,备份还可以测试应用、回滚数据修改、查询历史数据、审计等。之前已经学习过如何安装MySql,本小节我们将从生产运维的角度了解备份恢复的分类与方法。**

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

*  程序错误
 *  人为操作错误
 *  运算错误
 *  磁盘故障
 *  灾难（如火灾，地震，偷窃）

**物理逻辑备份**

1）冷备份  
关闭数据库服务对数据进行备份  
2）热备份  
启动数据库服务备份数据  
或者实时备份mysql数据  
3）温和备份  
禁止对数据进行写入操作允许读取  
最大限度保证数据的完整性、一致性和唯一性  
4）逻辑备份  
对数据库或者表的逻辑结构进行备份  
5）通过物理备份  
硬盘配置RAID5 将mysql数据保存在RAID5分区上

**数据库备份和策略**

1）完整备份  
第一次配置数据库备份使用  
备份速度慢  
占用空间大  
2）差异备份  
做完完整备份后使用差异备份  
差异备份只备份最新数据  
3）增量备份  
完整备份后使用增量  
备份增量后修改的数据  
备份速度快  
占用空间小

**常见的备份恢复工具类型**

1）物理备份  
tar：归档  
cp：复制数据副本  
2）使用备份工具  
mysqldump：mysql系统提供备份工具，通过备份sql语句实现  
mysqlhotcopy：通过perl语言编写数据库备份脚本，备份实现复杂  
3）二进制备份恢复  
mysql数据库的操作记录在二进制日志中  
通过二进制日志进行增量备份和数据恢复  
4）第三方工具  
Xtrabackup Innodbackup Xbstream

### 参考文档 ###

[MySQL备份与恢复策略][MySQL]  
接下来我们进入实际操作————

[MySQL]: http://kklin.farbox.com/post/shu-ju-ku-she-ji/mysql/1-gao-ji/6-mysqlbei-fen-yu-hui-fu-ce-lue#toc_0