数据库管理与高可用之MySQL备份与恢复

Aotpos

于 2024-07-29 17:28:39 发布

阅读量813

点赞数 12

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/m0_74210957/article/details/140774826

版权

1.1 Mysql数据库备份概述

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

备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史记录、审计等

1.1.1 数据备份的重要性

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

1.1.2 数据库备份类型

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

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

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

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

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

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

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

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

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

1.1.3 常见的备份方法

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

1）物理冷备份

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

2）专用备份工具mysqldump或mysqlhotcopy

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

mysqlhotcopy是由Tim Bunce最初编写和贡献的Perl脚本。mysqlhotcopy仅用于备份MylSAM和ARCHIVE表。它只能运行在UNIX或Linux上。因为使用范围很小

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

MySQL支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改)，需要刷新二进制日志。

4）通过第三方工具备份

Percona XtraBackup是一个免费的MySQL热备份软件，支持在线热备份Innodb和XtraDB，也可以支持 MySQL表备份，不过MyISAM表的备份要在表锁的情况下进行。本节对于 Percona XtraBackupr的叙述是基于2.4版本的。

xtrabackup工具可以单独使用，但推荐使用innobackupex来进行备份，这是因为innobackupex本身就已经包含了xtrabackup 的所有功能。

xtrabackup是基于Innodb的灾难恢复功能进行设计的，备份工具复制Innodb的数据文件。但是，由于不锁表，这样复制出来的数据将不一致。Innodb维护了一个重做日志，包含Innodb 数据的所有改动情况。在xtrabackup备份Innodb数据的同时，xtrabackup还有另外一个线程用来监控重做日志，一但日志发生变化，就把发生变化的日志数据复制走。这样就可以利用重做日志做灾难恢复了。

以上是备份过程，如果需要恢复数据，则在准备阶段，xtrabackup就需要使用之前复制的重做日志对备份出来的 Innodb 数据文件进行灾难恢复，此阶段完成之后，数据库就可以进行重建还原了。

Percona XtraBackup对MySIAM的复制，是按这样的一个顺序进行的:首先锁定表，然后复制，再解锁表。

1.2 数据库完全备份操作

1.2.1 物理冷备份与恢复

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

1）备份数据库

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

2）恢复数据库

执行下面操作将数据库文件/usr/local/mysqldata/转移至bak目录下，模拟故障。

执行下面操作可以从备份文件恢复数据。

1.2.2 mysqldump备份与恢复

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

1）备份数据库

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

其中，常用的选项包括"“-u"、“-p”，分别用于指定数据库用户名、密码。例如，以下操作分别使用格式1、格式2，将 mysql库中的user 表导出为mysql-user.sql，将整个auth库导出为 auth.sql文件，所有操作均以root用户身份验证。

若需要备份整个MySQL服务器中的所有库，应使用格式3。当导出的数据量较大的时候，可以添加"--opt"选项以优化执行速度。例如，执行以下操作将创建备份文件 all-data.sql,其中包括 MySQL服务器中的所有库。

2）查看备份文件

通过mysqldump工具导出的SQL脚本是文本文件，其中“T*..*T部分或以"--"开头的行表示注释信息。使用grep、less、cat等文本工具可以查看脚本内容。例如，执行以下操作可以过滤出auth.sql脚本中的数据库操作语句。

3）恢复数据库

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

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。例如，执行以下操作可以从备份文件 mysql-user.sql中将表导入test库。其中"-e"选项是用于指定连接MySQL后执行的命令，命令执行完后自动退出。

若备份文件中已经包括完整的库信息，则执行导入操作时无须指定库名。例如，执行以下操作可以从备份文件auth.sql恢复auth库。

1.3 MySQL增量备份与恢复

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

1.3.1 MySQL增量备份概述

1）增量备份的特点

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

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

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

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

1.3.2 MySQL增量恢复

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

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

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

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

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

1.3.3 MySQL企业备份案例

需求描述:北京移电通信公司的用户信息数据库为client,用户资费数据表为user_info,该公司每周需要进行完全备份，每天需要进行增量备份。新增加的用户信息如表4-1所示。

1）一般恢复

1）添加数据库、表、录入信息

在进行备份之前，先根据给出的需求创建用户信息数据库 client、用户资费数据表user_info，并且根据需求描述中的表格插入前三条用户的数据。

2）先进行一次完全备份

为方便验证二进制日志的增量恢复功能，在插入三条用户数据后先对client数据库的user_info表进行一次完全备份。然后在 Linux系统命令行下执行"mysqladmin -uroot -pflush-logs"命令或在"mysql>"命令提示符下执行"flush logs;"生成新的二进制日志。

3）继续录入新的数据并进行增量备份

继续录入两个用户的数据，并执行"mysqladmin -uroot -p flush-logs"命令刷新二进制日志，进行增量备份。如此，二进制日志文件 mysql-bin.000002中仅保留插入两个用户数据的操作。

4）模拟误操作删除user_info表

5）恢复操作

再执行恢复操作时，需要先恢复完全备份，然后恢复增量备份。

2）基于位置恢复

由于前面已经做过备份操作，接下来直接进行模拟故障与数据恢复的操作。

想要实现基于位置或时间点恢复数据,必须先通过查看二进制日志文件确定恢复的位置或时间点。使用"mysqlbinlog --no-defaults 二进制日志文件"可以查看二进制日志文件的具体内容。

通过查看日志文件的具体内容可以发现,在每进行一个操作之前都会有一个独特的编号，如“# at 623”"。此编号随着操作数增多而变大，我们称之为操作ID。在操作ID下面紧跟着的是时间标记。要实现基于位置或时间点恢复数据,需要分别依赖二进制日志文件中的操作ID或者时间标记。例如，通过二进制日志文件得知，在操作ID为“623”的时候，user_info表中插入了“孙七"的用户数据。因此执行以下命令可以实现仅恢复到操作ID为“623"之前的数据，即不恢复"孙七"的信息。这时所恢复的数据是从二进制日志文件的开始位置直到指定位置。

上述操作命令中，“--stop-position"指定的是停止的位置。如果仅恢复“孙七"的信息，跳过"赵六"的信息恢复，可以使用"--start-position"选项指定开始恢复数据的位置。这时所恢复的数据是从指定位置开始直到二进制日志文件的最后。

3）基于时间点恢复

基于时间点的数据恢复所使用的选项是"--stop-datetime"，指定的时间同样也是查询二进制日志所得。执行以下操作可以实现仅恢复到16∶41∶24之前的数据，即不恢复"孙七”的信息。

执行以下操作可以实现仅恢复"孙七”的信息，跳过“赵六"的信息恢复。

1.4 制定企业备份策略的思路

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

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

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