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进行快速批量插入千万条数据。 1. 准备工作 创建数据库及数据表; 准备好要插入的数据,以CSV文件形式存在; 2. 导入CSV文件 使用LOAD DATA INFILE语句将CSV文件导入到…

    MySQL 2023年5月18日
    00
  • MySQL错误提示:sql_mode=only_full_group_by完美解决方案

    MySQL错误提示:sql_mode=only_full_group_by 是在 MySQL 5.7 版本中引入的一个新特性。当开启该模式时,如果使用了GROUP BY语句但是SELECT语句中的列名没有在GROUP BY中出现,或者在SELECT语句中使用了聚合函数,但是列名并不在GROUP BY语句中,则会抛出“1055 error – ‘XXXX’ i…

    MySQL 2023年5月18日
    00
  • mysql数据库mysql: [ERROR] unknown option ‘–skip-grant-tables’

    首先,这个错误是因为该命令中使用了未知选项”–skip-grant-tables”,导致MySQL无法识别该选项,所以需要进行相应的处理来避免这个错误。下面是解决方案的完整攻略: 问题描述 在使用MySQL时,执行命令”mysql –skip-grant-tables”,会出现如下错误信息: mysql: [ERROR] unknown option ‘…

    MySQL 2023年5月18日
    00
  • windows无法启动MySQL服务报错1067的解决方法

    下面我来详细讲解“windows无法启动MySQL服务报错1067的解决方法”的完整攻略。 问题描述 在Windows操作系统中,有时候我们使用MySQL服务时会遇到无法启动MySQL服务的问题,并且在Windows系统日志中会出现“错误1067:进程意外终止”的提示信息。这种情况下,我们需要对MySQL服务进行重新配置,以解决该问题。 解决方法 方法一:检…

    MySQL 2023年5月18日
    00
  • MySQL数据库如何导入导出(备份还原)

    MySQL数据库备份还原攻略 MySQL数据库是一个非常流行的开源关系型数据库管理系统,它的备份还原功能也非常重要。 在备份还原的过程中,我们需要使用 mysqldump 命令来导出和导入数据库。下面是具体的步骤: 备份数据库 打开命令行终端(如Windows的CMD,或者macOS和Linux的终端); 进入MySQL的bin目录,即终端内输入cd /pa…

    MySQL 2023年5月18日
    00
  • mysql 8.0.11安装教程图文解说

    安装MySQL 8.0.11需要以下步骤: 下载安装包 首先,你需要从官方网站MySQL官网下载对应操作系统的安装包。选择相应的版本和安装方式,点击下载链接即可。 安装MySQL 运行安装程序并选择安装类型。 阅读许可协议并同意。 在安装类型界面中选择”Custom”自定义安装。 选择你想要安装的MySQL组件和功能。 点击”Next”开始安装MySQL。 …

    MySQL 2023年5月18日
    00
  • 教你为MySQL数据库换挡加速

    教你为MySQL数据库换挡加速 为什么要进行MySQL数据库换挡? 当我们的网站或应用程序的数据库开始增加数据时,它的性能可能会受到影响。为了提高MySQL数据库的性能,我们需要为其换挡或优化。 常见的基本优化包括添加索引、优化查询语句等,但这些方法往往不能解决性能限制。在这种情况下,教你进行数据库换挡,可以是提高性能的另一种方法。 如何进行MySQL数据库…

    MySQL 2023年5月19日
    00
  • MySQL配置文件my.cnf优化详解(mysql5.5)

    MySQL配置文件my.cnf优化详解(mysql5.5) MySQL作为一款广泛应用的数据库管理系统,在数据处理能力、存储空间使用效率,甚至是高并发下的性能表现等方面,都需要经过一些优化手段。其中,修改MySQL配置文件my.cnf是我们提高MySQL性能的一种可行方式。 本文将详细讲解如何对MySQL的my.cnf进行优化,以提高MySQL的性能表现。 …

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