準備
# /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
