Mysql 之完全备份+增量备份+备份恢复

水深无声 2023-06-28 06:11 120阅读 0赞

### 文章目录 ###

*   *  一、数据备份的重要性
     *  二、数据库备份的分类
     *   *  2.1、从物理与逻辑的角度
         *  2.2、从数据库的备份策略角度
     *  三、完全备份
     *   *  3.1、完全备份的优点
         *  3.2、完全备份的缺点
     *  四、mysqldump备份库
     *   *  4.1、备份单个库
         *  4.2、备份多个库
         *  4.3、备份所有库
         *  4.4、mysqldump备份表
     *  五、数据库的恢复
     *   *  5.1、source命令恢复
         *  5.2、mysql命令恢复
     *  六、增量备份
     *   *  6.1、增量备份的优点
         *  6.2、增量备份的缺点
         *  6.3、增量备份的方法
         *  6.4、增量备份的恢复

## 一、数据备份的重要性 ##

*  在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果
 *  造成数据丢失的原因：
    
     *  程序错误
     *  人为错误
     *  计算机失败
     *  磁盘失败
     *  灾难(如火灾、地震)和偷窃

## 二、数据库备份的分类 ##

### 2.1、从物理与逻辑的角度 ###

**备份可分为：**

*  **物理备份**：对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。
    
     *  **物理备份**又可以分为脱机备份(**冷备份**)和联机备份(**热备份**)。
        
         *  冷备份:是在关闭数据库的时候进行的。
         *  热备份:数据库处于运行状态，这种备份方法依赖于数据库的日志文件。
 *  **逻辑备份**：对数据库逻辑组件(如表等数据库对象)的备份。

### 2.2、从数据库的备份策略角度 ###

**备份可分为：**

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

## 三、完全备份 ##

*  完全备份是对整个数据库的备份、数据库结构和文件结构的备份
 *  完全备份保存的是备份完成时刻的数据库
 *  完全备份是增量备份的基础

### 3.1、完全备份的优点 ###

*  备份与恢复操作简单方便。

### 3.2、完全备份的缺点 ###

*  数据存在大量的重复。
 *  占用大量的备份空间。
 *  备份与恢复时间长。

## 四、mysqldump备份库 ##

*  MySQL数据库的备份可以采用用多种方式
    
     *  直接打包数据库文件夹，如/usr/ocal/mysql/data
     *  使用专用备份工具mysqldump
 *  mysqldump命令
    
     *  MySQL自带的备份工具，相当方便对MySQl进行备份
     *  通过该命令工具可以将指定的库、表或全部的库导出为SQL脚本，在需要恢复时可进行数据恢复

### 4.1、备份单个库 ###

*  mysqldump命令对**单个库进行完全备份**

mysqldump -u 用户名 -p[密码] [选项] [数据库名] > /备份路径/备份文件名

*  单库备份例子

mysqldump -u root -pabc123 student > /opt/student.sql

### 4.2、备份多个库 ###

*  mysqldump命令对**多个库进行完全备份**

mysqldump -U用户名-P [密码] [选项] --databases 库名1 [库名2]... >
    /备份路径/备份文件名

*  多库备份例子

mysqldump -u root -pabc123 --databases student mysql > /opt/db_student_mysql.sql

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70]

### 4.3、备份所有库 ###

*  对所有库进行完全备份

mysqldump -u 用户名 -p[密码] [选项] -all-atabases > /备份路径/备份文件名

*  所有库备份例子

mysqldump -uroot -pabc123 --opt --all-databases > /opt/all.sql

![在这里插入图片描述][20200107194844340.png]

### 4.4、mysqldump备份表 ###

*  在实际生产环境中，存在对某个特定表的维护操作，此时 mysqldump 同样发挥重大作用
 *  **使用mysqldump备份表的操作**

mysqldump -u用户名 -p[密码] [选项]数据库名 表名 > /备份路径/备份文件名

*  备份表的例子

mysqldump -uroot -pabc123 student user > /backup/mysql-user.sql

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 1]  
**只备份表的结构**

mysqldump -u用户名 -p[密码] -d 数据库名 表名 > /备份路径/备份文件名   
    例：
    mysqldump -uroot -pabc123 -d student stu01 > /opt/student_stu01.sql

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 2]

## 五、数据库的恢复 ##

*  **使用mysqldump命令导出的SQL备份脚本，在进行数据恢复时可使用以下方法导入**
    
     *  source命令
     *  mysql命令

### 5.1、source命令恢复 ###

*  **使用source恢复数据库的步骤**
    
     *  登录到MySQL数据库
     *  执行source 备份sql脚本的路径

**注意：当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在。**

**模拟环境：把数据库student的stu01表删除**

#登录数据库
    use student;
    drop table stu01;

**source恢复**

格式：source 表备份的绝对路径;
    mysql> source /opt/student_stu01.sql;   #注意：恢复的是绝对路径

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 3]

### 5.2、mysql命令恢复 ###

格式：
    mysql -u用户名 -p[密码] 库名 < 表备份脚本的路径
    例：
    mysql -uroot -pabc123 student < /opt/student_stu01.sql

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 4]

## 六、增量备份 ##

*  使用mysqldump进行完全备份的存在的问题
    
     *  备份数据中有重复数据
     *  备份时间与恢复时间长
 *  **增量备份就是备份自上一次备份之后增加或变化的文件或者内容**

### 6.1、增量备份的优点 ###

*  没有重复的备份数据
 *  备份的数据量不大
 *  备份所需的时间也很短

### 6.2、增量备份的缺点 ###

*  备份恢复比较麻烦，需要用上次完全备份及完全备份之后所有的增量备份才能恢复。
 *  要对所有增量备份进行逐个反推恢复。

### 6.3、增量备份的方法 ###

*  **MySQL没有提供直接的增量备份方法**
 *  **可以通过MySQL提供的二进制日志 (binary logs) 间接实现增量备份**
 *  **MySQL二进制日志对备份的意义**
    
     *  二进制日志保存了所有更新或者可能更新数据库的操作
     *  二进制日志在启动MySQL服务器后开始记录，并在文件达到max\_binlog\_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
     *  只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份

### 6.4、增量备份的恢复 ###

1、增量备份依赖二进制日志文件，开启MySQL的二进制日志功能

vim /etc/my.cnf
    在配置文件里插入一行代码 log-bin=mysql-bin
    systemctl restart mysqld #重启服务

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 5]  
2、重启服务后，创建新的数据库内容，在student库中创建info表，**完全备份**数据库student。  
在 /usr/local/mysql/data/目录下生成一个日志文件 mysql-bin.000001  
![在这里插入图片描述][20200108111808133.png]  
3、**刷新所有日志**后生成一个新日志文件即 **增量备份日志文件** mysql-bin.000002 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 6]  
4、修改一些数据。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 7]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 8]  
5、查看日志文件，记录错误操作的位置和时间点

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt

6、模拟故障：登录数据库，删除 student 库中的数据表 info 。  
**7、恢复：先恢复完全备份（ 用 source、mysql命令），再用以下的三种方法之一恢复增量备份部分。**

*  **一般恢复**  
    ![在这里插入图片描述][20200107185516325.png]

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

*  **基于位置恢复**  
    就是将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 9]

1、恢复数据到指定位置。

格式：mysqlbinlog --stop-position='操作id' 二进制日志 | mysql -u用户名 -p密码
    mysqlbinlog --no-defaults --stop-position='1131' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p

2、从指定的位置开始恢复数据。

格式：mysqlbinlog --start-position='操作id' 二进制日志 | mysql -u用户名 -p密码
    mysqlbinlog --no-defaults --start-position='1236' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p

*  **基于时间点恢复**  
    使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，所以我们需要一种更为精确的恢复方式。

**通过查看日志文件，得到恢复时间点**
    
    200108 11:12:55 --stop datetime    #错误操作时间点
    200108 11:13:27 --start-datetime   #下一条正确操作时间点

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

格式：mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u用户名-p密码
    mysqlbinlog --no-defaults --stop-datetime='2020-01-08 11:12:55' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p

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

格式：mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u用户名-p密码 mysqlbinlog --no-defaults --start-datetime='2020-01-08 11:13:27' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200107194555735.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[20200107194844340.png]: https://img-blog.csdnimg.cn/20200107194844340.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200107200614314.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200108082056260.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200108085446743.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2020010809020977.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200108093905345.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[20200108111808133.png]: https://img-blog.csdnimg.cn/20200108111808133.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/2020010810344494.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/2020010811232712.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/2020010811303322.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[20200107185516325.png]: https://img-blog.csdnimg.cn/20200107185516325.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200108134925616.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70