mysqldump原理分析-白红宇

mysqldump原理分析

阅读量：2443 次

发布时间：2019-05-10

本文共 2715 字，大约阅读时间需要 9 分钟。

测试库:mysqldump_test

命令:mysqldump -u root -p mysqldump_test > /User/admin/Desktop/mysqldump_test.sql

准备工作：

1、创建一个mysqldump_test的数据库

CREATE DATABASE `mysqldump_test` /*!40100 DEFAULT CHARACTER SET utf8 */

2、创建一个简单的person表

CREATE TABLE `person` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(32) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

3、随便插入几条数据

4、开启general日志

如果：show variables like ‘%general_log%’;展示出来的general_log为OFF，设置一个全局开启general日志：set @@global.general_log = ON;

日志通过general_log_file对应的文件进行查看。

5、通过执行mysqldump -u root -p mysqldump_test > /User/admin/Desktop/mysqldump_test.sql得到下面的日志

完整日志

171210 10:08:12

8 Connect root@localhost on

8 Query /*!40100 SET @@SQL_MODE='' */

8 Query /*!40103 SET TIME_ZONE='+00:00' */

8 Query SHOW VARIABLES LIKE 'gtid\_mode'

8 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 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 IN ('mysqldump_test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME

8 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 IN ('mysqldump_test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

8 Query SHOW VARIABLES LIKE 'ndbinfo\_version'

8 Init DB mysqldump_test

8 Query show tables

8 Query LOCK TABLES `person` READ /*!32311 LOCAL */

8 Query show table status like 'person'

8 Query SET SQL_QUOTE_SHOW_CREATE=1

8 Query SET SESSION character_set_results = 'binary'

8 Query show create table `person`

8 Query SET SESSION character_set_results = 'utf8'

8 Query show fields from `person`

8 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `person`

8 Query SET SESSION character_set_results = 'binary'