mysqldump测试--mysql基础系列
mysqldump测试--mysql基础系列
DBA札记
发布于 2021-07-06 10:58:50
发布于 2021-07-06 10:58:50
本系列文章主要是对mysql的一些基础功能做些测试记录，方便大家参考，也方便自己后续翻看。
本次测试较为简单，主要对比master-data、--set-gtid-purged这两个参数。
test库上创建一张简单的表a，包含id和age两个字段，都是int类型,导出语句分别如下：
mysqldump --single-transaction --master-data test > test1.sql
mysqldump -uroot -p --single-transaction --master-data test > test1.sql
mysqldump -uroot -p --single-transaction --set-gtid-purged=OFF test > test3.sql
mysqldump -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工具有很多参数，线上数据，备份完以后，恢复的时候最后确认一下脚本。防止对库线上库进行一些不必要的操作（风险很大）。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2021-06-15，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。