从导出单表和导入的操作，查看mysqldump到底在做什么。
包括查看dump信息，general日志，source
导出单表，观察其dump信息
[root@lzl ~]# mysqldump -uroot -poracle -v lzldb tab1
mysqldump: [Warning] Using a password on the command line interface can be insecure. --明文密码警告
-- Connecting to localhost... --版本，主机，数据库信息
-- MySQL dump 10.13 Distrib 5.7.20, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: lzldb
-- ------------------------------------------------------
-- Server version 5.7.20
--/* */是可执行注释信息，删除注释符号后，里面的内容是可执行的。--是纯注释
/*!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 */;
-- Retrieving table structure for table tab1...
--
-- Table structure for table `tab1`
--
--默认导入会先删除表，oracleimppdp默认不会
--dump元数据
DROP TABLE IF EXISTS `tab1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tab1` (
`SPECIFIC_CATALOG` varchar(512) NOT NULL DEFAULT '',
`SPECIFIC_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '',
`ORDINAL_POSITION` int(21) NOT NULL DEFAULT '0',
`PARAMETER_MODE` varchar(5) DEFAULT NULL,
`PARAMETER_NAME` varchar(64) DEFAULT NULL,
`DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
`CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_SCALE` int(21) DEFAULT NULL,
`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(64) DEFAULT NULL,
`COLLATION_NAME` varchar(64) DEFAULT NULL,
`DTD_IDENTIFIER` longtext NOT NULL,
`ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
-- Sending SELECT query...
--
-- Dumping data for table `tab1`
--
-- Retrieving rows...
--dump行数据
--默认锁表，不允许dml操作。oracleexpdp不会
LOCK TABLES `tab1` WRITE;
--打印了disable keys，但是默认不执行。在insert前disablekeys可以加快insert的速度，因为不用每次insert结束去维护索引，所有inert结束后维护索引即可
-- --skip-disable-keys只是不输出这段注释，但是没有打开DISABLE KEYS的选项，难道每次source前都要手动更改dump文件，删除注释吗？
/*!40000 ALTER TABLE `tab1` DISABLE KEYS */;
INSERT INTO `tab1` VALUES ('def','sys','create_synonym_db',1,'IN','in_db_name','varchar',64,192,NULL,NULL,NULL,'utf8','utf8
...
/*!40000 ALTER TABLE `tab1` ENABLE KEYS */;
UNLOCK TABLES;
--导完后unlock表
-- Disconnecting from localhost...
--退出后还输出了一些信息注释信息
/*!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 2020-07-16 17:35:21
在dump时查看general日志，看下数据库内部做了什么：
2020-07-16T09:35:21.631157Z 21 Connect root@localhost on using Socket
2020-07-16T09:35:21.631289Z 21 Query /*!40100 SET @@SQL_MODE='' */ --注释不执行，数据库是怎么获取到的？
2020-07-16T09:35:21.631364Z 21 Query /*!40103 SET TIME_ZONE='+00:00' */
2020-07-16T09:35:21.631517Z 21 Query SHOW VARIABLES LIKE 'gtid\_mode'
2020-07-16T09:35:21.634468Z 21 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.FILESWHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='lzldb' AND TABLE_NAME IN ('tab1'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2020-07-16T09:35:21.635662Z 21 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='lzldb' AND TABLE_NAME IN ('tab1')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME --获取文件、引擎、表空间等基本信息
2020-07-16T09:35:21.636708Z 21 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2020-07-16T09:35:21.638917Z 21 Init DB lzldb --使用lzldb
2020-07-16T09:35:21.639005Z 21 Query SHOW TABLES LIKE 'tab1'
2020-07-16T09:35:21.639145Z 21 Query LOCK TABLES `tab1` READ /*!32311 LOCAL */
2020-07-16T09:35:21.639219Z 21 Query show table status like 'tab1'
2020-07-16T09:35:21.639388Z 21 Query SET SQL_QUOTE_SHOW_CREATE=1
2020-07-16T09:35:21.639437Z 21 Query SET SESSION character_set_results = 'binary'
2020-07-16T09:35:21.639529Z 21 Query show create table `tab1`
2020-07-16T09:35:21.640123Z 21 Query SET SESSION character_set_results = 'utf8'
2020-07-16T09:35:21.640236Z 21 Query show fields from `tab1`
2020-07-16T09:35:21.640525Z 21 Query show fields from `tab1`
2020-07-16T09:35:21.641161Z 21 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tab1` --应是hint，不通过buffer直接导出
2020-07-16T09:35:21.642344Z 21 Query SET SESSION character_set_results = 'binary'
2020-07-16T09:35:21.642424Z 21 Query use `lzldb`
2020-07-16T09:35:21.642556Z 21 Query select @@collation_database
2020-07-16T09:35:21.642622Z 21 Query SHOW TRIGGERS LIKE 'tab1' --获取trigger
2020-07-16T09:35:21.642859Z 21 Query SET SESSION character_set_results = 'utf8'
2020-07-16T09:35:21.642903Z 21 Query UNLOCK TABLES
2020-07-16T09:35:21.644148Z 21 Quit
source查看数据库做了什么
mysql> drop table lzldb.tab1;
Query OK, 0 rows affected (0.03 sec)
mysql> use lzldb;
Database changed
mysql> source lzl.sql;
general log查看source命令做了什么:
2020-07-16T10:30:55.168194Z 28 Query /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
2020-07-16T10:30:55.168564Z 28 Query /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
2020-07-16T10:30:55.168762Z 28 Query /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */
2020-07-16T10:30:55.168896Z 28 Query /*!40101 SET NAMES utf8 */
2020-07-16T10:30:55.169190Z 28 Query /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */
2020-07-16T10:30:55.169309Z 28 Query /*!40103 SET TIME_ZONE='+00:00' */
2020-07-16T10:30:55.169383Z 28 Query /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */
2020-07-16T10:30:55.169433Z 28 Query /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
2020-07-16T10:30:55.169480Z 28 Query /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
2020-07-16T10:30:55.169534Z 28 Query /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
2020-07-16T10:30:55.169585Z 28 Query DROP TABLE IF EXISTS `tab1` --删除表，dump信息中确实有
2020-07-16T10:30:55.169666Z 28 Query /*!40101 SET @saved_cs_client = @@character_set_client */
2020-07-16T10:30:55.169713Z 28 Query /*!40101 SET character_set_client = utf8 */
2020-07-16T10:30:55.169858Z 28 Query CREATE TABLE `tab1` (
`SPECIFIC_CATALOG` varchar(512) NOT NULL DEFAULT '',
...
`ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2020-07-16T10:30:55.193735Z 28 Query /*!40101 SET character_set_client = @saved_cs_client */
2020-07-16T10:30:55.193873Z 28 Query LOCK TABLES `tab1` WRITE --锁表
2020-07-16T10:30:55.194200Z 28 Query /*!40000 ALTER TABLE `tab1` DISABLE KEYS */
2020-07-16T10:30:55.195298Z 28 Query INSERT INTO `tab1` VALUES cha'r('def','sys','create_synonym_db',1,'IN','in_db_name','varchar',64,192,NULL,NULL,NULL,'utf8','utf8--插入
2020-07-16T10:30:55.205985Z 28 Query /*!40000 ALTER TABLE `tab1` ENABLE KEYS */
2020-07-16T10:30:55.206108Z 28 Query UNLOCK TABLES--解锁
2020-07-16T10:30:55.206180Z 28 Query /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */
2020-07-16T10:30:55.206236Z 28 Query /*!40101 SET SQL_MODE=@OLD_SQL_MODE */
2020-07-16T10:30:55.206281Z 28 Query /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */
2020-07-16T10:30:55.206361Z 28 Query /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */
2020-07-16T10:30:55.206407Z 28 Query /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */
2020-07-16T10:30:55.206456Z 28 Query /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */
2020-07-16T10:30:55.206508Z 28 Query /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */
2020-07-16T10:30:55.206548Z 28 Query /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
--source就是执行dump，没有其他需要加载的信息