mysqldump 逻辑备份和物理备份

Wesley13
• 阅读 314

逻辑备份

逻辑备份是备份sql语句,在恢复的时候执行备份的sql语句实现数据库数据的重现。

工具:mysqldump

特点:

1、可移植性比较强

2、备份和恢复的花费时间长,不适用于大型业务系统

物理备份

物理备份就是备份数据文件了,比较形象点就是cp下数据文件,但真正备份的时候自然不是的cp这么简单。

工具:xtrabackup

特点:

1、不具备移植性,备份环境和恢复环境必须是完全相同的

2、备份和恢复时间较快,适用于大型业务系统

mysqldump特性

备份原理:

通过协议连接到mysql数据库,将需要备份的数据查询出来,将查询出的数据转换成对应的insert语句,当我们需要还原这些数据时,只要执行这些insert语句,即可将对应的数据还原。

优点:

可以直接使用文本处理工具处理对应的备份数据,因为备份数据已经被mysqldump转换为了对应的insert语句,所以,我们可以借助文件系统中的文本处理工具对备份数据进行直接处理。

缺点:

1、无法备份索引等信息(因为它只是单纯的将需要备份的数据查询出来,还原后需要重建索引)

2、当数据为浮点类型时,会出现精度丢失

3、备份过程属于逻辑备份,备份速度、恢复速度与物理备份工具相比较慢,而且mysqldump备份的过程是串行化的,不会并行的进行备份,如果想要并行备份,可以使用mydumper,但是此处我们不考虑这些,只考虑mysqldump,当数据量较大时,一般不会使用mysqldump进行备份,因为效率较低。

特性:

mysqldump对innodb存储引擎支持热备,innodb支持事务,我们可以基于事务通过mysqldump对数据库进行热备。

mysqldump对myisam存储引擎只支持温备,通过mysqldump对使用myisam存储引擎的表进行备份时,最多只能实现温备,因为在备份时会对备份的表请求锁,当备份完成后,锁会被释放。

备份数据长这样:

[root@backup2] /var/lib/mysql$ mysqldump -uroot test -p
Enter password:
-- MySQL dump 10.13 Distrib 5.7.21, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.7.21-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `tb1`
--

DROP TABLE IF EXISTS `tb1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tb1` (
`name` varchar(60) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tb1`
--

LOCK TABLES `tb1` WRITE;
/*!40000 ALTER TABLE `tb1` DISABLE KEYS */;
INSERT INTO `tb1` VALUES ('hanli',27),('dongpo',24),('heiqi',28),('yeyi',28),('xueji',24);
/*!40000 ALTER TABLE `tb1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-03-17 4:50:16

选项

--master-data   

标记备份开始时binlog所对应的position(位置)

值为0:不记录对应二进制日志文件位置,与不使用此选项的效果相同。

此值为1:记录对应二进制日志文件位置,默认值,也就是说,使用--master-data与使用--master-data=1的效果相同,会在备份文件中生成对应的"CHANGE MASTER TO"语句,此语句中标明了备份开始时二进制日志的前缀名以及其所处的position(位置),生成此语句的目的是,在主从复制结构中的"从服务器"中通过备份sql还原数据以后,告诉"从库",从"主库"的二进制日志文件中的哪个位置开始"同步"。如果我们没有使用主从复制结构,同时又想要在备份时记录二进制日志文件的position,则可以将此选项的值设置为2

此值为2:记录对应二进制日值文件的位置,如果将此选项的值设置为2,则会在备份文件中生成对应的"CHANGE MASTER TO"语句,但是此语句将会被注释,只是单纯的为了记录备份时的二进制日志文件位置

--flush-logs

如果,我们将二进制日志的大小设置为600兆,那么,每当二进制日志的大小满600兆,对应的二进制日志文件就会发生滚动,生成一个新的二进制文件,并将原来的600兆保存,假设,我们使用mysqldump对数据库进行备份的那一刻,对应binlog的大小为300兆,也就是说,备份操作开始时,二进制日志文件的position的位置则会处于文件居中的位置,那么,当我们想要找到对应position进行重放时,此位置之前的操作记录对于我们来说都是"无用"的,可是比较尴尬的是,我们还必须找到此位置,这样就会产生一些"多余的工作量"。如果使用了此选项,备份开始时就会滚动一次二进制日志,无论二进制日志对应的文件大小是否达到600兆,都会滚动,这样,我们就能在对应的二进制日志文件的开头部分开始重放日志了。

--routines

备份时存储过程和存储函数

--triggers

备份时触发器

--events

备份时事件表

--single-transaction(表类型为innodb,热备)

--lock-all-tables(表类型为myisam,温备)

对所有数据备份时,使用--lock-all-tables,表示对所有库的所有表加读锁,对应的短选项为-x,与--single-transaction不能同时存在,示例如下

mysqldump -uroot -h192.168.1.146 --lock-all-tables --all-databases -p > dbbackup.sql

对指定的数据库进行备份时,使用--lock-tables,表示对指定库的所有表加锁,对应的短选项为-l,与--single-transaction不能同时存在时,示例如下

mysqldump -uroot -h192.168.1.146 --lock-tables --databases zsythink -p > zsythink.sql

也可以使用上述语句对innodb存储引擎的表进行备份,不过这样就不是热备了,而是温备。

实例:

对于innodb存储引擎

1、未开启二进制日志,备份指定数据库

mysqldump -uroot -h192.168.1.146 --single-transaction --routines --triggers --events --databases test -p > test.sql

2、开启二进制日志,备份指定数据库

mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --single-transaction --routines --triggers --events --databases test -p >test.sql

3、开启二进制日志,备份所有数据库

mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --single-transaction --routines --triggers --events --all-databases -p > dbbackup.sql

对于myisam存储引擎

1、未开启二进制日志,备份指定数据库

mysqldump -uroot -h192.168.1.146 --routines --triggers --events --lock-tables --databases test -p > test.sql

2、开启二进制日志的情况下,备份指定数据库

mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --routines --triggers --events --lock-tables --databases test -p > test.sql

3、开启二进制日志,备份所有数据库

mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --routines --triggers --events --lock-all-tables --all-databases -p > dbbackup.sql

数据恢复

注意:

因为恢复数据时会执行大量的insert语句,如果没有特殊要求,还原时没有必要将这些操作记录到二进制日志中,所以关闭当前会话的二进制日志记录。
set sql_log_bin=OFF;
所有恢复操作完成后,再将当前会话中的sql_log_bin再次开启。

恢复命令
\. /testdir/zsythink.sql
此命令只是恢复到了备份sql对应的时间点,还需要进行时间点还原。

进行时间点恢复时,备份时间点之后的数据则需要通过二进制日志进行还原,首先,要从二进制日志中提取对应的sql,提取sql的起始位置为备份开始时那一刻二进制文件对应的position,因为在使用mysldump备份时,我们推荐使用--master-data=2选项,所以在对应的数据库备份sql文件中应该存在对应的position,提取sql的结束位置应该是drop语句对应的位置,因为咱们模拟的场景是有人误操作drop了数据库,所以结束位置应该是drop语句的位置。注意,不要把误操作的drop语句提取出来,否则重放对应sql时又会将对应的数据删除,如果是那样就前功尽弃了。

原文:http://www.zsythink.net/archives/1450

点赞
收藏
评论区
推荐文章
Wesley13 Wesley13
1年前
MySQL备份与恢复(3)mysqldump备份多个库
一、mysqldump的工作原理、    利用mysqldump命令备份数据的过程,实际上就是把数据从mysql库以逻辑的sql语句的形式直接输出或生成备份的文件的过程    利用这个备份文件恢复的时候的原理?就是把备份的sql命令再执行一遍。二、备份多个库mysqlshowdatabases;
Wesley13 Wesley13
1年前
cobol学习之十数据库的增删改查模板
这次连接数据库使用的是ODBC连接access数据库,里面主要是一个增删改查的模板备份,方便以后查询。000001IDENTIFICATIONDIVISION.000002PROGRAMID.SAMPLEDB2.00
SPDK QOS机制解析
本文关键词:intelspdkbdevqos序:intelspdk软件在存储领域应用广泛。因其可以高效管理linux系统的nvmessd盘,又支持vhostuser协议可以对接qemu虚拟机,在云计算领域通常被用来做本地盘云主机的存储管理软件。如此优秀的一款软件,有必要仔细分析其内部的实现机制,本篇文章主要介绍spdkqos机制。spdk
3A网络 3A网络
4个月前
理解 virt、res、shr 之间的关系(linux 系统篇)
理解virt、res、shr之间的关系(linux系统篇)前言想必在linux上写过程序的同学都有分析进程占用多少内存的经历,或者被问到这样的问题——你的程序在运行时占用了多少内存(物理内存)?通常我们可以通过t
初识DevOps
基本概念和延伸的思考DevOps,是Development(开发)和Operations(运维)组成的复合词,一般译为“开发运维一体化”。看到这个概念,首先会产生几个问题:开发是什么,哪些环节是开发?运维是什么,哪些环节是运维?开发人员写好代码在本地调试,环境出问题了自己来调整,这是开发工作还是运维工作?系统故障后,运维人员发现是配置文件内容出错了就改成了正
高性能API网关Kong介绍
本文关键词:高性能、API网关、Kong、微服务1.Introduction是随着微服务(Microservice)概念兴起的一种架构模式。原本一个庞大的单体应用(Allinone)业务系统被拆分成许多微服务(Microservice)系统进行独立的维护和部署,服务拆分带来的变化是API的规模成倍增长,API的管理难度也在日益增加,使用API网关发布和管
一个关于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方案进打通两个内网,像下图
3A网络 3A网络
4个月前
开发一个不需要重写成 Hive QL 的大数据 SQL 引擎
开发一个不需要重写成HiveQL的大数据SQL引擎学习大数据技术的核心原理,掌握一些高效的思考和思维方式,构建自己的技术知识体系。明白了原理,有时甚至不需要学习,顺着原理就可以推导出各种实现细节。各种知识表象看杂乱无章,若只是学习
SPDK对接Ceph性能优化
关键词:SPDK、NVMeOF、Ceph、CPU负载均衡SPDK是intel公司主导开发的一套存储高性能开发套件,提供了一组工具和库,用于编写高性能、可扩展和用户态存储应用。它通过使用一些关键技术实现了高性能:1.将所有必需的驱动程序移到用户空间,以避免系统调用并且支持零拷贝访问2.IO的完成通过轮询硬件而不是依赖中断,以降低时延3.使用消息传递,以避免IO
天翼云高可用虚拟IP(HAVIP)实践
(一)产品概述天翼云高可用虚拟IP(HighAvailabilityVirtualIPAddress,简称HAVIP)是一种可用独立创建和删除的私有网络IP地址资源。通过在VIPCIDR中申请一个私有网络IP地址,然后与高可用软件(如高可用软件Keepalived)配合使用,可用在VPC中搭建高可用的主备集群服务,提高VPC中服务的可用性。限制和说明