基于mysqldump聊一聊MySQL的备份和恢复

我会带着你远行 2023-02-12 10:26 2阅读 0赞

![format_png][]

## 前言 ##

Hi,大家好,我是麦洛,今天我们聊聊`MySQL`的备份和恢复,在下面文章中,你会了解到`MySQL`常见的备份类型,以及基于`mysqldump`命令在日常开发中如何做`MySQL`数据库以及表的备份和恢复。

## 一.为什么要做备份? ##

*  在数据丢失的情况下快速恢复数据。
 *  作为实验数据的来源：
    
     *  将生产数据拷贝一份到到本地测试库用于测试,不影响生产数据
     *  升级数据库版本时,需要将生产数据拷贝到本地,然后在本地测试是否兼容,找出潜在不安全性

![哎呀,手抖了,删库了][format_png 1]

## 二 数据库备份的类型 ##

### 1 物理备份(原始备份)与逻辑备份 ###

#### 1.1 物理备份 ####

**物理备份**指将数据库内容的目录和数据文件、日志文件及配置文件的原始副本全部备份,适用于大型重要的数据库在出现问题时需要快速恢复的场景。

##### 1.1.1 物理备份方法具有以下特征 #####

*  物理备份包括数据库目录和文件的精确副本。 通常这是 `MySQL` 数据目录的全部或部分副本。
 *  物理备份方法比逻辑备份方法快，因为它们只涉及文件复制而不设计文件转换。
 *  物理备份可以在 `MySQL` 服务器停止时执行备份。 如果服务器正在运行，则需要执行适当的锁定，以便服务器在备份期间不更改数据库内容。 `MySQL Enterprise Backup`（`MySQL` 提供企业级备份和恢复。 它可以在多种平台上提供热备份、在线备份和非阻塞备份）可以自动锁定需要备份的表。
 *  除了数据文件之外，备份还可以包括任何相关文件，如日志或配置文件。
 *  物理备份工具包括 `mysqlbackup` （`MySQL Enterprise Backup` 组件的一部分），或 `MyISAM` 表的文件系统命令(如 `cp、 scp、 tar、 rsync`)。

##### 1.1.2 物理备份恢复 #####

*  `MySQL Enterprise Backup` 恢复 `InnoDB` 和其他已备份的表
 *  **ndb\_restore**还原恢复 `NDB` 表(`NDB`引擎)
 *  使用文件系统命令复制的文件可以使用文件系统命令复制回原来的位置

#### 1.2 逻辑备份 ####

**逻辑备份**是指将数据库的逻辑结构(`CREATE DATABASE`、 `CREATE TABLE` 语句)和内容(`INSERT` 语句或带分隔符的文本文件)备份。 它适用于小量的数据，我们可以直接编辑备份的数据,修改表结构或者内容,然后在不同的服务器上面重新构建数据;

##### 1.2.1 逻辑备份方法具有以下特征 #####

*  逻辑备份是通过查询 `MySQL` 服务器获取数据库表结构和表内容信息来完成的。
 *  逻辑备份比物理备份方法慢，因为服务器必须访问数据库的信息并将其转换为逻辑格式。 如果输出是在客户端写的，服务器还必须将其发送到客户端侧,网络传输也是影响的一方面。
 *  逻辑备份是在运行 `MySQL` 服务器的情况下执行的。服务器不需要停机。
 *  逻辑备份工具包括 `mysqldump` 程序和 `SELECT... INTO OUTFILE` 语句。
 *  备份不包括日志或配置文件。
 *  要还原逻辑备份，可以使用 `mysql` 客户端(`Navicat`)处理 `sql` 格式的转储文件。 若要加载带分隔符的文本文件，请使用 `loaddata` 语句或 `mysqlimport` 客户端。

### 2 在线备份和离线备份 ###

在 `MySQL` 服务器运行期间进行备份，称为**在线备份**。反之服务器停止时进行备份。称为**离线备份**。 通常也可以称为“**热备份**”和“**冷备份**” ;

#### 1 在线备份方法具有以下特征 ####

*  在线备份对其他客户端的干扰较小，这些客户端可以在备份期间连接到 `MySQL` 服务器，并且可以访问数据。
 *  必须注意施加适当的锁定，防止备份期间数据修改导致备份数据不完整。 `MySQL Enterprise Backup`产品会自动执行这种锁定。

#### 2 离线备份方法具有以下特征 ####

*  客户端可能会受到负面影响，因为服务器在备份期间不可用。 由于这个原因，这些备份数据通常是从从服务器上（主从复制）获取的，当然是在不影响可用性的情况下离线执行。
 *  备份过程更简单，因为不存在客户端活动干扰的可能性。

在备份期间，客户端能够在备份数据时读取数据。 恢复修改数据并且不只是读取数据，因此在还原数据时必须阻止客户端访问数据。

### 3 本地备份与远程备份 ###

本地备份指在运行 `MySQL` 服务器的同一台主机上执行备份操作，而远程备份是在另一台主机上执行备份操作。 对于某些类型的备份，即使备份输出是在服务器上，但可能是从远程主机启动备份。

*  `mysqldump`可以连接到本地或远程服务器。
 *  [`SELECT ... INTO OUTFILE`][SELECT ... INTO OUTFILE] 可以从本地或远程客户端主机启动 `SELECT... INTO OUTFILE`，但是输出文件是在服务器主机上创建的。

更多内容阅读：
    mysqldump ： https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
    SELECT ... INTO OUTFILE ： https://dev.mysql.com/doc/refman/5.7/en/select-into.html

### 4 完全备份与增量备份 ###

**完全备份**指备份 `MySQL` 服务器在给定时间点管理的所有数据。 **增量备份**指备份给定时间范围内(从一个时间点到另一个时间点)对数据所做的更改。 `Mysql` 有不同的方法来执行完全备份，如前面所述。 通过启用 `MySQL` 服务器的`binlog`二进制日志(服务器用于记录数据更改) ，可以进行增量备份。

### 5 完全恢复与时间点(增量)恢复 ###

完全恢复还原备份的所有数据。这会将服务器的数据恢复到备份时的状态。如果该状态不是当前的，可以在完全恢复之后进行增量恢复，从而使服务器达到更新的状态。

增量恢复是对给定时间跨度内所做的更改进行恢复。这也称为时间点恢复。时点恢复基于binlog二进制日志，通常在完全恢复备份文件之后进行，然后将写入二进制日志文件中的数据更改作为增量恢复应用于重做数据修改，并将服务器带到所需的时间点。

## 三 . 使用mysqldump进行备份 ##

### 1备份SQL格式的数据 ###

首先,我们看看我们的演示数据

![format_png 2][]

![format_png 3][]

![format_png 4][]

![format_png 5][]

#### 1.1 备份所有数据库 ####

进入`MySQL`安装`bin`目录下面执行命令,接下来操作保持一致

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump --all-databases --hex-blob >d:\alldatabases.sql

*  –hex-blob :使用十六进制符号转储二进制字符串\[**解决导出中文乱码问题**\]

结果:

![format_png 6][]

#### 1.2 备份特定数据库 ####

mysqldump --databases girls > d:\girls.sql

结果:

![format_png 7][]

说明:

在单数据库情况下，可以忽略以下 [`--databases`][--databases]选项

如果不带此选项,生成文件会不包含`CREATE DATABASE`

![format_png 8][]

#### 1.3 备份数据库的特点表 ####

mysqldump girls boys >d:\boys.sql

结果:

![format_png 9][]

### 2.重载SQL格式的备份 ###

#### 2.1 不创建Database ####

如果备份文件是由`mysqldump`使用 `--all-databases`或 `--databases`选项创建的 ，则备份文件包含`CREATE DATABASE`和 `USE`语句，所以无需指定默认数据库就可以直接将数据重载;

![format_png 10][]

首先,我们删库跑路…

![format_png 11][]

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql <d:\alldatabases.sql

结果:

![format_png 12][]

#### 2.2 创建Database ####

备份文件不包含`CREATE DATABASE`和 `USE`语句 ，请首先创建数据库（如有必要）：

#创建数据库db1
    mysqladmin create db1

结果:(关闭数据库连接,重复开启,皆可以看到)

![format_png 13][]

我们重新生成一个不不包含`CREATE DATABASE`和 `USE`语句的文件

mysqldump girls > d:\nodatabasegirls.sql

![img][]

**彩蛋**:如果遇到这个,就重启下`mysql`服务吧,我也不知道为什么,哈哈

mysqldump: Got error: 1045: Access denied for user 'ODBC'@'localhost' (using password: NO) when trying to connect

向`db1`数据库重载我们的数据

mysql db1<d:\nodatabasegirls.sql

结果:

![format_png 14][]

## 四.mysqldump的应用场景 ##

### 1 制作数据库副本 ###

shell> mysqldump db1 > dump.sql
    shell> mysqladmin create db2
    shell> mysql db2 < dump.sql

### 2 将数据库从一台服务器复制到另一台服务器 ###

# 在服务器1上：
    shell> mysqldump --databases db1 > dump.sql
    # 在服务器2上：
    shell> mysql < dump.sql
    # 您可以--databases从 mysqldump命令中省略,然后自己创建数据库
    # 在服务器1上：
    mysqldump db1 > dump.sql
    # 在服务器2上：
    shell> mysqladmin create db1
    shell> mysql db1 < dump.sql

### 3 转储表定义和内容 ###

# 不转储表数据
    shell> mysqldump --no-data test > dump-defs.sql
    #不输出CREATE语句,只包含表数据
    shell> mysqldump --no-create-info test > dump-data.sql

### 4 使用mysqldump测试升级不兼容性 ###

在考虑进行`MySQL`升级时，应将新版本与当前生产版本分开安装。然后，您可以从生产服务器中转储数据库和表定义，并将它们加载到新服务器中，以验证它们是否正常。（这对于测试降级也很有用。）

#生产服务器
    shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql
    #升级服务器
    shell> mysql < dump-defs.sql

验证表定义正常后,在导入数据测试

#生产服务器
    shell> mysqldump --all-databases --no-create-info > dump-data.sql
    #升级服务器
    shell> mysql < dump-data.sql

这时候,可以进行一些简单查询测试,观察是否正常

## 五 总结 ##

在本文中,我们只是简单介绍了`mysqldump`的基础操作,关于它的选项总共有`113`个，大家可以根据自己需要自行去官网`https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html`查阅学习

## 关于作者 ##

笔者麦洛是`java`开发者和技术爱好者,目前关注`java`、`Spring`、微服务、云原生方向。为了将内容精准推给喜欢我的小伙伴,在大家建议下开通了公众号。喜欢的小伙伴可以关注我，第一时间获取文章信息。也可以后台回复**加群**加入交流群，大家一起进步！谢谢大家对我的支持。

![format_png 15][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjUxMzIwMzIuanBn?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly90aW1nc2EuYmFpZHUuY29tL3RpbWc_aW1hZ2UmcXVhbGl0eT04MCZzaXplPWI5OTk5XzEwMDAwJnNlYz0xNTkwMjA5MDIxMTI2JmRpPTUzZmI4YzY0ODE5ZGM4N2E4NTY3ZTNiZTg4NWE4Y2IxJmltZ3R5cGU9MCZzcmM9aHR0cCUzQSUyRiUyRmRpbmd5dWUubm9zZG4uMTI3Lm5ldCUyRnJsSUpDU0VTViUzRFVnUGpxdmVZQVZZVUdFcjU2NSUzRGJWSjVlNTVTdzA4UEtFUHMxNTIxODA0ODI1MzEyLmpwZw?x-oss-process=image/format,png
[SELECT ... INTO OUTFILE]: https://dev.mysql.com/doc/refman/5.7/en/select-into.html
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMDE5MjQucG5n?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMDIwMTkucG5n?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMDIwNTYucG5n?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjUxMzI1MzMucG5n?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMDI1MTkucG5n?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMDMzMDMucG5n?x-oss-process=image/format,png
[--databases]: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_databases
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMDQ2MDEucG5n?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMDM4MTgucG5n?x-oss-process=image/format,png
[format_png 10]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMDU2MDQucG5n?x-oss-process=image/format,png
[format_png 11]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMDQ5NTkucG5n?x-oss-process=image/format,png
[format_png 12]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMDU2NTcucG5n?x-oss-process=image/format,png
[format_png 13]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMTAxMzIucG5n?x-oss-process=image/format,png
[img]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly90aW1nc2EuYmFpZHUuY29tL3RpbWc_aW1hZ2UmcXVhbGl0eT04MCZzaXplPWI5OTk5XzEwMDAwJnNlYz0xNTkwMjE0MjA2Njc2JmRpPWI1NmY3MDc0YzYyNjkyMzg4Y2Q3ZDM1NjFlNjAyYzk5JmltZ3R5cGU9MCZzcmM9aHR0cCUzQSUyRiUyRmJwaWMuNTg4a3UuY29tJTJGZWxlbWVudF9vcmlnaW5fbWluX3BpYyUyRjAxJTJGMDIlMkYxOCUyRjY5NTZmNDBlMzlmMjU4NC5qcGc?x-oss-process=image/format,png
[format_png 14]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA1MjMxMTE5MDQucG5n?x-oss-process=image/format,png
[format_png 15]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbWlsb2dlbml1cy9taWxvZ2VuaXVzLWJsb2cvcmF3L21hc3Rlci9pbWcvMjAyMDA2MDEyMzA1NDkucG5n?x-oss-process=image/format,png