Mysqldump 详解之 --master-data --single-transaction

--master-data

This causes the binary log position and filename to be appended to the output. 
If equal to 1, will print it as a CHANGE MASTER command; 
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.


这个参数会把binary log的文件名和位置追加到输出中，等于1时不注释，默认是1，等于2时注释；
这个参数会 lock-all-tables ，不能再进行增删改查工作

[root@test ~]# mysqldump  -S /tmp/mysql_3307.sock -pmysql -A >all.sql

查看all.sql，会lock table

--
-- Current Database: `hello`
--

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

USE `hello`;

--
-- Table structure for table `helloa`
--

DROP TABLE IF EXISTS `helloa`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `helloa` (
  `id` int(11) DEFAULT NULL,
  `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `helloa`
--

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

[root@test ~]# mysqldump --master-data=2 -S /tmp/mysql_3307.sock -pmysql -A >all.sql

查看all.sql，会注释掉change

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

-- CHANGE MASTER TO MASTER_LOG_FILE='mybinlog.000005', MASTER_LOG_POS=120;

--
-- Current Database: `hello`
--

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

USE `hello`;

--
-- Table structure for table `helloa`
--

DROP TABLE IF EXISTS `helloa`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `helloa` (
  `id` int(11) DEFAULT NULL,
  `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `helloa`
--

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

--single-transaction

Creates a consistent snapshot by dumping all tables in a single transaction. 
Works ONLY for tables stored in storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines. While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, as consistent snapshot is not isolated from them. Option automatically turns off --lock-tables.

[root@test ~]# mysqldump --single-transaction  -S /tmp/mysql_3307.sock -pmysql -A >all.sql

查看general_log,会做一个snapshot，形成一个savepoint

170912  8:56:00     8 Connect   root@localhost on 
                    8 Query     /*!40100 SET @@SQL_MODE='' */
                    8 Query     /*!40103 SET TIME_ZONE='+00:00' */
                    8 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
                    8 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
                    8 Query     SHOW VARIABLES LIKE 'gtid\_mode'
                    8 Query     UNLOCK TABLES
                    8 Query     SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
                    8 Query     SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
                    8 Query     SHOW DATABASES
                    8 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'
                    8 Init DB   hello
                    8 Query     SHOW CREATE DATABASE IF NOT EXISTS `hello`
                    8 Query     SAVEPOINT sp
                    8 Query     show tables
                    8 Query     show table status like 'helloa'
                    8 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    8 Query     SET SESSION character_set_results = 'binary'
                    8 Query     show create table `helloa`
                    8 Query     SET SESSION character_set_results = 'utf8'
                    8 Query     show fields from `helloa`
                    8 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `helloa`
                    8 Query     SET SESSION character_set_results = 'binary'
                    8 Query     use `hello`
                    8 Query     select @@collation_database
                    8 Query     SHOW TRIGGERS LIKE 'helloa'
                    8 Query     SET SESSION character_set_results = 'utf8'
                    8 Query     ROLLBACK TO SAVEPOINT sp
                    8 Query     RELEASE SAVEPOINT sp
                    8 Init DB   mysql
                    8 Query     SHOW CREATE DATABASE IF NOT EXISTS `mysql`
                    8 Query     SAVEPOINT sp
                    8 Query     show tables
                    8 Query     show table status like 'columns\_priv'
                    8 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    8 Query     SET SESSION character_set_results = 'binary'
                    8 Query     show create table `columns_priv`
                    8 Query     SET SESSION character_set_results = 'utf8'
                    8 Query     show fields from `columns_priv`
                    8 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `columns_priv`
                    8 Query     SET SESSION character_set_results = 'binary'

结论：--single-transaction 搭配--master-data=2使用，可以知道备份时候的position；