MySQL备份与还原

MySQL使用过程中，难免会出现不可抗力，这时候就体现了备份的重要性，MySQL备份在日常操作中是非常重要的，应该不会不知道备份是什么意思吧。

数据备份的类型有很多，有物理备份和逻辑备份

物理备份

物理备份是指直接复制数据库的物理文件，如数据文件、日志文件等。这种备份方式通常速度较快，适合大型数据库的快速恢复

冷备份

冷备份就是再关闭数据库的时候进行的

冷备份也被称为脱机备份，它是指在数据库关闭的情况下进行的备份操作，因此也被称为全备份。这种 备份方式的优点是简单全面且无需额外资源，但劣势就是必须在数据库脱机的情况下进行，这在生产环 境中往往是无法接受的。

热备份

热备份就是处于运行状态进行的

热备份也称为在线备份，这种备份在数据库运行（在线）状态下进行，可以提供24小时的服务，不会因为备份而影响业务的正常运行。这是一个比较复杂的过程，需要数据库的支持，并且备份过程中需要对每个数据文件申请开始和结束备份的操作，否则可能会备份到一些不一致的数据。热备份优点是可以在数据库运行过程中进行备份，备份的细粒度可以调控，对业务几乎无影响；缺点是复杂，消耗系统和数据库资源

温备份

数据库锁定表格的状态下进行备份操作

温备份介于冷备份和热备份之间。一般来讲，温备份是指数据库在非峰值时间进行的备份，这时数据库可能未关闭但流量较小。和冷备份与热备份相比，温备份的优点是可以供不停机的环境下用作备份，同 时也不会像热备份那样对在线服务产生太大影响；但和热备份相比，它无法提供24小时的全天候备份服务。当然，实际应用中这个概念使用得较少，通常用冷备份和热备份来区分备份操作。

逻辑备份

逻辑备份是对数据库逻辑组件的备份，表示为逻辑数据库结构，这种类型的备份适用于可以编辑数据值或表结构

完全备份

完整备份是指备份整个数据库的所有数据和结构。这种备份方式确保了备份的完整性和一致性，但同时也需要较多的时间和存储空间

但是这种备份策略是最简单和最可靠的，但需要占用大量的存储空间和时间，这种策略通常再首次备份或者数据量不大的情况下使用

差异备份

差异备份是指备份自上次完整备份以来发生变化的所有数据。这种备份方式减少了备份所需的时间和存储空间，但恢复时需要先恢复最近的完整备份，再恢复最近的差异备份。

差异备份仅备份自上次完整备份以来的变更数据，恢复时需要先恢复完整备份，再恢复差异备份，占用较少的存储空间，但备份时间相对较长。

增量备份

增量备份是指备份自上次备份（无论是完整备份、差异备份还是增量备份）以来发生变化的所有数据。这种备份方式进一步减少了备份所需的时间和存储空间，但恢复时需要按顺序恢复所有相关的增量备份。

增量备份进行数据恢复时需要按顺序恢复所有相关的增量备份，占用最少的存储空间，但备份和恢复过程较为复杂，如果有一个备份丢失或者损坏，那么可能导致无法完全恢复数据

几种备份方式的比较：

首先三个数据库分别采用三种备份方式，然后都进行第一次完全备份，备份了表1、表2

然后三个数据库都插入表三

此时，完全备份会备份表123，差异备份会备份表3，增量备份会备份表3

再在三个数据库分别插入表4进行备份

此时，完全备份依然是备份所有表（表1234），差异备份会备份表34，增量备份会备份表4

MySQL完全备份

MySQL完全备份确实是对整个数据库、数据库结构和文件结构的备份，它保存的是备份完成时刻的数据库状态。完全备份是差异备份与增量备份的基础，因为它提供了一个基准点，后续的差异或增量备份都是基于这个基准点进行的。

MySQL完全备份的优缺点

优点：

备份与恢复操作方便，完全备份提供了一个完整的数据集，因此在恢复时不需要考虑其他备份文件，只需恢复这个完全备份即可。恢复过程也相对简单，不需要复杂的步骤或按特定顺序恢复多个备份文件。

缺点：

由于每次完全备份都包含所有数据，因此如果数据变化不大，备份文件中会包含大量重复的数据。

完全备份需要存储整个数据库的数据，因此会占用较多的存储空间。

由于需要备份所有数据，完全备份的过程通常比差异备份或增量备份更耗时，同样，在恢复时也需要更多的时间来恢复整个数据库。

物理备份与恢复很简单，关闭数据库，使用tar命令直接打包数据库文件夹，直接替换现有的MySQL目录即可。

MySQL有自带的备份工具mysqldump，可以将指定的库、表导出为SQL脚本，使用命令导入备份的数据。

首先创建一个新的表，插入几条数据，来模拟我们的数据库

物理冷备份与恢复

systemctl stop mysqld

tar jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/                   ##压缩备份

mv /usr/local/mysql/data/ /opt/                   ##移除数据，模拟数据丢失

解压恢复

tar jxvf /opt/mysql_all_[压缩时的时间，自动补全就好].tar.xz -C /usr/local/mysql/data/

mysqldump备份与恢复

1.完全备份一个或多个完整的库

mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql

导出的文件就是数据库的脚本文件

例：

备份abc 和 xyz 两个数据库的结构和数据

mysqldump -u root -p --databases xyz abc > /opt/bak.sql

2.完全备份 MySQL 服务器中的所有库

mysqldump -u -p[密码] --all-database > /opt/all.sql

3.完全备份指定库中的部分表

如备份test 库中的abc 和 xyz

mysqldump -u root -p[密码]  test 123 abc > /opt/bak.sql

使用 -d 选项与不使用 -d 选项的区别

使用 -d ，说明只保存该数据库的表结构

不使用 -d ，表数据和结构都进行备份

4.查看备份文件

grep -v "^--" /opt/kgc_info1.sql | grep -v "^/" | grep -v "^$"

备份完之后需要进行恢复的时候，翻过来重定向

mysql -uroot -p[密码] [库名] < /opt/bak.sql  

如果是

mysql -u root -p[密码] -database [库名] >

表示针对school库下的所有表

增量备份与恢复

MySQL中增量备份的恢复也分为集中

1.一般恢复

将所有备份的二进制日志内容全部恢复

2.基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作，可以基于精准的位置跳过错误的操作，从发生错误的节点之前的一个节点开始，到上一个正确操作的节点停止

3.基于时间点恢复

跳过某个发生错误的时间点实现诗句恢复

想要进行增量备份，首先要开启二进制日志功能

vim /etc/my.cnf

[mysqld]

log-bin=mysql-bin

binlog_format = mixed

server-id = 1

二进制文件记录了数据库的所有更改操作，包括数据修改和结构修改

首先复制二进制日志文件到/opt下

cp /usr/local/mysql/data/mysql-bin.000002 /opt/

想要查看二进制日志文件需要用到工具 mysqlbinlog

mysqlbinlog --no-defaults /opt/mysql-bin.000002

解码按行显示内容，进行详细查看：

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

导出为文本文件：

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

一般恢复其实就是和MySQL完全备份恢复操作差不多，只不过不指定所有库

还可以基于mysql-bin.000002恢复

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

断点恢复

基于位置恢复

分为两个步骤，首先确认位置点，然后再基于位置恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.00000X

查看二进制文件，确认操作错误的步骤的位置

比如操作id为 500   700

那我们就需要恢复到操作id为 500 之前的数据

mysqlbinlog --no-defaults --stop-position='500' /opt/mysql-bin.00000X | mysql uroot -p

恢复700之后的数据

mysqlbinlog --no-defaults --start-position='700' /opt/mysql-bin.00000X | mysql uroot -p

基于时间点恢复

其实和基于位置恢复大同小异，只是将操作id改成了时间

比如恢复2024年9月1日17：00之前的数据

mysqlbinlog --no-defaults --stop-datetime='2024-09-01 17:00:00' /opt/mysql bin.00000X | mysql -uroot -p

同理，恢复某时间之后的数据就是将stop改成start