Mysql数据库备份恢复操作解析

数据备份的重要性

在生产环境中，数据的安全性至关重要
任何数据的丢失都可以产生严重后果
造成数据丢失的原因：
1.程序错误
2.人为操作错误
3.运算错误
4.磁盘故障
5.灾难(如火灾，地震)和盗取
备份的目的：
用于灾难恢复、测试应用、回滚数据修改、查询历史数据、审计等

数据库备份的类型

物理备份： 对数据库操作系统的物理文件（数据文件、日志文件等）进行的备份（针对出现问题时需要快速恢复大型数据库）
物理备份方法
1.冷备份(脱机备份)：是在关闭数据库的时候进行的
注：备份速度快，恢复时简单，通过直接打包数据库文件

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

3.温备份：数据库锁定表格(不可写入但可读)的状态下进行备份操作
注：不可往表格中写入但是可以读取的状态

4.逻辑备份：对数据库逻辑组件(如：表等数据库对象)的备份
注：对create database、create table、insert等语句信息进行的备份，适用于可编辑数据值或者表结构较小的数据量

备份策略的三种角度

1.完全备份： 又叫完整备份，对整个数据库进行备份，保存的是备份完成时刻的数据库（是差异备份和增量备份的前提）
特点： 备份恢复简单，占用磁盘空间大，备份时间较长

2.差异备份： 备份上一次完全备份后被修改过的所有文件
特点： 备份数据量大，恢复时需要恢复上次的完全备份 + 最近一次的差异备份

3.增量备份： 以上一次完全备份或者上一次增量备份的时间为节点，只备份时间节点数据发生变化的部分（通过日志文件备份）
特点： 备份数据量小，占用空间小，备份速度快,恢复时需要一次完整备份 + 所有增量备份依次恢复

数据备份的方法

1.物理冷备份： （使用tar工具直接打包）
备份时数据库处于关闭状态，直接打包数据库文件
备份速度快，恢复时也是最简单的

2.专用备份工具： （mysqldump或者mysqlhotcopy）
mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

3.通过二进制日志进行增量备份
进行增量备份，需要刷新二级制日志

4.通过第三方工具进行备份
免费的MySQL热备份软件Percona XtraBackup

MySQL完全备份

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

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

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

mysql数据库的备份操作

物理冷备份与恢复

mysql先随便创建一个表看结果

create database class;
use class;
create table aaa(姓名 char(64),班级 int(20),年龄 int(20));
desc aaa;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| 姓名   | char(64) | YES  |     | NULL    |       |
| 班级   | int(20)  | YES  |     | NULL    |       |
| 年龄   | int(20)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+

create database aaa;
use aaa;
create table aaa(姓名 char(64),年龄 int(20));
desc aaa;
mysql> desc aaa;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| 姓名   | char(64) | YES  |     | NULL    |       |
| 年龄   | int(20)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

物理冷备份

systemctl stop mysqld //先关闭数据库，之后打包备份
mkdir /backup
tar zcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
cd /backup/ //ll查看备份
总用量 1376
-rw-r--r--. 1 root root 1408499 12月 24 17:40 mysql_all-2020-12-24.tar.gz


//再次开启数据库，删掉一个库如aaa；
systemctl start mysqld
mysql> drop database aaa;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
//库里就没有aaa了

恢复数据库

systemctl stop mysqld
cd /backup/
tar zxvf mysql_all-2020-12-24.tar.gz
// cd /backup/ ll 查看
-rw-r--r--. 1 root root 1408499 12月 24 17:40 mysql_all-2020-12-24.tar.gz
drwxr-xr-x. 3 root root      19 12月 24 17:44 usr

mkdir mysql_old
mv /usr/local/mysql/data/ mysql_old/
mv usr/local/mysql/data/ /usr/local/mysql/ //恢复数据库到原来位置
systemctl start mysql  //开启mysql 去查看aaa表恢复没有
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa     //恢复成功            |
| class              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

使用mysqldump实现备份恢复

使用mysqldump导出的脚本，可使用导入的方法如：source命令 mysql命令
使用source恢复数据库的步骤：1.登录到MySQL数据库里 2.执行source备份sql脚本的路径

备份指定库中的某个表

备份数据库
基本语法结构： mysqldump 选项 库名 表名 > /备份路径/文件名

恢复数据库
基本语法： mysql 选项 库名 表名 < /备份路径/备份文件名

mkdir /dunmp  //创建备份目录
mysqldump -uroot -p aaa aaa > /dunmp/aaa-aaa.sql  //前面aaa是库  后面aaa是表
Enter password:
[root@web3 ~]# ll /dunmp
总用量 4
-rw-r--r--. 1 root root 1772 12月 24 18:51 aaa-aaa.sql //备份的效果
mysql> drop table aaa; //删掉aaa表 尝试恢复
mysql>exit

mysql -u root -p aaa < /dunmp/aaa-aaa.sql
Enter password:
mysql> use aaa;
mysql> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| aaa   //恢复成功         |
+---------------+
1 row in set (0.00 sec)

//source恢复数据库
mysql> drop database aaa;
Query OK, 0 rows affected (0.01 sec)

mysql> create database aaa; //要先创建aaa库
Query OK, 1 row affected (0.00 sec)

mysql> use aaa; //切换到aaa
Database changed

mysql> source /dunmp/aaa-aaa.ssql //使用source恢复
//注：备份库里某张表时，应该针对这张被删除的表，使用原来数据库，重新source备份的sql来实现。
mysql> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| aaa  //成功         |
+---------------+
1 row in set (0.00 sec)

备份一个或者多个完整的库

语法结构： mysqldump 选项 --databases 库名1 库名2… > /备份路径/备份文件名

注：之前已经创建了备份目录，这里就不进行创建了 dunmp

mysqldump -u root -p --databases class >/dunmp/class.sql
//这里是将整个class数据库备份

ll /dunmp/
-rw-r--r--. 1 root root 1947 12月 24 19:39 class.sql //备份成功

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| class              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database class;  //删掉 尝试恢复 exit记得退出
mysql -u root -p < /dunmp/class.sql //恢复库class
Enter password: 
mysql -uroot -p -e 'show databases;' //不用进去mysql 查询
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| class   //恢复了           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

备份所有的库

 mysqldump -u root -p --all-databases > /dunmp/all.sql 
 //效果一样跟备份库操作一致 就备份--all all代表全部。

mysql数据库的增量备份与恢复

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

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

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

开启二进制日志

vi /etc/my.cnf  
[mysqld]
log-bin=/usr/local/mysql/mysql-bin //加入二级制日志

systemctl restart mysqld
ls -l /usr/local/mysql/mysql-bin* 
//查看日志 日志是以mysql-bin开头 名字是自己起的
-rw-r-----. 1 mysql mysql 154 12月 25 00:27 /usr/local/mysql/mysql-bin.000001
-rw-r-----. 1 mysql mysql  34 12月 25 00:27 /usr/local/mysql/mysql-bin.index
//注：二进制日志文件的扩展名是一个6 位数字

注：每生成一个二进制日志就会+000001

一般恢复

注：
将所有备份的二进制日志内容全部恢复
将所有备份的二进制日志内容全部恢复,完整备份和增量备份以及恢复

mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p

创建aaa表

mysql> create table aaa(学号 char(18) not null,姓名 char(12)not null,年龄 int(12) not null);    （创建表）
Query OK, 0 rows affected (0.00 sec)

insert into user_info values('');   //加入3条数据
---------+--------+--------+
| 学号    | 姓名   | 年龄   |
+---------+--------+--------+
| 000001  | xxz    |     18 |
| 0000002 | lisi   |     22 |
| 000003  | 王五   |     22 |
+---------+--------+--------+

创建完全备份与增量备份 的备份

mkdir /mysql_beifen1/  //创建备份目录  切记

mysqldump -u root -p aaa user > /mysql_beifen1/aaa-user-$(date +%F).sql    
//使用mysqldump工具进行完整备份
Enter password: 
mysqladmin -u root -p flush-logs //生成二进制日志文件

//可以查看备份目录
ll /mysql_beifen1/
总用量 8
-rw-r--r--. 1 root root 1901 12月 25 14:02 aaa-user-2020-12-25.sql

//查看二进制日志文件，发现多了一个002的文件
ls -l /usr/local/mysql/mysql-bin.*   
-rw-r-----. 1 mysql mysql  709 12月 25 12:18 /usr/local/mysql/mysql-bin.000001  
//完全备份存在000001里面
-rw-r-----. 1 mysql mysql  493 12月 25 14:07 /usr/local/mysql/mysql-bin.000002  
//现在000002是空的


//再次往表里插入数据 让增量备份加入2条数据
mysql> insert into user_info values('');  
mysqladmin -u root -p flush-logs 
// 增量备份 再次生成二进制日志文件
ls -l /usr/local/mysql/mysql-bin.*  
//查看二进制日志文件，发现多了一个003的文件
ls -l /usr/local/mysql/mysql-bin.*   
-rw-r-----. 1 mysql mysql  709 12月 25 12:18 /usr/local/mysql/mysql-bin.000001  
//完全备份存在000001里面
-rw-r-----. 1 mysql mysql  493 12月 25 14:07 /usr/local/mysql/mysql-bin.000002  
//现在000002是增量备份 加入了2条数据的
-rw-r-----. 1 mysql mysql 6462 12月 25 15:37 /usr/local/mysql/mysql-bin.000003  //为空

cp /usr/local/mysql/mysql-bin.000002 /mysql_beifen1/  
// 将二级制日志备份文件 备份到我们的备份目录

模拟删除库表 尝试恢复完全备份与增量备份

mysql -uroot -p -e 'drop table aaa.user;'
mysql -uroot -p -e 'select * from aaa.user;'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'aaa.user' doesn't exist
//删除后 找不到aaa.user表

ll /mysql_beifen1/  //查看自己的备份目录
总用量 8
-rw-r--r--. 1 root root 1901 12月 25 14:02 aaa-user-2020-12-25.sql //完全备份
-rw-r-----. 1 root root  493 12月 25 14:09 mysql-bin.000002 //增量备份


mysql -uroot -p aaa </mysql_beifen1/aaa-user-2020-12-25.sql 
Enter password: 
//尝试完全备份恢复

mysql -uroot -p -e 'select * from aaa.user;'
Enter password: 
+---------+--------+--------+
| 学号    | 姓名   | 年龄   |
+---------+--------+--------+
| 000001  | xxz    |     18 |
| 0000002 | lisi   |     22 |
| 000003  | 王五   |     22 |
+---------+--------+--------+
//完全备份成功

mysqlbinlog --no-defaults /mysql_beifen1/mysql-bin.000002 | mysql -u root -p
//尝试增量备份恢复
Enter password: 

mysql -uroot -p -e 'select * from aaa.user;'
Enter password: 
+---------+--------+--------+
| 学号    | 姓名   | 年龄   |
+---------+--------+--------+
| 000001  | xxz    |     18 |
| 0000002 | lisi   |     22 |
| 000003  | 王五   |     22 |
| 000004  | 老师   |     55 |
| 000005  | 猫咪   |      1 |
+---------+--------+--------+

基于位置恢复

注：
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作

1、恢复数据到指定位置
mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

2、从指定的位置开始恢复数据
mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

注：–base64-output=decode-rows 64解码 -v 换行显示

mysql -uroot -p aaa </mysql_beifen1/aaa-user-2020-12-25.sql
//恢复到完全备份 进行位置恢复增量备份创建的数据
mysql -uroot -p -e 'select * from aaa.user;'
Enter password: 
+---------+--------+--------+
| 学号    | 姓名   | 年龄   |
+---------+--------+--------+
| 000001  | xxz    |     18 |
| 0000002 | lisi   |     22 |
| 000003  | 王五   |     22 |
+---------+--------+--------+


mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_beifen1/mysql-bin.000002  //查看增量备份的位置id
## INSERT INTO `aaa`.`user`
### SET
###   @1='000004'
###   @2='老师'
###   @3=55
### INSERT INTO `aaa`.`user`
### SET
###   @1='000005'
###   @2='猫咪'
###   @3=1
# at 415
#201225 14:05:04 server id 1  end_log_pos 446 CRC32 0x412e6f4f  Xid = 299
COMMIT/*!*/;
# at 446   //这里at446

mysqlbinlog --no-defaults --stop-position='446' /mysql_beifen1/mysql-bin.000002 | mysql -u root -p
//恢复数据到指定位置
Enter password: 
mysql -uroot -p -e 'select * from aaa.user;'
Enter password: 
+---------+--------+--------+
| 学号    | 姓名   | 年龄   |
+---------+--------+--------+
| 000001  | xxz    |     18 |
| 0000002 | lisi   |     22 |
| 000003  | 王五   |     22 |
| 000004  | 老师   |     55 |
| 000005  | 猫咪   |      1 |
+---------+--------+--------+
//指定位置恢复成功

基于时间点恢复

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

1、从日志开头截止到某个时间点的恢复
mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

2、从某个时间点到日志结尾的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

3、从某个时间点到某个时间点的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

mysql -uroot -p aaa </mysql_beifen1/aaa-user-2020-12-25.sql
//恢复到完全备份 进行位置恢复增量备份创建的数据

mysql -uroot -p -e 'select * from aaa.user;'
Enter password: 
+---------+--------+--------+
| 学号    | 姓名   | 年龄   |
+---------+--------+--------+
| 000001  | xxz    |     18 |
| 0000002 | lisi   |     22 |
| 000003  | 王五   |     22 |
+---------+--------+--------+

mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_beifen1/mysql-bin.000002  //查看增量备份的时间
#201225 14:05:04 server id 1  end_log_pos 415 CRC32 0x18047fcc  Write_rows: table id 230 flags: STMT_END_F
### INSERT INTO `aaa`.`user`
### SET
###   @1='000004'
###   @2='老师'
###   @3=55
### INSERT INTO `aaa`.`user`
### SET
###   @1='000005'
###   @2='猫咪'
###   @3=1
# at 415
#201225 14:05:04 server id 1  end_log_pos 446 CRC32 0x412e6f4f  Xid = 299
COMMIT/*!*/;
# at 446
#201225 14:07:35 server id 1  end_log_pos 493 CRC32 0xee11dcad  Rotate to mysql-bin.000003  pos: 4 
//2020-12-25 14.05:04-2020-12-25 14:07:35 之间加入了2条数据


 mysqlbinlog --no-defaults --start-datetime='2020-12-25 14:05:04' --stop-datetime='2020-12-25 14:07:35' /mysql_beifen1/mysql-bin.000002 | mysql -u root -p  
 //从某个时间点到某个时间点的恢复
Enter password: 

mysql -uroot -p -e 'select * from aaa.user;'
Enter password: 
+---------+--------+--------+
| 学号    | 姓名   | 年龄   |
+---------+--------+--------+
| 000001  | xxz    |     18 |
| 0000002 | lisi   |     22 |
| 000003  | 王五   |     22 |
| 000004  | 老师   |     55 |
| 000005  | 猫咪   |      1 |
+---------+--------+--------+