Mysqldump 的 --dump-slave 参数理解

转载

小叔叔书 2021-08-09 16:36:57 博主文章分类：Mysql

文章标签 mysql sql 7z 服务器二进制日志 文章分类 MySQL 数据库

在mysqldump工具中，有个--dump-slave参数，用于在从服务器导出dmp文件的同时记录主服务器的二进制文件及位置，便于执行时间点恢复操作

但这里有个地方需要注意，就是在导出的过程中，会中断从服务器的sql_thread进程，导出结束后再启动sql_thread进程。需要注意的是在mysqldump导出异常时就不会再自动的启动sql_thread进程了。

具体测试如下(mysql5.7.23版本)：

mysqldump --help 查看帮助手册如下
--dump-slave[=#] This causes the binary log position and filename of the
master to be appended to the dumped data output. Setting
the value to 1, will printit as a CHANGE MASTER command
in the dumped data output; if equal to 2, that command
will be prefixed with a comment symbol. This option will
turn --lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump - don't
forget to read about --single-transaction below). In all
cases any action on logs will happen at the exact moment
of the dump.Option automatically turns --lock-tables off.

--官方文档的解释：https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_dump-slave
The option value is handled the same way as for --master-data (setting no value or 1 causes a CHANGE MASTER TO statement to be written to the dump, setting 2 causes the statement to be written but encased in SQL comments) and has the same effect as --master-data in terms of enabling or disabling other options and in how locking is handled.
This option causes mysqldump to stop the slave SQL thread before the dump and restart it again after.

--主库及从库：建立测试库及测试表
mysql> create database dumpdb;
Query OK, 1 row affected (0.20 sec)

mysql> use dumpdb;
Database changed

mysql> create table t1(a int);
Query OK, 0 rows affected (0.18 sec)

mysql> create table t2(a int);
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t1 values(1),(2);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.07 sec)

--从库开启general_log，用于观察每一步流程
mysql> show variables like '%gener%';
+------------------+--------------------------------+
| Variable_name | Value |
+------------------+--------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/hostmysql-s.log |
+------------------+--------------------------------+
2 rows in set (0.01 sec)

mysql> set global general_log=1;
Query OK, 0 rows affected (0.03 sec)

mysql> show variables like '%gener%';
+------------------+--------------------------------+
| Variable_name | Value |
+------------------+--------------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/hostmysql-s.log |
+------------------+--------------------------------+
2 rows in set (0.05 sec)

--从库开启--dump-slave=2 的备份，在从库备份取得主库的二进制日志
mysqldump -uroot -pRootXXXX --single-transaction --dump-slave=2 --databases dumpdb > flydb_mysqldump_dumpslave2.sql

--从库查看general_log文件中的备份操作流程如下：
--首先停止从库的sql_thread线程，等dump完成后，开启从库的sql_thread线程
[root@hostmysql-s flydb]# more /var/lib/mysql/hostmysql-s.log
2018-12-05T09:09:53.718663Z 14 Connect root@localhost on using Socket
2018-12-05T09:09:53.767640Z 14 Query /*!40100 SET @@SQL_MODE='' */
2018-12-05T09:09:53.780481Z 14 Query /*!40103 SET TIME_ZONE='+00:00' */
2018-12-05T09:09:53.780633Z 14 Query SHOW SLAVE STATUS
2018-12-05T09:09:53.780893Z 14 Query STOP SLAVE SQL_THREAD
2018-12-05T09:09:53.781704Z 14 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2018-12-05T09:09:53.781793Z 14 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2018-12-05T09:09:53.781877Z 14 Query SHOW VARIABLES LIKE 'gtid\_mode'
2018-12-05T09:09:53.785516Z 14 Query SHOW SLAVE STATUS
2018-12-05T09:09:53.785712Z 14 Query UNLOCK TABLES
2018-12-05T09:09:53.785866Z 14 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FI
LE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA
.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('dumpdb'))) GROUP
BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2018-12-05T09:09:53.788579Z 14 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHE
MA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('dumpdb')) ORDE
R BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2018-12-05T09:09:53.790368Z 14 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2018-12-05T09:09:53.793864Z 14 Init DB dumpdb
2018-12-05T09:09:53.793944Z 14 Query SHOW CREATE DATABASE IF NOT EXISTS `dumpdb`
2018-12-05T09:09:53.794009Z 14 Query SAVEPOINT sp
2018-12-05T09:09:53.794068Z 14 Query show tables
2018-12-05T09:09:53.794267Z 14 Query show table status like 't1'
2018-12-05T09:09:53.794515Z 14 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-12-05T09:09:53.794577Z 14 Query SET SESSION character_set_results = 'binary'
2018-12-05T09:09:53.794633Z 14 Query show create table `t1`
2018-12-05T09:09:53.794708Z 14 Query SET SESSION character_set_results = 'utf8'
2018-12-05T09:09:53.794769Z 14 Query show fields from `t1`
2018-12-05T09:09:53.795081Z 14 Query show fields from `t1`
2018-12-05T09:09:53.795404Z 14 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2018-12-05T09:09:53.795565Z 14 Query SET SESSION character_set_results = 'binary'
2018-12-05T09:09:53.795620Z 14 Query use `dumpdb`
2018-12-05T09:09:53.795677Z 14 Query select @@collation_database
2018-12-05T09:09:53.795743Z 14 Query SHOW TRIGGERS LIKE 't1'
2018-12-05T09:09:53.796035Z 14 Query SET SESSION character_set_results = 'utf8'
2018-12-05T09:09:53.796092Z 14 Query ROLLBACK TO SAVEPOINT sp
2018-12-05T09:09:53.796150Z 14 Query show table status like 't2'
2018-12-05T09:09:53.796394Z 14 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-12-05T09:09:53.796466Z 14 Query SET SESSION character_set_results = 'binary'
2018-12-05T09:09:53.796520Z 14 Query show create table `t2`
2018-12-05T09:09:53.796589Z 14 Query SET SESSION character_set_results = 'utf8'
2018-12-05T09:09:53.796649Z 14 Query show fields from `t2`
2018-12-05T09:09:53.814883Z 14 Query show fields from `t2`
2018-12-05T09:09:53.815393Z 14 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
2018-12-05T09:09:53.815532Z 14 Query SET SESSION character_set_results = 'binary'
2018-12-05T09:09:53.815590Z 14 Query use `dumpdb`
2018-12-05T09:09:53.815673Z 14 Query select @@collation_database
2018-12-05T09:09:53.815744Z 14 Query SHOW TRIGGERS LIKE 't2'
2018-12-05T09:09:53.816054Z 14 Query SET SESSION character_set_results = 'utf8'
2018-12-05T09:09:53.816110Z 14 Query ROLLBACK TO SAVEPOINT sp
2018-12-05T09:09:53.816155Z 14 Query RELEASE SAVEPOINT sp
2018-12-05T09:09:53.816193Z 14 Query SHOW SLAVE STATUS
2018-12-05T09:09:53.816378Z 14 Query START SLAVE
2018-12-05T09:09:53.853249Z 14 Quit

--具体的dmp文件，会记录主服务器的二进制日志及位置信息
[root@hostmysql-s mysqlbkp]# more flydb_mysqldump_dumpslave2.sql
-- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost Database: dumpdb
-- ------------------------------------------------------
-- Server version 5.7.23-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 */;

--
-- Position to start replication or point-in-time recovery from (the master of this slave)
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000102', MASTER_LOG_POS=1910;

--
-- Current Database: `dumpdb`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dumpdb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;

USE `dumpdb`;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1),(2);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t2`
--

DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t2`
--

LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
UNLOCK TABLES;
/*!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 */;

--源码mysqldump.c文件
--在mysqldump开始前关掉了slave线程
/* Add 'STOP SLAVE to beginning of dump */
if (opt_slave_apply && add_stop_slave())
goto err;

static int add_stop_slave(void)
{
if (opt_comments)
fprintf(md_result_file,
"\n--\n-- stop slave statement to make a recovery dump)\n--\n\n");
fprintf(md_result_file, "STOP SLAVE;\n");
return(0);
}

......mysqldump......

--在mysqldump结束后，启动slave线程
/* if --dump-slave , start the slave sql thread */
if (opt_slave_data && do_start_slave_sql(mysql))
goto err;

static int do_start_slave_sql(MYSQL *mysql_con)
{
MYSQL_RES *slave;
/* We need to check if the slave sql is stopped in the first place */
if (mysql_query_with_error_report(mysql_con, &slave, "SHOW SLAVE STATUS"))
return(1);
else
{
MYSQL_ROW row= mysql_fetch_row(slave);
if (row && row[11])
{
/* if SLAVE SQL is not running, we don't start it */
if (!strcmp(row[11],"Yes"))
{
mysql_free_result(slave);
/* Silently assume that they don't have the slave running */
return(0);
}
}
}
mysql_free_result(slave);

/* now, start slave if stopped */
if (mysql_query_with_error_report(mysql_con, 0, "START SLAVE"))
{
my_printf_error(0, "Error: Unable to start slave", MYF(0));
return 1;
}
return(0);
}