mysqldump详解

  • GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。
  • GreatSQL是MySQL的国产分支版本,使用上与MySQL一致。
  • 作者: 杨延昭
  • 文章来源:GreatSQL社区投稿

在进行数据库备份的时候主要分为了逻辑备份和物理备份这两种方式。在数据迁移和备份恢复中使用mysqldump将数据生成sql进行保存是最常用的方式之一。

本文将围绕着mysqldump的使用,工作原理,以及对于InnoDB和MyISAM两种不同引擎如何实现数据一致性这三个方面进行介绍。

一.mysqldump 简介

mysqldump是MySQL自带的逻辑备份工具。
它的备份原理是通过协议连接到 MySQL数据库,将需要备份的数据查询出来,
将查询出的数据转换成对应的insert语句,当我们需要还原这些数据时,
只要执行这些insert语句,即可将对应的数据还原。

二.备份的命令

2.1命令的格式

1.mysqldump [选项] 数据库名 [表名] > 脚本名
2.mysqldump [选项] --数据库名 [选项 表名] > 脚本名
3.mysqldump [选项] --all-databases [选项]  > 脚本名

2.2选项说明

参数名 缩写 含义
--host -h 服务器IP地址
--port -P 服务器端口号
--user -u MySQL 用户名
--pasword -p MySQL 密码
--databases 指定要备份的数据库
--all-databases 备份mysql服务器上的所有数据库
--compact 压缩模式,产生更少的输出
--comments 添加注释信息
--complete-insert 输出完成的插入语句
--lock-tables 备份前,锁定所有数据库表
--no-create-db/--no-create-info 禁止生成创建数据库语句
--force 当出现错误时仍然继续备份操作
--default-character-set 指定默认字符集
--add-locks 备份数据库表时锁定数据库表

三.还原的命令

3.1系统行命令

mysqladmin -uroot -p create db_name 
mysql -uroot -p  db_name < /backup/mysqldump/db_name.db

注:在导入备份数据库前,db_name如果没有,是需要创建的; 而且与db_name.db中数据库名是一样的才可以导入。

3.2source方式

mysql > use db_name;
mysql > source /backup/mysqldump/db_name.db;

四.mysqldump实现的原理

4.1备份流程如下

1.调用FWRL(flush tables with read lock),全局禁止读写
2.开启快照读,获取此期间的快照(仅仅对innodb起作用)
3.备份非innodb表数据(*.frm,*.myi,*.myd等)
4.非innodb表备份完毕之后,释放FTWRL
5.逐一备份innodb表数据
6.备份完成

图片

4.2执行mysqldump,分析备份日志

#  执行语句
[root@localhost backup]# mysqldump -uroot -proot -h127.0.0.1 --all-databases --single-transaction --routines --events --triggers --master-data=2 --hex-blob --default-character-set=utf8mb4 --flush-logs --quick > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# tail -f  /var/lib/mysql/localhost.log
第一步:
  FLUSH /*!40101 LOCAL */ TABLES
# 这里是刷新表


第二步:
  FLUSH TABLES WITH READ LOCK
# 因为开启了--master-data=2,这时就需要flush tables with read lock锁住全库,
  记录当时的master_log_file和master_log_pos点
  这里有一个疑问?
  执行flush tables操作,并加一个全局读锁,那么以上两个命令貌似是重复的,
  为什么不在第一次执行flush tables操作的时候加上锁呢?
  简而言之,是为了避免较长的事务操作造成FLUSH TABLES WITH READ LOCK操作迟迟得不到
  锁,但同时又阻塞了其它客户端操作。
  
  
第三步:
  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
#  --single-transaction参数的作用,设置事务的隔离级别为可重复读,
  即REPEATABLE READ,这样能保证在一个事务中所有相同的查询读取到同样的数据,
  也就大概保证了在dump期间,如果其他innodb引擎的线程修改了表的数据并提交,
  对该dump线程的数据并无影响,然而这个还不够,还需要看下一条


第四步:
  START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
# 获取当前数据库的快照,这个是由mysqldump中--single-transaction决定的。
# WITH CONSISTENT SNAPSHOT能够保证在事务开启的时候,第一次查询的结果就是
  事务开始时的数据A,即使这时其他线程将其数据修改为B,查的结果依然是A。简而言之,就是开启事务并对所有表执行了一次SELECT操作,这样可保证备份时,
  在任意时间点执行select * from table得到的数据和
  执行START TRANSACTION WITH CONSISTENT SNAPSHOT时的数据一致。
 【注意】,WITH CONSISTENT SNAPSHOT只在RR隔离级别下有效。

第五步:
  SHOW MASTER STATUS
# 这个是由--master-data决定的,记录了开始备份时,binlog的状态信息,
  包括MASTER_LOG_FILE和MASTER_LOG_POS

这里需要特别区分一下master-data和dump-slave
master-data:
--master-data=2表示在dump过程中记录主库的binlog和pos点,并在dump文件中注释掉这一行;
--master-data=1表示在dump过程中记录主库的binlog和pos点,并在dump文件中不注释掉这一行,即恢复时会执行;
dump-slave
--dump-slave=2表示在dump过程中,在从库dump,mysqldump进程也要在从库执行,
  记录当时主库的binlog和pos点,并在dump文件中注释掉这一行;
--dump-slave=1表示在dump过程中,在从库dump,mysqldump进程也要在从库执行,
  记录当时主库的binlog和pos点,并在dump文件中不注释掉这一行;
 
第六步:
  UNLOCK TABLES
# 释放锁。

五.mysqldump对InnoDB和MyISAM两种存储引擎进行备份的差异。

5.1对于支持事务的引擎如InnoDB,参数上是在备份的时候加上 –single-transaction 保证数据一致性

–single-transaction 实际上通过做了下面两个操作 :

① 在开始的时候把该 session 的事务隔离级别设置成 repeatable read ;

② 然后启动一个事务(执行 begin ),备份结束的时候结束该事务(执行 commit )

有了这两个操作,在备份过程中,该 session 读到的数据都是启动备份时的数据(同一个点)。可以理解为对于 InnoDB 引擎来说加了该参数,备份开始时就已经把要备份的数据定下来了,
备份过程中的提交的事务时是看不到的,也不会备份进去。

5.2对于不支持事务的引擎如MyISAM,只能通过锁表来保证数据一致性,这里分两种情况:

1)导出全库:加 –lock-all-tables 参数,这会在备份开始的时候启动一个全局读锁 
  (执行 flush tables with read lock),其他 session 可以读取但不能更新数据,
   备份过程中数据没有变化,所以最终得到的数据肯定是完全一致的;

2)导出单个库:加 –lock-tables 参数,这会在备份开始的时候锁该库的所有表,
   其他 session 可以读但不能更新该库的所有表,该库的数据一致;

Enjoy GreatSQL ?

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。

相关链接: GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区:

社区博客有奖征稿详情:https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技术交流群:

微信:扫码添加GreatSQL社区助手微信好友,发送验证信息加群

image-20221030163217640

原文链接:https://www.cnblogs.com/greatsql/p/17273464.html

本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:mysqldump详解 - Python技术站

(0)
上一篇 2023年4月18日
下一篇 2023年4月18日

相关文章

  • MySQL表设计与优化

    影响MySQL查询性能的因素有很多,我们经常会对查询语句、索引字段做一些优化,而其实在表设计的阶段就可能产生一些问题。对于表设计,可以对表结构进行优化,也可以对表字段进行优化。以下通过一个具体的案例演示一些常用的表设计优化的方法。 一、业务需求 这里,就以学生-教师-课程业务作为示例。数据库需要存放学生、教师、课程相关信息。学生信息包括学号、姓名、性别、专业…

    MySQL 2023年4月17日
    00
  • 高效利用mysql索引指南

    下面我将详细讲解“高效利用mysql索引指南”的完整攻略。 1. 索引是什么 索引是一种特殊的数据结构,它可以帮助数据库系统高效地定位和访问数据。在MySQL中,主要有五种类型的索引:B-Tree索引、HASH索引、FULLTEXT索引、SPATIAL索引和RTREE索引。 其中,最常用的是B-Tree索引,因为它对于各种类型的数据都可以有效地工作,并且具有…

    MySQL 2023年5月19日
    00
  • Windows安装MySQL8.0.16 的步骤及出现错误问题解决方法

    下面我就为大家讲解 “Windows安装MySQL8.0.16 的步骤及出现错误问题解决方法”的完整攻略。 步骤: 1. 下载MySQL安装包 首先,我们需要来到MySQL官网 (https://dev.mysql.com/downloads/mysql/) 下载安装包。点击“MySQL Community (GPL)s Downloads”链接,然后在页面…

    MySQL 2023年5月18日
    00
  • MySQL命令行登入的方式有哪些

    这篇“MySQL命令行登入的方式有哪些”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“MySQL命令行登入的方式有哪些”文章吧。 方法一: 开始菜单 -> 所有程序 -> MySQL -> MySQL Server 8.0 -&…

    MySQL 2023年4月14日
    00
  • mysql 无法联接常见故障及原因分析

    MySQL 无法连接常见故障及原因分析 1. 前言 MySQL 是一种常见的关系型数据库,但在使用过程中,我们可能会遇到无法连接数据库的情况。本文将重点介绍 MySQL 无法连接的常见故障及其解决方法,帮助开发者更好地处理这类问题。 2. 常见问题及解决方法 2.1. 无法连接到 MySQL 服务器 2.1.1. 故障描述 当我们使用客户端工具连接 MySQ…

    MySQL 2023年5月18日
    00
  • Windows 下 zip 版的 MySQL 的安装

     创建 配置文件 当 MySQL server 启动时,它会在按照下表列出位置的顺序寻找并读取配置文件: File Name Purpose %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.ini, %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.cnf Global options %WIND…

    MySQL 2023年4月13日
    00
  • MySQL 之 索引原理与慢查询优化

    1. 索引介绍 需求:   一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,因此对查询语句的优化显然是重中之重。说起加速查询,就不得不提到索引了。 索引:    简单的说,相当于图书的目录,可以帮助用户快速的找到需要的内容.    在MySQL中也…

    MySQL 2023年4月13日
    00
  • 【必知必会的MySQL知识】③DML语言

    目录 前言 准备 插入数据 语法格式 插入完整行数据 插入多行数据 将检索出来的数据插入表 更新数据 准备两张表 语法 实践操作 删除数据 语法 实践操作 小结 前言 前面的两篇文章中,我们已经对MySQL有了基本了解。并且知道了怎么用工具连接数据库?怎么创建数据库?怎么创建表?这一篇呢我们就来看看怎么在我们创建的表中插入数据、删除数据和修改数据。也就是上一…

    MySQL 2023年5月2日
    00
合作推广
合作推广
分享本页
返回顶部