<span>SQL_LOG_BIN</span>

 

问题:

       在一次线上将一个库的表结构用mysqldump后再导入集群主库,结果主库有数据,而从库始终没有数据,一度以为是中间件有问题,经排查后,发现是SQL_LOG_BIN的问题:

 

原因:

  发现文件中会记录SET @@SESSION.SQL_LOG_BIN= 0;,即之后执行的sql都不会计入binlog,若还原时是主从结构,还需要在从库还原一次,否则主从数据库将不同步。

 

环境:

数据库:Percona MySQL 5.7.25.28版本

操作系统:CentOS 7.3  kernel3.10

集群特点:已开启binlog和gtid

操作命令:mysqldump   -utmp   -ptmp   -h10.10.10.10   -P3307 --single-transaction  --master-data=2   -d --databases database_name > dump.sql

 

dump.sql文件内容如下:

 

-- MySQL dump 10.13  Distrib 5.7.25-28, for Linux (x86_64)
--
-- Host: a6-apple-iphone-db06.wh    Database: database_name
-- ------------------------------------------------------
-- Server version   5.7.25-28-log

/*!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 */;
/*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */;
/*!50717 SET @rocksdb_get_is_supported = IF (@rocksdb_has_p_s_session_variables, 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables 
 WHERE VARIABLE_NAME=\'rocksdb_bulk_load\'', 'SELECT 0') */;
/*!50717 PREPARE s FROM @rocksdb_get_is_supported */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;
/*!50717 SET @rocksdb_enable_bulk_load = IF (@rocksdb_is_supported, 'SET SESSION rocksdb_bulk_load = 1', 'SET @rocksdb_dummy_bulk_load = 0') */;
/*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;
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='247a27ad-e058-11ea-8d08-02cd2fd57f00:1-20392264';

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000059', MASTER_LOG_POS=143795776;

--
-- Current Database: `gmivideo`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `gmivideo` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

-- USE `gmivideo`;
USE `database_name`;

--
-- Table structure for table `Apollo_Config_App`
--
--

DROP TABLE IF EXISTS `App`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `App` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `AppId` varchar(500) NOT NULL DEFAULT 'default' COMMENT 'AppID',
  `Name` varchar(500) NOT NULL DEFAULT 'default' COMMENT '应用名',
  `OrgId` varchar(32) NOT NULL DEFAULT 'default' COMMENT 'Id',
  `OrgName` varchar(64) NOT NULL DEFAULT 'default' COMMENT '名字',
  `OwnerName` varchar(500) NOT NULL DEFAULT 'default' COMMENT 'ownerName',
  `OwnerEmail` varchar(500) NOT NULL DEFAULT 'default' COMMENT 'ownerEmail',
  `IsDeleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1: deleted, 0: normal',
  `DataChange_CreatedBy` varchar(32) NOT NULL DEFAULT 'default' COMMENT '邮箱前缀',
  `DataChange_CreatedTime` timestamp NOT NULL DEFAULT '2018-12-31 16:00:00' COMMENT '创建时间',
  `DataChange_LastModifiedBy` varchar(32) DEFAULT '' COMMENT '修改人邮箱前缀',
  `DataChange_LastTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`Id`),
  KEY `AppId` (`AppId`(191)),
  KEY `DataChange_LastTime` (`DataChange_LastTime`),
  KEY `IX_Name` (`Name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='应用';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Apollo_Config_AppNamespace`
--

DROP TABLE IF EXISTS `AppNamespace`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `AppNamespace` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `Name` varchar(32) NOT NULL DEFAULT '' COMMENT 'namespace名字,注意,需要全局唯一',
  `AppId` varchar(32) NOT NULL DEFAULT '' COMMENT 'app id',
  `Format` varchar(32) NOT NULL DEFAULT 'properties' COMMENT 'namespace的format类型',
  `IsPublic` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'namespace是否为公共',
  `Comment` varchar(64) NOT NULL DEFAULT '' COMMENT '注释',
  `IsDeleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1: deleted, 0: normal',
  `DataChange_CreatedBy` varchar(32) NOT NULL DEFAULT '' COMMENT '创建人邮箱前缀',
  `DataChange_CreatedTime` timestamp NOT NULL DEFAULT '2018-12-31 16:00:00' COMMENT '创建时间',
  `DataChange_LastModifiedBy` varchar(32) DEFAULT '' COMMENT '修改人邮箱前缀',
  `DataChange_LastTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`Id`),
  KEY `IX_AppId` (`AppId`),
  KEY `Name_AppId` (`Name`,`AppId`),
  KEY `DataChange_LastTime` (`DataChange_LastTime`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='应用定义';


----------------------------------------------------------------------
----------------------------------------------------------------------
/*!40101 SET character_set_client = @saved_cs_client */; SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN; /*!50112 SET @disable_bulk_load = IF (@is_rocksdb_supported, 'SET SESSION rocksdb_bulk_load = @old_rocksdb_bulk_load', 'SET @dummy_rocksdb_bulk_load = 0') */; /*!50112 PREPARE s FROM @disable_bulk_load */; /*!50112 EXECUTE s */; /*!50112 DEALLOCATE PREPARE s */; /*!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-10-23 18:08:30

 

 

然后将数据库名称database_name更改为database_apple后就导入了,发现主库有表结构,而从库没有表结构:

 

一、实际上需要修改dump.sql文件4个地方:

SET @@SESSION.SQL_LOG_BIN= 0;

#  1,更改为:SET @@SESSION.SQL_LOG_BIN= 1;
SET @@GLOBAL.GTID_PURGED='247a27ad-e058-11ea-8d08-02cd2fd57f00:1-20392264'; 

# 2,注释掉
SET @@GLOBAL.GTID_PURGED='247a27ad-e058-11ea-8d08-02cd2fd57f00:1-20392264';


CREATE DATABASE /*!32312 IF NOT EXISTS*/ `gmivideo` /*!40100 DEFAULT CHARACTER SET utf8mb4 */; -- USE `gmivideo`;

# 3,注释掉 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `gmivideo` /*!40100 DEFAULT CHARACTER SET utf8mb4 */; -- USE `gmivideo`;


USE `database_name`;

# 4,更改为:use database_apple

 

#####################################################################################################

 

二、当备份文件非常大时,vi或者sed处理文件会非常困难,因此最好从根源上解决。当使用参数文件--set-gtid-purged=off,即可解决:


 

全部评论

相关推荐

10-16 22:56
门头沟学院 C++
1234567800:歌尔今年给211开14-15k吗,我本地人连面试都不给😂
点赞 评论 收藏
分享
12-02 14:27
Java
牛可乐121381:好的,谢谢你,韩明轩同学
点赞 评论 收藏
分享
评论
点赞
收藏
分享
牛客网
牛客企业服务