526互联

MySQL-mysqldump原理

发布时间 2023-04-23 18:56:08作者: zibuyu886

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