MySQL备份与恢复

---

引言

备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。在备份、恢复中，日志起到了很重要的作用。
一、MySQL日志管理
MySQL 的日志默认保存位置为**/usr/local/mysql/data**
MySQL 的日志配置文件为/etc/my.cnf，里面有个**[mysqld]**项。

[root@yzq ~]# vim /etc/my.cnf
1

1、日志的分类

1.1 错误日志

用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

vim /etc/my.cnf
log-error=/usr/local/mysql/data/mysql_error.log	 #指定日志的保存位置和文件名

1
2
3

1.2 通用查询日志

用来记录MySQL的所有连接和语句，默认是关闭的

vim /etc/my.cnf
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

1
2
3
4

1.3 二进制日志

用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启

vim /etc/my.cnf

log-bin=mysql-bin
或
log_bin=mysql-bin				

1
2
3
4
5
6

1.4 中继日志

一般情况下它在Mysql主从同步（复制）、读写分离集群的节点开始。主节点一般不需要这个日志。

1.5 慢查询日志

用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的

vim /etc/my.cnf
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5	

1
2
3
4
5

1.6 配置日志

[root@yzq ~]# vim /etc/my.cnf
[root@yzq ~]# systemctl restart mysqld

1
2
3

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  #设置超过5秒执行的语句被记录，缺省时为10秒
1
2
3
4
5
6
7

2、日志的查询

#登入mysql
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

PS:variables 表示变量 like 表示模糊查询

#xxx（字段）
xxx% 以xxx为开头的字段
%xxx 以xxx为结尾的字段
%xxx% 只要出现xxx字段的都会显示出来
xxx 精准查询

#二进制日志开启后，重启mysql 会在目录中查看到二进制日志
[root@yzq data]# systemctl restart mysqld.service
[root@yzq data]# cd /usr/local/mysql/data/
[root@yzq data]# ls
mysql-bin.000001		#开启二进制日志时会产生一个索引文件及一个索引列表
1
2
3
4
5
6

索引文件：记录更新语句
索引文件刷新方式：
1、重启mysql的时候会更新索引文件，用于记录新的更新语句
2、刷新二进制日志

mysql-bin.index：
二进制日志文件的索引

二、数据备份的重要性

• 1.备份的主要目的是灾难恢复

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

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

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

三、备份类型

1、物理备份

数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库。

物理备份又可以成为冷备份（脱机备份）、热备份（连接备份）和温备份

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

1.1 冷备份

冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份时将要害性文件拷贝到另外的位置的一种说法。对于备份Oracle信息而言，冷备份是最快和最安全的方法。冷备份的优点是：

• 1.是非常快速的备份方法（只需拷文件）
• 2.轻易归档（简单拷贝即可）
• 3.轻易恢复到某个时间点上（只需将文件再拷贝回去）
• 4能与归档方法相结合，做数据库“最佳状态”的恢复。
• 5.低度维护，高度安全。

冷备份也有如下不足：

• 1.单独使用时，只能提供到“某一时间点上”的恢复。
• ２.再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
• 3.若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。
• 4.不能按表或按用户恢复。
  　　
  　　假如可能的话（主要看效率），应将信息备份到磁盘上，然后启动数据库（使用户可以工作）并将备份的信息拷贝到磁带上（拷贝的同时，数据库也可以工作）。冷备份中必须拷贝的文件包括：
• 1所有数据文件
• 2.所有控制文件
• 3.所有联机REDO LOG文件
• 4.Init.ora文件（可选）
  　　
  　　值得注重的使冷备份必须在数据库关闭的情况下进行，当数据库处于打开状态时，执行数据库文件系统备份是无效的。
  　　
  冷备份的完整例子。
• 1.关闭数据库

　　sqlplus /nolog
　　sql>connect /as sysdba
　　sql>shutdown normal;
1
2
3

• 2.用拷贝命令备份全部的时间文件、重做日志文件、控制文件、初始化参数文件

　　sql>cp
1

• 3重启Oracle数据库

　　sql>startup
1

1.2 热备份

热备份是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法。所以，假如你有昨天夜里的一个冷备份而且又有今天的热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可以做备份了。热备份的命令文件由三部分组成：

1.2.1 数据文件一个表空间的备份

• 1.设置表空间为备份状态
• 2.备份表空间的数据文件
• 3.回复表空间为正常状态

1.2.2 备份归档log文件

• 1.临时停止归档进程
• 2.log下那些在archive rede log目标目录中的文件
• 3.重新启动archive进程
• 4.备份归档的redo log文件

1.2.3 用alter database bachup controlfile命令来备份控制文件热备份的优点

• 1.可在表空间或数据库文件级备份，备份的时间短。
• 2.备份时数据库仍可使用。
• 3.可达到秒级恢复（恢复到某一时间点上）。
• 4.可对几乎所有数据库实体做恢复
• 5.恢复是快速的，在大多数情况下爱数据库仍工作时恢复。

1.2.4 热备份的不足

• 1.不能出错，否则后果严重
• 2.若热备份不成功，所得结果不可用于时间点的恢复
• 3.因难于维护，所以要非常仔细小心，不答应“以失败告终”。

2、逻辑备份

• 1.逻辑备份是对数据库逻辑组件的备份，表示为逻辑数据库结构
• 2.这种类型的备份适用于可以编辑书库值或表结构

四、备份策略

从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份。

1、完全备份

每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长

每次都进行完全备份，会导致备份文件占用空间巨大，并且有大量的重复数据，恢复时，直接使用完全备份的文件即可。

完全备份是对整个数据库、数据库结构和文件结构的备份。
保存的是备份完成时刻的数据库。
是差异备份与增量备份的基础。
相当于基石。
1
2
3
4

优点：

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

缺点：

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

2、差异备份

• 备份那些自从上次完全备份之后"被修改"过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时只需要恢复上次的完全备份与最佳的一次差异备份

• 每次差异备份，都会备份上一次完全备份之后的数据，可能会出现重复数据。恢复时，先恢复完全备份的数据，再恢复差异备份的数据。

3、增量备份

• 只有那些在上次完全备份或者增量备份后被修改的文件才会被备份以上次完整备份或上次增量备份的时间为时间点，仅备份期间内的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。（类似火车）

• 每次增量备份都是在备份在上一次完全备份或者增量备份之后的数据，不会出现重复数据的情况，也不会占用额外的磁盘空间。

• 恢复数据，需要按照次序恢复完全备份和增量备份的数据

4、如何选择逻辑备份策略

合理值区间

• 1.一周一次的全备，全备的时间需要在不提供业务的时间区间进行，22点到5点之间进行全备
• 2.增量：3天/2天/1天一次增量备份
• 3.差异：选择特定的场景进行备份
• 4.一个处理（NAS）提供额外空间给mysql服务器用

五、常见的备份方法

1、物理冷备

• 备份时数据库处于关闭状态，直接打包数据库文件
• 备份速度快，恢复时也是最简单的
• 关闭MySQL数据库
• 使用tar命令直接打包数据库文件夹
• 直接替换现有的MySQL目录即可

2、专用备份工具mysqldump或者mysqlhotcopy

2.1 mysqldump工具

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

2.2 mysqlhotcopy工具

• mysqlhotcopy仅拥有备份myisam和archive表

3、启动二进制日志进行增量备份

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

4、第三方工具备份

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

六、MySQL完全备份与恢复

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

1、优点：

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

2、缺点：

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

数据库完全备份分类

1、物理冷备份与恢复（tar）

• 关闭MySQL数据库
• 使用tar命令直接打包数据库文件夹
• 直接替换现有MySQL目录即可
  物理冷备份—》关闭mysqld服务----》tar命令打包data目录（恢复就是解压他的tar包即可）

关闭数据库，下载xz

[root@yzq data]# systemctl stop mysqld.service
[root@yzq data]# yum -y install xz

1
2
3

压缩备份

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

1
2

解压恢复

[root@yzq data]# tar Jxf /opt/mysql_all_2022-09-18.tar.xz -C /usr/local/mysql/data #如果数据库文件损坏数据丢失，可以直接解压备份文件
[root@yzq data]# systemctl restart mysqld

1
2
3

2、mysqldump备份与恢复

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

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

2.2.1 备份yzq库

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

[root@yzq opt]# mysqldump -uroot -p123123 --databases yzq > /opt/yzq.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@yzq opt]# cd /opt/
[root@yzq opt]# ls
1
2
3
4

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

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

1
2

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

mysqldump -u root -p[密码] [-d] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
#不使用“-d”选项，说明表数据也进行备份
#使用“-d”选项，说明只保存数据库的表结构
1
2
3

只备份yzq库下haha表的结构

[root@yzq opt]# mysqldump -uroot -p123123 -d yzq haha  > /opt/yzq_haha.sql
[root@yzq opt]# cat /opt/yzq_haha.sql 
1
2

2.4 查看备份文件

[root@yzq opt]# grep -v "^--" /opt/yzq_haha.sql | grep -v "^/" | grep -v "^$"
DROP TABLE IF EXISTS `haha`;
CREATE TABLE `haha` (
  `id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `habby` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

1
2
3
4
5
6
7
8
9

3、MySQL完全恢复

3.1 恢复数据库

使用mysqldump导出的文件，可使用导入的方法

• 1.source命令
• 2.mysql命令

使用source恢复数据库的步骤

• 1.登录到MySQL数据库
• 2.执行source备份sql脚本的路径

1.source命令恢复整库

2.mysql命令整库恢复

因为库被删了，需要先创建库再进行恢复，否则会报错

免交互
-e是调用mysql里的应用程序

mysql -uroot -p123123 -e 'drop database yzq;'
mysql -uroot -p123123 -e 'show databases;'

mysql -uroot -p123123 < /opt/yzq.sql #恢复数据库，将库备份文件重定向到MySQL
mysql -uroot -p123123 -e 'show databases;'

1
2
3
4
5
6

3.2 恢复数据表

1.source恢复表

drop table haha;
show tables;
source /opt/yzq_haha.sql
show tables;
1
2
3
4

表恢复

mysql命令恢复表

[root@yzq opt]# mysql -uroot -p123123 -e 'show tables in yzq;'
[root@yzq opt]# mysql -uroot -p123123 -e 'drop table yzq.haha;'
[root@yzq opt]# mysql -uroot -p123123 -e 'show tables in yzq;'
[root@yzq opt]# mysql -uroot -p123123 yzq < /opt/yzq_haha.sql
[root@yzq opt]# mysql -uroot -p123123 -e 'show tables in yzq;'
1
2
3
4
5

haha表删除

3.3 加–databases和不加的区别

mysqldump 严格来说属于温备份，会需要对表进行写入的锁定

在全量备份与恢复实验中，假设现有yzq库，school库中有一个haha表，需要注意的一点为:

当备份时加–databases ,表示针对于school库

备份命令

[root@yzq opt]# mysqldump -uroot -p123123 --databases yzq > /opt/yzq1.sql
1

备份库后恢复命令过程为:

mysql -uroot -p123123
drop database yzq;
exit

mysql -uroot -p123456 < /opt/yzq1.sql

1
2
3
4
5
6

当备份时不加–databases，表示针对yzq库下的所有表

mysqldump -uroot -p123123 school > /opt/yzq_all.sql
mysql -uroot -p123123 -e 'drop database yzq;'
mysql -uroot -p123123 < /opt/yzq_all.sql

mysql -uroot -p123123 -e 'create database yzq;'
mysql -uroot -p123123 school < /opt/yzq_all.sql
mysql -uroot -p123123 -e 'use yzq;show tables;'

1
2
3
4
5
6
7
8

七、MySQL 增量备份与恢复

MySQL数据库增量恢复有，一般恢复、基于位置恢复、基于时间点恢复。

1.增量备份的特点

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

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

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

2.实现增量备份

2.1 开启二进制日志功能

[root@yzq opt]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED      #可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入）
server-id = 1              #可加可不加该命令
[root@yzq opt]# systemctl restart mysqld.service 
1
2
3
4
5
6

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

• 每一条涉及到被修改的sql都会记录在binlog中

缺点:日志量过大，如sleep()函数，last_insert_id()>，以及user-definedfuctions ( udf) 、垂从复制等架构记录日志时会出现问题

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

ROW(基于行)

• 只记录变动的记录，不记录sql的上下文环境

缺点:如果遇到update… …set… . .where true那么binlog的数据量会越来越大
总结: update、 delete以多行数据起作用，来用行记录下来，只记录变动的记录，不记录sql的上下文环境，比如sql语句记录一行，但是Row就可能记录10行，但是准确性高，高并发的时候由于操作量能变低比较大所以记录都记下来。

2.2 查看二进制文件内容

二进制文件无法直接编辑查看，需要对其进行转换

mysqlbinlog --no-defaults 二进制日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件
#--base64-output=decode-rows 使用64位编码机制去解码；decode按行读取rows
#-v 显示详细内容
#--no-defaults 默认字符集（不加会报UTF-8错误）
#将解码后的文件导出为txt文件，重定向输出
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件 > 文件.txt
#刷新日志文件
mysqladmin -uroot -p flush-logs

1
2
3
4
5
6
7
8
9
10

at ：开始的位置点
end_log_pos：结束的位置
时间戳： 220919 0:53:18
SQL语句

2.3 进行完全备份并更新二进制日志

[root@yzq data]# mysqldump -uroot -p123123 yzq > /opt/yzq_all_$(date +%F).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@yzq data]# mysqladmin -u root -p flush-logs #刷新，可每天进行增量备份操作，生成新的二进制日志文件
Enter password:
1
2
3
4

2.4 插入新数据，模拟数据的增加并再次生成新的二进制日志

PS:在第一次完全备份之后刷新二进制文件，在第二个二进制文件中记载着"增量备份的数据"

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

[root@yzq data]# mysqladmin -u root -p flush-logs
Enter password: 
[root@yzq data]# ls
auto.cnf        ib_logfile1       mysql-bin.000003  mysql_error.log       sys
CLASS           ibtmp1            mysql-bin.000004  mysql_general.log     usr
ib_buffer_pool  mysql             mysql-bin.000005  mysql_slow_query.log  yzq
ibdata1         mysql-bin.000001  mysql-bin.000006  performance_schema
ib_logfile0     mysql-bin.000002  mysql-bin.index   school

1
2
3
4
5
6
7
8
9

3.实现增量恢复

3.1 删除表test

3.2 基于二进制日志文件恢复

[root@yzq data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -p123123
[root@yzq data]# mysql -uroot -p123123 -e 'show tables in yzq;'
1
2

3.3断点恢复

• 数据库在某一时间点可能既有错误的操作也有正确的操作，可以基于精准的位置跳过错误的操作
• 发生错误节点指点的上一个节点，上一次正确操作的位置点停止
• 模拟节点恢复，刷新日志，生成新的日志文件
  
  基于时间点恢复

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
1

[root@yzq data]# mysqlbinlog --no-defaults --stop-datetime='2022-09-19 1:11:48' mysql-bin.000005 | mysql -uroot -p123123 #从执行1后开始恢复
mysql: [Warning] Using a password on the command line interface can be insecure.

1
2
3

基于位置点恢复

mysqlbinlog [--no-defaults] --start-position=' ' --stop-position=' ' 二进制日志 | mysql -u 用户名 -p 密码
1

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

总结

• 物理冷备份—》关闭mysqld服务----》tar命令打包data目录（恢复就是解压他的tar包即可）

• 逻辑备份—》mysqldump -u(用户) -p(密码) --databases 库1 库2 > xxx.sql
  mysqldump -u -p --all-databases >xxx.sql
  mysqldump -u -p 库1 表1 表2 > xxx.sql

• 完全恢复
  mysql -u -p < xxx.sql (恢复数据库)
  mysql -u -p 库名 < xxx.sql (恢复表)

• 增量备份
  开启二进制日志，设置二进制格式MIXED（混合）
  进行一次完全备份，可每周备份一次，通过crontab -e
  使用mysqladmin -uroot -p flush-logs刷新分割出二进制日志，由于刷新之前的数据操作都记录在老的二进制日志里，所以需要把老的二进制日志复制到一个安全的目录中保管
  可以通过mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件名查看日志内容
  可以通过mysqlbinlog --no-dedfaults 二进制日志文件名
  mysql -uroot -p 恢复丢失的数据
  位置恢复
  时间回复

• show variables like ‘%storage_engine%’; #查看默认存储引擎