目录

一、MySQL日志管理

1、日志的分类

1.1、错误日志

1.2、通用查询日志

1.3、二进制日志

1.4、中继日志

1.5、慢查询日志

1.6、配置日志

2、日志查询

二、数据备份的重要性

三、MySQL数据备份类型

1、物理备份

2、逻辑备份

四、常用的备份方法

1、物理冷备

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

2.1、完全备份一个或多个完整的库 (包括其中所有的表)

五、MySQL增量备份与恢复

1、恢复的方式

1.1、一般恢复

1.2、基于位置恢复

1.3、基于时间点恢复

2、二进制文件介绍

1、开启二进制日志功能

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

3、查看二进制日志文件的内容

4、增量备份过程（全备+增备）

5、增量恢复 

6、节点恢复

7、基于时间点恢复

六、总结

---

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

一、MySQL日志管理

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

1、日志的分类

1.1、错误日志

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

vim /etc/my.cnf
log-error=/usr/local/mysql/data/mysql_error.log	 #指定日志的保存位置和文件名

1.2、通用查询日志

用来记录MySQL的所有连接和语句，默认是关闭的

vim /etc/my.cnf
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

1.3、二进制日志

用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启

vim /etc/my.cnf
 
log-bin=mysql-bin
或
log_bin=mysql-bin				

1.4、中继日志

 一般情况下它在Mysql主从同步（复制）、读写分离集群的节点开始。主节点一般不需要这个日志。

1.5、慢查询日志

用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的。

vim /etc/my.cnf
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5	

1.6、配置日志

1. #修改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
 
2. #重新mysql服务
systemctl restart mysqld.service

2、日志查询

登入MySQL数据库，查询日志是否开启

#登入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;									

 查询通用日志是否开启

show variables like ‘general%’;

查询二进制日志是否开启

show variables like ‘log_bin%’;

查看慢查询日志是否开启

show variables like ‘long_query_time’;

 查询慢查询日志超时时间

show variables like ‘long%’;

二、数据备份的重要性

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

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

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

造成数据丢失的原因

1. 程序错误
2. 人为操作错误
3. 运算错误
4. 磁盘故障
5. 灾难(如火灾、地震)和盗窃

三、MySQL数据备份类型

在生产环境中，数据的安全性至关重要，任何数据的丢失都可能产生严重的后果，数据备份的主要目的是灾难恢复
数据备份分为物理备份和逻辑备份两类

1、物理备份

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

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

• 冷备份（脱机备份）：是在关闭数据库的时候进行的（tar）
• 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）
• 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作（mysqldump）

2、逻辑备份

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

1、完全备份
每次都进行完全备份，会导致文件占用空间巨大，并且有大量重复数据，恢复时，直接使用完全备份的文件即可
优点：备份与恢复操作简单方便
缺点：数据存在大量的重复、占用大量的备份空间及备份与恢复时间长
2、差异备份
每次差异备份，都会备份上一次完全备份之后的数据，可能会出现备份重复数据，导致占用额外的磁盘空间，恢复时，先恢复完全备份的数据，再恢复差异备份的数据
3、增量备份
每次增量备份都是在上一次完全备份或者增量备份之后的数据，不会出现备份重复数据的情况，也不会占用额外的磁盘空间。恢复数据时，需要按照次序恢复完全备份和增量备份的数据

四、常用的备份方法

1、物理冷备

备份时，数据库处于关闭状态，直接打包数据库文件，备份速度快，恢复时也是最简单的

systemctl stop mysqld
yum -y install xz
 
#压缩备份
cd /usr/local/mysql/data
tar jcvf mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
systemctl start mysqld
 
#模拟故障，删除数据库
drop database kgc;
 
#解压恢复
tar jxvf /opt/mysql_all_2022-06-21.tar.xz -C /usr/local/mysql/data
cd /usr/local/mysql/data
mv usr/local/mysql/data/* ./

 开启服务

模拟故障，删除库school

 恢复数据

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

常用的工具有
mysqldump 常用的逻辑备份工具
mysqlhotcopy 仅拥有备份 MyISAM和ARCHIVE表
使用mysqldump工具备份一个或多个完整的库（包括其中的表）

#备份一个或多个库
mysqldump -u root -p --databases 库名1 库名2 > 备份路径/文件名.sql
#备份所有库（完全备份）
mysqldump -u root -p --all-databases > 备份路径/文件名.sql
#备份的文件名后缀必须为.sql
#备份一个库中的一个或多个表
mysqldump -u root -p --databases 库名 表名1 表名2 > 备份路径.sql

2.1、完全备份一个或多个完整的库 (包括其中所有的表)

mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql   
#导出的就是数据库脚本文件
 
例:
 
mysqldump -u root -p --databases test > /opt/testw.sql       #备份一个kgc库
mysqldump -u root -p --databases test liy > /opt/mysql-test.sql    #备份test的 liy表

完全备份

mysqldump -u root -p --all-databases > /opt/mysql_all.sql
备份数据库中的所有库文件

完全备份恢复
方法一：

#在数据库中，使用source恢复数据
mysql> source /opt/mysql_all.sql

删除表与表内内容

 进行完全备份恢复

 查看数据是否恢复

备份test 库

方法二：

#完全恢复
mysql -uroot -p < 备份文件路径
#恢复指定库的指定表
mysql -uroot -p 库名 表名1 表名2 < 备份文件路径

 mysqldump -uroot -p --databases test > /opt/mysql_test.sql
当备份时加 --databases，表示针对库test
当备份时不加–databases时，表示针对test库下的所有表

备份数据表结构

mysqldump -u root -p -d test liy > /opt/xy_bank.sql
#-d 选项，说明只保留表的结构，不保留数据
#不加-d，则连同数据一起备份

也可以打开备份文件查看

 备份test库中的liy表 

 备份test库中liy表的数据结构 

在生产环境中，可以使用Shell脚本自动实现定时备份（时间频率需要确认）

0 1* * 7 /usr/local/mysql/bin/mysqldump -uroot -p123456 kgc info1 > ./kgc_infol_$(date +%Y%m%d).sql ;
/usr/local/mysql/bin/mysqladmin -u root -p flush-logs

 小结：

在数据恢复的时候建议使用source，简单方便
mysqldump工具属于温备份，在其进行数据备份的时候，会进行锁表，禁止写入
在备份库的时候要使用–databases

五、MySQL增量备份与恢复

1、恢复的方式

1.1、一般恢复

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

1.2、基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作

可以基于精准的位置跳过错误的操作

发生错误节点之前的一个节点，上一次正确操作的位置点停止

1.3、基于时间点恢复

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

在错误时间点停止，在下一个正确时间点开始

2、二进制文件介绍

1、开启二进制日志功能

vim /etc/my.conf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED        
#可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入）
server-id = 1
 

 重启服务

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

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

STATEMENT（基于SQL语句）

每一条涉及到被修改的sql 都会记录在binlog中

缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题

ROW(基于行)

只记录变动的记录，不记录sql的上下文环境

缺点:如果遇到update......set....where true 那么binlog的数据量会越来越大

MIXED 推荐使用

一般的语句使用statement，函数使用ROW方式存储。

3、查看二进制日志文件的内容

二进制文件无法直接编辑查看，需要对其进行转换

cp /usr/local/mysql/data/mysql-bin.000002 /opt/
 
mysqlbinlog --no-defaults  /opt/mysql-bin.000002
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
 
#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
#-v: 显示详细内容
#--no-defaults : 默认字符集（不加会报UTF-8的错误）
PS: 可以将解码后的文件导出为txt格式，方便查阅
 
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin.000002

二进制日志中需要关注的部分 

at ：开始的位置点
end_log_pos：结束的位置
时间戳： 210712 11:50:30
SQL语句

4、增量备份过程（全备+增备）

使用mysqldump

可每天进行增量备份操作，生成新的二进制日志文件(例如:mysql-bin.000003)
 
mysqladmin -u root -p flush-logs

插入新数据，模拟数据的增加或变更

在第一次完全备份之后刷新二进制文件，在第二个二进制文件中记载着"增量备份的数据

在test库中创建表bin,并插入数据

mysql -uroot -p
show databases;
use test;
 
create table bin(id int(10),name varchar(10));

创建info库，并复制test库中的bin表，命名为zzb表

再次生成新的二进制日志文件(例如:mysql-bin.000004)
 
mysqladmin -u root -p flush-logs
 
#之前的步骤创建test库中的bin表和创建info库以及库中的zzb表的操作会保存到mysql-bin.000004文件中

5、增量恢复 

 全部恢复
模拟所有数据丢失，直接删除test和info两个库

 先恢复完全备份 

 使用日志文件对数据进行恢复

6、节点恢复

数据库在某一时间点可能既有错误的操作也有正确的操作，可以基于精准的位置跳过错误的操作
发生错误节点指点的上一个节点，上一次正确操作的位置点停止
模拟节点恢复，刷新日志，生成新的日志文件
 
 
基于位置点恢复
#仅恢复到操作 ID 为“623"之前的数据，即不恢复"user4"的数据
 
mysqlbinlog --no-defaults --stop-position='1793' /opt/mysql-bin.000002 | mysql -uroot -p密码
#仅恢复"user4"的数据，跳过"user3"的数据恢复
 
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p
mysqlbinlog --no-defaults --start-position='400' --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p      
#恢复从位置为400开始到位置为623为止

对数据库进行操作

 基于节点进行数据恢复
查看日志文件

恢复数据

mysqlbinlog --no-defaults --stop-position='433' /opt/mysql-bin.000007 |mysql -uroot -p
#--stop-position='433'    恢复到433节点之前
#--start-position='433'   从节点433开始

7、基于时间点恢复

跳过某个发生错误的时间点实现数据恢复
在错误时间点停止，在下一个正确时间点开始
 
模拟时间点恢复，刷新日志，生成新的日志文件
 
 
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
 
#仅恢复"user4"的数据，跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-2216:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
 
如果恢复某条SQL语之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

修改表内数据

备份日志文件，并查看

根据时间恢复数据内容

mysqlbinlog --no-defaults --stop-datetime='2022-06-22 23:20:51' /opt/mysql-bin.000008 |mysql -uroot -p
 
 
mysql -uroot -p -e 'select * from test.bin;'

六、总结

在增量备份恢复时，要先从完全备份恢复，再到二进制日志1、日志2…逐一恢复，如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点，如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start，全备库中source针对库mysql针对库中的表，备份时使用--database或者-B使恢复时source和mysql效果一致。