数据库（mysql）之备份与恢复

芒地狠

已于 2022-09-21 21:45:16 修改

阅读量2.6k

点赞数 2

文章标签：数据库 mysql java

于 2022-09-18 01:00:00 首次发布

本文链接：https://blog.csdn.net/m0_71521555/article/details/126880669

版权

本文详细介绍了MySQL的各种日志类型及其作用，强调了数据备份的重要性，并详细讲解了物理备份（冷备份、热备份、温备份）与逻辑备份（全备、差异备、增量备）的策略及常用方法。重点讨论了MySQL的完全备份与恢复，包括物理冷备份与mysqldump工具的使用，以及增量备份和恢复的实现。最后，提出了周期性备份的方案，建议利用cron任务定期执行备份。

摘要由CSDN通过智能技术生成

4、slow query log（慢查询日志）☆

2、专用备份工具 mysqldump 或 mysqlhotcopy

2、mysqldump备份与恢复（温备份）

1、完全备份一个或多个完整的库（包含其中所有的表）

2、恢复一个库或多个库

3、加-databases和不加的区别实验

一、MySQL日志管理

备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。在备份、恢复中，日志起到了很重要的作用。

MySQL 的日志默认保存位置为**/usr/local/mysql/data**
MySQL 的日志配置文件为/etc/my.cnf，里面有个**[mysqld]**项。

1、日志类型与作用

1、redo （重做日志）

用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

达到事务一致性（每次重启都会重做）

作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行 redo log 执行重做，达到事务一致性。

2、undo（回滚日志）

作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复和读取已提交隔离级别就是通过mvcc+undo实现。

3、errorlog（错误日志）☆

作用：mysql本身启动，停止，运行期间发生的错误信息

4、slow query log（慢查询日志）☆

作用：记录执行时间过长的sql语句，时间默认值为10s，可以配置，只记录执行成功，超过设置时间的sql语句，都会被存在到慢查询日志中。

还可以提醒优化，对于查询慢的语句进行操作（比如是网络问题，还是未添加索引等）

5、bin log （二进制日志）☆☆

作用：用于基于日志形式的数据恢复。用于主从复制，实现主从同步，记录的内容是：数据库中执行的sql语句

6、relay log（中继日志）☆☆

作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放。

7、general log（普通日志）

记录数据库的操作明细，默认关闭，开启后会降低数据库的性能

2、数据备份的重要性

备份的主要目的是灾难恢复

在生产环境中，数据的安全性至关重要

任何数据的丢失都可能产生严重的后果

造成数据丢失的原因

① 程序错误

② 人为操作错误

③ 运算错误

④ 磁盘故障

⑤ 灾难(如火灾、地震)和盗窃

3、日志配置文件

#修改my.cnf配置文件
vim /etc/my.cnf
#错误日志
log-error=/usr/local/mysql/data/mysql_error.log
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志
log-bin=mysql-bin
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
#重新mysql服务
systemctl restart mysqld.service

当我们重启了myslq服务之后，会在数据库目录下，生成以下日志文件

索引文件：记录更新语句
索引文件刷新方式：
1、重启mysql的时候会更新索引文件，用于记录新的更新语句
2、刷新二进制日志

4、日志配置文件的查询

#登入mysql
mysql -u root -p[密码]

#查看通用查询日志是否开启
show variables like 'general%';

#查看二进制日志是否开启
show variables like 'log_bin%';

#查看慢查询日功能是否开启
show variables like '%slow%';

#查看慢查询时间设置
show variables like 'long_query_time';

#在数据库中设置开启慢查询的方法
set global slow_query_log=ON;

二、备份的类型

数据库备份可以分为物理备份和逻辑备份

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

1、物理备份

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

1、冷备份（脱机备份）

是在关闭数据库的时候进行的备份，可以直接（tar）当正常关闭时会提供给我们一个完整的数据库。对于备份Oracle信息而言，冷备份是最快和最安全的方法。

冷备份的优点

使用tar命令直接打包数据库文件夹

直接替换现有mysql目录即可（/usr/local/mysql/data 数据目录）

是非常快速的备份方法（只需拷文件）

轻易归档（简单拷贝即可）

轻易恢复到某个时间点上（只需将文件再拷贝回去）

能与归档方法相结合，做数据库“最佳状态”的恢复。

低度维护，高度安全。

冷备份也有如下不足

① 单独使用时，只能提供到“某一时间点上”的恢复。
② 再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
③ 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。
④ 不能按表或按用户恢复。

假如可能的话（主要看效率），应将信息备份到磁盘上，然后启动数据库（使用户可以工作）并将备份的信息拷贝到磁带上（拷贝的同时，数据库也可以工作）。冷备份中必须拷贝的文件包括：
１、所有数据文件
２、所有控制文件
３、所有联机REDO LOG文件
４、 Init.ora文件（可选）

值得注重的使冷备份必须在数据库关闭的情况下进行，当数据库处于打开状态时，执行数据库文件系统备份是无效的。

2、热备份 (联机备份)

数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）

3、温备份

数据库锁定表格(不可写入但可读)的状态下进行备份操作（mysqldump）

物理备份的优点及使用场景：

优点：速度快，效率高。

场景：可用于停机维护及数据库物理迁移场景中。

2、逻辑备份 ☆☆

逻辑备份是对数据库逻辑组件的备份.表示为逻辑数据库结构
这种类型的备份适用于可以编辑数据值或表结构
从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份

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

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

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

备份策略的比较