本文详细介绍了MySQL数据库的备份与恢复，包括完全备份、增量备份的原理和操作方法，以及如何利用二进制日志进行恢复。文章通过实例演示了物理冷备份、mysqldump逻辑备份以及不同类型的恢复策略，对于数据库管理员来说是一份实用的指南。
MySQL数据库的备份与恢复
前言
一、数据库备份的分类
二、MySQL完全备份与恢复
三、MySQL增量备份与恢复
前言
前几天我们已经讨论了 ，怎么对数据库进行基本的增删改查的操作，今天我们就来谈谈数据库的安全问题。一个很简单的问题，数据库在我们日常工作中，由于软硬件的问题，出现了丢失或者损坏怎么办？这个是个非常严重的问题。而造成数据丢失的问题有很多种：程序错误、人为操作事故（※这个占的比分比较大，多少人员自己的问题）、运算错误、磁盘故障、灾难（如火灾、地震等等外部不可抗力）还有盗窃（作为企业安保的重要不言而喻）。假如数据库出现了问题，我们应该怎么办？把磁盘拆下来重新检查？显然不可能，而且也不现实，所以我们就需要在日常中对数据库进行备份（玩游戏的都不会知道，我要睡觉了该怎么办？存档嘛），对！这个就数据局库进行存档(save)。既然有save那就有重载(load)，也就是恢复。
一、数据库备份的分类
-
从物理与逻辑的角度，备份可分为：
-
从数据库的备份策略角度，备份可分为
- 完全备份：每次对数据库进行的完成的备份
- 差异备份：备份自从上次完全备份之后被修改过的文件
- 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会备份
常见的备份方法
-
物理冷备
- 备份时数据库处于关闭状态，直接打包好数据库文件
- 备份速度快，恢复是也是最简单的
-
专用备份工具mydump或mysqlhotcopy
- mysqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
-
启用二进制日志进行增量备份
- 进行增量备份，需要刷新二进制日志
-
第三方工具备份
- 免费的MySQL热备份软件Percona XtraBackup
二、MySQL完全备份与恢复
- 是对整个数据库、数据库结构和文件结构的备份
- 保存的是备份完成时刻的数据库
- 是差异备份与增量备份的基础
看到上面的备份方法，我们可猜到完全备份的优缺点了：
由此可以看出，完全备份完全取决于数据库的内容大小，假如数据库过于庞大，则完全备用耗费的资源会很多。
接下来我们来看一下怎么进行数据库的完全备份
-
物理冷备份与恢复
- 关闭MySQL数据库
- 使用tar命令直接打包数据库文件夹
- 直接替换现在的MySQL目录即可
-
mysqldump备份恢复
- MySQL自带的备份工具，可方便实现对MySQL的备份
- 可以将指定的库、表导出为SQL脚本
-
使用命令mysql导入备份的数据
我们现在就开始真机操作吧
物理冷备份与恢复
首先我们需要创建一个名为“text”的数据库，对它进行模拟操作
[root@zhu ~]# systemctl stop mysqld ###关闭数据库####
[root@zhu ~]# mkdir /backup ###在根下新建个backup目录
[root@zhu ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/ #####/usr/local/mysql/data备份压缩
[root@zhu ~]# cd /backup/ ###进入/backup/
[root@zhu backup]# ll ##查看备份
然后，我们在模拟下数据库的故障
[root@zhu backup]# mkdir /bak ####在根下新建bak目录
[root@zhu backup]# mv /usr/local/mysql/data/ /bak/ ####将目录data数据移动到bak 里面去
[root@zhu backup]# cd /usr/local/mysql/ #####仅mysql目录查看data
[root@zhu mysql]# ll ####查看后data目录被移走 ，模拟故障成功
上面看出data目录已经被移除，数据库无法读取，接下来我们开始恢复操作，需要做的就是将打包的数据库文件再次导入原有数据库内
[root@zhu mysql]# mkdir /restore ####在跟目录新建一个restore的目录
[root@zhu mysql]# tar xzvf /backup/mysql_all-2020-09-13.tar.gz -C /restore/ ####将备份数据库解压到 /restore目录下
[root@zhu mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/ ####将备份数据到/usr/local/mysql中
[root@zhu mysql]# systemctl start mysqld ####启动mysql数据库
[root@zhu mysql]# systemctl status mysqld ####查看mysql数据库启动状态 ，日志文件显示正常。
最后我们进行验证
可以看出，创建的数据库已经恢复。
mysqldump备份恢复
将指定的库、表、或全部的库导出为SQL脚本
mysqldump备份需要和mysql进行数据交互，如果关闭mysql 则无法备份和恢复
#####备份操作#####
mysqldump [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名
mysqldump [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名
mysqldump [选项] --all-databases > /备份路径/备份文件名
#####恢复操作#####
mysql [选项] [库名] [表名] < /备份路径/备份文件名
我们还是用text得数据库进行验证，这时候我们要在库中创造一个名为student的表,然后在里面增加信息
mysql> create table student(学号 int(3) NOT NULL, 姓名 CHAR(16), primary key(学号));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student values('20','lisi');
Query OK, 1 row affected (0.00 sec)
接着导出数据库和表
[root@zhu ~]# mysqldump -u root -p text student >mysql-student.sql
Enter password:
[root@zhu ~]# mysqldump -u root -p --databases text >text.sql
Enter password:
通过命令查看导出的数据
下面我们看是导入数据，可以在创建一个text1的数据库
mysql> create database text1; ####新建一个数据库，等下进行导入
Query OK, 1 row affected (0.00 sec)
mysql> exit
下面开始数据导入
[root@zhu ~]# mysql -uroot -p text1 <mysql-student.sql ###切记跟导出不一样，导入是没有dump的
可以看出数据表已经导入到新的数据库中了
然后我们把text数据库删了然后在导入新的数据
mysql> drop database text; ####删除text数据库
Query OK, 1 row affected (0.01 sec)
mysql> exit
[root@zhu ~]# mysql -uroot -p <text.sql ####将备份文件导回数据库中
Enter password:
[root@zhu ~]#
最后查看验证是否已经备份恢复
验证成功！
但是请注意运用mysqldump方式备份恢复是有他的弊端的！
- 备份数据中有重复数据
- 备份时间与恢复时间过长
所以我们会用到下面这种方法-----
三、MySQL增量备份与恢复
增量备份顾名思义是备份上一次备份之后增加/变化的文件或者内容，这种方法的特点是：
- 没有重复诗句，恢复量不大，时间短
- 恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
※请注意：MySQL是没有能提供直接的增量备份方法的，要实线增量备份可以通过MySQL提供的二进制日志来间接实线，所以二进制日志对备份的意义是巨大。
体现在在下面几个方面：
- 二进制日志保存了所有更新或者可能更新数据库的操作
- 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
- 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就王城了一个时间段的增量备份
可以看出对二进制文件操作的话可以得出不同的恢复内容，所以增量备份恢复可以分为：一般恢复、基于位置恢复、基于时间点恢复。
- 一般恢复
mysqlbinlog [--no-defaults]增量备份文件 | mysql -u 用户名 -p
-
基于位置的的恢复
- 恢复数据到指定位置
mysqlbinlog --stop -position='操作 id'二进制日志 | mysql -u 用户名 -p 密码- 从指定的位置开始恢复数据
mysqlbinlog --start -position='操作 id'二进制日志 | mysql -u 用户名 -p 密码 -
基于时间点的恢复
- 从日志开头截止到某个时间点的恢复
mysqlbinlog [--no-default] --stop -datetime='年-月-日 小时:分:秒' 二进制日志 | mysql -u 用户名 -p 密码- 从某个时间点到日志结尾的恢复
mysqlbinlog [--no-defaults] --start -datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码- 从某个时间点到某个时间点的恢复
mysqlbinlog [--no-defaults] --start -datetime='年-月-日 小时:分钟:秒' --stop -datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
要实现增量备份，首先我们要开机数据库的二进制日志功能，下面介绍方法：
对my.cnf进行配置
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
log-bin=/usr/local/mysql/data/mysql-bin ####添加这段来开启二进制功能
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
[root@zhu ~]# systemctl restart mysqld ####重启数据库服务
[root@zhu ~]# ll /usr/local/mysql/data/ ####查看二进制日志
上面已经生成了二进制日志
下面我们开始增量操作，前门我们提过，记性增量备份恢复前我们必须进行一次完全备份，这边我们就快速的完成下完全备份，并且这边我们需要在备份前把数据库上锁，防止备份时有数据写入
mysql> flush tables with read lock; ####开启数据全局读锁定以便记性数据库备份
Query OK, 0 rows affected (0.00 sec)
mysql> exit
[root@zhu ~]# mysqldump -u root -p text student >/mysql_bak/text_student-$(date +%F).sql ####全局备份打上时间戳
[root@zhu ~]# cd /mysql_bak/ ####开始验证是已经完成备份数据
[root@zhu mysql_bak]# ll
总用量 4
-rw-r--r-- 1 root root 1868 9月 14 16:56 text_student-2020-09-14.sql
[root@zhu mysql_bak]#
接下来我们对数据库写入数据，开始进行增量备份，记住我们要把表的锁定去除
mysql> unlock tables; ####在mysql数据库中解锁
然后添加变量
接下来开始生成新的二进制文件
[root@zhu mysql_bak]# mysqladmin -u root -p flush-logs
Enter password:
[root@zhu mysql_bak]# ll
总用量 4
-rw-r--r-- 1 root root 1868 9月 14 16:56 text_student-2020-09-14.sql
[root@zhu mysql_bak]# ll /usr/local/mysql/data
总用量 122928
drwxr-x--- 2 mysql mysql 64 9月 11 09:45 @6570@636e@5e93@ff1b
drwxr-x--- 2 mysql mysql 64 9月 11 11:49 @73ed@7ea7
drwxr-x--- 2 mysql mysql 54 9月 10 16:31 admin
-rw-r----- 1 mysql mysql 56 9月 10 11:49 auto.cnf
-rw-r----- 1 mysql mysql 333 9月 14 16:15 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 9月 14 17:10 ibdata1
-rw-r----- 1 mysql mysql 50331648 9月 14 17:10 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 9月 10 11:49 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 9月 14 16:56 ibtmp1
drwxr-x--- 2 mysql mysql 4096 9月 10 11:49 mysql
-rw-r----- 1 mysql mysql 1276 9月 14 17:21 mysql-bin.000001
-rw-r----- 1 mysql mysql 154 9月 14 17:21 mysql-bin.000002 #####这边2行就是新生成的二进制文件
-rw-r----- 1 mysql mysql 78 9月 14 17:21 mysql-bin.index
drwxr-x--- 2 mysql mysql 8192 9月 10 11:49 performance_schema
drwxr-x--- 2 mysql mysql 8192 9月 10 11:49 sys
drwxr-x--- 2 mysql mysql 58 9月 13 20:24 text
drwxr-x--- 2 mysql mysql 58 9月 13 20:12 text1
模拟删除数据表
[root@zhu mysql_bak]# mysql -u root -p -e 'drop table text.student' ####删除数据表
Enter password:
[root@zhu mysql_bak]# mysql -u root -p -e 'select * from text.student' ####查看能否获取表数据
Enter password:
ERROR 1146 (42S02) at line 1: Table 'text.student' doesn't exist ####无法获取数据，说明数据表删除
[root@zhu mysql_bak]#
然后我们将备份的数据表导回到数据库中
[root@zhu mysql_bak]# mysql -u root -p text < /mysql_bak/text_student-2020-09-14.sql ####导回数据表
Enter password:
[root@zhu mysql_bak]# mysql -u root -p -e 'select * from text.student' ####查看数据表是不是有数据
Enter password:
+--------+--------+
| 学号 | 姓名 |
+--------+--------+
| 20 | lisi |
+--------+--------+
[root@zhu mysql_bak]# ####导入成功
这个属于一般恢复，下面我们开始看怎么进行基于位置的恢复，看到新生成的二进制文件了嘛？那个就是我们进行位置恢复的基础
[root@zhu mysql_bak]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 |mysql -u root -p
Enter password:
[root@zhu mysql_bak]# mysql -u root -p -e'select * from text.student'
Enter password:
+--------+---------+
| 学号 | 姓名 |
+--------+---------+
| 20 | lisi |
| 26 | wangliu |
+--------+---------+
[root@zhu mysql_bak]#
可以看出已经通过我们保存的二进制日志通过位置恢复了
最后我们再来看一下怎么进行时间恢复
首先我们需要读取二进制日志中的各种节点
[root@zhu mysql_bak]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002
####通过base64解码 然后分行显示数据
[root@zhu mysql_bak]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200914 17:21:09 server id 1 end_log_pos 123 CRC32 0xbf56fe15 Start: binlog v 4, server v 5.7.20-log created 200914 17:21:09
# at 123
#200914 17:21:09 server id 1 end_log_pos 154 CRC32 0xde2ae007 Previous-GTIDs
# [empty]
# at 154
#200914 17:26:42 server id 1 end_log_pos 219 CRC32 0x0391d89c Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200914 17:26:42 server id 1 end_log_pos 291 CRC32 0xc9f61105 Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1600075602/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073414/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#200914 17:26:42 server id 1 end_log_pos 344 CRC32 0x4bcb9a26 Table_map: `text`.`student` mapped to number 228
# at 344
#200914 17:26:42 server id 1 end_log_pos 392 CRC32 0x06e1921c Write_rows: table id 228 flags: STMT_END_F
### INSERT INTO `text`.`student`
### SET
### @1=26
### @2='wangliu'
# at 392
#200914 17:26:42 server id 1 end_log_pos 423 CRC32 0x4db4b844 Xid = 91
COMMIT/*!*/;
# at 423
#200914 17:26:51 server id 1 end_log_pos 470 CRC32 0x8e41b473 Rotate to mysql-bin.000003 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
然后我们可以看每段时间后面都会有一个节点，我们恢复可以通过节点恢复
[root@zhu mysql_bak]# mysqlbinlog --no-defaults --stop-position='291' /mysql_bak/mysql-bin.000002 |mysql -u root -p
Enter password:
[root@zhu mysql_bak]# mysql -u root -p -e'select * from text.student'
Enter password:
+--------+---------+
| 学号 | 姓名 |
+--------+---------+
| 20 | lisi |
| 26 | wangliu |
+--------+---------+
[root@zhu mysql_bak]#
最后我们还可以通过时间戳对来恢复
[root@zhu mysql_bak]# mysqlbinlog --no-defaults --stop-datetime='2020-9-14 17:26:42' /mysql_bak/mysql-bin.000002 |mysql -u root -p
####记住时间戳不要搞错了
nter password:
[root@zhu mysql_bak]# mysql -u root -p -e’select * from text.student’
Enter password:
±-------±--------+
| 学号 | 姓名 |
±-------±--------+
| 20 | lisi |
| 26 | wangliu |
±-------±--------+
[root@zhu mysql_bak]#
最后我们还可以通过时间戳对来恢复
```shell
[root@zhu mysql_bak]# mysqlbinlog --no-defaults --stop-datetime='2020-9-14 17:26:42' /mysql_bak/mysql-bin.000002 |mysql -u root -p
####记住时间戳不要搞错了
以上就是MySQL数据库的恢复操作，其实虽然洋洋洒洒的这么多，其实真正细分起来也没多少，老铁们可以好好的琢磨下。希望对各位的数据库理解来带方便。
155
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
