MySQL的备份与恢复

一、备份

1.1 数据备份的必要性

备份的主要目的是灾难恢复

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

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

造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障
灾难(如火灾、地震)和盗窃

1.2 数据备份分类

1.2.1 物理备份

对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。

物理备份的方法 ：

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

1.2.2 逻辑备份

对数据库的表或者对象进行备份。

1.3 数据库备份策略

完全备份：整个数据集，每次都备份完整的数据库。

差异备份：仅备份最近一次完全备份后变化的数据，备份较慢，还原简单

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

1.4 常用的备份方法和工具

物理冷备
备份时数据库处于关闭状态，直接打包数据库文件

备份速度快，恢复时也是最简单的。

专用备份工具mysqldump或mysqlhotcopy

mysqldump常用的逻辑备份工具（逻辑热备）

备份速度较慢（如果数据库超过100G，一般不建议使用mysqldump)

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

archive

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

第三方工具备份
免费的MySQL热备份软件Percona;XtraBackup

XtraBackup(物理热备)

https://mp.weixin.qq.com/s/d3spGhku154dOgWSct-3Ww

1.5 数据库上云迁移

冷迁移

热迁移

二、MySQL完全备份

2.1 简介

优点
备份与恢复操作简单方便
缺点
数据存在大量的重复占用大量的备份空间
备份与恢复时间长

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

2.2 物理冷备份与恢复

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

2.2.1 物理冷备份

#先关闭数据库
systemctl stop mysqld
#建立备份文件存放目录
mkdir /opt/bak#压缩备份
tar zcvf /opt/bak/mysql_full_$(date +%F).tar.gz /usr/local/mysql/data/$(date -d "-1 day" +%Y%m%d)

2.2.2 解压恢复

主机1：192.168.2.106

主机2：192.168.2.105

备份到另一台主机

#先关闭数据库
systemctl stop mysqldscp 192.168.2.106:/opt/bak/* /opt 
#将主机1的备份包远程传输到主机2

#先备份数据，防止恢复失败
mv /usr/local/mysql/data /usr/local/mysql/data.bak

tar xf mysql_full_2023-09-18.tar.gzcp -a /../data /usr/local/mysql/data  
#不用-a 权限不足，无法启动

systemctl start mysqld
mysql -u root -p 
#登录到数据库

2.3 mysqldump备份与恢复

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

mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql	
#导出的就是数据库脚本文件如果不加 --databases ,只备份库中的表，不会备份库

#举个例子#单个数据库
mysqldump -u root -p --databases byyb > /opt/bak/byyb.sql#多个数据库
mysqldump -u root -p --databases byyb byyd > /opt/bak/bd.sql

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

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

#举个例子
mysqldump -u root -p123123 --all-databases > /opt/bak/all.sqlcat /opt/all.sql | grep "^CERATE DATABASES"

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

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

举个例子

#备份byyb的student表
mysqldump -u root -p [-d] byyb student > /opt/bak/byyb-student.sql#使用“-d”选项，说明只保存数据库的表结构
#不使用“-d”选项，说明表数据也进行备份

4）查看备份文件

cat xx.sql|grep -v "^--"| grep -v "^/" | grep -v "^$"egrp -v "^--|^/\*|^$" #或运行符

#举个例子
cat byyb-student.sql|grep -v "^--" | grep -v "^/" | grep -v "^$"

三、MySQL 完全恢复

3.1 恢复数据库

当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在。

在生产环境中，可以使用Shell脚本自动实现定时备份

3.1.1 通过source命令恢复

先使用mysqldump工具，生成备份sql脚本；

然后登录到MySQL数据库；

再执行source 备份sql脚本的路径

MySQL[(none)]> source /backup/all-data.sql

举个例子

先备份目标数据库，以byyb为例
mysqldump -uroot -p123123 --databases byyb > /opt/bak/byyb.sql

模拟数据库被删除
mysql -uroot -p123123 -e "drop database byyb;"
mysql -uroot -p123123 -e "show databases;"

source命令执行sql脚本#先登录到数据库
mysql -u root -p
#执行脚本
source /opt/bak/byyb.sql;

3.1.2 通过mysql命令

输入重定向

#先模拟数据库删除，这里不再赘述#恢复数据库
mysql -u root -p123123 < /opt/bak/byyb.sql
mysql -u root -p123123 -e 'show databases;'

管道符

cat byyb.sql | mysql -uroot -p123123

3.2 恢复数据表

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

#备份byyb的student表
mysqldump -u root -p123123 [-d] byyb student > /opt/bak/byyb-student.sql

#模拟删除表
mysql -u root -p -e 'drop table byyb.student;'
mysql -u root -p -e 'show tables from byyb;'

#恢复表数据
mysql -uroot -p123123 byyb < /opt/bak/byyb-student.sql
#查看表数据，验证是否成功恢复
mysql -uroot -p123123 -e "select * from byyb.student;"

四、MySQL增量备份与恢复

4.1 MySQL增量备份

4.1.1 开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED				#可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

systemctl start mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

4.2.2 通过刷新二进制日志文件间接实现增量备份

mysqladmin -uroot -p[密码] flush-logs

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

mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制文件（绝对路径）
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容

#举个例子
cp /usr/local/mysql/data/mysql-bin.000001 /opt 
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001

4.2 MySQL增量恢复

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

4.2.1 一般恢复

1）模拟丢失更改的数据的恢复步骤

use byyb;
delete from student where id=3;
delete from student where id=4;mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

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

use kgc;
drop table info1;mysql -u root -p kgc < /opt/kgc_info1_2020-11-22.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

4.2.2 断点恢复

#查看二进制文件 
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

1）基于位置恢复

mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 二进制日志文件（绝对路径） | mysql -uroot -p[密码]

#仅恢复到操作 ID 为“623”之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -uroot -p

2）基于时间点恢复

mysqlbinlog --no-defaults --start-datetime='时间点' --stop-datetime='时间点' 二进制日志文件（绝对路径） | mysql -uroot -p[密码]

#仅恢复到 16∶41∶24 之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p

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

4.3 备份与恢复配置实例（完全+增量）

为了测试备份数据是否可用，模拟数据丢失故障，进行数据还原。

在数据库表中，分三次录入学生考试成绩。

首次录入成绩后，做该表的完全备份。

后两次成绩的录入之后，分别作增量备份。

模拟数据丢失，并使用增量备份分别基于位置和时间点恢复一班所有学生成绩与二班陈铭与付杰的成绩。

1）录入第一次数据，并进行完全备份

mysql -u root -p 
create database byyb;
use byyb;
create table student ("姓名" varchar(10),"班级" varchar(10),"学号" int,'语文' int,"数学" int,"英语" int,"理综" int);
insert into student values ('张三','一班',20170822,110,105,92,235);
insert into student values ('李四','一班',20170820,95,115,110,260);
insert into student values ('王五','一班',20170818,95,103,108,270);
insert into student values ('赵六','一班',20170816,100,109,112,265);
#其余数据添加格式类似

通过mysqldump工具完全备份
mysqldump -uroot -p123123  byyb student > /opt/bak/byyb-student.sql

2）录入第二次数据，进行增量备份

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED				#可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

insert into student values ('李宁','二班',20170824,92,98,105,235);
insert into student values ('陈铭','二班',20170826,111,107,96,204);

#通过刷新日志文件，实现增量备份
mysqladmin -uroot -p123123 flush-logs
#查看二进制日志文件，不是看最新的，看倒数第二个
mysqlbinlog --no-defaults --base64-output=decode-rows -v
/usr/local/mysql/data/mysql-bin.000002

3）录入第三次数据，进行增量备份

再次插入数据
insert into byyb.student values ('付杰','二班',20170828,115,118,116,268);
insert into byyb.student values ('郭尚','二班',20170830,111,99,80,259);insert into byyb.student values ('王云雪','我们班',123321,111,99,80,259);

#通过刷新日志文件，实现增量备份
mysqladmin -uroot -p123123 flush-logs
#查看二进制日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v
/usr/local/mysql/data/mysql-bin.000003

4）模拟删除表，先进行完全恢复

模拟删除表
drop table student from byyb;

先完全恢复，一班所有学生的成绩
mysql -u root -p123123 byyb < /opt/bak/byyb.sql

mysql -uroot -p123123 -e "select * from byyb.student;"

5）进行增量恢复（通过位置+时间）

通过位置恢复二班陈铭的成绩
#先查看二进制日志文件 
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002

#根据位置恢复
mysqlbinlog --no-defaults --start-position='616' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123

通过时间恢复二班付杰的成绩
#先查看二进制日志文件 
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003

#根据时间恢复
mysqlbinlog --no-defaults --start-datetime='23-09-18 19:38:48' --stop-datetime='23-09-18 19:39:16' /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p123123mysql -uroot -p123123 -e " select * from byyb.student;"  

五、MySQL日志管理

5.1 日志分类

• 事务日志：transaction log

  事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write ahead logging事务日志文件： ib_logfile0， ib_logfile1

• 错误日志 error log

• 通用日志 general log

• 慢查询日志 slow query log

• 二进制日志 binary log

• 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

5.2 日志管理

MySQL 的日志默认保存位置为 /usr/local/mysql/data

vim /etc/my.cnf
[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##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5												#设置超过5秒执行的语句被记录，缺省时为10秒systemctl restart mysqld

mysql -u root -p
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;				#在数据库中设置开启慢查询的方法

#!/bin/bash
#增量备份Mysql数据库
TODAY=$(date +%Y%m%d)
YESTERDAY=$(date -d "-1 day!+%Y%m%d)MYSQL 
HOME=/usr/local/mysglDATA HOME=SMYSQL HOME/data
USERNAME=root
PASSWORD=abc123#备份二进制日志索弓文件 mysgl-bin.index
/usr/bin/cp -f $DATA_HOME/mysgl-bin.index /opt/backup/mysgl-bin-$TODAY.index
#判断是否存在前一天的日志索引文件，如果存在则通过反向查询获取今天生成的二进制日志文件
if [ -f /opt/backup/mysgl-bin-$YESTERDAY.index ] 
then
COUNT=$(cat /opt/backup/mysgl-bin-$TODAY.index|grep -v "`cat /opt/backup/mysgl-bin-$YESTERDAY.index`"|awk -F '/' '{print $2}')
else
COUNT=$(cat /opt/backup/mysgl-bin-$TODAY.index | awk -F '/' '{print $2}')
fi
#刷新二进制日志文件
$MYSQL_HOME/bin/mysqladmin -u"$USERNAME"-p"$PASSWORD" flush-logs &> /dev/null
#使用循环备份当天的二进制日志
for BINLOG in $COUNT
/usr/bin/mv -f "$DATA_HOME/$BINLOG" "/opt/backup/$BINLOG-$TODAY"
done

小结

mysqldump逻辑热备
mysqldump -uXXX -p[XXX] --databases 库1 [库2 ...] > XXX.sql           备份一个或多个库及库中的表数据
mysqldump -uXXX -p[XXX] --all-databases > XXX.sql                     备份所有的库
mysqldump -uXXX -p[XXX] 库名 > XXX.sql                                备份指定库中的所有表数据（不包含创建库的操作）
mysqldump -uXXX -p[XXX] 库名 表1 表2 ... > XXX.sql                    备份指定库中的一个或多个表数据（不包含创建库的操作）xtrabackup物理热备完全恢复
先登录到mysql，再执行 source XXX.sql（注：如果sql文件只包含表的备份，需要先创建库并use切换库）mysql -uXXX -pXXX [库名] < XXX.sql          #如果sql文件只包含表的备份，要指定库名
cat XXX.sql | mysql -uXXX -pXXX [库名]增量备份
通过刷新二进制日志文件间接实现增量备份
mysqladmin -uXXX -pXXX flush-logs查看二进制日志文件内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件使用二进制日志增量恢复
mysqlbinlog --no-defaults 二进制日志文件 | mysql -uXXX -pXXX端点恢复
mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 二进制日志文件 | mysql -uXXX -pXXXmysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-datetime='YYYY-mm-dd HH:MM:SS' 二进制日志文件 | mysql -uXXX -pXXX如果需要恢复到某条SQL语句之前的所有数据，就stop在这个语句的位置点或时间点
如果需要恢复某条SQL语句及其之后的所有数据，就从这个语句的位置点或时间点start