mysql（四）数据备份

目录

前言

一、概述

二、备份的类型

（一）物理与逻辑角度 

（二）数据库备份策略角度

三、常见的备份方法

四、完整备份

（一）打包数据库文件备份

（二）备份工具备份

五、增量备份

六、操作

（一）完整备份

 （二）使用工具备份mysqldump 

 （三）增量修改

总结

---

---

前言

在生产环境下，备份数据是非常重要的，原因如下：

1. 数据保护: 备份数据可以提供保护机制，以防止由于硬件故障、人为错误、恶意攻击等原因导致的数据丢失。

2. 灾难恢复: 当发生自然灾害、系统崩溃、黑客攻击等灾难性事件时，备份数据可以用于快速恢复业务和数据，减少停机时间和损失。

3. 数据完整性: 有时数据可能会被意外修改、删除或被恶意篡改。备份数据可以用于验证或还原数据的完整性，确保业务的可靠性和准确性。

4. 合规性要求: 许多行业和法规对数据备份和保留有严格的要求，例如金融、医疗等领域。备份数据可以帮助机构满足合规性要求。

备份数据可以通过多种方式进行，包括：

1. 磁盘备份：将数据复制到物理磁盘或磁带上，可以通过快速恢复文件，但在灾难发生时可能会受到同一地点的影响。

2. 远程备份：将数据复制到位于不同地理位置的远程服务器上，以提供更高的可用性和灾难恢复能力。

3. 云备份：将数据备份到云存储提供商的服务器上，提供方便的存储和恢复选项。

数据丢失的原因有很多，包括但不限于以下几种：

1. 硬件故障：磁盘故障、服务器故障等硬件故障可能导致数据丢失。

2. 人为错误：误删除、误格式化、错误操作等人为因素可能导致数据不可恢复。

3. 黑客攻击：恶意攻击者可能通过病毒、勒索软件、数据泄露等方式导致数据丢失或遭到破坏。

4. 自然灾害：火灾、水灾、地震等自然灾害可能导致数据中心设备损坏或无法访问。

5. 软件故障：操作系统崩溃、应用程序故障等软件故障可能导致数据丢失。

综上所述，备份数据是为了保护数据安全和业务连续性的重要措施，在备份过程中应选择合适的备份方式，并且在恢复时要进行测试以确保备份数据的完整性和可用性。

---

一、概述

（一）数据库备份是指将数据库中的数据、表格、视图、存储过程、触发器等信息备份到另一个地方，以便在数据库丢失或损坏时进行恢复。数据库备份是数据库管理中必不可少的一项工作，通过备份可以保护数据库中的数据和业务

（二）数据备份的重要性：备份的主要目的是灾难恢复，备份还可以测试应用，回滚数据修改，查询历史数据，审计等。

（三）造成数据丢失的原因：
    程序错误
    人为错误
    运算失败
    磁盘故障
    灾难（如火灾、地震）和盗窃

二、备份的类型

（一）物理与逻辑角度 

（1）物理备份：
    对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。
    冷备份：是在关闭数据库的时候进行的
    热备份： 数据库处于运行状态，这种备份方法依赖于数据库的日志文件

（2）逻辑备份
    对数据库逻辑组件（如表等数据库对象）的备份，表时为逻辑数据库结构（create database、 create table等语句）和内容（insert语句或分割文本文件）的信息

（二）数据库备份策略角度

（1）完整备份
    完整备份指的是备份整个数据库的所有信息，包括数据库文件中所有的数据、表、视图、存储过程、触发器等。一般来说，完整备份是备份最全面、可靠的方式，但备份文件体积较大，备份时间也相对较长

（2）增量备份
    增量备份指的是在完整备份的基础上，每隔一定时间备份新增的数据或修改的数据。这种备份方式可以节省备份时间和备份文件的空间，但需要配合特定软件或命令进行

三、常见的备份方法

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

（2）使用专用备份工具
    mysqldump
    mysqlhotcopy

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

（4）第三方工具备份
    Percona XtraBackup是一个免费的MySQL热备份软件，支持在线备份innodb和XtraDB，也可以支持MySQL表备份。

（5）注意事项
    定期备份，指定备份计划，严格遵守
    除了完全备份之外，开启binlog日志功能很重要
    使用统一的、容易理解的备份名称，推荐使用库名或者表名加上时间的命名规则

四、完整备份

（一）打包数据库文件备份

    1）文件位置：
        源码包的位置/usr/local/mysql/data/
        rpm包的位置/var/lib/mysql
    2）备份：
        在数据库中创建数据表，并写入数据
        停止数据库服务
        创建备份目录
        按照特定格式对数据库目录进行备份
            tar  czf  mysql_all-$(date  +%F).tar.gz  /var/lib/mysql/*
    3）还原
        创建还原目录
        模拟元数据丢失
        将解压之后的数据还原值原目录
        登录查看

（二）备份工具备份

这里我们使用mysqldump进行备份

1）备份 ：
    对单个库进行完全备份
        mysqldump -u用户名 -p[密码] [选项] --databases [数据库名] > /备份路径/备份文件名
    对多个库进行完全备份
        mysqldump -u用户名 -p[密码] [选项] --databases 库名1 [库名2]…… > /备份路径/备份文件名
    对所有库进行完全备份
        mysqldump -u用户名 -p[密码] [选项] --opt --all-databases > /备份路径/备份文件名
    对表进行完全备份
        mysqldump -u用户名 -p[密码] [选项] 数据库名 表名 > /备份路径/备份文件名
    对表的结构进行备份
        mysqldump -u用户名 -p[密码] -d 数据库名 表名 > /备份路径/备份文件名

2）还原：

source和mysql
    source：登录mysql 数据库执行source  备份sql脚本路径
    mysql：
        mysql -u用户名 -p[密码] < 库备份脚本的路径
        mysql -u用户名 -p[密码] 库名 < 表备份脚本的路径

五、增量备份

（一）区别：使用完全备份时，备份与恢复的时间过长，增量备份就是备份自上一次备份之后增加的或改变的文件内容

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

2）MySQL二进制日志对备份的意义：
    二进制日志保存了所有更新或者可能更新数据库的操作。
    二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。

3）开启二进制日志备份功能：
    MySQL的配置文件的[mysqld]项中加入 log-bin=文件存放路径/文件前缀，如 log-bin=mysql-bin，然后重启mysqld服务。默认此配置存在。
    使用mysqld --log-bin=文件存放路径/文件前缀 重新启动mysqld服务每周选择服务器负载较轻的时间段，或者用户访问较少的时间段进行备份

（二）增量恢复的方法

1）应用场景：
    人为的SQL语句破坏了数据库
    在进行下一次全备之前发生系统故障导致数据库丢失
    在主从架构中，主库数据发生了故障，保证从库数据一致性

2）一般恢复：
    备份的二进制日志内容全部恢复
    格式： mysqldbinlog [--no-defaults] 增量备份文件 | mysql -u用户名 -p密码

3）基于时间点的恢复：
    便于跳过某个发生错误的时间点实现数据恢复
    格式：从日志开头截止到某个时间点的恢复：
    mysqlbinlog [--no-defaults] --stop-datetime=’年-月-日 小时：分钟：秒‘ 二进制日志 | mysql -u用户名 -p密码

4）从某个时间点到日志结尾的恢复：
    mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时：分钟：秒‘ 二进制日志 | mysql -u用户名 -p密码

5）从某个时间点到某个时间点的恢复：
    mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时：分钟：秒‘ --stop-datetime=’年-月-日 小时：分钟：秒‘ 二进制日志 | mysql -u用户名 -p密码

6）基于位置的恢复：
    可能在同一时间点既有错误的操作也有正确的操作，基于位置进行恢复更加准确
    mysqlbinlog --stop-position=‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码
    mysqlbinlog --start-position=‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码

六、操作

（一）完整备份

如图，我们已经拥有了一个简单的表格了

1）回到命令行，停止mysql服务，将原数据直接打包

2）模拟数据库数据丢失

 3）恢复数据

 （二）使用工具备份mysqldump 

MySQL自带的备份工具mysqldump，可以很方便的对MySQL进行备份。通过该命令工具可以将数据库、数据表或全部的库导出为SQL脚本，便于该命令在不同版本的MySQL服务器上使用。例如，当需要升级MySQL服务器时，可以先使用mysqldump命令将原有库信息导出，然后直接在升级后的MySQL服务器中导入即可。

1)对单个库进行完全备份

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

 验证：删除数据库test，使用mysqldump还原

 2）对多个库进行完全备份

格式：mysqldump -u用户名 -p[密码] [选项] --databases 库名1 [库名2]…… > /备份路径/备份文件名

 删除数据库hy ，test

 恢复并验证

3）对所有库进行完全备份

格式：mysqldump -u用户名 -p[密码] [选项] --opt --all-databases > /备份路径/备份文件名

4）对表进行完全备份

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

将数据库test中的表作业备份

5）对表结构的备份

格式：mysqldump -u用户名 -p[密码] -d 数据库名 表名 > /备份路径/备份文件名

增量恢复的方法

1. 一般的恢复：备份的二进制日志内容全部恢复

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

2. 基于时间点的恢复：便于跳过某个发生错误的时间点实现数据恢复

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

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

从某个时间点到日志结尾的恢复：

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

从某个时间点到某个时间点的恢复：

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

3. 基于位置的恢复：可能在同一时间点既有错误的操作也有正确的操作，基于位置进行恢复更加准确

格式：

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

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

 （三）增量修改

1）添加数据库、表、并录入数据

 先进行一次完全备份

 修改配置文件vim /etc/my.cnf 并重启服务使之生效

 进行一次日志回滚（生成新的二进制日志）mysqladmin -u用户 -p[密码] flush-logs

查看日志回滚mysqlbinlog -v /mysql_bak/mysql-bin.000002

 继续录入新的数据

 进行增量备份模拟误操作删除user_info 表

 恢复完全备份

 恢复增量备份

---

总结

在日常中备份是非常的重要的希望看完本篇内容能帮助到你。拜拜~~~~~