关系型数据库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中

总结 

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

三.常见的备份方法

物理冷备

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

专用备份工具mysqldump或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

备份所有的库

查看

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

mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql
例:
mysqldump -u root -p [-d] school test01 > /opt/kgc_test01.sql
#使用“-d”选项，说明只保存数据库的表结构 
#不使用“-d"选项，说明表数据也进行备份
#做为一个表结构模板

结构数据都备份 

只备份表结构 

查看备份文件 

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

 -d选项只备份了表的结构

Mysql 完全恢复 

使用mysqldump导出的文件，可使用导入的方法
source命令
mysql命令 

使用source恢复数据库的步骤 

1. 登录到MySQL数据库
2. 执行source备份sql脚本的路径

1.创建备份(对表进行备份) 

mysqldump -uroot -p123456 school test01 > /opt/test01.sql  
mysql -uroot -p123456 登录数据库查看

 

 2.删除数据库，模拟错误

[root@server1 ~]# mysql -uroot -p123456 -e 'drop table school.test01;'  #删除数据库的表

3. 恢复数据表

mysq 
mysql> select * from test01;  查询所有字段
mysql> show tables; 查看表信息
mysql> source /opt/test01.sql  
mysql -uroot -p123456-e 'show tables from school;'

 可以看出test01表被删除无法找出

 

成功恢复数据表

 使用mysql语句重定向恢复

删除表格

 mysql语句重定向导入之前备份的sql文件

 查看

备份加与不加 --databases 

mysqldump 严格来说属于温备份，会需要对表进行写入的锁定，在全量备份与恢复实验中：

① 当备份时加 --databases，表示针对 school库

测试

删除 school库

 恢复

查看 

school库又回来了 

②当备份时不加 --databases，表示针对school库下的所有表 

导回数据时，发现提示找不到库 

 

这时库里面是空的表

恢复数据 

 数据回来了

主要区别：

在于两种方式的备份（前者则全是针对表格进行操作，而后者会从 "create databases"开始）

Crontab -e——执行定时备份

0 1 * * 6 /usr/local/mysql/bin/mysqldump -uroot -p123456 class > class_all_$(date +%F).sql;
/usr/local/mysql/bin/mysqladmin -uroot -p flush-logs
 
或者
 
0 1 * * 6 /usr/local/mysql/bin/mysqldump -uroot -p123456 class > class_all_$(date +%Y%m%d).sql;
/usr/local/mysql/bin/mysqladmin -uroot -p flush-logs

五.MySQL 增量备份与恢复操作

MySQL 恢复的三种方式

一般恢复

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

基于位置恢复

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

基于时间点恢复

• 跳过某个发生错误的时间点实现数据恢复
• 在错误时间点停止，在下一个正确时间点开始

增量备份产生的原因 

• 使用 mysqldump 进行完全备份存在的问题
  • 备份数据中有重复数据
  • 备份时间与恢复时间过长

• 增量备份是什么：
  • 是自上一次备份后增加/变化的文件或者内容
• 增量备份的特点
  • 没有重复数据，备份量不大，时间短
  • 恢复需要上次完全备份及完全备份之后所有的增量备份才 能恢复，而且要对所有增量备份进行逐个反推恢复

 增量备份的过程 

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

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

mysql日志

配置文件：/etc/my.cnf

MySQL的日志默认保存位置为：/usr/local/mysql/data下
配置文件位于：/etc/my.cnf文件中的[mysqld]内

[mysqld]  模块下

错误日志

用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log 

通用查询日志

通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

二进制日志

二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
log-bin=mysql-bin      
或
log_bin=mysql-bin

中继日志

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

慢查询日志

慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化，默认是关闭的
s1ow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5        #设置超过5秒执行的语句被记录，缺省时为10秒

日志配置 

vim /etc/my.conf

 
#错误日志
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
#配置文件添加完后需要重启MySQL
systemctl restart mysql
 

复制段
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

日志查询  

登陆进mysql中，查询日志是否开启

#查看通用查询日志是否开启
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;		

#variables 表示变量
#like 表示模糊查询
#%xxx，以什么结尾，xxx%以什么开头，%xxx%包含 

二进制日志开启后，重启 mysql 会在目录中查看到二进制日志
cd /usr/local/mysql/data
ls
mysql-bin.000001        
 
#开启二进制日志时会产生一个索引文件及一个索引列表 

 MySQL 增量备份

一般恢复

开启二进制日志功能

vim /etc/my.cnf
[mysqld]
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
binlog_format = MIXED      #可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入）
server-id = 1              #可加可不加该命令

重启服务 

二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

① STATEMENT（基于SQL语句）:

每一条涉及到被修改的sql 都会记录在binlog中
缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题
总结：增删改查通过sql语句来实现记录，如果用高并发可能会出错，可能时间差异或者延迟，可能不是我们想想的恢复可能你先删除或者在修改，可能会倒过来。准确率底
② ROW(基于行)

只记录变动的记录，不记录sql的上下文环境
缺点:如果遇到update......set....where true 那么binlog的数据量会越来越大
总结：update、delete以多行数据起作用，来用行记录下来，只记录变动的记录，不记录sql的上下文环境，
比如sql语句记录一行，但是ROW就可能记录10行，但是准确性高，高并发的时候由于操作量，性能变低 比较大所以记录都记下来，
③ MIXED （推荐使用）

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

 进入到数据库中

mysql> show variables like 'general%';
#查看通用日志是否开启

mysql> show variables like 'log_bin%';
#查看二进制日志是否开启

我们可以看出mysql-bin.000001文件就是我们的日志存放位置 

查看二进制日志

[root@localhost data]#mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001

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

 二进制日志中需要关注的部分
1、at ：开始的位置点 
2、end_log_pos：结束的位置
3、时间戳： 210712 11:50:30 
4、SQL语句

准备环境

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> 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));
rt into test01 values(3,'wangwu',20,'anmo');
insert into test01 values(4,'zhaoliu',19,'tangtou');
 
select * from test01;Query OK, 0 rows affected (0.01 sec)

mysql>  
mysql>  
mysql> insert into test01 values(1,'zhangsan',22,'xizao');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test01 values(2,'lisi',21,'xijiao');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test01 values(3,'wangwu',20,'anmo');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test01 values(4,'zhaoliu',19,'tangtou');
Query OK, 1 row affected (0.00 sec)

mysql>  
mysql> select * from test01;
+----+----------+-----+---------+
| id | name     | age | hobby   |
+----+----------+-----+---------+
|  1 | zhangsan | 22  | xizao   |
|  2 | lisi     | 21  | xijiao  |
|  3 | wangwu   | 20  | anmo    |
|  4 | zhaoliu  | 19  | tangtou |
+----+----------+-----+---------+
4 rows in set (0.00 sec)

刷新日志

[root@localhost ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf        ib_logfile1       mysql-bin.000002   mysql_slow_query.log
ib_buffer_pool  ibtmp1            mysql-bin.index    performance_schema
ibdata1         mysql             mysql_error.log    sys
ib_logfile0     mysql-bin.000001  mysql_general.log  test

我们创建class.class的数据库操作会保存到mysql-bin.000002文件中

进行完全备份(增量备份时基于完全备份的，所以我们直接完全备份数据库)

[root@localhost data]# mysqldump -uroot -p123456 test test01 > /opt/test_test01_$(date +%F).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# 
[root@localhost data]# mysqldump -uroot -p123456 test > /opt/test_all_$(date +%F).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

准备环境

mysql> create database class;
Query OK, 1 row affected (0.00 sec)

mysql> use class;
Database changed
mysql> create table class(id int(6),name char(8),hobby varchar(40));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into class values(1,'zhangsan','xizao');
Query OK, 1 row affected (0.01 sec)

mysql> insert into class values(2,'lisi','anmo');
Query OK, 1 row affected (0.00 sec)

刷新日志

模拟丢失更改的数据的恢复步骤（直接使用恢复即可）

备份class库中的class表

删除class库中的class表

恢复class表 

查看 

 模拟丢失所有数据的恢复步骤

① 模拟丢失所有数据
mysql -uroot -p123456 -e 'drop database class;'
 
② 基于mysql-bin.000002恢复
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p123456

 查看

 断点恢复

 添加新的记录

mysql> use class
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into class values(3,'wangwu','tangtou');
Query OK, 1 row affected (0.01 sec)

mysql> insert into class values(4,'zhaoliu','hejiu');
Query OK, 1 row affected (0.01 sec)

mysql> select * from class;
+------+----------+---------+
| id   | name     | hobby   |
+------+----------+---------+
|    1 | zhangsan | xizao   |
|    2 | lisi     | anmo    |
|    3 | wangwu   | tangtou |
|    4 | zhaoliu  | hejiu   |
+------+----------+---------+
4 rows in set (0.00 sec)

 确认位置点，刷新二进制日志并删除test1表

 mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003

#刷新日志
mysqladmin -uroot -p123456 flush-logs
#删除class
mysql -uroot -p123456 -e 'drop table class.class;'

基于位置点恢复 

 ①仅恢复到操作 ID 为“2987"之前的数据，即不恢复"user4"的数据

mysqlbinlog --no-defaults --stop-position='2987' /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p123456

基于时间点恢复

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码

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

六.总结

1.物理冷备份

• 关闭Mysqld服务
• 使用tar命令进行打包data目录
• 恢复直接解压即可

2.逻辑备份

• mysqldump -u -p --database 备份库1,库2 > xxx.sql   #备份多个数据库
• mysqldump -u -p --all-database > xxx.sql   #备份所有数据库
• mysql -u -p 库1,库2 表1,表2 > xxx.sql   #备份多库多表

3.完全恢复

• mysql -u -p < xxx.sql #恢复整个库
• mysql -u -p 库名 < xxx.sql  #恢复表

4.增量备份

1. 要先开启二进制日志，设置二进制格式为MIXED
2. 进行一次完全备份，可每周备份一次，通过crontable -e 进行编辑
3. mysqladmin -uroot -p flush-logs 刷新二进制日志文件 分割出二进制日志文件，由于刷新之前的数据都会记录在老的二进制文件里
4. 可以通过mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制文件名称 可以查看日志内容
5. 可以通过mysqlbinlog --no-defaults 二进制日志文件名 mysql -uroot -p 恢复丢失的数据库

位置恢复——position

• start：--start-position
• stop：--stop-position
• start、stop：--start-position --stop-position

时间恢复——datetime

• start：--start-datetime
• stop：--stop-datetime
• start、stop：--start-datetime --stop-datetime