MySQL备份与恢复_mysql数据库备份与还原

MySQL备份与恢复

一、备份

1、数据备份的重要性

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

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

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

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

2、数据备份分类

2.1 物理备份

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

物理备份的方法 ：

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

2.2 逻辑备份

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

3、数据库备份策略

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

差异备份：只备份上一次完全备份后的更新数据

增量备份：每次备份只备份上一次完全备份或增量备份后的更新数据

4、常用的备份方法和工具

物理冷备

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

专用备份工具mysqldump或mysqlhotcopy

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

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

• mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

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

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

第三方工具备份

• 免费的MySQL热备份软件Percona;XtraBackup
• XtraBackup(物理热备)

5、数据库上云迁移

冷迁移

热迁移

二、数据库完全备份

1、简介

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

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

2、物理冷备份与恢复

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

2.1 物理冷备份

7-3
#准备数据
create table kkk1 (id int,name varchar(10),age int,sex char(2),hobby varchar(50));
insert into kkk1 values (1,'www',22,'女','java');
insert into kkk1 values (2,'yyy',20,'男','cake');
insert into kkk1 values (3,'xxx',21,'女','shell');
insert into kkk1 values (4,'hhh',23,'男','web');
select * from kkk1;

create table kkk2 like kkk1;
insert into kkk2 (select * from kkk1);
select * from kkk2;
1
2
3
4
5
6
7
8
9
10
11
12

7-3
systemctl stop mysqld
#先关闭数据库
mkdir /opt/backup
#建立备份文件存放目录
tar zcvf /opt/backup/mysql_full_$(date -d "-1 day" +%Y%m%d).tar.gz /usr/local/mysql/data/
#压缩备份
#对/usr/local/mysql/下的data目录进行备份，里面包含所有数据库的文件
1
2
3
4
5
6
7
8

2.2 备份恢复

主机1：192.168.210.103

主机2：192.168.210.106

scp /opt/backup/mysql_full_20230922.tar.gz 192.168.210.106:/opt
#将主机1的备份包远程传输到主机2
1
2

主机2：
systemctl stop mysqld
#先关闭数据库
mv /usr/local/mysql/data /usr/local/mysql/data.bak
#先备份数据，防止恢复失败
1
2
3
4
5

cd /opt/
tar xf mysql_full_20230922.tar.gz
#解压
cp -a /opt/usr/local/mysql/data /usr/local/mysql/
#把opt下的data目录移到/usr/local/mysql/目录下
1
2
3
4
5

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

2.3 补充知识date

date --help
#命令帮助
1
2

date +%F
#完整日期格式
date +%Y%m%d
#年月日
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) -3 day" +%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
#当月的最后一天
date -d "-1 day" +%Y%m01
#这个月第一天
date -d "1 mouth" +%Y%m01
#下个月第一天
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

3、mysqldump备份与恢复

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

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

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

如果不加 --databases ,只备份库中的表，不会备份库
1
2
3
4

#举个例子

#单个数据库
mysqldump -u root -p --databases www > /opt/backup/www.sql

#多个数据库
mysqldump -u root -p --databases www www > /opt/backup/3w.sql

1
2
3
4
5
6
7
8

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

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

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

1
2

#举个例子
mysqldump -u root -pabc123 --all-databases > /opt/backup/all.sql

cat /opt/all.sql | grep "^CERATE DATABASES"
1
2
3
4

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

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

#备份www的kkk1表
mysqldump -u root -p [-d] www kkk1 > /opt/backup/www-kkk1.sql

#使用“-d”选项，说明只保存数据库的表结构
#不使用“-d”选项，说明表数据也进行备份
1
2
3
4
5

3.4 查看备份文件

cat xx.sql|grep -v "^--"| grep -v "^/" | grep -v "^$"

egrep -v "^--|^/\*|^$" 
1
2
3

#举个例子
cat www-kkk1.sql|grep -v "^--" | grep -v "^/" | grep -v "^$"
1
2

三、MySQL 完全恢复

1、恢复数据库

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

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

1.1 通过source命令恢复

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

然后登录到MySQL数据库；

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

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

举例
1

先备份目标数据库，以www数据库为例
mysqldump -u root -pabc123 --databases www > /opt/backup/www.sql
1
2

假设数据库www被删除
drop database www;
show databases;
1
2
3

source命令执行sql脚本
#先登录到数据库
mysql -u root -p
#执行脚本
source /opt/backup/www.sql 
#查看
show databases;
1
2
3
4
5
6
7

1.2 通过mysql命令恢复

输入重定向
1

mysql -u 用户名 -p[密码] < 库备份脚本的路径
1

举例
1

#先模拟数据库删除
mysql -u root -pabc123 -e 'drop database www;'
mysql -u root -pabc123 -e 'show databases;'
1
2
3

#恢复数据库
mysql -u root -pabc123 < /opt/backup/www.sql
mysql -u root -pabc123 -e 'show databases;'
1
2
3

---

管道符
1

mysql -u root -pabc123 -e 'drop database www;'
mysql -u root -pabc123 -e 'show databases;'

cat www.sql | mysql -uroot -pabc123
mysql -u root -pabc123 -e 'show databases;'
1
2
3
4
5

2、恢复数据表

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

#备份www的kkk1表
mysqldump -u root -pabc123 [-d] www kkk1 > /opt/backup/www-kkk1.sql
1
2

#模拟删除数据表
mysql -u root -pabc123 -e 'drop table www.kkk1;'
mysql -u root -pabc123 -e 'show tables from www;'
1
2
3

#恢复表数据
mysql -uroot -pabc123 www < /opt/backup/www-kkk1.sql
#查看表数据，验证是否成功恢复
mysql -uroot -pabc123 -e "select * from www.kkk1;"
1
2
3
4

四、MySQL增量备份与恢复

1、简介

使用mysqldump进行完全备份存在的问题

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

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

特点

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

MySQL没有提供直接的增量备份方法

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

MySQL二进制日志对备份的意义

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

2、MySQL增量备份

2.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
1
2
3
4
5
6
7
8

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

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

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

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

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

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

3、MySQL增量恢复

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

3.1 一般恢复

mysqlbinlog --no-defaults 二进制日志文件 | mysql -uXXX -pXXX
1

mkdir mysql-backup
mkdir /mysql-backup/full
mkdir /mysql-backup/bin 
#full文件夹做完全备份
#bin文件夹做增量备份
1
2
3
4
5

1）在www数据库中，有一个kkk1的数据表,对表中的初始数据进行完全备份

#对kkk1表做完全备份
mysqldump -uroot -pabc123 www kkk1 > /root/mysql-backup/full/www_kkk1-$(date +%Y%m%d).sql
#二进制日志文件刷新
mysqladmin -uroot -pabc123 flush-logs
#生成新的二进制日志文件
ls /usr/local/mysql/data/
1
2
3
4
5
6

2）录入新的数据，进行增量备份

insert into kkk1 values (5,'aaa',23,'男','football');
insert into kkk1 values (6,'bbb',24,'女','dancing');
insert into kkk1 values (7,'ccc',25,'女','singing');
1
2
3

#二进制日志文件刷新
mysqladmin -uroot -pabc123 flush-logs
#生成新的二进制日志文件
ls /usr/local/mysql/data/
1
2
3
4

#进行增量备份，备份的不是最新的那一个，备份的是倒数第二个
mv -f /usr/local/mysql/data/mysql-bin.000002 /root/mysql-backup/bin/mysql-bin.00000 -$(date -d '1 day' +%Y%m%d)

ls /mysql-backup/bin 
1
2
3
4

3）模拟删除库，先进行表的完全恢复

show databases;
drop database www;
#因为只对表做了备份，没有对库做备份，所以要先建一个数据库
create database www;
show databases;
quit
#先完全恢复数据表里的初始数据
mysql -uroot -pabc123 www < /mysql-backup/full/www_kkk1-20230923.sql
1
2
3
4
5
6
7
8

4）进行增量恢复

#通过管道符导入的方式
mysqlbinlog --no-defaults /mysql-backup/bin/mysql-bin.000002-20230924 | mysql -uroot -pabc123
1
2

3.2 断点恢复

#查看二进制文件 
mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql-backup/bin/mysql-bin.000002-20230924
#重定向输出到文件里方便查看
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002-20230924 > mysql-bin-20230924
1
2
3
4

#删除id>=5的数据
delete from kkk1 where id>=5;
select * from kkk1;
1
2
3

3.2.1基于位置恢复

#仅恢复id=5和6的数据，即不恢复“id=7”的数据
mysqlbinlog --no-defaults --start-position='296' --stop-position='891' /mysql-backup/bin/mysql-bin.000002-20230924 | mysql -uroot -pabc123
1
2

#仅恢复“id=7”的数据
mysqlbinlog --no-defaults --start-position='891' /mysql-backup/bin/mysql-bin.000002-20230924 | mysql -uroot -pabc123
1
2

3.2.2 基于时间点恢复

#仅恢复id=5和6的数据
mysqlbinlog --no-defaults --start-datetime='2023-09-23 14:24:05' --stop-datetime='2023-09-23 14:24:32' /mysql-backup/bin/mysql-bin.000002-20230924 | mysql -uroot -pabc123
1
2

#仅恢复id=6和7的数据
mysqlbinlog --no-defaults --start-datetime='2023-09-23 14:24:27' /mysql-backup/bin/mysql-bin.000002-20230924 | mysql -uroot -pabc123
1
2

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

4、备份与恢复实例(完全+增量)

为了测试备份数据是否可用，模拟数据丢失故障，进行数据还原。
在数据库表中，分三次录入学生考试成绩。
首次录入成绩后，做该表的完全备份。
后两次成绩的录入之后，分别作增量备份。
模拟数据丢失，并使用增量备份分别基于位置和时间点恢复一班所有学生成绩与二班陈铭与付杰的成绩。
1
2
3
4
5

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

mysql -u root -pabc123
create database score;
use score;
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);
select * from student;
1
2
3
4
5
6
7
8
9

#通过mysqldump工具完全备份
mysqldump -u root -pabc123 score student > /mysql-backup/full/score-student.sql
1
2

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

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED				
#可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1
#刷新日志文件
mysqladmin -uroot -pabc123 flush-logs
1
2
3
4
5
6
7
8

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

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

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

#再次插入数据
insert into student values ('付杰','二班',20170828,115,118,116,268);
insert into student values ('郭尚','二班',20170830,111,99,80,259);
1
2
3

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

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

#删除表
drop table student from score;
1
2

#先完全恢复，一班所有学生的成绩
mysql -uroot -pabc123 score < /mysql-backup/full/score-student.sql
1
2

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

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

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

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

#根据时间恢复
mysqlbinlog --no-defaults --start-datetime='23-09-24 14:36:13' --stop-datetime='23-09-24 14:36:18' /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -pabc123
1
2

五、MySQL日志管理

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秒

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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;				
#在数据库中设置开启慢查询的方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

#!/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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25