本系列文章主要是对mysql的一些基础功能做些测试记录，方便大家参考，也方便自己后续翻看。
本次测试较为简单，主要对比master-data、--set-gtid-purged这两个参数。
test库上创建一张简单的表a，包含id和age两个字段，都是int类型,导出语句分别如下：
mysqldump --single-transaction --master-data test > test1.sqlmysqldump -uroot -p --single-transaction --master-data test > test1.sqlmysqldump -uroot -p --single-transaction --set-gtid-purged=OFF test > test3.sqlmysqldump -uroot -p --single-transaction test > test4.sql
利用linux自带diff工具，对比test1.sql 和test2.sql
diff -y test1.sql test2.sqlroot@15d0fdf7b43a:~# diff -y test1.sql test2.sql-- MySQL dump 10.13 Distrib 8.0.25, for Linux (x86_64) -- MySQL dump 10.13 Distrib 8.0.25, for Linux (x86_64)-- ---- Host: localhost Database: test -- Host: localhost Database: test-- ------------------------------------------------------ -- -------------------------------------------------------- Server version 8.0.25 -- Server version 8.0.25/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESUL /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESUL/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION/*!50503 SET NAMES utf8mb4 */; /*!50503 SET NAMES utf8mb4 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */; /*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECK /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECK/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FO /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FO/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALU /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALU/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;SET @@SESSION.SQL_LOG_BIN= 0; SET @@SESSION.SQL_LOG_BIN= 0;-- ---- GTID state at the beginning of the backup -- GTID state at the beginning of the backup-- --SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ ''; SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';-- ---- Position to start replication or point-in-time recovery fr <-- <<CHANGE MASTER TO MASTER_LOG_FILE='binlog.000005', MASTER_LOG_ <<-- <-- Table structure for table `a` -- Table structure for table `a`-- --DROP TABLE IF EXISTS `a`; DROP TABLE IF EXISTS `a`;/*!40101 SET @saved_cs_client = @@character_set_client */ /*!40101 SET @saved_cs_client = @@character_set_client *//*!50503 SET character_set_client = utf8mb4 */; /*!50503 SET character_set_client = utf8mb4 */;CREATE TABLE `a` ( CREATE TABLE `a` (`id` int NOT NULL, `id` int NOT NULL,`age` int DEFAULT NULL, `age` int DEFAULT NULL,PRIMARY KEY (`id`) PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_/*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET character_set_client = @saved_cs_client */;-- ---- Dumping data for table `a` -- Dumping data for table `a`-- --LOCK TABLES `a` WRITE; LOCK TABLES `a` WRITE;/*!40000 ALTER TABLE `a` DISABLE KEYS */; /*!40000 ALTER TABLE `a` DISABLE KEYS */;INSERT INTO `a` VALUES (1,2),(2,3); INSERT INTO `a` VALUES (1,2),(2,3);/*!40000 ALTER TABLE `a` ENABLE KEYS */; /*!40000 ALTER TABLE `a` ENABLE KEYS */;UNLOCK TABLES; UNLOCK TABLES;SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN; SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT * /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION * /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2021-06-15 14:58:58 | -- Dump completed on 2021-06-15 14:59:28
可以明显看出，有master-data是有change master to语句切换binlog位点信息。
接下来，对比一下3和4的区别。
root@15d0fdf7b43a:~# diff -y test3.sql test4.sql-- MySQL dump 10.13 Distrib 8.0.25, for Linux (x86_64) -- MySQL dump 10.13 Distrib 8.0.25, for Linux (x86_64)-- ---- Host: localhost Database: test -- Host: localhost Database: test-- ------------------------------------------------------ -- -------------------------------------------------------- Server version 8.0.25 -- Server version 8.0.25/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESUL /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESUL/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION/*!50503 SET NAMES utf8mb4 */; /*!50503 SET NAMES utf8mb4 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */; /*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECK /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECK/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FO /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FO/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALU /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALU/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;> SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;> SET @@SESSION.SQL_LOG_BIN= 0;>> --> -- GTID state at the beginning of the backup> -->> SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';-- ---- Table structure for table `a` -- Table structure for table `a`-- --DROP TABLE IF EXISTS `a`; DROP TABLE IF EXISTS `a`;/*!40101 SET @saved_cs_client = @@character_set_client */ /*!40101 SET @saved_cs_client = @@character_set_client *//*!50503 SET character_set_client = utf8mb4 */; /*!50503 SET character_set_client = utf8mb4 */;CREATE TABLE `a` ( CREATE TABLE `a` (`id` int NOT NULL, `id` int NOT NULL,`age` int DEFAULT NULL, `age` int DEFAULT NULL,PRIMARY KEY (`id`) PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_/*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET character_set_client = @saved_cs_client */;-- ---- Dumping data for table `a` -- Dumping data for table `a`-- --LOCK TABLES `a` WRITE; LOCK TABLES `a` WRITE;/*!40000 ALTER TABLE `a` DISABLE KEYS */; /*!40000 ALTER TABLE `a` DISABLE KEYS */;INSERT INTO `a` VALUES (1,2),(2,3); INSERT INTO `a` VALUES (1,2),(2,3);/*!40000 ALTER TABLE `a` ENABLE KEYS */; /*!40000 ALTER TABLE `a` ENABLE KEYS */;UNLOCK TABLES; UNLOCK TABLES;> SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT * /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION * /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2021-06-15 15:00:29 | -- Dump completed on 2021-06-15 15:21:40
可以明显看出，有--set-gtid-purged=off情况下，没有gtid_purged操作。
mysqldump工具有很多参数，线上数据，备份完以后，恢复的时候最后确认一下脚本。防止对库线上库进行一些不必要的操作（风险很大）。
原文作者：DBA札记；如有侵权，请联系管理员删除。
上一篇：
mysql参数的"双1"设置
下一篇：
OCP真题精简版--你值得拥有
请登录后发表评论
登录
全部评论
