程序员的资源宝库

网站首页 > gitee 正文

SQL_LOG_BIN

sanyeah 2024-04-13 16:35:31 gitee 4 ℃ 0 评论

 

问题：

       在一次线上将一个库的表结构用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，即可解决：






 






    

        

                        
Tags：

                    

                

                        

                
            

            
                    

            





    






    
猜你喜欢

        










        
本文暂时没有评论，来添加一个吧(●'◡'●)

    

    
    
        
        






    
欢迎 你 发表评论:

    

        
        
                
 

                        
                

            
            
        

    




                    
    

    
        

                

 
    
 
        
 
             
        
 
        
 
            

                
            

            

                
            

            

                
            

        
 
    



                




最近发表












标签列表