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

麦洛 等级 341 0 0

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

前言

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

一.为什么要做备份?

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

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

二 数据库备份的类型

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

1.1 物理备份

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

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

1.2 逻辑备份

逻辑备份是指将数据库的逻辑结构(CREATE DATABASECREATE 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格式的数据

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

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

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

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

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

1.1 备份所有数据库

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

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump --all-databases --hex-blob >d:\alldatabases.sql
  • --hex-blob :使用十六进制符号转储二进制字符串[解决导出中文乱码问题]

结果:

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

1.2 备份特定数据库

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

结果:

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

说明:

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

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

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

1.3 备份数据库的特点表

mysqldump girls boys >d:\boys.sql

结果:

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

2.重载SQL格式的备份

2.1 不创建Database

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

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

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

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

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

结果:

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

2.2 创建Database

备份文件不包含CREATE DATABASEUSE语句 ,请首先创建数据库(如有必要):

#创建数据库db1
mysqladmin create db1

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

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

我们重新生成一个不不包含CREATE DATABASEUSE语句的文件

mysqldump girls > d:\nodatabasegirls.sql

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

彩蛋:如果遇到这个,就重启下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聊一聊MySQL的备份和恢复

四.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开发者和技术爱好者,目前关注javaSpring、微服务、云原生方向。为了将内容精准推给喜欢我的小伙伴,在大家建议下开通了公众号。喜欢的小伙伴可以关注我,第一时间获取文章信息。也可以后台回复加群加入交流群,大家一起进步!谢谢大家对我的支持。

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

收藏
评论区

相关推荐

postgresql和mysql哪个好
postgresql和mysql都是免费且功能强大的开源数据库,很多用户面对这两个库都会有一个问题,那就是哪一个才是最好的开源数据库,MySQL还是PostgreSQL呢?该选择哪一个开源数据库呢? postgresql和mysql哪个好 一.PostgreSQL相对于MySQL的优势 1、在SQL的标准实现上要比MySQL完善,而且功能实现比较严谨;
[DB]PostgreSQL 与 MySQL 相比,优势何在?
PostgreSQL 与 MySQL 相比,优势何在? 数据库 知乎 Pg 没有 MySQL 的各种坑 MySQL 的各种 text 字段有不同的限制, 要手动区分 small text, middle text, large text... Pg 没有这个限制, text 能支持各种大小. 按照 SQL 标准, 做 null 判断不能用
基于mysqldump聊一聊MySQL的备份和恢复
前言 Hi,大家好,我是麦洛,今天我们聊聊MySQL的备份和恢复,在下面文章中,你会了解到MySQL常见的备份类型,以及基于mysqldump命令在日常开发中如何做
MySQL的语句执行顺序
今天遇到一个问题就是mysql中insert into 和update以及delete语句中能使用as别名吗?目前还在查看,但是在查阅资料时发现了一些有益的知识,给大家分享一下,就是关于sql以及MySQL语句执行顺序: sql和mysql执行顺序,发现内部机制是一样的。最大区别是在别名的引用上。  一、sql执行顺序 
golang实现MySQL数据库事物的提交与回滚
MySQL 事务主要用于处理操作量大,复杂度高的数据。在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务用来管理 insert,update,delete 语句,事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。 一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicit
47 张图带你 MySQL 进阶!
我们在 MySQL 入门篇主要介绍了基本的 SQL 命令、数据类型和函数,在局部以上知识后,你就可以进行 MySQL 的开发工作了,但是如果要成为一个合格的开发人员,你还要具备一些更高级的技能,下面我们就来探讨一下 MySQL 都需要哪些高级的技能
21分钟 MySQL 入门教程
21分钟 MySQL 入门教程 目录 一、MySQL的相关概念介绍(c1) 二、Windows下MySQL的配置(c2) 配置步骤(d1) MySQL服务的启动、停止与卸载(d2) 三、
MySQL基础(非常全)
MySQL基础一、MySQL概述1、什么是数据库 ? 答:数据的仓库,如:在ATM的示例中我们创建了一个 db 目录,称其为数据库2、什么是 MySQL、Oracle、SQLite、Access、MS SQL Server等 ? 答:他们均是一个软件,都有两个主要的功能: a. 将数据保存到文件或内存 b. 接收特定的命令,然后对文件进行相应
MySQL(一)MySQL基础介绍
最近的学习内容是数据库相关的一些知识,主要以MySQL为主,参考书籍——《MySQL必知必会》MySQL学习及下载地址:https://dev.mysql.com/MySQL学习使用注意事项:1、必须访问一个已有的MySQL服务器,需要一个服务器账号(一个登录名和一个口令)2、MySQL运行在所有主要平台上,包括Windows、Linux、Solaris、M
Mysql Workbench使用教程
<1 MySQL WorkbenchMySQL Workbench 为数据库管理员、程序开发者和系统规划师提供可视化的Sql开发、数据库建模、以及数据库管理功能。 <2.MySQL Workbench 的下载和安装 (1)安装最新MySql时,有是否安装MySql Workbench的选项,可选择安装。 (2)可以独立安装MySql Workbench。
什么是索引?Mysql目前主要的几种索引类型
一、索引MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。创
一文读懂一条 SQL 查询语句是如何执行的
2001 年 MySQL 发布 3.23 版本,自此便开始获得广泛应用,随着不断地升级迭代,至今 MySQL 已经走过了 20 个年头。为了充分发挥 MySQL 的性能并顺利地使用,就必须正确理解其设计思想,因此,了解 MySQL 的逻辑架构是必要的。本文将通过一条 SQL 查询语句的具体执行过程来详细介绍 MySQL 架构中的各个组件。MySQL 逻辑架构
MySQL最全整理,1200页文档笔记,从高级到实战讲的太清楚了
闲话作为一名编程人员,对MySQL一定不会陌生,尤其是互联网行业,对MySQL的使用是比较多的。对于求职者来说,MySQL又是面试中一定会问到的重点,很多人拥有大厂梦,却因为MySQL败下阵来。实际上,MySQL并不难,今天这份最全的MySQL总结,一共1200页,几乎涵盖了MySQL的所有知识,尤其突出了实战技能和高级知识点,无论是工作还是面试看完这篇就足
Python中如何判断表的存在
MySQL如何判断表是否存在MySQL判断表是否存在可以使用下面语句:select from informationschema.tables where tablename 'student';select from informationschema.tables where tablename 'teacher';SQL语句说明:informati
百度后端二面有哪些内容,万字总结(一)
前言这是最近一位老朋友去百度面试,应该是面试资深工程师岗位,他跟我讲被问到mysql索引知识点?其实面试官主要还是考察对mysql的性能调优相关,问理论知识其实也是想知道你对原理的认知,从而确认你是否有相关的调优经验。朋友说他回答的还行,然后很顺利进行了三面四面。那么本文将跟大家一起来聊一聊这个如何回答面试官的这个问题! 公众号:我是阿沐 以下是自己的理解