MySQL的备份与恢复

一、数据库备份的分类

1.数据备份的重要性

1.1 在生产环境中，数据的安全性至关重要（数据库是不对外的，数据库的端口号为3306）

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

举例：支付宝的数据丢失可能你的money会被转走

1.3 造成数据丢失的原因

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

2.数据库备份的分类

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

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

物理备份方法

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

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

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

• 完全备份：每次对数据库进行完整的备份
• 差异备份：备份自从上次完全备份之后被修改过的文件
• 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

2.21 完全备份过程：

在完全备份过程中，每次都进行完全备份，会导致备份文件占用居多的空间并且有大量的重复数据。

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

注：在生产环境中不适合常用，因为每次备份都会诞生一个文件，适合第一次做备份

2.22 差异备份过程

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

恢复时 先恢复完全备份 在恢复导入差异备份的数据

注：一般企业用的也很少 因为会备份占用空间

2.23 增量备份过程

每次增量备份数据都是备份在上一次完全备份或者增量备份之后的数据

不会出现重复数据 也不会占用额外的磁盘空间

恢复时，需要完全恢复 在增量恢复（恢复时按照次序）

2.3备份方式比较

备份方式	完全备份	差异备份	增量备份
完全备份时的状态	表1，表2	表1，表2	表1，表2
第1次添加内容	创建表3	创建表3	创建表3
备份内容	表1，表2，表3	表3	表3
第2次添加内容	创建表4	创建表4	创建表4
备份内容	表1，表2，表3，表4	表3，表4	表4

逻辑备份的策略（增、全、差异）
如何选择逻辑备份策略（频率）
一周一次的全备，全备的时间需要在不提供业务的时间区间进行 PM 10点 AM 5:00之间进行全备 凌晨 1~5点
增量：3天/2天/1天一次增量备份
差异：选择特定的场景进行备份

3.常见的备份方法

1、物理冷备

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

2.专用备份工具 mysqldump 或 mysqlhotcopy 

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

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

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

4.第三方工具备份

• 免费的MySQL 热备份软件 Percona XtraBackup mysqlbackup

二、MySQL完全备份与恢复

1. MySQL完全备份

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

优点：

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

缺点：

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

2.数据库完全备份分类

物理冷备份与恢复

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

方法1：

首先先关闭mysqld，并下载xz

直接压缩备份

tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

[root@localhost opt]#tar Jcvf /opt/mysql_all$(date +%F).tar.xz /usr/local/mysql/data/

方法2：

切换进入/usr/local/mysql，直接压缩

然后移动至/opt下

解压恢复

记得在/home/mysql创建一个data（mkdir data）

tar Jxvf /opt/mysql_all_2020-11-22.tar.xz -C /home/mysql/data

mysqldump备份与恢复

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

实验：

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

首先进入数据库

查看发现ufc数据库和njzb中含有数据表

然后切入/opt 并进行备份

mysqldump -u root -p --databases njzb > /nzjb.sql

mysqldump -uroot -pabc123456 --databases njzb > njzb.sql

然后查看njzb.sql

发现数据过来

接下来将数据库ufc一起备份过来

mysqldump -uroot -pabc123456 --databases ufc > njzb_ufc.sql

查看发现所有的数据都在里边

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

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

[root@localhost opt]#mysqldump -uroot -pabc123456 --all-databases > /opt/all-database.sql

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

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

[root@localhost opt]#mysqldump -uroot -pabc123456 ufc ky35 > /opt/kgc_ky35.sql

4.查看备份文件

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

首先备份

然后过滤查看

3.MySQL完全恢复

1.恢复数据库

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

如果删除了表中数据

[root@localhost opt]#mysql -uroot -pabc123456 -e 'drop table ufc.ky35;'

发现ky35没了

使用source恢复

因为在/opt下备份了ufc数据库中的ky35

发现ky35又恢复过来了

并发现数据都还在

2.删除数据库并恢复

然后可以直接恢复备份

如果没有直接插入数据的话会报错 因为没有数据库

三、MySQL增量备份与恢复

1.MySQL数据库增量恢复

1.一般恢复

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

2.基于位置恢复

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

3.基于时间点恢复

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

2.MySQL增量备份

2.1增倍实验

首先开启二进制日志功能

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
binlog_format = MIXED

然后重启数据库

systemctl restart mysqld

进入查询

show variables like 'log_bin%';

发现有了二进制文件

查看data发现有了error语句 查询语句 慢查询语句

为什么用MIXED

① STATEMENT（基于SQL语句）:
每一条涉及到被修改的sql 都会记录在binlog中
缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题

总结：增删改查通过sql语句来实现记录，如果用高并发可能会出错，可能时间差异或者延迟，可能不是我们想想的恢复可能你先删除或者在修改，可能会倒过来。准确率底

② ROW(基于行)
只记录变动的记录，不记录sql的上下文环境
缺点:如果遇到update......set....where true 那么binlog的数据量会越来越大

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

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

MIXED是上面两种的结合

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

cp /usr/local/mysql/data/mysql-bin.000001 /opt/

mysqlbinlog --no-defaults /opt/mysql-bin.000001

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.

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

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

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

[root@mysql data]# mysqldump -uroot -p school info > /opt/kgc_info1_$(date +%F).sql
[root@mysql data]# mysqldump -uroot -p123123 school > /opt/school_all_$(date +%F).sql

3.可每天进行增量备份操作，生成新的二进制日志文件(例如:mysql-bin.000002)

mysqladmin -u root -p flush-logs

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

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

mysql> use ky13;
Database changed
mysql> create table test1 (id int(4),name varchar(4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(1,'one');
Query OK, 1 row affected (0.00 sec)



mysql> insert into test1 values(2,'two');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | one  |
|    2 | two  |
+------+------+
2 rows in set (0.00 sec)

5.再次生成新的二进制日志文件

mysqladmin -u root -p flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后我们测试删除ky13库的操作会保存在mysql-bin.000003文件中 （以免当我们基于mysql-bin.000002日志进行恢复时，依然会删除库

3.MySQL增量恢复

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

再次生成新的二进制日志文件

mysqladmin -u root -p123456 flush-logs

因为存在mysql-bin.000001中所以mysql-bin.000002中是空的，所以要覆盖

也可以执行以下命令，将打印内容重定生成为txt文件，便于以后查看

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin_$(date +%F).000002.txt

此时再刷新，生成新的二进制日志文件（因为下一步需要模拟数据丢失，删除work库文件，此操作会计入二进制日志文件中，在使用二进制日志文件恢复数据时，依然会删除库）

删除数据库nba

3.11数据恢复

完全恢复

直接使用二进制日志文件恢复会报错，因为它是基于完全备份后的增量备份，完全备份的数据是它的基础，在上面的操作中，对emp_user表进行添加三条数据，所以二进制日志文件，只记录了对表的一些操作语句，如果直接使用增量恢复，会因为找不到库文件与表而报错

所以首先需要进行完全备份的恢复

5.2 断点恢复

使用--stop-position与--start-position指令进行断点恢复

--stop-position='at值' 
#从开始值向下匹配，不设定--start-position，表示匹配到目标二进制日志文件的结尾
 
--start-position='end_log_pos值'
#结束位置，不设定--stop-position，表示从目标二进制日志文件的开头匹配，到设定值结束

过滤出转换为txt格式后的二进制文件的开始值与结束值

同样删除原本的数据信息，将信息恢复到完全备份之前的状态

5.3 时间戳恢复

基于时间戳恢复原理与断点恢复原理一致，将--stop-position与--start-position指令更改为

--stop-datetime与--start-datetime即可

--stop-datetime='at值' 
#从开始值向下匹配，不设定--start-datetime，表示匹配到目标二进制日志文件的结尾
 
--start-datetime='end_log_pos值'
#结束位置，不设定--stopt-datetime，表示从目标二进制日志文件的开头匹配，到设定值结束

找到需要恢复的数据的开始时间戳与结束时间戳

恢复该条数据，使用--stop-datetime与--start-datetime选项

总结

（一）备份原理
备份方式的基本原理，主要将库或者表的SQL语句进行备份，在数据恢复时，将SQL语句重新导入到MySQL服务当中，再次执行一遍SQL语句，达到数据恢复的效果

（二）常用的备份方式
1.冷备份

2.专用备份工具

3.二进制日志备份

4.第三方工具备份

（三）制定备份策略
在生产环境中，可以根据清空制定备份策略

定期备份：可根据数据变化频率设定每日、每周或每月的备份计划。

使用crontab -e，制定计划任务，来定期进行数据备份

比如每周六对wor库下emp_user表进行备份

0 1 * * 6  /usr/local/mysql/bin/mysqldump -uroot -pabc123 work emp_user > /bak/work_emp_user_$(date +%F).sql;/usr/local/mysql/bin/mysqladmin -u root -p flush-logs

也可以使用shell脚本进行备份

备份保留策略：根据法规要求和存储资源，确定备份文件的保留周期。

异地备份：将备份文件存放在不同的地理位置，以防单一地点的灾难事件