关系型数据库mysql（6）备份与恢复

一.数据备份的重要性

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

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

（3）造成数据丢失的原因

• 程序错误
• 人为操作失误
• 运算错误
• 磁盘故障
• 灾难（如火灾，地震）和盗窃

扩展：容灾

容灾系统是指，在相隔较远的异地，建立两套或者多套功能相同的IT系统，相互之间可以进行健康状态的监控和功能切换，当一处系统因为意外（如，火灾、地震、服务器人为破坏等）停止工作后，整个应用系统可以切换到另一处，使得该系统功能可以继续正常工作。

二. 数据库备份的分类

从物理与逻辑的角度，备份可分为

• 物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

物理备份方法：

（1）冷备份（脱机备份）：是在关闭数据库的时候进行的

把数据库用systemctl stop关闭的时候进行备份

（2）热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件

从数据库的日志文件进行备份（不建议用这种方式去备份）

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

温备份不影响数据库的运行，但是你不能访问

• 逻辑备份：对数据库逻辑组件（入：表等数据库对象）的备份

这种类型的备份适用于可以编辑数据值或表结构    

从数据库的备份策略角度，备份可以分为

• 完全备份：每次对数据库进行完整的备份

完全备份过程

每次都会进行完全备份，会导致备份文件占用巨大的空间，并且有大量的重复数据

生产环境中只适合第一次，不适合天天备份

恢复时直接把文件导入进去即可

• 差异备份：备份自从上次完全备份之后被修改过的文件

差异备份过程

上次差异备份，都会备份上一次完全备份之后的数据，可能会出现备份重复数据，导致占用大量额外的磁盘空间

恢复时，先回复完全备份，再回复导入差异备份的数据

• 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

增量备份过程

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

恢复时 需要 完全恢复 再增量恢复（次序）

备份方式的比较

备份方式	完全备份时状态	第一次添加内容	备份内容	第二次添加内容	备份内容
完全备份	表1+表2	表3	表1+表2+表3	表4	表1+表2+表3+表4
差异备份	表1+表2	表3	表3	表4	表3+表4
增量备份	表1+表2	表3	表3	表4	表4

完全备份创建多少都会把原来的数据再备份一遍

差异备份会把上一次备份多出来的数据再备份一遍

增量备份都会备份每一份多出来的的数据

选择备份的策略

备份方式	备份策略频率
完全备份	一周做一次完全备份。在不需要提供业务的时间例如PM 10点 AM 5:00，最好在凌晨1~5点
差异备份	选择特定的场景进行备份
增量备份	1/2/3天一次增量备份

我们为了数据的安全一般会放在NFS中

总结 

	完全备份	差异备份	增量备份
执行顺序	每次完全备份会备份之前完全备份的数据，会出现重复数据	每次执行差异备份会备份之前每次的差异备份	每一次增量备份的数据都是备份上一次增量备份后新增的数据
占用磁盘空间	占用大量的磁盘空间	占用少量额外的磁盘空间	占用极少量的磁盘空间
数据恢复	把完全备份的文件导入即可 恢复速度很快	先恢复完全备份数据，再导入差异备份数据	先恢复完全备份数据，再恢复增量备份数据（需要按照次序进恢复）

 

三.常见的备份方法

物理冷备

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

专用备份工具mudump或mysqlhotcopy

• mysqldump常用的逻辑备份工具
• mysqlhotcopy仅拥有备份MyLSAM和ARCHIVE表

启用二进制日志进行增量备份

• 进行增量备份，需要刷新二进制文件

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

第三方工具备份

• 免费的MYSL热备份软件Percona XtraBackup

完全备份

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

完全备份的优点

• 备份与恢复操作简单方便

完全备份的缺点

• 数据存在大量的重复
• 占用大量的备份空间
• 备份与回复时间长

完全备份的分类

物理冷备份与恢复

• 关闭MYSQL数据库
• 使用tar命令直接打包数据库文件夹
• 直接替换现有MYSQL目录即可

mysqldump备份与和恢复

• MySQL自带的备份工具，可方便实现对MYSQL的备份
• 可以将指定的库，表导入为SQL脚本
• 使用命令mysql导入备份的数据

导出使用的是Mysqldump；导入使用的是Mysql命令 

四.Mysql数据库完全备份操作

InnoDB 存储引擎的数据库在磁盘上存储成三个文件:

• db.opt(表属性文件)
• 表名.frm(表结构文件)
• 表名.ibd(表数据文件)

环境准备-创建数据库和表，插入若干条记录

use test;
create table test01(id int(4) not null auto_increment primary key,
name varchar(10) not null,
age char(10) not null,
hobby varchar(50));
 
 
insert into test01 values(1,'zhangsan',22,'xizao');
insert into test01 values(2,'lisi',21,'xijiao');
insert into test01 values(3,'wangwu',20,'anmo');
insert into test01 values(4,'zhaoliu',19,'tangtou');
 
select * from test01;

物理冷备份与恢复

方法一 

先关闭数据库

备份

[root@localhost mysql]#pwd
/usr/local/mysql
[root@localhost mysql]#tar zcvf data.tar.gz data/
 
[root@localhost mysql]#mv data.tar.gz /opt
 

 mysql下的data就是我们的数据

 

 这时mysql下多了我们备份的data.tar.gz

将备份的数据移动到我们指定的目录 

 

恢复

 将备份的数据解压回源目录

 这时我们发现原数据又回来了

方法二

备份： 

使用tar命令直接打包数据库文件夹
yum -y install xz
#压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
 
3、直接替换现有MySQL目录即可
mv /usr/local/mysql/data/ /opt/
 
4、解压恢复
tar Jxvf /opt/mysql_all_2024-03-25.tar.xz  -C ./
cd /usr/local/mysql/data
mv /usr/local/mysql/data/* ./

 

压缩备份

 

成功得到备份 

[root@localhost mysql]#ls
[root@localhost mysql]#mkdir data
[root@localhost mysql]#ls
data
[root@localhost mysql]#pwd
/home/mysql
[root@localhost mysql]#tar Jxvf /opt/mysql_all_2024-03-25.tar.xz -C ./
 
 

这时我们发现在mysql下创建的data目录里面没有数据

 

 解压到data目录下

 

解压的移动到指定目录下 

 这时我们发现mysql目录下有了我们的data数据

mysqldump 备份与恢复 

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

mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql    
 
#导出的就是数据库脚本文件

mysqldump -uroot -p123 --databases test > /opt/test.sql

mysqldump -u root -p123456 --databases mysql test > /opt/test_mysql.sql

 备份一个test库

 备份mysql和test两个库

 查看

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

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

mysqldump -u root -p123456 --all-databases > /opt/all.database.sql

备份所有的库

查看