When exporting, backing up and restoring Mysql database from the master database to the slave database, you need to pay attention to whether to enable GTID mode for the database. If you enable it, you should add the parameter -- set GTID purged = off to mysqldump data.
See reprint for details:
Here are the first 30 lines of a.sql exported with mysqldump:
[root@aaa0-D14-042 mysqlbackup]# head -30 t1.sql -- MySQL dump 10.13 Distrib 5.6.24-72.2, for Linux (x86_64) -- -- Host: localhost Database: yyf -- ------------------------------------------------------ -- Server version 5.6.24-72.2-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 */; <!--Focus on the following two lines--> 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='18f9a804-343b-11e5-a21d-b083fed01601:1-2'; -- -- Table structure for table `t1` -- DROP TABLE IF EXISTS `t1`;
Generally, for the database of master-slave architecture, the relevant import operations only need to be carried out on the master side. However, as shown in the highlighted part of the exported a.sql code, the session level SQL log bin is forbidden. Therefore, when the target side imports the t1 table, the corresponding bin log log will not be generated, so the table and its data will not be synchronized to the slave side. This is something we need to pay attention to when we test the database.
After the test, it is found that the above-mentioned can only appear when the database is in GTID mode. Therefore, if the database is in GTID mode, when mysqldump data is in use, you should add the parameter -- set GTID purged = off as shown in the warning prompt. At this time, the exported sql file (code of the location not marked with emphasis):
-- MySQL dump 10.13 Distrib 5.6.26, for Linux (x86_64) -- -- Host: localhost Database: yyf -- ------------------------------------------------------ -- Server version 5.6.26-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 */; -- -- Table structure for table `t1`; ` -- DROP TABLE IF EXISTS `t1`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */;