【小菜鸟之---MySQL基础-数据备份】

1【数据库备份类型】backup

热备
热备份可以在数据库运行中直接备份，对正在运行的数据库操作没有任何的影响，数
据库的读写操作可以正常执行。这种方式在 MySQL 官方手册中称为 Online Backup
（在线备份）。

冷备
冷备份必须在数据库停止的情况下进行备份，数据库的读写操作不能执行。这种备份
最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在 MySQL 官方手
册中称为 Offline Backup（离线备份）。

温备
温备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，备份时
仅支持读操作，不支持写操作。

按照备份后文件的内容，热备份又可以分为：

    ***逻辑备份*** 

​    在 MySQL 数据库中，逻辑备份是指备份出的文件内容是可读的，一般是文本内容。内容一般是由一条条 SQL 语句，或者是表内实际数据组成。如 mysqldump 和SELECT * INTO OUTFILE 的方法。这类方法的好处是可以观察导出文件的内容，一般适用于数据库的升级、迁移等工作。但其缺点是恢复的时间较长。

	***裸文件备份***

​    裸文件备份是指复制数据库的物理文件，既可以在数据库运行中进行复制（如ibbackup、xtrabackup 这类工具），也可以在数据库停止运行时直接复制数据文件。这类备份的恢复时间往往比逻辑备份短很多。

​    对于跨平台迁移，逻辑备份通常比裸文件备份更方便。这是因为逻辑备份将数据以可读的格式进行备份，可以在不同操作系统和数据库软件之间进行转换和导入。而裸文件备份只能在相同类型的操作系统和数据库软件上进行恢复，如果需要跨平台迁移，就需要先将数据进行转换，才能进行导入。因此，在跨平台迁移时，推荐使用逻辑备份。

按照备份数据库的内容来分，备份又可以分为：

    ***完全备份***
    
	完全备份是指对数据库进行一个完整的备份，即备份整个数据库，如果数据较多会占 用较大的时间和空间。 部分备份是指备份部分数据库（例如，只备份一个表）
	
	***部分备份***
	
	部分备份又分为：
    	增量备份
    	差异备份

​    增量备份需要使用专业的备份工具。也就是说每次备份只会备份自上次备份之后到这次备份的时间之内产生的数据。 因此每次备份都比差异备份节约空间，但是恢复数据麻烦。

​    差异备份指的是自上一次完全备份以来变化的数据。和增量备份相比，浪费空间，但恢复数据比增量备份简单。    

​    也就是说，增量备份是，只要数据与上次备份有修改或增加或删除就备份，差异备份是上一次全备份到这次备份之间产生变化就备份

MySQL 中进行不同方式的备份还要考虑存储引擎是否支持，如 MyISAM 不支持热 备，支持温备和冷备。而 InnoDB 支持热备、温备和冷备。

**一般情况下，我们需要备份的数据分为以下几种：**

* 表数据
* 二进制日志、InnoDB
* 事务日志
* 代码（存储过程、存储函数、触发器、事件调度器）
* 服务器配置文件

建议的恢复策略：
完全+增量+二进制日志
完全+差异+二进制日志

常用的备份工具

mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备。

cp、tar 等归档复制工具：物理备份工具，适用于所有的存储引擎、冷备、完全备份、部分备份。
lvm2 snapshot：借助文件系统管理工具进行备份。
mysqlhotcopy：名不副实的一个工具，仅支持 MyISAM 存储引擎。
xtrabackup：一款由 percona 提供的非常强大的 InnoDB/XtraDB 热备工具，支持完全备份、增量备份。

2【冷备份以及恢复（完全备份）】

1、先看通用查询日志看是否开启

SHOW VARIABLES LIKE '%general%';

显示OFF没有开启

2、开启通用查询日志

 SET GLOBAL general_log=1;

3、mkdir /backup/ #创建备份目录

4、#打包数据库文件。/var/lib/mysql为数据库文件存放目录

 tar -zcvf /backup/mysql_all$(date +%F-%m-%S).tar.zip *

5、查看是否压缩成功

6、开启mysql服务，去删掉指定的数据库

查看数据库 show databases

删除数据库 drop database test

7、停掉mysql服务，将之前打包的数据库文件压缩包解压到/var/lib/mysql

systemctl stop mysqld 

8、开启mysql服务，查看Class_Linux数据库是否恢复成功

systemctl restart mysql

恢复成功

3【mysqldump备份（热备份的逻辑备份）（完全备份）】

1. 备份整个数据库：

bash
mysqldump -u [用户名] -p [数据库名] > [备份文件名].sql

mysqldump -u root -p mydatabase > /iau/test.sql

注意：在linux界面执行，不是在mysql环境下

这个命令会备份整个数据库，包括结构和数据，并将备份内容输出到指定的 SQL 文件中。执行命令后会要求输入密码。

1. 备份指定表：

bash
mysqldump -u [用户名] -p [数据库名] [表名] > [备份文件名].sql

mysqldump -u root -p mydatabase mytable > mytable_backup.sql

mysqldump -u root -p [-d] kgc info1 info2 > /opt/kgc_info1.sql
#使用“-d”选项，说明只保存数据库的表结构
#不使用“-d”选项，说明表数据也进行备份

这个命令会备份指定数据库中的特定表，包括结构和数据，并将备份内容输出到指定的 SQL 文件中。执行命令后会要求输入密码。

1. 备份整个数据库并压缩：

bash
mysqldump -u [用户名] -p [数据库名] | gzip > [备份文件名].sql.gz

mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

​ 这个命令会备份整个数据库并使用 gzip 进行压缩，将备份内容输出到指定的 gzip 文件中。执行命令后会要求输入密码。

2.查看备份文件

vim test.sql

3、登录mysql，删除数据库

show databases;
drop database test;
show databases;

4、导入

创建数据库 test
create database test;

进入数据库
use test;

导入
source /iau/test.sql;

4【增量备份】

MySQL没有提供直接的增量备份方法，可通过MySQL提供的二进制日志间接实现增量备份。

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

1. 增量备份首先开启mysql数据的二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
#指定二进制日志(binlog)的记录格式为 MIXED
binlog_format = MIXED				
server-id = 1

​

​ 开启二进制日志文件后，每一次重启数据库或是使用命令后，都会在默认的日志路径中生成一个日志文件（上图日志文件路径为 /var/lib/mysql）

​ 刷新命令

mysqladmin -u root -p flush-logs

	是一个用于 MySQL 数据库的命令，它用于刷新二进制日志文件并创建一个新的二进制日志文件。这个命令通常用于在备份数据库时，切换到一个新的二进制日志文件，以确保备份是在一个一致的状态下进行的。

二进制日志(binlog)有3种不同的记录格式：

• STATEMENT(基于SQL语句)
• ROW(基于行)
• MIXED(混合模式)，默认格式是STATEMENT

先进行完全备份，为恢复数据做准备
mysqldump -u root -p mydatabase > /iau/test.sql

2.删除一条数据，模拟数据变更

select * from yonghu
 delete from yonghu where id = ' 1685279379920';

3.文件查看

​ 二进制文件使用一般的编辑器是无法查看的，想要查看二进制文件我们需要使用命令

mysqlbinlog --no-defaults --base64-output=decode-rows -v /文件路径
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v /var/lib/mysql/binlog.000005

4.备份恢复

• 增量备份的恢复方法有三种
  
  * 一般恢复
  * 基于位置
  * 基于时间
  

  刚刚已经删除了数据并且产生了二进制文件，现在进行恢复。

4.1一般恢复

使用命令恢复数据

mysqlbinlog --no-defaults /路径/二进制文件名 | mysql -u root -p

#mysqlbinlog --no-defaults /var/lib/mysql/binlog.000005 | mysql -u root -p

4.2 基于位置

基于位置的恢复是通过日志文件中的位置码来定位

基于位置恢复的命令

mysqlbinlog --no-defaults --start-position='XXX'      从某一个位置点开始恢复，一直到日志结尾
mysqlbinlog --no-defaults --stop-position='XXX'      从日志开头，一直恢复到某一个位置点前结束
mysqlbinlog --no-defaults --start-position='XXX' --stop-position='XXX'    从某一个位置点开始恢复，一直到某一个位置点前结束

 # mysqlbinlog -v /var/lib/mysql/binlog.000005 --start-position='312' --stop-position='527' |mysql -uroot -p

4.3基于时间

【开始时间可以忽略，因为默认就是日志最开始的时间】
--start-datetime=           从某一个时间点开始恢复，一直到日志结尾
--stop-datetime=            从日志开头，一直恢复到某一个时间点前结束

# mysqlbinlog -v /var/lib/mysql/binlog.000005 --start-datetime='2024-04-13  0:03:53' --stop-datetime='2024-04-13  0:03:53' |mysql -uroot -p  

4.4 事项

“ERROR 1032 (HY000) at line 19: Can’t find record in
‘yonghu’’”这个错误，是因为 mysqlbinlog xxx | mysql -uroot xxxxx
这个命令是执行mysqlbinlog里的sql的，因为你删了一条数据，再次执行时就会再删一次这个数据，就会报记录不存在了。

正确恢复数据的操作应该是：先将数据库数据全恢复到上次备份时候，再mysqlbinlog执行从上次备份到现在的所有sql