十、Mysql - 全备份 - 根据二进制日志还原数据

目录

知识点1：数据库的备份

物理（原始）与逻辑备份

 在线与离线备份

本地与远程备份 

知识点2：mysqldump备份数据库

知识点3：备份和还原操作

1、产生一个全新的二进制日志

2、进行数据库全备份

3、让数据库进行新增，删除操作。

4、模拟出现故障，删除数据库

5、开始取恢复数据

根据起始时间恢复数据 

第一步、恢复全备

找到起始时间点 

恢复数据，可以看到，我们删除的数据就又回来了

根据位置号恢复数据

首先将数据库删除

然后导入全备份的数据

 找到起始位置点

选择位置点进行恢复

---

知识点1：数据库的备份

备份数据库非常重要，这样您就可以恢复数据并在出现问题时重新启动并运行，例如系统崩溃、硬件故障或用户误删除数据。在升级 MySQL 安装之前，备份也是必不可少的保障，它们可用于将 MySQL 安装转移到另一个系统或设置副本服务器。

物理（原始）与逻辑备份

物理备份由存储数据库内容的目录和文件的原始副本组成。这种类型的备份适用于出现问题时需要快速恢复的大型重要数据库。

逻辑备份将信息保存为逻辑数据库结构（CREATE DATABASE, CREATE TABLE语句）和内容（INSERT语句或分隔文本文件）。这种类型的备份适用于少量数据，您可以在其中编辑数据值或表结构，或在不同的机器架构上重新创建数据。

逻辑备份方法具有以下特点：

• 备份是通过查询 MySQL 服务器以获取数据库结构和内容信息来完成的。

• 备份比物理方法慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。如果输出是在客户端写入的，服务器也必须将其发送到备份程序。

• 输出大于物理备份，尤其是以文本格式保存时

 在线与离线备份

在 MySQL 服务器运行时进行在线备份，以便可以从服务器获取数据库信息。离线备份在服务器停止时进行。这种区别也可以描述为“热”备份与 “冷”备份；“热”备份是服务器保持运行但在您从外部访问数据库文件时锁定以防止修改数据的备份 。

本地与远程备份 

本地备份是在 MySQL 服务器运行的同一主机上执行的，而远程备份是从不同的主机上完成的。对于某些类型的备份，即使输出是本地写入服务器上的，也可以从远程主机启动备份。

########################################################################### 

知识点2：mysqldump备份数据库

这是我们备份之前的数据库

root@student 20:36  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sanchuang          |
| student            |
| sys                |
| test               |
| ucar_cloud         |
| wangsh             |
+--------------------+
9 rows in set (0.00 sec)

现在使用mysqldump进行备份

[root@localhost mysql]# mysqldump --all-databases -uwangsh -p'123456' >all_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]# 

[root@localhost mysql]# cat all_db.sql | more
-- MySQL dump 10.13  Distrib 5.7.34, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version	5.7.34-log
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Current Database: `mysql`
--
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
USE `mysql`;
 
--
-- Table structure for table `columns_priv`
--
 
DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
--More--

然后将删除两个数据库

root@student 20:36  mysql>drop database wangsh;
Query OK, 0 rows affected (0.00 sec)
 
root@student 20:40  mysql>drop database sanchuang;
Query OK, 0 rows affected (0.00 sec)
 
root@student 20:40  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| test               |
| ucar_cloud         |
+--------------------+
7 rows in set (0.00 sec)

使用刚才备份的文件进行还原，可以看到，刚才删除的数据库又还原了，这就是热备份

[root@localhost mysql]# mysql -u wangsh -p'123456' <all_db.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]# 
 
root@student 20:40  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sanchuang          |
| student            |
| sys                |
| test               |
| ucar_cloud         |
| wangsh             |
+--------------------+
9 rows in set (0.00 sec)
 

###########################################################################  

知识点3：备份和还原操作

1、产生一个全新的二进制日志

root@student 22:03  mysql>reset master;
Query OK, 0 rows affected (0.01 sec)
 
root@student 22:04  mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000001 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
root@student 22:04  mysql>

###########################################################################  

2、进行数据库全备份

因为我们创建全备份并没有产生数据改变，所以不会产生二进制日志，也不会产生新的位置号

[root@localhost backup]# mysqldump -u root -p'Sanchuang123#' --databases sanchuang>/backup/sanchuang.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost backup]# ls
sanchuang.sql
 
 
root@sanchuang 22:13  mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000001 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
root@sanchuang 22:14  mysql>

###########################################################################  

3、让数据库进行新增，删除操作。

root@sanchuang 22:17  mysql>select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | wang | M    |
+----+------+------+
1 row in set (0.01 sec)
 
root@sanchuang 22:17  mysql>insert into student(id,name,sex) values(4,'liu','M');
Query OK, 1 row affected (0.00 sec)
 
root@sanchuang 22:18  mysql>insert into student(id,name,sex) values(5,'deng','W');
Query OK, 1 row affected (0.00 sec)
 
root@sanchuang 22:18  mysql>insert into student(id,name,sex) values(3,'zhao','W');
Query OK, 1 row affected (0.00 sec)
 
root@sanchuang 22:19  mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000001 |      996 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
root@sanchuang 22:19  mysql>

###########################################################################  

4、模拟出现故障，删除数据库

root@sanchuang 22:19  mysql>drop database sanchuang;
Query OK, 1 row affected (0.00 sec)
 
root@(none) 22:20  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| test               |
| ucar_cloud         |
| wangsh             |
+--------------------+
8 rows in set (0.00 sec)

###########################################################################  

5、开始取恢复数据

###########################################################################  

根据起始时间恢复数据 

第一步、恢复全备

[root@localhost backup]# mysql -uroot -p'Sanchuang123#' </backup/sanchuang.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost backup]# 

root@(none) 22:20  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sanchuang          |
| student            |
| sys                |
| test               |
| ucar_cloud         |
| wangsh             |
+--------------------+
9 rows in set (0.00 sec)
 
root@(none) 22:23  mysql>
root@(none) 22:23  mysql>use sanchuang;
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
root@sanchuang 22:24  mysql>show tables;
+---------------------+
| Tables_in_sanchuang |
+---------------------+
| student             |
+---------------------+
1 row in set (0.00 sec)
 
root@sanchuang 22:24  mysql>select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | wang | M    |
+----+------+------+
1 row in set (0.00 sec)
 
root@sanchuang 22:24  mysql>

可以看到，数据已经恢复到了我们进行全备的状态，但是我们在全备之后的操作却都丢失了

接下来就要根据产生的二进制日志来恢复数据。

[root@localhost mysql]# mysqlbinlog -v localhost-bin.000001 | egrep -C 10 "drop database sanchuang"
###   @3='W'
# at 965
#220812 22:19:00 server id 1  end_log_pos 996 CRC32 0xfe82499a 	Xid = 1722
COMMIT/*!*/;
# at 996
#220812 22:20:39 server id 1  end_log_pos 1061 CRC32 0x69c7638b 	Anonymous_GTID	last_committed=3	sequence_number=4	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1061
#220812 22:20:39 server id 1  end_log_pos 1168 CRC32 0x94b027d3 	Query	thread_id=2   exec_time=0	error_code=0
SET TIMESTAMP=1660314039/*!*/;
drop database sanchuang
/*!*/;
# at 1168
#220812 22:23:16 server id 1  end_log_pos 1233 CRC32 0x37d3da41 	Anonymous_GTID	last_committed=4	sequence_number=5	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1233
#220812 22:23:16 server id 1  end_log_pos 1408 CRC32 0xcf76bd79 	Query	thread_id=8   exec_time=0	error_code=0
SET TIMESTAMP=1660314196/*!*/;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/;
SET @@session.sql_mode=524288/*!*/;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sanchuang` /*!40100 DEFAULT CHARACTER SET utf8 */
[root@localhost mysql]# 

找到起始时间点 

可以看到，drop操作是在1061位置点之后发生的，所以我们要恢复1061到之前去

mysql根据二进制日志恢复数据有两种方式，一种是根据时间，一种是根据位置点

首先是根据时间恢复

        因为我们是产生了一个全新的二进制日志来进行测试的，所以起始时间就是二进制日志的最开始部分里面

 结束时间我们选距离删库操作最近的一个时间

 

恢复数据，可以看到，我们删除的数据就又回来了

[root@localhost mysql]# mysqlbinlog --start-datetime="2022-08-12 22:11:04" --stop-datetime="2022-08-12 22:20:39" /data/mysql/localhost-bin.000001 | mysql -uroot -p'Sanchuang123#'
mysql: [Warning] Using a password on the command line interface can be insecure.

root@sanchuang 22:26  mysql>select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | wang | M    |
|  3 | zhao | W    |
|  4 | liu  | M    |
|  5 | deng | W    |
+----+------+------+
4 rows in set (0.00 sec)
 
root@sanchuang 22:51  mysql>

###########################################################################  

根据位置号恢复数据

首先将数据库删除

root@sanchuang 22:51  mysql>drop database sanchuang;
Query OK, 1 row affected (0.01 sec)
 

然后导入全备份的数据

[root@localhost mysql]# mysql -uroot -p'Sanchuang123#' </backup/sanchuang.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]# 
 
root@(none) 22:55  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sanchuang          |
| student            |
| sys                |
| test               |
| ucar_cloud         |
| wangsh             |
+--------------------+
9 rows in set (0.00 sec)
 
root@(none) 22:56  mysql>use sanchuang;
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
root@sanchuang 22:56  mysql>select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | wang | M    |
+----+------+------+
1 row in set (0.00 sec)
 
root@sanchuang 22:56  mysql>

 找到起始位置点

选择位置点进行恢复

 恢复数据，可以看到，删除的数据成功恢复

[root@localhost mysql]# mysqlbinlog --start-position=154 --stop-position=1061 /data/mysql/localhost-bin.000001 |mysql -uroot -p'Sanchuang123#'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]# 

root@sanchuang 22:56  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sanchuang          |
| student            |
| sys                |
| test               |
| ucar_cloud         |
| wangsh             |
+--------------------+
9 rows in set (0.00 sec)
 
root@sanchuang 23:03  mysql>select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | wang | M    |
|  3 | zhao | W    |
|  4 | liu  | M    |
|  5 | deng | W    |
+----+------+------+
4 rows in set (0.00 sec)
 
root@sanchuang 23:03  mysql>