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

爱写Bug的麦洛
• 阅读 1193

基于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的备份和恢复

点赞
收藏
评论区
推荐文章
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
京东云开发者 京东云开发者
2个月前
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
3A网络 3A网络
4个月前
开发一个不需要重写成 Hive QL 的大数据 SQL 引擎
开发一个不需要重写成HiveQL的大数据SQL引擎学习大数据技术的核心原理,掌握一些高效的思考和思维方式,构建自己的技术知识体系。明白了原理,有时甚至不需要学习,顺着原理就可以推导出各种实现细节。各种知识表象看杂乱无章,若只是学习
初识DevOps
基本概念和延伸的思考DevOps,是Development(开发)和Operations(运维)组成的复合词,一般译为“开发运维一体化”。看到这个概念,首先会产生几个问题:开发是什么,哪些环节是开发?运维是什么,哪些环节是运维?开发人员写好代码在本地调试,环境出问题了自己来调整,这是开发工作还是运维工作?系统故障后,运维人员发现是配置文件内容出错了就改成了正
高性能API网关Kong介绍
本文关键词:高性能、API网关、Kong、微服务1.Introduction是随着微服务(Microservice)概念兴起的一种架构模式。原本一个庞大的单体应用(Allinone)业务系统被拆分成许多微服务(Microservice)系统进行独立的维护和部署,服务拆分带来的变化是API的规模成倍增长,API的管理难度也在日益增加,使用API网关发布和管
SPDK对接Ceph性能优化
关键词:SPDK、NVMeOF、Ceph、CPU负载均衡SPDK是intel公司主导开发的一套存储高性能开发套件,提供了一组工具和库,用于编写高性能、可扩展和用户态存储应用。它通过使用一些关键技术实现了高性能:1.将所有必需的驱动程序移到用户空间,以避免系统调用并且支持零拷贝访问2.IO的完成通过轮询硬件而不是依赖中断,以降低时延3.使用消息传递,以避免IO
3A网络 3A网络
4个月前
理解 virt、res、shr 之间的关系(linux 系统篇)
理解virt、res、shr之间的关系(linux系统篇)前言想必在linux上写过程序的同学都有分析进程占用多少内存的经历,或者被问到这样的问题——你的程序在运行时占用了多少内存(物理内存)?通常我们可以通过t
SPDK QOS机制解析
本文关键词:intelspdkbdevqos序:intelspdk软件在存储领域应用广泛。因其可以高效管理linux系统的nvmessd盘,又支持vhostuser协议可以对接qemu虚拟机,在云计算领域通常被用来做本地盘云主机的存储管理软件。如此优秀的一款软件,有必要仔细分析其内部的实现机制,本篇文章主要介绍spdkqos机制。spdk
一个关于SDWAN单臂部署方案验证的实验
假设有这样一张网络,其中RTA和PCA表示某公司的A分支,通过中国电信CT路由器接入互联网ISP;RTB和PCB表示某公司的B分支,通过中国联通CU路由器接入互联网ISP。DNS(8.8.8.8)表示某互联网应用。为实现A分支私网192.168.2.0/24和B分支私网192.168.3.0/24的互通,现计划使用某厂商的SDWAN方案进打通两个内网,像下图
天翼云高可用虚拟IP(HAVIP)实践
(一)产品概述天翼云高可用虚拟IP(HighAvailabilityVirtualIPAddress,简称HAVIP)是一种可用独立创建和删除的私有网络IP地址资源。通过在VIPCIDR中申请一个私有网络IP地址,然后与高可用软件(如高可用软件Keepalived)配合使用,可用在VPC中搭建高可用的主备集群服务,提高VPC中服务的可用性。限制和说明