MySQL备份与恢复

一：MySQL数据库备份

1.1：数据库备份类型

1.从物理与逻辑的角度

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

物理备份又可以分为冷备份（ 脱机备份）、热备份（ 联机备份） 和温备份。
► 冷备份： 在数据库关闭状态下进行备份操作。
► 热备份： 在数据库处千运行状态时进行备份操作， 该备份方法依赖数据库的日志文件。
► 温备份： 数据库锁定表格（ 不可写入但可读）的状态下进行备份操作。

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

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

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

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

1.2：常见的备份方法

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

1.物理冷备份

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

2. 专用备份工具mysqldump 或mysqlhotcopy

mysqldump 程序和mysqlhotcopy 都可以做备份。mysqldump 是客户端常用逻辑备份程序， 能够产生一组被执行以后再现原始数据库对象定义和表数据的SQL 语句。它可以转储一个到多个MySQ L 数据库， 对其进行备份或传输到远程SQL 服务器。mysqldump 更为通用， 因为它可以备份各种表。mysq lhotcopy 仅适用千某些存储引擎。
mysqlhotcopy 是由Tim Bunce 最初编写和贡献的Perl 脚本。mysq lhotcopy 仅用于备份MylSAM 和ARCHIVE 表。它只能运行在UNIX 或Li nux 上

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

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

4. 通过第三方工具备份

Percona XtraBackup 是一个免费的MySQL 热备份软件， 支待在线热备份lnnodb和XtraDB , 也可以支持MySQL 表备份， 不过MylSAM 表的备份要在表锁的情况下进行。

Percona XtrBackup 有三个主要的工具： xtrabackup 、innobackupex 、xbstream 。

• xtrabackup : 是一个编译了的二进制文件， 只能备份lnnodb/Xtradb 数据文件。
• innodbackupex : 是一个封装了邓abacku p 的PerI 脚本， 除了可以备份lnnodb/Xtradb 之外， 还可以备份MySIAM 。
• xbstream ： 是一个新组件， 能够允许将文件格式转成xbstream 格式或从xbstream格式转到文件格式。

xtrabackup 工具可以单独使用， 但推荐使用innobacku pex 来进行备份， 这是因为innobacku pex 本身就已经包含了xtrabackup 的所有功能。
xtrabackup 是基千lnnodb 的灾难恢复功能进行设计的，备份工具复制lnnodb 的数据文件。但是， 由千不锁表， 这样复制出来的数据将不一致。lnnodb 维护了一个重做日志，包含lnnodb 数据的所有改动情况。在xtrabackup 备份lnnodb 数据的同时， xtrabackup 还有另外一个线程用来监控重做日志， 一但日志发生变化， 就把发生变化的日志数据复制走。这样就可以利用重做日志做灾难恢复了。
以上是备份过程， 如果需要恢复数据， 则在准备阶段， xtrabackup 就需要使用之前复制的重做日志对备份出来的lnnodb 数据文件进行灾难恢复， 此阶段完成之后， 数据库就可以进行重建还原了

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

二：数据库完全备份操作

2.1：物理冷备份与恢复

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

1.备份数据库

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

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup

[root@localhost ~]# cd /usr/local/
[root@localhost local]# tar zcvf /backup/mysql_$(date +%F).tar.gz mysql/
[root@localhost local]# cd /backup/
[root@localhost backup]# ls
mysql_2023-06-08.tar.gz

2.模拟故障

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

[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root -p -e 'drop database auth;'
[root@localhost backup]# mysql -u root -p -e 'select * from auth.users;'

3.恢复数据库

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# cd /backup/
[root@localhost backup]# tar zxvf mysql_2024-06-27.tar.gz

[root@localhost backup]# cd /usr/local/
[root@localhost local]# mv mysql/ mysql.bak

[root@localhost local]# cd /backup/
[root@localhost backup]# mv mysql /usr/local/
[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root -p -e 'select * from auth.users;'

2.2：mysqldump 备份与恢复

通过mysqldump 命令可以将指定的库、表或全部的库导出为SQL 脚本， 便于该命令在不同版本的MySQ L 服务器上使用

1.备份数据库

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

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

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

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

格式3 : 备份MySQL 服务器中所有的库。

mysqldump［选项］--  all-databases > /备份路径/备份文件名

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

[root@localhost ~]#mysqldump -u root -p mysql user> mysql-user.sql
Enter password:
[root@localhost ~]#mysqldump -u root -p --databases auth > auth.sql
Enter password:

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

[root@localhost ~]#mysqldump -u root -p --opt --all-databases> all-data.sql

 2 查看备份文件

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

[root@localhost~]#grep -v ""--" auth.sql I grep -v ""/" I grep -v ""$"

3. 恢复数据库

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

mysql ［选项］［库名］［表名］ ＜/备份路径/备份文件名

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

[root@localhost ~]#mysql -u root-p test < mysql-user.sql
Enter password:
[root@localhost ~]#mysql -u root -p -e 'SHOW TABLES FROM test;'//验证导入结果
Enter password:

三:MySQL增量备份与恢复

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

MySQL二进制日志保存了所有更新数据库的操作。二进制日志在启动MySQ L 服务器后开始记录， 并在文件达到二进制日志所设置的最大值或者接收到flush logs 命令后重新创建新的日志文件， 生成二进制文件序列， 并及时把这些日志保存到安全的存储位置， 即可完成一个时间段的增l量备份。使用max_binlog_size 配置项可以设置二进制日志文件的最大值， 如果二进制文件的大小超过了max_binlog_size , 它就会自动创建新的二进制文件。
要进行MySQL 的增量备份， 首先要开启二进制日志功能。开启MySQL 的二进制日志功能的实现方法有很多种， 最常用的是在MySQ L 配置文件的mysqld 项下加入“log-bin=/文件路径/文件名“前缀， 如log-bin=/usr/local/mysq l/mysql-bin , 然后重启MySQL 服务就可以在指定路径下查看二进制日志文件了。默认情况下， 二进制日志文件的扩展名是一个六位的数字， 如mysql-bin . 000001 

增量恢复

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

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

mysqlbinlog [--no-defaults] 增量备份文件 |mysq l -u 用户名－p 密码 

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

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

mysqlbinlog --stop-positi on='操作 id' 二进制日志 lmysql -u 用户名－p密码

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

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

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

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

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

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

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