MySQL备份与恢复

引言：在生产环境中，数据的安全性至关重要，任何数据的丢失都可能产生严重的后果，那么对于数据的备份就将显的十分重要。对于MySQL而言，在其备份中，日志起到了很重要的作用

一、MySQL日志管理、备份与恢复

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

在备份、恢复中，日志起到了很重要的作用

MySQL日志默认保存在/usr/local/mysql/data

1，MySQL常用日志类型及开启

vim /etc/my.cnf
1

1.1错误日志

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

指定日志的保存位置和文件名
log-error=/usr/local/mysql/data/mysql_error.log
1
2

1.2通用查询日志

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

general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
1
2

1.3二进制日志(binlog)

用来记录所有MySQL启动、停止或运行时发送的错误信息，默认关闭

log-bin=mysql-bin				
或
log_bin=mysql-bin
1
2
3

1.4慢查询日志

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

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5	   		
1
2
3

1.5中继日志，

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

1.6日志配置

vim /etc/my.cnf

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

systemctl restart mysqld 
1
2
3
4
5
6
7

2.查看日志状态

variables 表示变量 like 表示模糊查询

xxx(字段)
xxx% 以xxx为开头的字段
%xxx 以xxx为结尾的字段
%xxx% 只要出现xxx字段都会显示出来
xxx	  精准查询
1
2
3
4
5

2.1查看通用查询日志是否开启

mysql -uroot -p
show variables like 'general%'
1
2

2.2查看二进制日志是否开启

show variables like 'log_bin';
1

2.3查看慢查询日志功能是否开启

如何知道需要索引优化，使用慢查询日志：生产环境中一般是10-15秒左右，

show variables like '%slow%';

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

在数据库中设置开启慢查询的方法
set global slow_query_log=ON	服务重启后失效
1
2
3
4
5
6
7
8
9

二、MySQL备份与恢复

1.数据备份的重要性

备份的主要目的就是灾难恢复，在生产环境中数据的安全性至关重要;任何数据的丢失都肯可能产生严重的后果

2.造成数据丢失的原因

①程序错误

②人为操作错误

③运算错误

④磁盘故障

⑤灾难(火灾、地震、盗窃等)

三、MySQL数据库备份类型

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

1.物理备份

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

①、冷备份(脱机备份)：是在关闭数据库的时候进行的（tar)

②、热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件(mysqlhotcopy mysqlbackup)

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

2.逻辑备份

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

从数据库的备份策略角度来看，备份可以分为完全备份、差异备份和增量备份

3.备份策略分类（完全备份、差异备份、增量备份）

从数据库的备份策略角度看，备份可以分为完全备份、差异备份和增量备份

3.1完全备份：

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

• 完全备份是对整个数据库、数据库结构和文件结构的备份
• 保存的是备份完成时刻的数据库
• 是差异备份与增量备份的基础
• 相当于基石

**优点：**备份与恢复操作简单

**缺点：**数据存在大量的重复
​ 占用大量的备份空间
​ 备份与恢复时间长

3.2差异备份：

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

每次差异备份，都会备份上一次完全备份之后的数据，可能会出现重复数据，恢复时，先恢复完全备份的数据，再恢复差异备份的数据

3.3增量备份：

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

每次增量备份都是备份在上一次完全备份或增量备份之后的数据，不会出现重复数据的情况，也不会占用额外的磁盘空间；

恢复数据，需要按照次序恢复完全备份和增量备份的数据

4.逻辑备份策略选择

合理值区间：

①、一周一次的全备，全备的时间需要在不提供业务的时间区间进行，22点至5点之间进行全备

②、3天/2天/1天一次增量备份

③、差异：选择特定的场景进行备份

④、一个处理（NFS）提供额外空间给mysql服务器用

5.常见的备份方法

5.1.物理冷备

①备份时数据库处于关闭状态，直接打包数据库文件

②备份速度快，恢复时也是最简单的

③关闭MySQL数据库

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

⑤直接替换现有的MySQL目录即可

5.2mysql专用备份工具

专用备份工具mysqldump或者mysqlhotcopy

mysqldumo工具：

mysqldumo常用的逻辑备份工具；
MySQL自带的备份工具，可实现对MySQL的备份
可以将指定的库，表导出为SQL脚本
使用命令mysql导入备份的库

**mysqlhotcpoy工具：**mysqlhotcpoy仅拥有备份myisam和archive表

**第三方工具备份：**免费的MySQL热备份软件Percona XtraBackup

5.3.启动二进制日志进行增量备份

进行增量备份需要刷新二进制日志

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

四、MySQL完全备份

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.idb(表数据文件)

是对整个数据库、数据库结构和文件结构的备份；备份内容是备份完成时的数据库；是差异备份与增备份的基础条件

**优点：**备份与恢复操作简单方便

**缺点：**数据存在大量重复、占用大量备份空间、备份与恢复时间长、不能灵活使用

备份前准备

 create table if not exists dump(id int(6),name varchar(10),age varchar(10),score varchar(10));
insert into dump values(1,'huazai',24,100);
insert into dump values(2,'xiaoc',23,88);
insert into dump values(3,'haiming',29,90);
insert into dump values(4,'laoyang',26,99);
insert into dump values(5,'xiaoqu',28,50);
select * from dump;

1
2
3
4
5
6
7
8

1.物理冷备份与恢复

systemctl stop mysqld.service 	关闭MySQL数据库
yum -y install xz				下载xz工具也可以直接使用tar命令打包
tar jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
tar jxf /opt/mysql_all_2022-06-21.tar.xz -C /usr/local/mysql
cd /
1
2
3
4
5

2.mysqldump备份与恢复

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

mysqldump -u用户名 -p[密码] --databases 库名1 库名2 … > /备份路径/备份名称.sql
													导出的就是数据

例：mysqldump -uroot -p123456 --databases shiwu > /opt/shiwu.sql
   mysqldump -uroot -p123456 --databases boss shiwu > /opt/boss_shiwu.sql
1
2
3
4
5

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

mysqldump -uroot -p[密码] --all-databases > /备份路径/备份文件名.sql

mysqldump -uroot -p123456 --all-databases > /opt/all.sql
1
2
3

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

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

mysqldump -uroot -p123456 

使用"-d"选项，只保存数据库的表结构，不使用"-d"选项，代表只保存数据库的表结构
1
2
3
4
5

2.4查看备份文件

grep -v "^--" /opt/shiwu_boy.sql | grep -v "^/" | grep -v "^$"
1

3.MySQL完全恢复

3.1恢复数据库两种方法

使用mysqldump导出的文件，可以使出导入的方法

source命令、mysql命令

-e调用mysql的应用程序

mysql -uroot -p123456 -e 'drop database shiwu;'
mysql -uroot -p -e 'show databases;'

mysql -uroot -p123456 < /opt/shiwu.sql
mysql -uroot -p123456 -e 'show databases;'
1
2
3
4
5
6
7

-e选项用于指定连接mysql后执行的命令，执行完成后自动退出，相当于linux命令中执行MySQL命令

3.2恢复数据表

mysqldump严格来说属于温备份，会需要对表进行写入的锁定

方法一：source恢复

方法二：

mysqldump -uroot -p --databases shiwu > /opt/shiw.sql
备份时需要加--databases,表示针对库shiwu
不加--databases时，表示针对库下所有表
1
2
3

五、增量备份与恢复

1.恢复的方式

MySQL数据库增量恢复有，一般恢复、基于位置恢复、基于时间点恢复

1.1一般恢复：

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

1.2基于位置恢复

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

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

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

1.3基于时间点恢复

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

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

2.开启二进制日志功能

vim /etc/my.conf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED		#可选，指定二进制日志(binlog)的记录格式为MIXED混合输入
server-id = 1				可加可不加该命令
#二进制日志有三种不同的记录格式：STATEMENT（基于SQL语句）、ROW（基于行）、MIXED（混合模式），默认为STATEMENT
#server-id 为服务的序号，在MySQL主备、高可用中需要指定服务的序号
#改为配置文件需要重启服务
systemctl restart mysqld
1
2
3
4
5
6
7
8
9

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

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

3.1 STAEMENT(基于SQL语句)

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

3.2 ROW(基于行)

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

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

3.3 MIXED 推荐使用

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

4.二进制日志读取

二进制文件不能直接查看，需要通过mysql命令对其进行转换

msyqlbinlog --no-defaults 二进制日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件
#--base64-output=decode-rows 使用64位编码机制去解码；decode按行读取rows
#-v 显示详细内容
#--no-defaults 默认字符集（不加会报UTF-8错误）
#将解码后的文件导出为txt文件，重定向输出

为了方便检阅，可以把转换后的二进制日志导出为txt格式。
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件 > /文件.txt

mysqladmin -uroot -p flush_logs	刷新日志，每刷新一次就会生成一次二进制文件日志
1
2
3
4
5
6
7
8
9
10
11

cp /usr/local/mysql/data/mysql-bin.000009 /opt/
mysqlbinlog --no-defaults /opt/mysql-bin.000009
1
2

4.1二进制日志中关注点

at:开始的位置点
end_log_pos:结束的位置
时间戳:220622 0:30:32
SQL语句
1
2
3
4

5.增量备份过程（全备+增备）

使用mysqldump

一般恢复

mysqlbinlog --no-defaults -v /opt/mysql-bin.000011 | mysql -uroot -p
1

节点恢复

数据库在某一时间点可能既有错误的操作也有正确的操作，可以基于精准的位置跳过错误的操作，发生错误节点指定的上一个节点，上一次正确操作的位置点停止；模拟节点恢复，刷新日志，生成新的日志文件

mysqlbinlog --no-defaults --stop-position='446' /opt/mysql-bin.000013 | mysql -uroot -p

基于时间点恢复

跳过某个发生错误的时间点实现数据恢复，在错误时间点停止，下一个正确时间开始；模拟时间点恢复，刷新日志，生成新的日志文件

备份日志文件，并查看

总结

本文讲述了MySQL数据库的数据内容进行备份、恢复；在生产环境中是必备的需求

1.在增量备份恢复时，要先从完全备份恢复，再到二进制日志1、日志2逐一恢复

2.如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或时间点

3.如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或时间点start

4.全备库中source针对库mysql针对库中的表，备份时使用–databases或者-B使恢复时source和mysql效果一致