数据库的备份与恢复（完全备份，增量备份）

最新推荐文章于 2024-09-24 14:19:22 发布

与之二三事、

最新推荐文章于 2024-09-24 14:19:22 发布

阅读量2.8k

点赞数

分类专栏：数据库文章标签： mysql

原文链接：https://blog.csdn.net/weixin_42300424/article/details/113948577

版权

数据库专栏收录该内容

13 篇文章 1 订阅

订阅专栏

在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果，所以我们要及时做好备份的工作
在使用数据库的过程中，有多种原因造成数据的丢失：
程序错误
人为错误（大部分原因）
计算机失败
磁盘失败
灾难（如火灾，地震）和偷窃

1. 数据库备份的分类

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

物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
物理备份的方法：
冷备份（脱机备份）：是在关闭数据库的时候进行的
热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
温备份（联机备份）：数据库锁定表格（不可写入但可读）的状态下进行备份操作
数据库在物理层面分成了三类文件，每一个文件的名字以表的名字开始，扩展名指出文件类型：
表结构文件：.frm文件存储表定义
表数据文件的扩展名为.MYD（ MYData）
表索引文件的扩展名是.MYI（ MYIndex）

逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

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

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

2. 常见的备份方法

物理冷备
备份时数据库处于关闭状态，直接打包数据库文件
备份速度快，恢复时也是最简单的
专用备份工具mydump或mysqlhotcopy
mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
启用二进制日志进行增量备份
进行增量备份，需要刷新二进制日志
第三方工具备份
免费的MySQL热备份软件Percona XtraBackup

3. MySQL完全备份与分类

3.1 完全备份概述

完全备份是对整个数据库的备份、数据库结构和文件结构的备份
完全备份保存的是备份完成时刻的数据库
完全备份是增量备份的基础
优点：
安全性高
备份与恢复操作简单方便
缺点：
数据存在大量的重复
占用大量的备份空间，空间利用率低
备份与恢复时间长

3.2 备份方式

3.2.1 物理冷备份及恢复

首先先关闭数据库，然后进行备份，关闭是为了防止数据的丢失

物理冷备：
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

恢复数据库：
1、先使用tar zxvf命令解压打包的数据库文件
2、使用mv命令将其覆盖原有的数据库data目录
3、使用systemctl start mysqld命令启动数据库

3.2.2 使用专用备份工具 mysqldump

mysqldump命令是MySQLI自带的备份工具，相当方便对MySQL进行备份
通过该命令工具可以将指定的库、表或全部的库导出为SQL脚本，在需要恢复时可进行数据恢复 .sql脚本
备份单个库是备份所有表还原的时候要先创建此数据库

备份库语法格式：
mysqldump -u 用户名 -p [密码] [选项] [库名] > /备份路径/备份文件名

备份表语法格式：
mysqldump -u 用户名 -p [密码] [选项] 数据库名 表名 > /备份路径/备份文件名

3.2.2.1 备份库

备份单个库：
[root@client ~]# mysqldump -uroot -pabc123 --databases test > /opt/bak.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@client ~]# 
[root@client ~]# cd /opt
[root@client opt]# ls
bak.sql   mysql-2021-07-14.tar.xz  mysql-boost-5.7.20.tar.gz   mysql-5.7.20   

备份多个库：
[root@client ~]# mysqldump -uroot -pabc123 --databases test test01 > /opt/bak01.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@client ~]# cd /opt
[root@client opt]# 
[root@client opt]# ls
bak01.sql                mysql-2021-07-14.tar.xz    
bak.sql                  mysql-5.7.20               
mysql-boost-5.7.20.tar.gz  rh

备份全部库：
[root@client ~]# mysqldump -uroot -pabc123 --all-databases > /opt/bakall.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@client ~]# cd /opt
[root@client opt]# ls
bak01.sql   mysql-5.7.20     bakall.sql       mysqlbak.sql    bak.sql     mysql-2021-07-14.tar.xz  mysql-boost-5.7.20.tar.gz

3.2.2.1 备份表

备份单个表：
[root@client ~]# mysqldump -uroot -pabc123 test aaa > /opt/aaa.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

备份表结构：
[root@client opt]# mysqldump -uroot -pabc123 -d test aaa > /opt/aaa-d.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

3.3 恢复方式

使用 mysqldump命令导出的SQL备份脚本，在进行数据恢复时可使用以下方法导入：
source命令
mysql命令

3.3.1 使用source命令恢复数据库

登录到 MySQL数据库
执行 source备份sql脚本的路径（绝对路径）

mysql > source /opt/bak.sql
'//source命令在mysql库中使用'

3.3.2 使用mysql命令恢复数据库

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

3.3.3 恢复数据表

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

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

4. MySQL增量备份与恢复

4.1 增量备份概述

只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
增量备份就是备份自上一次备份之后增加或变化的文件或者内容

优点：
没有重复数据，效率高，空间利用率最大化
备份量不大，时间短

缺点：
恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
安全性较低

4.2 如何实现增量备份?

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

4.3 实现增量备份

4.3.1 开启二进制文件日志的功能

[root@client ~]# vim /etc/my.cnf
在后面添加如下内容:
log-bin=mysql-bin		   #开启bin-log
binlog_format = MIXED      #指定二进制日志(binlog)的记录格式为MIXED（混合输入），STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT
server-id = 1			   #标识该语句最初是从哪个server写入的

[root@client ~]# cd /usr/local/mysql/data/
[root@client data]# systemctl restart mysqld
[root@client data]# ls
auto.cnf        ibdata1      ib_logfile1  mysql             mysql-bin.index     sys
ib_buffer_pool  ib_logfile0  ibtmp1       mysql-bin.000001  performance_schema
#可以看到多了mysql-bin.000001这个文件，发现已经生成了二进制文件，设置成功

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

STATEMENT（基于SQL语句）:
每一条涉及到被修改的sql 都会记录在binlog中

ROW(基于行)
只记录变动的记录，不记录sql的上下文环境

MIXED 推荐使用
一般的语句使用statement，函数使用ROW方式存储。

查看二进制文件：
[root@client data]# cp /usr/local/mysql/data/mysql-bin.000001 /opt/
[root@client data]# cd /opt
[root@client opt]# 
[root@client opt]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001
#需要解码才能看到二进制文件

mysqlbinlog：基于日志管理操作的命令
--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
-v: 显示详细内容
--no-defaults : 默认字符集（不加会报错：UTF-8的错误）

二进制文件几个参数解释：

at ：开始的位置点
end_log_pos：结束的位置
时间戳： 210715 14:35:33
SQL语句;insert、select等

4.3.2 创建库和表，进行完全备份和总量备份

[root@client ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table aaa (id int(4) primary key,name char(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into aaa values(1,'zhangsan'),(2,'lisi');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *from aaa;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

mysql> exit
Bye

[root@client ~]# mysqldump -uroot -p test > /opt/test.sql #完全备份
Enter password: 
[root@client ~]# mysqladmin -uroot -p flush-logs #增量备份
Enter password: 
[root@client ~]# cd /usr/local/mysql/data/
[root@client data]# ls
mysql-bin.000001     mysql-bin.000002  ......
'//发现已经有了增量备份文件，刚刚备份的操作记录都存放在 mysql-bin.000001中， mysql-bin.000002是准备存放后续操作的'

4.3.3 进行正常操作和误操作，进行增量备份

mysql> insert into aaa values(3,'wangwu');#正常操作
Query OK, 1 row affected (0.00 sec)

mysql> delete from aaa where id=3;#误操作
Query OK, 1 row affected (0.01 sec)

mysql> insert into aaa values(4,'zhaoliu');#正常操作
Query OK, 1 row affected (0.00 sec)

mysql> select *from aaa;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  4 | zhaoliu  |
+----+----------+
3 rows in set (0.00 sec)
mysql> exit
Bye

[root@client data]# mysqladmin -uroot -p flush-logs #再次进行增量备份
Enter password: 
[root@client data]# ls
mysql-bin.000003 mysql-bin.000001 mysql-bin.000002 ......
#发现再次生成新的增量备份文件ysql-bin.000003
#刚刚的正常操作和无操作都存放到了mysql-bin.000002中

4.3.4 查看增量备份文件

[root@client data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt
[root@client data]# cat /opt/bak.txt #可以看到刚才的mql操作语句
......
# at 298
#210715 23:50:17 server id 1  end_log_pos 406 CRC32 0xfb755924 	Quer
use `test`/*!*/;
SET TIMESTAMP=1626364217/*!*/;
insert into aaa values(3,'wangwu')#正常操作
/*!*/;
# at 406
#210715 23:50:17 server id 1  end_log_pos 437 CRC32 0x057a5b37 	Xid 
COMMIT/*!*/;
# at 437
#210715 23:50:42 server id 1  end_log_pos 502 CRC32 0x9c8e094c 	Anon
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 502
#210715 23:50:42 server id 1  end_log_pos 581 CRC32 0xe022458d 	Quer
SET TIMESTAMP=1626364242/*!*/;
BEGIN
/*!*/;
# at 581
#210715 23:50:42 server id 1  end_log_pos 681 CRC32 0x42ff9a02 	Quer
SET TIMESTAMP=1626364242/*!*/;
delete from aaa where id=3  #误操作
/*!*/;
# at 681
......

4.4 增量恢复方式

增量恢复的步骤：
添加数据–进行完全备份–录入新的数据–进行增量备份–模拟故障–恢复操作

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

mysqlbinlog --no-defaults --base64-output=decode-rows -v 日志文件名称 /opt/aaa.txt	'//使用64位解码器按行输出日志文件放到/opt/aaa.txt中'
cat /opt/aaa.txt  '//查看日志文件的详细信息'

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

查看是否恢复成功：

方式一：交互式–使用source命令
登录到MySQL数据库
执行source备份sql脚本的路径

方式二：无交互式–使用musql命令
无须登录MySQL数据库
使用-e删除数据表
执行mysql备份sql脚本的路径

4.4.1 模拟故障，删除表并恢复，一般恢复

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| aaa            |
+----------------+
1 row in set (0.00 sec)

mysql> drop table aaa;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> source /opt/test.sql

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| aaa            |
+----------------+
1 row in set (0.00 sec)

mysql> select *from aaa;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

mysql>

4.4.2 基于时间点恢复

查看一下表的内容：
mysql> select * from aaa;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[root@client data]# mysqldump -uroot -pabc123 --databases test > /opt/123.sql
#先进行完全备份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@client data]# ls /opt/
......    123.sql       ......
[root@client data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 

[root@client data]# mysqladmin -u root -pabc123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@client data]# ls
auto.cnf        ib_logfile0  mysql             mysql-bin.index     test
ib_buffer_pool  ib_logfile1  mysql-bin.000001  performance_schema
ibdata1         ibtmp1       mysql-bin.000002  sys
[root@client data]# mysql -uroot -pabc123 < /opt/123.sql
#先恢复完全备份
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@client data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 

# at 298
#210716 13:27:00 server id 1  end_log_pos 407 CRC32 0xc382d6ad 	Query	thread_id=4	exec_time=0error_code=0
use `test`/*!*/;
SET TIMESTAMP=1626413220/*!*/;
insert into aaa values(3,'zhaoliu')
/*!*/;
# at 407

# at 582
#210716 13:29:14 server id 1  end_log_pos 682 CRC32 0xfb0cdcb2 	Query	thread_id=4	exec_time=0error_code=0
SET TIMESTAMP=1626413354/*!*/;
delete from aaa where id=3
/*!*/;
# at 682

[root@client data]# mysqlbinlog --no-defaults --stop-datetime='2021-07-16 13:29:14' mysql-bin.000001 | mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.


这时候可以看到数据库恢复了之前的一条记录：
mysql> select * from aaa;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

mysql> select * from aaa;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | zhaoliu  |
+----+----------+
3 rows in set (0.00 sec)

4.4.3 基于位置恢复

mysql> insert into aaa values(4,'wangwu');
Query OK, 1 row affected (0.00 sec)

mysql> delete from aaa where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select *from aaa;
+----+---------+
| id | name    |
+----+---------+
|  2 | lisi    |
|  3 | zhaoliu |
|  4 | wangwu  |
+----+---------+
3 rows in set (0.00 sec)

[root@client data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
#这是进行的操作存放在02之中，可以进行查看

# at 1931
#210716 15:31:30 server id 1  end_log_pos 2039 CRC32 0x5c7fd8aa 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1626420690/*!*/;
insert into aaa values(4,'wangwu')
/*!*/;
# at 2039

# at 2214
#210716 15:32:42 server id 1  end_log_pos 2314 CRC32 0x45194e2c 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1626420762/*!*/;
delete from aaa where id=1
/*!*/;
# at 2314

[root@client data]# mysqladmin -u root -pabc123 flush-logs
[root@client data]# ls
auto.cnf        ib_logfile0  mysql             mysql-bin.000003    sys
ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql-bin.index     test
ibdata1         ibtmp1       mysql-bin.000002  performance_schema

[root@client data]# mysqlbinlog --no-defaults --stop-position='1931' mysql-bin.000002 | mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.

#查看一下数据库，数据恢复了
mysql> select *from aaa;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | zhaoliu  |
+----+----------+
3 rows in set (0.00 sec)