常见数据库备份方式：MySQLbinlog数据恢复详解

再了解一下什么是快照备份：

快照备份不是数据库本身提供的能力，本质上它是借助于文件系统的快照功能来实现的对数据库的备份。

我们知道的Linux服务器本质上也是电脑的，它会有自己的磁盘，无论是固态硬盘，还是机械磁盘。反正会有这种固态存储。还需要进一步对磁盘进行分区。然后才有将Linux文件系统中的目录都会挂载在不同的分区上。这么做的目的，简单来说就像你的window有C盘、D盘、E盘。D盘中的出问题后不会影响E盘一样。

快照备份要求：数据库的所有数据文件都要放在一个数据分区中。

常见的支持快照工具的文件系统和设备有：FreeBSD、UFS文件系统、Solaris的ZFS文件系统。GNU/Linux的LVM（Logical Volume Manager）

实用的mysqldump备份方式#

=====================

本小节看几个实战mysqldump备份case。

测试环境：创建如下表

CopyCREATE TABLE test_backup2 (

id int(11) NOT NULL AUTO_INCREMENT,

name varchar(255) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据表中写入3条数据

mysqldump语法

Copymysqldump [arguments] > file_name;

1、备份指定的数据库

通过参数–databases 指定你要备份的数据库

Copy# mysqldump -uroot -p --databases db1 db2 db3 > 自定义名.sql;

./mysqldump -uroot -p --databases stusy > test_backup.sql;

因为我开启了GTID，所以直接执行如上的命令行有报错提示说：如果我只想完成数据的dump，需要在命令行中添加上它提示的那些参数。

Copy# 如果你没有开启GTID选项，它提示我加的这些参数你都没有必要添加的。

–triggers 备份触发器

–routines 备份存储过程和函数

–events 备份事件调度器

./mysqldump --set-gtid-purged=OFF --databases stusy --triggers --routines --events -uroot -p > test_backup.sql;

查看产出的SQL文件：

注意点：使用参数 --databases 参数。最终产出的SQL中为你创建数据库了。

文件开始和结束的部分有很多注释，这些注释可以用来设置MYSQL数据的各项参数。一般用来保证还原数据时可以更加有效准确的工作。

2、备份指定数据库中的指定数据表： 通过参数 `–tables` 指定你要备份的数据表。

Copy# mysqldump -uroot -p --databases db1 db2 db3 --tables t1 t2 > 自定义名.sql;

./mysqldump --set-gtid-purged=OFF --databases stusy --tables test_backup --triggers --routines --events -uroot -p > test_backup.sql;

3、对一个架构进行备份

不使用–databases，直接写数据库名。对整库架构进行备份

Copy./mysqldump --set-gtid-purged=OFF --triggers --routines --events -uroot -p mysql> mysql_backup.sql;

查看备份的结果

注意点：相对于使用 --databases 参数来说。最终产出的SQL中！！没有！！为你创建数据库。

4、重点理解参数：–single-transaction

如果你想获得一份“一致性备份”可以使用该参数。那什么是一致性备份呢？

todo 下面的：我劝！这位年轻人不讲MVCC，耗子尾汁！ 贴上链接。

添加–single-transaction参数后，mysqldump会自动帮你执行 start transaction 开启事务的SQL。如果你看过白日梦之前写的 “我劝！这位年轻人不讲MVCC，耗子尾汁！”，想必你一定了解，MVCC的实现原理，回到现在的这个问题中，也就是说，只要你执行开启事务的语句就会得到一个一致性可重复读的视图（read view）。说白了：此次执行mysqldump得到的SQL文件中的数据，就是你执行的该命令的那个瞬间，打下的快照的数据。

注意：如果你不使用–single-transaction参数，会自动添加上–lock-all-tables。此外，还需要知道当我们使用参数–single-transaction获取到的那个一致性实图并不能隔离DDL（表级别的操作，比如添加列）。所以你要确保在备份时没有其他的DDL语句执行。

5、重点理解参数：–master-data

Copy# 当值为1时，转存文件中会有change master 语句。

–master-data = 1

当值为2时，转存文件中当 change master 语句会被注释。

–master-data = 2

下面分别让 --master-data 为不同的值。查看产出。

Copy./mysqldump --set-gtid-purged=OFF --databases stusy --tables test_backup --triggers --routines --events --master-data=2 -uroot -p > test_backup.sql;

Copy./mysqldump --set-gtid-purged=OFF --databases stusy --tables test_backup --triggers --routines --events --master-data=1 -uroot -p > test_backup.sql;

一般搭建过mysql集群的同学都知道这条change master sql语句的作用是： 从库认主库的命令。

是的，使用参数–master-data=1得到的备份文件通常主要作用是创建一个replication（从库）。

上面介绍了工作中常用的几种用法和注意点。

其实mysqldump支持的参数多达几十个。你可以使用 --help查看它们。

如果上面的参数不能满足你的需求。你可去官网查阅：https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

得先知道什么是GTID#

================

GTID （global transcation identifier）它是MySQL5.6版本中添加进来的新特性 ，使用GTID可以唯一的标识一个事物。

我用大白话描述一下GTID常见的作用：

比如一条update有语句进入MySQL之后经历如下过程：

Copy1. 写undolog

2. 写redolog（prepare）

3. 写binlog

4. 写redolog（commit）

这也是所谓的两阶段提交

不管你有没有自己搭建过MySQL集群，你一定听说过MySQL集群！主库将自己成功执行过的事物都写在binlog，然后集群中的从库会dump主库记录的binlog回放出数据，完成数据同步。当我们将GTID相关的配置打开后，update语句经历如下过程：

Copy1. 写undolog # 回滚

2. 写redolog（prepare）# 保证提交的不会丢失

3. 写一个特殊的Binlog Event，类型为GTID_Event，指定下一个事务的GTID

4. 写binlog # 主从同步事物使用

5. 写redolog（commit）

也就是说mysql会在binlog中多为我们记录一行gtid。这个gtid和当前事物唯一对应。不会重复。

这时当从库向主库发送同步数据当请求时：bin-log和gtid都会传送到slave端，从库在回放日志同步数据时，同样会使用gtid写bin-log，这样主库和从库之间的数据，就通过GTID强制性的关联并且保持同步了。

下图截取自binlog一条事务，你可以看到里面会记录gtid。

这时如果从库想在主库同步数据，只需要告诉主库自己有哪些gtid就好了，主库会把从库没有的gtid对应的事务日志给从库让它去同步数据。

而在这种方式出现之前，主从之间同步数据时，从库需要告诉主库自己已经同步到binlog.0000x，position=yyy的地方了。这个binlog.0000x，position=yyy需要人为地去查看一下。不能说查看这两个信息比较麻烦，但是肯定不如GTID来的方便。

看一条binlog长啥样#

=================

为了对小白友好一点，再看一下这张图：

首先你得知道，像select这种查询类型的sql，是不会被记录进binlog中的，binlog中只会记录对数据库作出修改的写入或者更新的sq。就像上图中，你可以看我图中begin、xxx、commit。

另外binlog中是有位点的，人们一般把称它叫：position。其实所谓的位点就是上图中的at xxx中的xxx。

每一个事物都有自己的开启、结束位点，换句话说我们可以通过开始和结束的位点找到一个或者是好多和事物。就上图来说，这个事物的start-positon=956，stop-position=1230。

这个位点有啥用呢？

作用1:搭建主从集群时，通过下面的命令告诉从库，应该从主库的哪个binlog的哪个位点开始同步数据

CopyCHANGE MASTER TO

MASTER_HOST=‘10.157.23.158’,

MASTER_USER=‘mysqlsync’,

MASTER_PASSWORD=‘mysqlsync123’,

MASTER_PORT=8882,

MASTER_LOG_FILE=‘mysql-bin.000008’,

MASTER_LOG_POS=1013; # 这就是位点

作用2:数据恢复时，指定从哪个位点恢复到哪个位点。或者跳过哪个位点，下面我们一起看下基于binlog的数据恢复。

数据恢复#

=========

不知道你有没有误删过数据库中的数据，之前我就误删过。不过还好是测试环境的。

其实误删数据后是可以通过binlog将数据恢复出来的。既然是使用binlog恢复数据，前提是你的MySQL开启了binlog（默认情况下mysql不会帮你记录binlog，如果你还不知道什么是binlog也没关系，白日梦前面的文章有分享，你可以去看下）。

大部分情况下，DBA同学会将你使用的MySQL binlog打开。你可以像下面这样验证一下自己使用的数据库binlog是否打开了。如果没有打开binlog，数据可能真的没办法恢复。

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后

本人也收藏了一份Java面试核心知识点来应付面试，借着这次机会可以送给我的读者朋友们:

目录：

Java面试核心知识点

一共有30个专题，足够读者朋友们应付面试啦，也节省朋友们去到处搜刮资料自己整理的时间！

Java面试核心知识点
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
img src=“https://img-blog.csdnimg.cn/img_convert/4824a7f2d315d7aab5c937900db61a62.jpeg” alt=“img” style=“zoom: 33%;” />

最后

本人也收藏了一份Java面试核心知识点来应付面试，借着这次机会可以送给我的读者朋友们:

目录：

[外链图片转存中…(img-ng4M7bvl-1713664779566)]

Java面试核心知识点

一共有30个专题，足够读者朋友们应付面试啦，也节省朋友们去到处搜刮资料自己整理的时间！

[外链图片转存中…(img-hVieKLoD-1713664779566)]

Java面试核心知识点
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！