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

前言

Hi,大家好,我是麦洛,今天我们聊聊MySQL的备份和恢复,在下面文章中,你会了解到MySQL常见的备份类型,以及基于mysqldump命令在日常开发中如何做MySQL数据库以及表的备份和恢复。最近开始玩公众号了,喜欢的小伙伴可以关注我

0.png

一.为什么要做备份?

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

哎呀,手抖了,删库了

二 数据库备份的类型

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，但是输出文件是在服务器主机上创建的。

更多内容阅读：
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格式的数据

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

1.1 备份所有数据库

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

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

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

结果:

1.2 备份特定数据库

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

结果:

说明:

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

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

1.3 备份数据库的特点表

mysqldump girls boys >d:\boys.sql

结果:

2.重载SQL格式的备份

2.1 不创建Database

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

首先,我们删库跑路..........

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

结果:

2.2 创建Database

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

#创建数据库db1
mysqladmin create db1

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

我们重新生成一个不不包含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

结果:

四.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查阅学习