9.MySQL-数据库的备份-全备份-根据二进制日志恢复数据

Zengkaijie

于 2022-09-03 23:45:13 发布

阅读量311

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签：数据库 mysql 服务器

本文链接：https://blog.youkuaiyun.com/Zengkaijie/article/details/126684296

MySQL 专栏收录该内容

10 篇文章

订阅专栏

本文详细介绍了如何在MySQL中进行全备份，并演示了根据二进制日志恢复数据的过程，包括时间点和位置号的恢复策略。涉及mysqldump备份、逻辑备份与物理备份的区别，以及远程备份的方法如scp和rsync。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL-数据库的备份-全备份-根据二进制日志恢复数据

文章目录

MySQL-数据库的备份-全备份-根据二进制日志恢复数据
@[toc]
一、数据库的备份
1.1 物理（原始）与逻辑备份
1.2 在线与离线备份
1.3 本地与远程备份
1.4 mysqldump备份数据库
1.5 备份和还原操作
根据二进制日志恢复数据（时间点、位置号）

1.6 远程备份 -- scp
1.7 远程备份 -- rsync

一、数据库的备份

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

1.1 物理（原始）与逻辑备份

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

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

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

备份是通过查询 MySQL 服务器以获取数据库结构和内容信息来完成的。
备份比物理方法慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。如果输出是在客户端写入的，服务器也必须将其发送到备份程序。
输出大于物理备份，尤其是以文本格式保存时

1.2 在线与离线备份

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

1.3 本地与远程备份

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

1.4 mysqldump备份数据库

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2aD7IaMx-1662219632057)(D:\Typora\photo\image-20220818110120688.png)]$

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

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

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

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-adG2OTuB-1662219632058)(D:\Typora\photo\image-20220818110305770.png)]$

[root@zkj-mysql 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 zkj;
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@zkj-mysql mysql]# mysql -u root -p'123456' <all_db.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

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

1.5 备份和还原操作

先查看二进制日志是否开启

root@(none) 11:18  mysql>show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |	（已经打开）
+---------------+-------+
1 row in set (0.01 sec)

模拟一个新的环境进行试验

root@(none) 12:12  mysql>create database zengkaijie;
Query OK, 1 row affected (0.00 sec)

root@(none) 12:13  mysql>use zengkaijie;
Database changed
root@zengkaijie 12:13  mysql>create table t1(id int primary key,name varchar(20) not null);
Query OK, 0 rows affected (0.01 sec)

root@zengkaijie 12:14  mysql>insert into t1(id,name) values(1,'zkj'),(2,'thm'),(3,'cc');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@zengkaijie 12:14  mysql>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | zkj  |
|  2 | thm  |
|  3 | cc   |
+----+------+
3 rows in set (0.00 sec)

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

为了方便我们后面查找，特意新建一个新的二进制日志

root@zengkaijie 12:14  mysql>flush logs;
Query OK, 0 rows affected (0.02 sec)

root@zengkaijie 12:15  mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| zkj-mysql-bin.000005 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

对数据库进行全备份

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

[root@zkj-mysql backup]# rm -rf zkj.sql 
[root@zkj-mysql backup]# mysqldump -u root -p'Sanchuang123#' --databases zengkaijie >/backup/zkj.sql 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@zkj-mysql backup]# ls
all_da.sql  zkj.sql

模拟全备份后，对数据库进行新增数据操作

root@zengkaijie 12:15  mysql>insert into t1(id,name) values(4,'zzz'),(5,'xxx'),(6,'ccc');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@zengkaijie 12:16  mysql>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | zkj  |
|  2 | thm  |
|  3 | cc   |
|  4 | zzz  |
|  5 | xxx  |
|  6 | ccc  |
+----+------+
6 rows in set (0.00 sec)

root@zengkaijie 12:16  mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| zkj-mysql-bin.000005 |      444 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

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

root@zengkaijie 12:16  mysql>drop database zengkaijie;
Query OK, 1 row affected (0.00 sec)

root@(none) 12:34  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sc                 |
| sys                |
| zkj                |
+--------------------+
6 rows in set (0.00 sec)

开始恢复数据

根据二进制日志恢复数据（时间点、位置号）

方法一：根据时间恢复数据

第一步，恢复全备份

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

root@(none) 12:35  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sc                 |
| sys                |
| zengkaijie         |	(数据库已经恢复)
| zkj                |
+--------------------+
7 rows in set (0.00 sec)

root@(none) 12:37  mysql>use zengkaijie;
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@zengkaijie 12:38  mysql>show tables;
+----------------------+
| Tables_in_zengkaijie |
+----------------------+
| t1                   |
+----------------------+
1 row in set (0.00 sec)

root@zengkaijie 12:38  mysql>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | zkj  |
|  2 | thm  |
|  3 | cc   |
+----+------+
3 rows in set (0.00 sec)

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

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

[root@zkj-mysql mysql]# mysqlbinlog -vv zkj-mysql-bin.000005 |egrep -i "drop database zengkaijie" -C 100
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220818 12:15:03 server id 1  end_log_pos 123 CRC32 0x77ab977d 	Start: binlog v 4, server v 5.7.37-log created 220818 12:15:03
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
x7z9Yg8BAAAAdwAAAHsAAAABAAQANS43LjM3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AX2Xq3c=
'/*!*/;
# at 123
#220818 12:15:03 server id 1  end_log_pos 154 CRC32 0x1bf09c9c 	Previous-GTIDs
# [empty]
# at 154
#220818 12:16:21 server id 1  end_log_pos 219 CRC32 0x31e01677 	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
#220818 12:16:21 server id 1  end_log_pos 297 CRC32 0xc604f7f1 	Query	thread_id=3	exec_time=0error_code=0
SET TIMESTAMP=1660796181/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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 297
#220818 12:16:21 server id 1  end_log_pos 351 CRC32 0xf4502db9 	Table_map: `zengkaijie`.`t1` mapped to number 120
# at 351
#220818 12:16:21 server id 1  end_log_pos 413 CRC32 0x5d8aa517 	Write_rows: table id 120 flags: STMT_END_F

BINLOG '
Fb39YhMBAAAANgAAAF8BAAAAAHgAAAAAAAEACnplbmdrYWlqaWUAAnQxAAIDDwI8AAC5LVD0
Fb39Yh4BAAAAPgAAAJ0BAAAAAHgAAAAAAAEAAgAC//wEAAAAA3p6evwFAAAAA3h4ePwGAAAAA2Nj
Yxelil0=
'/*!*/;
### INSERT INTO `zengkaijie`.`t1`
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='zzz' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### INSERT INTO `zengkaijie`.`t1`
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='xxx' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### INSERT INTO `zengkaijie`.`t1`
### SET
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ccc' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
# at 413
#220818 12:16:21 server id 1  end_log_pos 444 CRC32 0x6cf2a2e0 	Xid = 100
COMMIT/*!*/;
# at 444
#220818 12:34:59 server id 1  end_log_pos 509 CRC32 0x742a9099 	Anonymous_GTID	last_committed=1   sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 509
#220818 12:34:59 server id 1  end_log_pos 619 CRC32 0x390de153 	Query	thread_id=3	exec_time=0error_code=0
SET TIMESTAMP=1660797299/*!*/;
drop database zengkaijie
/*!*/;
# at 619
#220818 12:36:54 server id 1  end_log_pos 684 CRC32 0x7e44e69c 	Anonymous_GTID	last_committed=2   sequence_number=3	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 684
#220818 12:36:54 server id 1  end_log_pos 862 CRC32 0x0afa1060 	Query	thread_id=6	exec_time=0error_code=0
SET TIMESTAMP=1660797414/*!*/;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/;
SET @@session.sql_mode=524288/*!*/;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `zengkaijie` /*!40100 DEFAULT CHARACTER SET utf8 */
/*!*/;
# at 862
#220818 12:36:54 server id 1  end_log_pos 927 CRC32 0x434703d3 	Anonymous_GTID	last_committed=3   sequence_number=4	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 927
#220818 12:36:54 server id 1  end_log_pos 1064 CRC32 0xd5c14c42 	Query	thread_id=6	exec_time=0	error_code=0
use `zengkaijie`/*!*/;
SET TIMESTAMP=1660797414/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
DROP TABLE IF EXISTS `t1` /* generated by server */
/*!*/;
# at 1064
#220818 12:36:54 server id 1  end_log_pos 1129 CRC32 0x392de197 	Anonymous_GTID	last_committed=4	sequence_number=5	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1129
#220818 12:36:54 server id 1  end_log_pos 1348 CRC32 0x0ba27373 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1660797414/*!*/;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!*/;
# at 1348
#220818 12:36:54 server id 1  end_log_pos 1413 CRC32 0xdef5280e 	Anonymous_GTID	last_committed=5	sequence_number=6	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1413
#220818 12:36:54 server id 1  end_log_pos 1540 CRC32 0xffcd69be 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1660797414/*!*/;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */
/*!*/;
# at 1540
#220818 12:36:54 server id 1  end_log_pos 1605 CRC32 0x94a300f8 	Anonymous_GTID	last_committed=6	sequence_number=7	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1605
#220818 12:36:54 server id 1  end_log_pos 1683 CRC32 0x62a7142d 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1660797414/*!*/;
BEGIN
/*!*/;
# at 1683
#220818 12:36:54 server id 1  end_log_pos 1737 CRC32 0x6cc1481e 	Table_map: `zengkaijie`.`t1` mapped to number 121
# at 1737
#220818 12:36:54 server id 1  end_log_pos 1798 CRC32 0x32908d0d 	Write_rows: table id 121 flags: STMT_END_F

BINLOG '
5sH9YhMBAAAANgAAAMkGAAAAAHkAAAAAAAEACnplbmdrYWlqaWUAAnQxAAIDDwI8AAAeSMFs
5sH9Yh4BAAAAPQAAAAYHAAAAAHkAAAAAAAcAAgAC//wBAAAAA3pravwCAAAAA3RobfwDAAAAAmNj
DY2QMg==
'/*!*/;
### INSERT INTO `zengkaijie`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='zkj' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### INSERT INTO `zengkaijie`.`t1`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='thm' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### INSERT INTO `zengkaijie`.`t1`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='cc' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
# at 1798
#220818 12:36:54 server id 1  end_log_pos 1829 CRC32 0xbfcc663c 	Xid = 129
COMMIT/*!*/;
# at 1829
#220818 12:36:54 server id 1  end_log_pos 1894 CRC32 0xb680bbae 	Anonymous_GTID	last_committed=7	sequence_number=8	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1894
#220818 12:36:54 server id 1  end_log_pos 2020 CRC32 0xe1f2d0ff 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1660797414/*!*/;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */
/*!*/;
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*/;

可以看到，drop操作是在509位置点之后发生的，对应的时间点为：220818 12:34:59，所以我们要恢复509到之前去，也就是对应时间为：220818 12:34:59之前。

第二步，找到起始时间点

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

首先是根据时间恢复

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

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pBOiGxiX-1662219632058)(D:\Typora\photo\image-20220818125249178.png)]$

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

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IjnjJlJO-1662219632059)(D:\Typora\photo\image-20220818125342963.png)]$

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

[root@zkj-mysql mysql]# mysqlbinlog --start-datetime="2022-08-18 12:15:03" --stop-datetime="2022-08-18 12:34:59" /data/mysql/zkj-mysql-bin.000005 | mysql -u root -p 'Sanchuang123#'
mysql: [Warning] Using a password on the command line interface can be insecure.

root@zengkaijie 12:38  mysql>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | zkj  |
|  2 | thm  |
|  3 | cc   |
|  4 | zzz  |
|  5 | xxx  |
|  6 | ccc  |
+----+------+
6 rows in set (0.00 sec)

方法二：根据位置号恢复数据

前置操作同方法一

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KaCgCniS-1662219632059)(D:\Typora\photo\image-20220818130246260.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YZA6tv5T-1662219632059)(D:\Typora\photo\image-20220818130101936.png)]$

恢复数据

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