首页 > 数据库 >MySQL-mysqldump原理

MySQL-mysqldump原理

时间：2023-04-23 18:55:16浏览次数：60  
标签：SET 04 MySQL 42 23T03 2023 原理 Query mysqldump

mysqldump原理解析

mysqldump命令执行和输出

mysqldump --no-defaults -h localhost -uroot -p12345678 -P3306 --databases eo_oslog --tables t1 --single-transaction --master-data=2 --set-gtid-purged=OFF > t1.sql

日志输出

2023-04-23T03:42:18.124438Z     2 Connect   root@localhost on  using Socket
2023-04-23T03:42:18.125005Z     2 Query /*!40100 SET @@SQL_MODE='' */
2023-04-23T03:42:18.126586Z     2 Query /*!40103 SET TIME_ZONE='+00:00' */
2023-04-23T03:42:18.126947Z     2 Query FLUSH /*!40101 LOCAL */ TABLES
2023-04-23T03:42:18.129381Z     2 Query FLUSH TABLES WITH READ LOCK
2023-04-23T03:42:18.131081Z     2 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2023-04-23T03:42:18.131396Z     2 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2023-04-23T03:42:18.131732Z     2 Query SHOW MASTER STATUS
2023-04-23T03:42:18.132302Z     2 Query UNLOCK TABLES
2023-04-23T03:42:18.134675Z     2 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='eo_oslog' AND TABLE_NAME IN ('t1'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2023-04-23T03:42:18.150663Z     2 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='eo_oslog' AND TABLE_NAME IN ('t1')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2023-04-23T03:42:18.157585Z     2 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2023-04-23T03:42:18.168951Z     2 Init DB   eo_oslog
2023-04-23T03:42:18.169373Z     2 Query SHOW TABLES LIKE 't1'
2023-04-23T03:42:18.170167Z     2 Query SAVEPOINT sp
2023-04-23T03:42:18.170419Z     2 Query show table status like 't1'
2023-04-23T03:42:18.171219Z     2 Query SET SQL_QUOTE_SHOW_CREATE=1
2023-04-23T03:42:18.171582Z     2 Query SET SESSION character_set_results = 'binary'
2023-04-23T03:42:18.171930Z     2 Query show create table `t1`
2023-04-23T03:42:18.172532Z     2 Query SET SESSION character_set_results = 'utf8'
2023-04-23T03:42:18.172746Z     2 Query show fields from `t1`
2023-04-23T03:42:18.173806Z     2 Query show fields from `t1`
2023-04-23T03:42:18.174683Z     2 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2023-04-23T03:42:18.177802Z     2 Query SET SESSION character_set_results = 'binary'
2023-04-23T03:42:18.177967Z     2 Query use `eo_oslog`
2023-04-23T03:42:18.178154Z     2 Query select @@collation_database
2023-04-23T03:42:18.178402Z     2 Query SHOW TRIGGERS LIKE 't1'
2023-04-23T03:42:18.179549Z     2 Query SET SESSION character_set_results = 'utf8'
2023-04-23T03:42:18.179805Z     2 Query ROLLBACK TO SAVEPOINT sp
2023-04-23T03:42:18.179970Z     2 Query RELEASE SAVEPOINT sp
2023-04-23T03:42:18.198236Z     2 Quit

有一些关键的点

1. flush tables

Flush tables简介

官方手册中关于Flush tables的介绍， Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.其解析就是关闭所有已打开的表对象，同时将查询缓存中的结果清空。就是说Flush tables的一个效果就是会等待所有正在运行的SQL请求结束。

因为，SQL语句在执行前，都会打开相应的表对象，如select * from t1语句，会找到t1表的frm文件，并打开表内存对象。为了控制表对象使用的内存空间和其他资源，MySQL会隐式（后台表对象管理线程）或显式（flush tables等）来关闭已打开但并没有使用的表对象。

然而，正在使用的表对象是不能关闭的（如SQL请求仍在运行），因此，Flush Tables操作会被正在运行的SQL请求阻塞。

2. fush tables with read lock

库级别读锁

为什么不直接执行flush tables with read lock，而是先执行flush tables呢？

这样执行FTWRL有个好处，flush tables是不加锁的，避免执行FTWRL时，等待业务释放表锁时间过长，导致部分表锁住影响业务。

image-20230423151943957

可以看到，当有事务锁住表时lock table t2 read，执行flush tables with read lock会被阻塞，此时表t1执行insert into t1 values (2,2);会阻塞，影响业务。其实就是，执行两次降低了加read lock的等待时间。

3. set session隔离级别为可重复读

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

在此隔离级别下，可以实现在一个事务下，数据的可重复读（废话。。）。

4. 开启一致性快照事务

START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
  1. 提供一致性快照：该命令会创建一个具有一致性快照（consistent snapshot）的事务，并将其与当前线程关联。一致性快照可以确保事务中的所有查询都使用相同的数据视图，避免了因并发访问而导致的数据不一致问题。
  2. 支持回滚操作：一旦启动了事务，并创建了一致性快照，就可以执行多个查询语句，并对查询结果进行修改和更新。如果某些操作出现错误或者需要回滚，则可以通过 ROLLBACK 命令回滚整个事务，并恢复到一致性快照所表示的状态。

需要注意的是，该命令仅适用于 InnoDB 存储引擎，并要求数据库版本不低于 4.1.0 版本。此外，该命令还可能影响系统性能，特别是在大型数据库和高并发场景下。因此，在实际应用中，需要根据具体情况合理使用，并结合其他技术手段来提高系统的性能和可靠性。

5. show master status

SHOW MASTER STATUS

查看本地binlog状态信息

image-20230423160808085

6. unlock tables

UNLOCK TABLES

疑问点：在事务内执行unlock tables可以释放表锁吗？

image-20230423161322517

经过测试，是可以的。

graph LR; A(FTWRL) B(set RR) C(start tx) D(unlock tables) A-->B; B-->C; C-->D;

7. 查询undo log

SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='eo_oslog' AND TABLE_NAME IN ('t1'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME

直接执行是会报错的：

ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'information_schema.FILES.EXTRA' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

sql_mode=only_full_group_by的情况下不允许执行，所以在开始时有SET @@SQL_MODE=''的操作。

这条SQL在mysql5.7版本中查询结果是空的：首先mysql5.7中默认的undo log是在系统表空间中的，所以file type中没有'UNDO LOG'，如果需要处理，参考另一篇文档：mysql undo log管理 (lingxi.site)

在8.0中，默认LOGFILE_GROUP_NAME也是空的，一般手动管理的undo log会有一些日志组。

8. 查询分区信息

SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='eo_oslog' AND TABLE_NAME IN ('t1')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

自动管理的tablespace中名字都为空。

9. 查看ndb version

SHOW VARIABLES LIKE 'ndbinfo\_version'

10. 创建保存点

2023-04-23T03:42:18.168951Z     2 Init DB   eo_oslog
2023-04-23T03:42:18.169373Z     2 Query SHOW TABLES LIKE 't1'
2023-04-23T03:42:18.170167Z     2 Query SAVEPOINT sp
2023-04-23T03:42:18.170419Z     2 Query show table status like 't1'

首先检查表是不是存在，然后创建保存点 savepoint，查询表的状态。

11. 设置SQL_QUOTE_SHOW_CREATE

SQL_QUOTE_SHOW_CREATE是MySQL系统变量之一，用于控制在使用SHOW CREATE TABLE命令生成表定义时是否对列名和其他标识符进行引用。当该变量的值为1时，SHOW CREATE TABLE将引用所有标识符（表、列、索引等），以确保生成的输出可以直接用作SQL语句。当该变量的值为0时，SHOW CREATE TABLE将不会引用标识符。

例如，假设有一个名为my_table的表，它包含一个名为my_column的列。如果SQL_QUOTE_SHOW_CREATE的值为1，则使用以下命令将显示创建表的SQL语句：

SHOW CREATE TABLE my_table;

输出结果可能如下所示：

CREATE TABLE `my_table` (
  `my_column` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

注意到列名my_column被反引号(`)括起来了，这是因为SQL_QUOTE_SHOW_CREATE的值为1，使得SHOW CREATE TABLE命令对所有标识符进行了引用。如果SQL_QUOTE_SHOW_CREATE的值为0，则输出将如下所示，没有对标识符进行引用：

CREATE TABLE my_table (
  my_column int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

需要注意的是，在实际编写应用程序代码时，最好不要依赖SHOW CREATE TABLE命令自动生成的SQL语句，因为生成的语句可能包含一些非必要的信息或注释。而是应该手动编写和维护DDL语句。

12. 导出表结构和数据

2023-04-23T03:42:18.171219Z     2 Query SET SQL_QUOTE_SHOW_CREATE=1
2023-04-23T03:42:18.171582Z     2 Query SET SESSION character_set_results = 'binary'
2023-04-23T03:42:18.171930Z     2 Query show create table `t1`
2023-04-23T03:42:18.172532Z     2 Query SET SESSION character_set_results = 'utf8'
2023-04-23T03:42:18.172746Z     2 Query show fields from `t1`
2023-04-23T03:42:18.173806Z     2 Query show fields from `t1`
2023-04-23T03:42:18.174683Z     2 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`

13. 后续操作

2023-04-23T03:42:18.178154Z     2 Query select @@collation_database
2023-04-23T03:42:18.178402Z     2 Query SHOW TRIGGERS LIKE 't1'
2023-04-23T03:42:18.179549Z     2 Query SET SESSION character_set_results = 'utf8'
2023-04-23T03:42:18.179805Z     2 Query ROLLBACK TO SAVEPOINT sp
2023-04-23T03:42:18.179970Z     2 Query RELEASE SAVEPOINT sp

设置排序规则，导出触发器，回滚到保存点，释放保存点。

附录，导出的数据：

-- MySQL dump 10.13  Distrib 5.7.39, for Linux (x86_64)
--
-- Host: localhost    Database: eo_oslog
-- ------------------------------------------------------
-- Server version       5.7.39-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 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=460;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,1),(2,2),(3,3),(3,3),(4,4);
/*!40000 ALTER TABLE `t1` 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 2023-04-23 17:57:58

标签：SET,04,MySQL,42,23T03,2023,原理,Query,mysqldump
From： https://www.cnblogs.com/zibuyu886/p/17347445.html

相关文章

  • 读《mysql是怎样运行的》有感
    最近读了一本书《mysql是怎样运行的》，读完后在大体上对mysql的运行有一定的了解。在以前，我对mysql有以下的为什么：InnoDB中的表空间、段、区和页是什么？redolog为什么就能实现事务的持久性？到底什么是意向锁？意向锁有什么用？mysql中的外连接、内连接到底是什么？事务中的一致性到......
  • 批量替换 MySQL 指定字段中的字段
    批量替换MySQL指定字段中的字符串是数据库应用中很常见的需求，但是有很多初学者在遇到这种需求时，通常都是用脚本来实现；其实，MySQL内置的有批量替换语法，效率也会高很多；批量替换的具体语法是：Code:UPDATE表名SET指定字段=replace(指定字段,’要替......
  • C# Mysql The given key '12599' was not present in the dictionary.
    如果查询语句没有问题数据库连接字符串也没有问题，可能是Mysql.Data引用与当前安装的Mysql数据库版本不兼容的问题。我本地安装的mysql版本是8.30，在VS里使用Nuget程序包下载的Mysql.Data引用是其他更低的版本，将程序里用的Mysql.Data也更新到8.30就好了。版本保持一致。 ......
  • 做网站的一些定律 原理和效应汇总（26条）
    做网站的一些定律原理和效应汇总（26条）做网站符合人类发现的些许定律和原理，也体现了一些著名的效应，请看：1.250定律拉德认为：每一位顾客身后，大体有250名亲朋好友。如果您赢得了一位顾客的好感，就意味着赢得了250个人的好感；反之，如果你得罪了一名顾客，也就意味......
  • MySQL日期字符串转日期格式，日期格式数据转为字符串
    如下:1、日期字符串转换为日期格式数据SELECTDATE('2017-02-11');SELECTDATE('2017/02/11');SELECTSTR_TO_DATE('2015/02/25','%Y/%m/%d');SELECTSTR_TO_DATE('2015-02-25','%Y-%m-%d');返回日期格式数据 2、DATE_FORMAT......
  • MySQL常用函数
    mysql是一个常用的关系型数据库管理系统，提供了许多有用的函数来帮助处理数据。下面是一些mysql中常用的函数：字符串函数concat()：将两个或多个字符串连接起来。length()：返回指定字符串的字符数。substr()：返回指定字符串的子串。lower()：将指定字符串转换为小写形式。......
  • MySQL索引失效原因
    索引介绍MySQL默认的存储引擎是InnoDB，它采用B+树作为索引的数据结构。InnoDB和MyISAM存储引擎都支持B+树索引，但是它们的数据存储结构实现方式不同。InnoDB：B+树索引的叶子节点保存全部数据MyISAM：B+树索引的叶子节点保存数据的物理地址在创建表的时候，InnoDB存储引擎默认会创......
  • mysql select for update + 事务处理数据一致性
    如果SELECT后面若要UPDATE同一个表数据的相关操作，最好使用SELECT...FORUPDATE。一：举例说明假设商品表单test_leyangjun 内有一个存放商品库存的num字段，一个id主键 ，在生成订单前须先确定num>0 ，然后才把数量更新。代码如下(比如现在的库存：num=3对应的id=3，现在生成一个订单......
  • 16张动图讲透网络原理，可真是个人才
    晚上好，我是老杨。今天这篇文章来自于小白粉丝的分享，我一看，这内容不错，务必也要众乐乐一下。网络相关的技术解读，我在文章里讲过了很多次，换着花样解读的那种。如果你想看，推荐你阅读以下这几篇：《别再要这张网工技能图谱了，老杨就发这一遍 》《这份高清版TCP/IP全景图，网络工程师人手一份......
  • 基于Canal实现MySQL 8.0 数据库数据同步
    前言服务器说明主机名称操作系统说明192.168.11.82Ubuntu22.04主库所在服务器192.168.11.28OracleLinuxServer8.7从库所在服务器版本说明MySQL版本：MySQL_8.0.32Canal版本：Canal_1.1.7//我的canal安装部署在192.168.11.82上，当然你也可以部......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99