mysqldumpのかんさつ

準備

# /etc/my.cnf
[mysqld]
.
.
.
general_log=ON
general_log_file=/tmp/mysql_general.log
.
.
.
mysql> create database test;
mysql> use test;

mysql> CREATE TABLE `test_myisam` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

mysql> insert into test_myisam values (1,'test');

mysql> CREATE TABLE `test_innodb` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> insert into test_innodb values (2,'testtest');

以下、ダンプ祭り。

mysqldump test

-- MySQL dump 10.13  Distrib 5.6.36, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version   5.6.36-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 `test_innodb`
--

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

--
-- Dumping data for table `test_innodb`
--

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

--
-- Table structure for table `test_myisam`
--

DROP TABLE IF EXISTS `test_myisam`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_myisam` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test_myisam`
--

LOCK TABLES `test_myisam` WRITE;
/*!40000 ALTER TABLE `test_myisam` DISABLE KEYS */;
INSERT INTO `test_myisam` VALUES (1,'test');
/*!40000 ALTER TABLE `test_myisam` 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 */;

-- Dump completed on 2017-05-22  8:12:57

データ取得処理中にテーブルロックかけてる。

# 一般クエリログ
170522  8:12:57     2 Connect   root@localhost on
                    2 Query     /*!40100 SET @@SQL_MODE='' */
                    2 Query     /*!40103 SET TIME_ZONE='+00:00' */
                    2 Query     SHOW VARIABLES LIKE 'gtid\_mode'
                    2 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 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 ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
                    2 Query     SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
                    2 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'
                    2 Init DB   test
                    2 Query     show tables
                    2 Query     LOCK TABLES `test_innodb` READ /*!32311 LOCAL */,`test_myisam` READ /*!32311 LOCAL */
                    2 Query     show table status like 'test\_innodb'
                    2 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    2 Query     SET SESSION character_set_results = 'binary'
                    2 Query     show create table `test_innodb`
                    2 Query     SET SESSION character_set_results = 'utf8'
                    2 Query     show fields from `test_innodb`
                    2 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
                    2 Query     SET SESSION character_set_results = 'binary'
                    2 Query     use `test`
                    2 Query     select @@collation_database
                    2 Query     SHOW TRIGGERS LIKE 'test\_innodb'
                    2 Query     SET SESSION character_set_results = 'utf8'
                    2 Query     show table status like 'test\_myisam'
                    2 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    2 Query     SET SESSION character_set_results = 'binary'
                    2 Query     show create table `test_myisam`
                    2 Query     SET SESSION character_set_results = 'utf8'
                    2 Query     show fields from `test_myisam`
                    2 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
                    2 Query     SET SESSION character_set_results = 'binary'
                    2 Query     use `test`
                    2 Query     select @@collation_database
                    2 Query     SHOW TRIGGERS LIKE 'test\_myisam'
                    2 Query     SET SESSION character_set_results = 'utf8'
                    2 Query     UNLOCK TABLES
                    2 Quit

mysqldump –single-transaction test

ダンプSQLは素のものと変わらない。

トランザクション開始直後にアンロックしている。

# 一般クエリログ
170522  8:20:38     2 Connect   root@localhost on
            2 Query /*!40100 SET @@SQL_MODE='' */
            2 Query /*!40103 SET TIME_ZONE='+00:00' */
            2 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
            2 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
            2 Query SHOW VARIABLES LIKE 'gtid\_mode'
            2 Query UNLOCK TABLES
            2 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 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 ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
            2 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
            2 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
            2 Init DB   test
            2 Query SAVEPOINT sp
            2 Query show tables
            2 Query show table status like 'test\_innodb'
            2 Query SET SQL_QUOTE_SHOW_CREATE=1
            2 Query SET SESSION character_set_results = 'binary'
            2 Query show create table `test_innodb`
            2 Query SET SESSION character_set_results = 'utf8'
            2 Query show fields from `test_innodb`
            2 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
            2 Query SET SESSION character_set_results = 'binary'
            2 Query use `test`
            2 Query select @@collation_database
            2 Query SHOW TRIGGERS LIKE 'test\_innodb'
            2 Query SET SESSION character_set_results = 'utf8'
            2 Query ROLLBACK TO SAVEPOINT sp
            2 Query show table status like 'test\_myisam'
            2 Query SET SQL_QUOTE_SHOW_CREATE=1
            2 Query SET SESSION character_set_results = 'binary'
            2 Query show create table `test_myisam`
            2 Query SET SESSION character_set_results = 'utf8'
            2 Query show fields from `test_myisam`
            2 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
            2 Query SET SESSION character_set_results = 'binary'
            2 Query use `test`
            2 Query select @@collation_database
            2 Query SHOW TRIGGERS LIKE 'test\_myisam'
            2 Query SET SESSION character_set_results = 'utf8'
            2 Query ROLLBACK TO SAVEPOINT sp
            2 Query RELEASE SAVEPOINT sp
            2 Quit

mysqldump –lock-all-tables test

ダンプSQLは素のものと変わらない。

全体でグローバルリードロックがかかる。

# 一般クエリログ
170522  8:26:09     2 Connect   root@localhost on
            2 Query /*!40100 SET @@SQL_MODE='' */
            2 Query /*!40103 SET TIME_ZONE='+00:00' */
            2 Query FLUSH TABLES
            2 Query FLUSH TABLES WITH READ LOCK
            2 Query SHOW VARIABLES LIKE 'gtid\_mode'
            2 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 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 ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
            2 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
            2 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
            2 Init DB   test
            2 Query show tables
            2 Query show table status like 'test\_innodb'
            2 Query SET SQL_QUOTE_SHOW_CREATE=1
            2 Query SET SESSION character_set_results = 'binary'
            2 Query show create table `test_innodb`
            2 Query SET SESSION character_set_results = 'utf8'
            2 Query show fields from `test_innodb`
            2 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
            2 Query SET SESSION character_set_results = 'binary'
            2 Query use `test`
            2 Query select @@collation_database
            2 Query SHOW TRIGGERS LIKE 'test\_innodb'
            2 Query SET SESSION character_set_results = 'utf8'
            2 Query show table status like 'test\_myisam'
            2 Query SET SQL_QUOTE_SHOW_CREATE=1
            2 Query SET SESSION character_set_results = 'binary'
            2 Query show create table `test_myisam`
            2 Query SET SESSION character_set_results = 'utf8'
            2 Query show fields from `test_myisam`
            2 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
            2 Query SET SESSION character_set_results = 'binary'
            2 Query use `test`
            2 Query select @@collation_database
            2 Query SHOW TRIGGERS LIKE 'test\_myisam'
            2 Query SET SESSION character_set_results = 'utf8'
            2 Quit

TODO

いつかやる

mysqldump --dump-slave
mysqldump --master-data