<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，即可解决：
查看2道真题和解析
拼多多集团-PDD公司氛围 523人发布
