MySQL的备份和恢复

MySQL的备份与恢复

一、简聊数据备份

​ 　所谓备份,就是通过特定的办法,将数据库的必要文件复制到存储设备的过程，其中，存储设备是指用于放置数据库拷贝的磁带或磁盘。
　　选择备份的依据是：丢失数据的代价与确保数据不丢失的代价之比。还有的时候，硬件的备份有时根本就满足不了现实需要，比如误删了一个表，又想恢复该表的时候,数据库备份就变得重要了。

​ 数据备份的主要是实现数据的完整性，防止重要数据丢失无法恢复，造成数据丢失的原因：

• 程序错误
• 人为操作错误
• 运算错误
• 磁盘错误
• 灾难（如火灾、地震）和盗窃等

二、数据库备份的分类

从备份的物理和逻辑的角度，可以分为物理备份和逻辑备份。

1、物理备份

• 物理备份就是对数据库操作系统的物理数据（数据文件和日志文件等mysql的文件）的备份。
• 这种类型的备份适用于在出现问题的时候需要快速恢复的大型周昂要数据库。

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

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

物理备份的优点及使用场景：

优点：速度快，效率高。

场景：可用于停机维护及数据库物理迁移场景中。

2、逻辑备份

​ 逻辑备份其实就是利用MySQL数据库自带的mysqldump工具，或者使用第三方的工具，然后把数据库里的数据以SQL语句的方式导出成文件的形式。在需要恢复数据时，通过使用相关的命令（如：source ）将备份文件里的SQL语句提取出来重新在数据库中执行一遍，从而达到恢复数据的目的。

逻辑备份的优点和适用场景：

优点：简单，易操作，自带工具方便、可靠。

使用场景：数据库数据量不大的情况可以使用，数据量比较大时备份速度比较慢，一定程度上还会影响数据库本身的性能。

三、数据库备份策略

1、完全备份

• 每次对数据进行完整的备份。
• 可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。
• 但它需要花费更多的时间和空间，所以，做一次完全备份的周期较长。
• 恢复时，直接使用完全备份的文件。

简单描述：

2、差异备份

• 备份那些自从上次完全备份之后被修改过的所有文件。
• 它比最初的完全备份小，因为只包含自上次完全备份以来所改变的数据库，但是随着变化的越大，备份量也会越来越大。
• 它的优点是存储和恢复速度快。
• 恢复时，适用之前的完全备份和最佳的一次差异备份文件

3、增量备份

备份自从上次完全备份或增量备份的时间为时间点，只备份时间内的变化的数据

备份的数据量小，占用空间小，备份速度快。

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

由于恢复时需要所有备份，如果中间某次的备份数据损坏，将导致数据的丢失。

备份策略的比较

• 完全备份每次都是对整个数据或表的内容进行全部备份。
• 差异备份在第1次添加内容后备份，即备份新添加的内容，第2次备份会把第1次变化的和第2次变化的内容都备份，可以理解差异备份的时间点是根据完全备份来的。
• 增量备份只备份每次新修改的内容，不管变化多少，都是备份上次备份之后变化的内容，所以恢复状态时需要从完全备份到增量备份依次进行。

四、备份方法

1、物理冷备份

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

2、专用备份工具mysqldump和mysqlhotcopy

mysqldump：

mysqldump是 MySQL自带的逻辑备份工具。它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只要执行这些insert语句，即可将对应的数据还原。

• mysqldump常用的逻辑备份工具
• MySQL自带的备份工具，可实现对MySQL的备份

mysqlhotcopy：

MySQL mysqlhotcopy主要功能是当服务器在运行时快速备份MyISAM或ISAM表的工具。mysqlhotcopy是一个Perl脚本，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行运行在Unix和NetWare，并且MySQL数据库必须在本机。

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

MySQL支持增量备份，进行增量备份时必须启用二进制日志，二进制日志文件为用户提供复制对执行备份点后进行的数据库更改所需的信息进行恢复，如果进行增量备份（包含自上次完全备份或增量备份以来发生的数据修改），需要刷新二进制日志。

4、第三方工具备份

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

五、完全备份和恢复

1、物理冷备份和恢复

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

注：浅聊XZ压缩格式

xz是一个使用 LZMA压缩算法的无损数据压缩文件格式。和gzip与bzip2一样，同样支持多文件压缩，但是约定不能将多于一个的目标文件压缩进同一个档案文件。相反，xz通常作为一种归档文件自身的压缩格式。但是其压缩比比gzip和bzip2高。
官网链接： https://tukaani.org/xz/
官网介绍到： 压缩后的文件大小 gzip 小30％，比bzip2小15％。
1
2
3

1.1、开始进行备份

systemctl stop mysqld //关闭数据库
    
tar zcvf /opt/mysql_full_$(date +%Y%m%d).tar.gz /usr/local/mysql/data/
或
tar jcvf /opt/mysql_full_$(date +%F).tar.bz /usr/local/mysql/data/
//压缩格式随个人和实际环境进行选择，也可以使用cp复制。
1
2
3
4
5
6

1.2、恢复备份文件

模拟数据丢失

rm -rf /usr/local/mysql/data/*  //删除mysql的数据
1

systemctl start mysqld  //开启mysql
mysql -uroot -p         //尝试登陆root用户
ls /usr/local/mysql/data/  //查看现在的mysql的数据文件
1
2
3

systemctl stop mysqld //关闭数据库
tar zxvf /opt/mysql_all_*.tar.gz -C /  //解压备份文件
    
systemctl start mysqld //开启数据库
mysql -uroot -p         //尝试登陆root用户
1
2
3
4
5

2、使用mysqldump备份

2.1、完全备份一个或多个完整的库

mysqldump -u root -p --databases 库名 >/opt/库名_$(date +%F).sql
mysqldump -u root -p --databases 库名1 库名2 >/opt/库名_$(date +%F).sql
1
2

2.2、完全备份所有的库

mysqldump -u root -p --all-databases > /备份路径/备份文件名.sql
1

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

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

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

2.4 查看备份文件

因为mysqldump的工作原理其实备份文件就是sql的脚本文件，我们可以通过cat等阅读文件。
备份文件中存在一些其他注释，可以通过
grep -v "^--" /opt/*.sql | grep -v "^/" | grep -v "^$"
1
2
3

3、使用mysqldump恢复

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

1. source命令
2. mysql命令

3.1、source恢复

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

进入数据库
    source /opt/*.sql //恢复数据
1
2

3.2、mysql命令恢复

-e是调用mysql里的应用程序
    例：mysql -uroot -p123456 -e 'drop database 库名;'
 mysql -u root -p密码 < /opt/*.sql  //恢复数据
1
2
3

注：

mysql: [Warning] Using a password on the command line interface can be insecure.
    是mysql 5.6+以后针对安全性的一个提示，就是提示'在命令行上使用密码可能是不安全的。
1
2

4、加–databases和不加的区别

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

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

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

简单可以理解为，-databases的恢复会创建备份时指定的表在执行后续的sql语句。

而不加-databases则是直接开始执行sql语句，由于没有创建的指定数据库，所以会提示’No database selected‘，如果想要还原该备份文件，则选哟进入msql，新建指定库在使用source恢复数据

正确使用不带–databases的备份文件

六、增量备份和恢复

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

1. 一般恢复

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

2. 基于位置恢复

   • 数据库在某一时间点可能既有错误的操作也有正确的操作

   • 可以基于准确的位置跳过错误的操作

   • 发生错误节点之前的一个节点，上一次正确操作的位置点停止

3. 基于时间点恢复

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

   • 在错误时间停止，在下一个正确时间开始

1、开启二进制日志功能

二进制日志对备份的意义如下：

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

vim /etc/my.conf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED
server-id = 1
#二进制日志有三种不同的记录格式：STATEMENT（基于SQL语句）、ROW（基于行）、MIXED（混合模式），默认为STATEMENT
#server-id 为服务的序号，在MySQL主备、高可用中需要指定服务的序号
#改为配置文件需要重启服务
systemctl restart mysqld
1
2
3
4
5
6
7
8
9

注：三种不同的记录格式说明

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行，但是准确性高，高并发的时候由于操作量，性能变低比较大所以记录都记下来。
    
MIXED 推荐使用
-般的语句使用statement，函数使用ROw方式存储。
1
2
3
4
5
6
7
8
9
10
11
12

2、查看二进制文件的内容

2.1、生成新的二进制文件并查看其内容

cd /usr/local/mysql/data/
ls

mysqladmin -uroot -p密码 flush-logs
1
2
3
4

2.2、查看二进制文件

cp mysql-bin.000002 /opt/
#将记录变更的二进制文件02复制至/opt目录下
cd /opt/
ls

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#使用64位编码机制去解码，按行读取详细内容
1
2
3
4
5
6
7

3、一般恢复（完全备份+增量备份恢复方式）

首先创建好测试的库和表。

3.1、对测试库和表进行完全备份

mysqldump -u root -p123456 --databases test > /opt/mysql_bak/test.sql
mysqldump -uroot -p123456 test test01 > /opt/mysql_bak/test.test01.sql;
1
2

3.2、修改测试库并增量备份

对完备后的数据进行增量备份。
    mysqladmin -uroot -p123456 flush-logs
1
2

插入数据

insert into test02 values(7,'mahan','02',321321);
insert into test01 values(7,'mahan','beijin',321321);
insert into test01 values(6,'wangchao','beijin',321321);
1
2
3

对这次插入进行增量备份

mysqladmin -uroot -p123456 flush-logs
    ls /usr/local/mysql/data
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000007 >/opt/mysql_bak/mysql-bin.000007
1
2
3

再次修改数据并进行数据恢复

3.3、对完全备份之后的数据进行恢复

对完全备份和增量备份后，所有的数据丢失，需要把完全备份和所有增量备份的文件依次恢复。

完全备份恢复：

mysql -uroot -p123456 test < /opt/mysql_bak/test.sql
1

增量备份恢复：

mysqlbinlog --no-defaults 增量备份文件 | mysql -u -p  //增量备份数据恢复
    
    
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000007 | mysql -uroot -p123456
1
2
3
4

4. 基于位置和时间的恢复

• 利用二进制日志可实现基于时间点与位置的恢复，例如由于误操作删除了一张表，这时完全恢复是没有用的
• 因为日志里还有误操作的语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作的语句，再恢复后面操作的语句

4.1 基于位置的操作

• 基于位置的恢复，就是使用基于位置点的恢复。
• 针对特定的记录进行恢复，或者只需要指定的记录，进行位置恢复。

mysqlbinlog --no-defaults --stop-position='***' /***/mysql-bin.00000* | mysql -uroot -p
#使用64位编码机制去解码并按行读取二进制文件02（增量备份）的详细内容
mysqlbinlog --no-defaults --start-position='***' --stop-position='***' /***/mysql-bin.00000* | mysql -uroot -p 
//断点恢复，start表示从哪个点开始恢复（缺省为开头开始恢复），stop表示从哪个点结束（缺省为恢复到日志末尾）。

1
2
3
4
5

位置节点说明：

mysqlbinlog --no-defaults --stop-position='602' /usr/local/mysql/data/mysql-bin.000007 | mysql -uroot -p123456
1

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IBxD4geS-1660610096149)(C:\Users\15560\AppData\Roaming\Typora\typora-user-images\image-20220811162042433.png)]

只恢复test01.id=7的记录：

mysqlbinlog --no-defaults --start-position='602' --stop-position='728' /usr/local/mysql/data/mysql-bin.000007 | mysql -uroot -p123456
1

4.2 基于时间点的恢复

• 基于时间点的恢复，就是将某个起始时间的二进制文件导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复。
• 使用 mysqlbinlog 加上 --stop-datetime 选项，表示在哪个时间点结束，后面误操作的语句不执行。
• –start-datetime 选项表示执行后面的语句。
• 结合使用它们就可以跳过误操作的语句，完成恢复工作。
• 需要注意的是，二进制文件中保存的日期格式需要调整为用“-”分割。

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

例：仅恢复表test02的记录

mysqlbinlog --no-defaults --start-datetime='2022-08-11 14:57:08' /usr/local/mysql/data/mysql-bin.000007 | mysql -uroot -p123456
1

七、总结

如何选择备份时间？

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

通过定时任务进行备份。

crontab -e
#使用计划性任务来执行
30 3 * * 3 mysqldump -uroot -p123123 库 表 > /opt/备份文件名_$(date +%F).sql
30 3 * * 3 mysqldump -uroot -p123123 --all-databases 库 > /opt/备份文件名_$(date +%F).sql
    每周3的3：30进行完全备份
1
2
3
4
5