MySQL备份及恢复

一、为什么要数据备份

备份的主要目的是灾难恢复，在生产环境中，数据的安全性至关重要，任何数据的丢失都可能产生严重的后果。

造成数据丢失的原因

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

二、数据库备份的分类

从物理与逻辑的角度，备份可分为物物理备份与逻辑备份

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

2.1、物理备份

对数据库操作系统的物理文件（如数据文件、 日志文件等）的备份

物理备份方法

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

2.2、逻辑备份

对数据库逻辑组件（如：表等数据库对象）的备份

2.3、完全备份，差异备份，增量备份

完全备份：每次对数据库进行完整的备份
差异备份：备份自从上次完全备份之后被修改过的文件
增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

 三、数据库备份操作

3.1、物理冷备份与恢复

tar命令直接打包数据库文件夹，备份前需要关闭MySQL服务。

• 备份数据库

创建一个/backup目录作为备份数据存储路径，使用tar创建备份文件。

 [root@mysql ~]# systemctl stop mysqld
 [root@mysql ~]# mkdir /backup
 [root@mysql ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
 tar: Removing leading `/' from member names
 [root@mysql ~]# ll /backup/
 total 728
 -rw-r--r-- 1 root root 741611 May 13 19:48 mysql_all-2022-11-30.tar.gz

mysql_all-$(date +%F).tar.gz为日期函数变量

• 恢复数据库

将数据库文件/usr/local/mysql/data/转移到bak目录下模拟故障。

 [root@mysql ~]# mkdir /bak
 [root@mysql ~]# mv /usr/local/mysql/data/ /bak/
 [root@mysql ~]# systemctl start mysqld
 Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

数据库已经损坏无法开启，执行以下操作可以从备份文件恢复数据。

创建restore目录并解压tar包，移动到/usr/local/mysql/ 下完成恢复，成功启动。

[root@mysql ~]# mkdir restore
[root@mysql ~]# tar zxf /backup/mysql_all-2022-11-30.tar.gz -C restore/
[root@mysql ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/
[root@mysql ~]# systemctl start mysqld
[root@mysql ~]# 

 

3.2、mysqldump备份与恢复

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

• 备份数据库

命令格式如下：

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

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

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

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

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

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

注：常用的选项包括“-u”，“-p”,分别用于指定数据库用户名，密码。

例1：备份mysql库中的user到当前路径名称为mysql-user.sql。

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

例2：备份数据库auth到当前路径名称为auth.sql。

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

例3：备份所有库到当前路径名称为all-data.sql，当导出的数据量较大的时候，添加”--opt“选项优化执行速度。

[root@mysql ~]# mysqldump -u root -p --all-databases --opt > all-data.sql
Enter password: 
[root@mysql ~]# 

• 查看备份文件

通过mysqldump工具导出的SQL脚本式文本文件，使用grep,less,cat等文本工具就可以查看脚本内容。

• 恢复数据库

命令格式如下：

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

使用mysqldump命令导出的SQL备份脚本，在需要恢复时可以通过mysql命令对其进行导出操作，当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

[root@mysql ~]# mysql -u root -p test < mysql-user.sql
 Enter password: 

不进mysql查询库添加"-e"选项。

[root@mysql ~]# mysql -u root -p -e 'show tables from test;'
 Enter password: 
 +----------------+
 | Tables_in_test |
 +----------------+
 | user           |
 +----------------+

若备份文件中已经包括完整的库信息，则执行导入操作时无需指定数据库名。

 [root@mysql ~]# mysql -u root -p -e 'drop database auth;'       //删除auth库，模拟故障
 Enter password: 
 [root@mysql ~]# mysql -u root -p -e 'show databases;'           //查看auth库是否存在
 Enter password: 
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | test               |
 +--------------------+        
 [root@mysql ~]# mysql -uroot -p < auth.sql                      //执行导入恢复操作
 Enter password: 
 [root@mysql ~]# mysql -u root -p -e 'show databases;'           //确认恢复后结果
 Enter password: 
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | auth               |
 | mysql              |
 | performance_schema |
 | test               |
 +--------------------+

3.3、MySQL增量备份与恢复

• 增量备份的特点

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

3.4、MySQL二进制日志对备份的意义

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

要进行MySQL的增量备份，首先要开启二进制日志功能。默认情况下，二进制日志文件的扩展名是一个六位的数字，如mysql-bin.000001。

 [root@mysql ~]# vim /etc/my.cnf 
 文本[mysqld]项下加入
 log-bin=/usr/local/mysql/mysql-bin
 保存退出：wq
 [root@mysql ~]# ll /usr/local/mysql/mysql-bin.*
 -rw-rw---- 1 mysql mysql 120 May 14 10:31 /usr/local/mysql/mysql-bin.000001
 -rw-rw---- 1 mysql mysql  34 May 14 10:31 /usr/local/mysql/mysql-bin.index

四、MySQL增量备份

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

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

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

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

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

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

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

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

两者的区别在于--stop-psition='操作id'

从开始到指定位置停止，--start-psition='操作id'从指定位置到结束。

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

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

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

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

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

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

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