暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

mysqldump测试--mysql基础系列

DBA札记 2021-06-16
1193

      本系列文章主要是对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.sql
      复制
        root@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札记,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

          评论