MySQL 备份和恢复

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

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

物理备份的方法：

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

②逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份，即以sql语句的形式，把库、表结构、表数据保存下来。

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

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

②备份策略：

完全备份:每次备份都备份完整的数据库.

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

差异备份:只备份上一次完全备份后的更新数据.
增量备份:每次备份只备份上一次完全备份或增量备份后的最新数据.

完全备份：

增量备份：

差异备份：

1.3.常见的备份方法

物理冷备：（完全备份）

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

专用备份工具mydump或mysqlhotcopy （完全备份，逻辑备份）

mysqldump常用的逻辑备份工具（导出为sql脚本）
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

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

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

第三方工具备份

免费的MySQL热备份软件Percona XtraBackup

（阿里云的工具：dts，支持热迁移）

二.MySQL完全备份

2.1.什么是完全备份

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

2.2.完全备份的优缺点

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

缺点：

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

2.3.实现物理冷备份与恢复

1）实现流程

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

2）前置准备

mysql1 :192.168.47.105
mysql2 :192.168.47.106

mysql -uroot -p369369
#登录数据库

create database zr;
#新建数据库

use zr;
#进入数据库

create table zr1 (id int, name varchar(10), age int, sex char(2), hobby varchar(50));
#新建表

show tables;
#查看数据库

#添加数据
insert into zr1 values (1, '张三', 22, '男', 'java');
insert into zr1 values (2, '李四', 23, '男', '测试');
insert into zr1 values (3, '王五', 24, '男', '云计算');
insert into zr1 values (4, '赵四', 25, '男', '云原生');
insert into zr1 values (5, '凹凸曼', 26, '男', '网络安全');
insert into zr1 values (6, '大飞', 27, '男', '前端开发');

select * from zr1;  #查看数据是否添加成功

create table zr2 like zr1;
insert into zr2 (select * from zr1);
#克隆一个名为zr2的表

show tables from zr;
#查看是否创建成功

3）实现物理冷备份

quit
#退出数据库

systemctl stop mysqld
#关闭数据库
cd /usr/local/mysql/data
#前往mysql默认日志目录下

mkdir /opt/backup
#创建备份日志文件目录,将数据库备份文件保存到/opt/backup目录下

cd /usr/local/mysql

tar zcf /opt/backup/mysql_data-$(date +%F).tar.gz data/
#打包压缩今天的data目录到/opt/backup目录下，并标上日期

#拓展
date +%Y/%m/%d
#只显示当前日期

date +%Y-%m01
#显示当月第一天

date -d "+1 day" +%Y/%m/%d
#显示明天

date -d "-1 day" +%Y/%m/%d
#显示昨天
或
date -d "1 day ago" +%Y/%m/%d

date -d "$(date +%Y%m01) -1 day" +%Y/%m/%d
#显示上月最后一天

date -d "(date -d "1 mouth" +%Y%m01) -1 day" +%Y/%m/%d
#显示当月最后一天

4）恢复数据库

上面备份的数据库文件数据mysql_data-2023-11-20.tar.gz，作为远端异地已经备份好的数据库文件（IP:192.168.47.106），然后加载到另一台需要恢复的数据库主机（IP:192.168.47.105）。

#远端已备份数据文件主机（IP:192.168.47.106）
cd /opt/backup

scp mysql_data-2023-11-20.tar.gz 192.168.47.105:/opt
#将打包的data目录远程拷贝到另一台mysql主机的opt目录下

#需要恢复数据库文件主机（IP:192.168.47.107）
systemctl stop mysqld    #关闭mysql服务
cd /opt
tar xf mysql_data-2023-11-20.tar.gz 
mv /usr/local/mysql/data /usr/local/mysql/data.bak    #将原有的数据库文件移走
mv /opt/data/ /usr/local/mysql/
systemctl restart mysqld   #重新启动数据库服务
mysql -uroot -p369369      #此数据库密码是你备份数据库密码

show databases;
#查看现有数据库

show tables from zr;
#查看库中现有数据库

select * from zr.zr1;
#查看表内容

2.4.使用mysqldump备份数据库

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

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

mysqldump -uXXX -p[XXX] 库名 > XXX.sql
#备份指定库中的所有表数据（不包含创建库的操作）

mysqldump -uroot -p369369 zr > /opt/backup/zr.sql
#只备份表和表中的内容，并没有备份库

cd /opt/backup
ls

cat zr.sql |egrep -v "^--|^/\*|^$"           #过滤出需要的信息

mysqldump -uroot -p369369 --databases zr > /opt/backup/zr_data.sql
#备份一个完整的库到/opt/backup目录下

cat zr_data.sql |egrep -v "^--|^/\*|^$"            #过滤出需要的信息

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

mysqldump -u root -p369369 --all-databases > /opt/backup/all_data.sql
#完全备份 MySQL 服务器中所有的库

cat all_data.sql |grep "^CREATE DATABASE"

mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
#备份指定库中的一个或多个表数据（不包含创建库的操作）

mysqldump -u root -p369369 zr zr2 > /opt/backup/zr_zr2.sql
#备份zr库中zr2表

cat zr_zr2.sql |egrep -v "^--|^/\*|^$"
#查看备份内容信息

2.5.使用source命令恢复数据库

#记得提前备份

#模拟环境

#先登录mysql
mysql -uroot -p369369

drop database zr;
#删除zr库

source /opt/backup/zr_data.sql
#恢复完整的scj库

#注：一定要用绝对路径

#也可以只用表数据进行恢复
create database zr;
#创建新scj库

use zr;
#进入zr库

source /opt/backup/zr.sql
#恢复库中表数据

#因为zr.sql中不包含有创建库的操作，所以可以在任意一个库中导入表数据

2.6.使用mysql命令恢复数据库

#基本格式
mysql -uXXX -pXXX [库名] < XXX.sql          #如果sql文件只包含表的备份，要指定库名
或
cat XXX.sql | mysql -uXXX -pXXX [库名]

mysql -uroot -p369369 zr < zr.sql
#需要提前创建库

mysql -uroot -p369369  < zr_data.sql
#直接恢复完整的数据库

mysql -uroot -p369369 -e "drop database zr;"
#-e 执行命令后退出数据库

mysql -uroot -p369369 -e "show databases;"
#查看是否删除完成

mysql -uroot -p369369  < zr_data.sql

mysql -uroot -p369369 -e "show databases;"
#查看是否恢复完成

三.MySQL增量备份与恢复

3.1 使用完全备份所带来的问题

备份数据中有重复数据
备份时间与恢复时间过长

3.2.增量备份简介

是自上一次备份后增加和变化的文件或内容

3.3.特点

没有重复数据，备份量不大，时间短
恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

3.4.增量备份与日志的关系

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

3.4.1.二进制日志对备份的意义

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

3.5 增量恢复方法类别

3.5.1.一般恢复

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

3.5.2.基于位置恢复

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

3.5.3.基于时间点恢复

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

3.6 增量备份

3.6.1.开启二进制日志功能

vim /etc/my.cnf
#编辑配置文件

#添加
log-bin=mysql-bin
binlog_format = MIXED				#可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

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

systemctl restart mysqld
#重新启动服务

ls /usr/local/mysql/data

#因为二进制文件内容默认是二进制字符，人是看不懂的

#所以使用mysqlbinlog命令

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000001
#以base64方式进行解码，按行进行读取编码，显示详细内容，指定mysql-bin.000001文件

#除了可以用重启来刷新mysql二进制文件，还可以使用mysqladmin命令来刷新二进制文件

mysqladmin -uroot -p369369 flush-logs
#再次生成新的二进制日志文件

cat mysql-bin.index
#该文件保存着所有的二进制文件的序列号

3.6.2.实现增量备份

#模拟环境
select * from zr.zr1；
#事先准备表

#当天二进制文件是没有操作数据的
cat /usr/local/mysql/data/mysql-bin.000003  
#查看最新二进制日志文件

#首先实现完全备份
mkdir /opt/backup

cd /opt/backup
mkdir data bin
#data目录做完全备份，bin做增量备份

vim /opt/mysqlquan.sh     
#编写完全备份脚本  
#!/bin/bash  
mysqldump -u root -p369369 --all-databases > /bak/all_$(date +%F).sql  

chmod 700 /opt/mysqlquan.sh 
#添加属主的权限

vim /opt/binlog.sh 
#编写增量备份脚本
#!/bin/bash  
filename=$(sed -n '$p' /usr/local/mysql/data/mysql-bin.index | awk -F '/' '{print $2}') 
#使用sed命令打印二进制日志文件最后一行内容作为filename变量的值 
mv /usr/local/mysql/data/$filename /bak/binlog_$(date +%F)
#将二进制日志移动到备份目录下，并将移动后的文件名称加上日期  
mysqladmin -uroot -pabc123 flush-logs  
#生成新的二进制日志文件  

chmod 700 /opt/binlog.sh 
#添加属主的权限

crontab -e      #做计划任务

mysqldump -uroot -p369369 zr zr1 > /opt/backup/data/zr_zr1-$(date +%Y.%m.%d).sql
#将zr库下zr1表备份到/opt/backup/data下

ls /data/backup/   #查看是否备份完成

mysqladmin -uroot -p369369 flush-logs
#重新生成一个二进制文件

#进入数据库
#插入两条数据
insert into zr.zr1 values (7, '斗鸿劫', 25, '男', '老炮儿');
insert into zr.zr1 values (8, '粥芸饭', 18, '女', '厨师');

select * from zr.zr1;
#查看内容

mysqladmin -uroot -p369369 flush-logs
#保存上一个文件并生成新一个文件

mv -f mysql-bin.000006 /opt/backup/bin/mysql-bin.000006-$(date -d '1 day' +_%Y.%m.%d)

#进入数据库
#插入两条数据
insert into zr.zr1 values (9, '笑零乘', 28, '男', '宝马');
insert into zr.zr1 values (10, '记小䪊', 18, '女', '保时捷');

select * from zr.zr1;
#查看内容

mv -f mysql-bin.000007 /opt/backup/bin/mysql-bin.000007-$(date -d '1 day' +_%Y.%m.%d)

#进入数据库
#模拟被删库跑路了
drop database zr;

3.6.3.一般恢复

#现在进行恢复
create database zr;
#新建库

mysql -uroot -p369369 zr < /opt/backup/data/zr_zr1-2023.11.20.sql
#首先恢复基础表数据

#进入数据库
select * from zr.zr1；
#查看表内容

#恢复第二次输入的内容
mysqlbinlog --no-defaults /opt/backup/bin/mysql-bin.000006-_20231120 | mysql -uroot -p369369

#进入数据库
select * from zr.zr1；
#查看表内容

3.6.4.端点恢复

#将二进制文件转译
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000006-_2023.11.20 > mysql-bin-2023.11.20

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000007-_2023.11.21 > mysql-bin-2023.11.21

3.6.4.1.基于位置恢复

#基本格式
mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 二进制日志文件 | mysql -uXXX -pXXX

#示例
#只恢复斗鸿劫那一段

#进入数据库清空表数据
truncate table zr.zr1;

cat mysql-bin-2023.11.20
#查看具体位置点

mysqlbinlog --no-defaults --start-position='292' --stop-position='522' /opt/backup/bin/mysql-bin.000006-_2023.11.20 | mysql -uroot -p369369
#将具体的位置点传往数据库服务器
#注：要使用二进制文件，不要用编码后的二进制文件

#前往数据库
select * from zr.zr1;
#查看内容

3.6.4.2.基于时间点恢复

#基本格式
mysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-datetime='YYYY-mm-dd HH:MM:SS' 二进制日志文件 | mysql -uXXX -pXXX

#示例
#只恢复粥芸饭那一段

#进入数据库清空表数据
truncate table zr.zr1;

cat mysql-bin-2023.11.21
#查看具体时间点

mysqlbinlog --no-defaults --start-datetime='2023-11-20 19:19:26' --stop-datetime='2023-11-20 19:19:36' /opt/backup/bin/mysql-bin.000007-_2023.11.21 | mysql -uroot -p369369
#将具体时间点范围内的数据传往数据库服务器内

#前往数据库
select * from zr.zr1;
#查看内容