mysqldump備份原理解析
這裡透過開啟general log追蹤mysqldump的備份過程，可以動態調整general log的引數設定。
一、不加--single-transaction備份
mysqldump --default-character-set=utf8mb4 -uroot --triggers -R -E -A --master-data=2 |gzip >dbbackup.sql.gz
1、連線資料庫
2、調整sql_mode、time_zone
3、重新整理tables(關閉所有開啟的表並清空快取資料)
4、FLUSH TABLES WITH READ LOCK(執行全域性讀鎖)
5、記錄相關變數、引數、binlog位置等
6、備份資料檔案、表空間等
7、備份業務表結構、表資料、觸發器
8、備份系統表結構、表資料、觸發器
9、備份函式、儲存過程
10、備份結束退出，全域性鎖自動釋放
2017-06-24T00:48:48.622170Z 56 Connect root@localhost on using Socket
2017-06-24T00:48:48.622372Z 56 Query /*!40100 SET @@SQL_MODE='' */
2017-06-24T00:48:48.622500Z 56 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-06-24T00:48:48.622654Z 56 Query FLUSH /*!40101 LOCAL */ TABLES
2017-06-24T00:48:48.623539Z 56 Query FLUSH TABLES WITH READ LOCK
2017-06-24T00:48:48.623669Z 56 Query SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema =
'performance_schema' AND table_name = 'session_variables'
2017-06-24T00:48:48.624191Z 56 Query SELECT COUNT(*) FROM performance_schema.session_variables WHERE VARI
ABLE_NAME LIKE 'rocksdb\_skip\_fill\_cache'
2017-06-24T00:48:48.625417Z 56 Query SHOW VARIABLES LIKE 'gtid\_mode'
2017-06-24T00:48:48.627542Z 56 Query SHOW MASTER STATUS
2017-06-24T00:48:48.627743Z 56 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, E
NGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_
NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_
NAME
2017-06-24T00:48:48.628321Z 56 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTE
NT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, L
OGFILE_GROUP_NAME
2017-06-24T00:48:48.628778Z 56 Query SHOW DATABASES
2017-06-24T00:48:48.629166Z 56 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2017-06-24T00:48:48.631175Z 56 Init DB dbtest
2017-06-24T00:48:48.631282Z 56 Query SHOW CREATE DATABASE IF NOT EXISTS `dbtest`
2017-06-24T00:48:48.631415Z 56 Query show tables
2017-06-24T00:48:48.631704Z 56 Query show table status like 't'
2017-06-24T00:48:48.632269Z 56 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:48:48.632372Z 56 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:48:48.632462Z 56 Query show create table `t`
2017-06-24T00:48:48.632642Z 56 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:48:48.632810Z 56 Query show fields from `t`
2017-06-24T00:48:48.633252Z 56 Query show fields from `t`
2017-06-24T00:48:48.633664Z 56 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t`
2017-06-24T00:48:48.633894Z 56 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:48:48.634000Z 56 Query use `dbtest`
2017-06-24T00:48:48.634136Z 56 Query select @@collation_database
2017-06-24T00:48:48.634281Z 56 Query SHOW TRIGGERS LIKE 't'
2017-06-24T00:48:48.634750Z 56 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:48:48.634887Z 56 Query show table status like 't1'
2017-06-24T00:48:48.635388Z 56 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:48:48.635483Z 56 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:48:48.635575Z 56 Query show create table `t1`
2017-06-24T00:48:48.635719Z 56 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:48:48.635852Z 56 Query show fields from `t1`
2017-06-24T00:48:48.636268Z 56 Query show fields from `t1`
2017-06-24T00:48:48.636699Z 56 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2017-06-24T00:48:48.636911Z 56 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:48:48.637030Z 56 Query use `dbtest`
2017-06-24T00:48:48.637132Z 56 Query select @@collation_database
2017-06-24T00:48:48.637264Z 56 Query SHOW TRIGGERS LIKE 't1'
2017-06-24T00:48:48.637719Z 56 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:48:48.637856Z 56 Query show table status like 't2'
2017-06-24T00:48:48.638347Z 56 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:48:48.638441Z 56 Query SET SESSION character_set_results = 'binary'
二、加--single-transaction備份
mysqldump --default-character-set=utf8mb4 -uroot --triggers -R -E -A --master-data=2 --single-transaction |gzip >dbbackup.sql.gz
1、連線資料庫
2、調整sql_mode,time_zone
3、調整事務隔離界別為RR（SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ）
4、開啟事務一致性快照（START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */）
5、記錄gtid_mode、binlog位置等資訊
6、釋放全域性鎖
7、備份資料檔案、表空間資料
8、備份業務資料庫表結構
9、設定savepoint sp(SAVEPOINT sp)
10、備份資料表結構、表資料、觸發器、事件(每備份完一個表，做一次rollback to savepoint sp，釋放DDL鎖)
11、備份系統資料庫、表結構、觸發器
12、備份儲存過程、函式
13、釋放savepoint
14、備份事件
15、備份結束會話退出
2017-06-24T00:58:05.650060Z 58 Connect root@localhost on using Socket
2017-06-24T00:58:05.650336Z 58 Query /*!40100 SET @@SQL_MODE='' */
2017-06-24T00:58:05.650491Z 58 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-06-24T00:58:05.650703Z 58 Query SHOW STATUS LIKE 'binlog_snapshot_%'
2017-06-24T00:58:05.660129Z 58 Query SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema =
'performance_schema' AND table_name = 'session_variables'
2017-06-24T00:58:05.660477Z 58 Query SELECT COUNT(*) FROM performance_schema.session_variables WHERE VARI
ABLE_NAME LIKE 'rocksdb\_skip\_fill\_cache'
2017-06-24T00:58:05.662214Z 58 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-06-24T00:58:05.662314Z 58 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-06-24T00:58:05.663369Z 58 Query SHOW VARIABLES LIKE 'gtid\_mode'
2017-06-24T00:58:05.667436Z 58 Query SHOW STATUS LIKE 'binlog_snapshot_%'
2017-06-24T00:58:05.670160Z 58 Query UNLOCK TABLES
2017-06-24T00:58:05.670343Z 58 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, E
NGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_
NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_
NAME
2017-06-24T00:58:05.671210Z 58 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTE
NT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, L
OGFILE_GROUP_NAME
2017-06-24T00:58:05.671971Z 58 Query SHOW DATABASES
2017-06-24T00:58:05.672341Z 58 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2017-06-24T00:58:05.675964Z 58 Init DB dbtest
2017-06-24T00:58:05.676080Z 58 Query SHOW CREATE DATABASE IF NOT EXISTS `dbtest`
2017-06-24T00:58:05.676223Z 58 Query SAVEPOINT sp
2017-06-24T00:58:05.676343Z 58 Query show tables
2017-06-24T00:58:05.676641Z 58 Query show table status like 't'
2017-06-24T00:58:05.677297Z 58 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:58:05.677404Z 58 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:58:05.677525Z 58 Query show create table `t`
2017-06-24T00:58:05.677659Z 58 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:58:05.677821Z 58 Query show fields from `t`
2017-06-24T00:58:05.678319Z 58 Query show fields from `t`
2017-06-24T00:58:05.678746Z 58 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t`
2017-06-24T00:58:05.678967Z 58 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:58:05.679059Z 58 Query use `dbtest`
2017-06-24T00:58:05.679211Z 58 Query select @@collation_database
2017-06-24T00:58:05.679357Z 58 Query SHOW TRIGGERS LIKE 't'
2017-06-24T00:58:05.679851Z 58 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:58:05.679958Z 58 Query ROLLBACK TO SAVEPOINT sp
2017-06-24T00:58:05.680059Z 58 Query show table status like 't1'
2017-06-24T00:58:05.680589Z 58 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:58:05.680702Z 58 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:58:05.680807Z 58 Query show create table `t1`
2017-06-24T00:58:05.680945Z 58 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:58:05.681071Z 58 Query show fields from `t1`
2017-06-24T00:58:05.681579Z 58 Query show fields from `t1`
2017-06-24T00:58:05.682021Z 58 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2017-06-24T00:58:05.682244Z 58 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:58:05.682369Z 58 Query use `dbtest`
2017-06-24T00:58:05.682477Z 58 Query select @@collation_database
2017-06-24T00:58:05.682609Z 58 Query SHOW TRIGGERS LIKE 't1'
2017-06-24T00:58:05.683147Z 58 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:58:05.683268Z 58 Query ROLLBACK TO SAVEPOINT sp
2017-06-24T00:58:05.683366Z 58 Query show table status like 't2'
2017-06-24T00:58:05.683899Z 58 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:58:05.684005Z 58 Query SET SESSION character_set_results = 'binary'
三、資料庫恢復
gunzip <dbbackup.sql.gz |mysql -uroot --default-character-set=utf8mb4 -o dbtest
2017-06-24T01:06:22.926385Z 64 Connect root@localhost on dbtest using Socket
2017-06-24T01:06:22.926645Z 64 Query select @@version_comment limit 1
2017-06-24T01:06:22.927001Z 64 Query /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
2017-06-24T01:06:22.927161Z 64 Query /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
2017-06-24T01:06:22.927286Z 64 Query /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */
2017-06-24T01:06:22.927426Z 64 Query /*!40101 SET NAMES utf8mb4 */
2017-06-24T01:06:22.927580Z 64 Query /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */
2017-06-24T01:06:22.927735Z 64 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-06-24T01:06:22.927856Z 64 Query /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */
2017-06-24T01:06:22.927986Z 64 Query /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_K
EY_CHECKS=0 */
2017-06-24T01:06:22.928099Z 64 Query /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZE
RO' */
2017-06-24T01:06:22.928234Z 64 Query /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
2017-06-24T01:06:22.928337Z 64 Query /*!50717 SET @rocksdb_bulk_load_var_name='rocksdb_bulk_load' */
2017-06-24T01:06:22.928527Z 64 Query /*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FRO
M INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */
2017-06-24T01:06:22.928995Z 64 Query /*!50717 SET @rocksdb_get_is_supported = IF (@rocksdb_has_p_s_sessio
n_variables, 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NA
ME=?', 'SELECT 0') */
2017-06-24T01:06:22.929105Z 64 Query /*!50717 PREPARE s FROM @rocksdb_get_is_supported */
2017-06-24T01:06:22.929259Z 64 Prepare SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.s
ession_variables WHERE VARIABLE_NAME=?
2017-06-24T01:06:22.929346Z 64 Query /*!50717 EXECUTE s USING @rocksdb_bulk_load_var_name */
2017-06-24T01:06:22.929370Z 64 Execute SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.s
ession_variables WHERE VARIABLE_NAME='rocksdb_bulk_load'
2017-06-24T01:06:22.931165Z 64 Query /*!50717 DEALLOCATE PREPARE s */
2017-06-24T01:06:22.931306Z 64 Query /*!50717 SET @rocksdb_enable_bulk_load = IF (@rocksdb_is_supported,
'SET SESSION rocksdb_bulk_load = 1', 'SET @rocksdb_dummy_bulk_load = 0') */
2017-06-24T01:06:22.931426Z 64 Query /*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */
2017-06-24T01:06:22.931463Z 64 Prepare SET @rocksdb_dummy_bulk_load = 0
2017-06-24T01:06:22.931558Z 64 Query /*!50717 EXECUTE s */
2017-06-24T01:06:22.931573Z 64 Execute SET @rocksdb_dummy_bulk_load = 0
2017-06-24T01:06:22.931666Z 64 Query /*!50717 DEALLOCATE PREPARE s */
2017-06-24T01:06:22.931805Z 64 Query CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dbtest` /*!40100 DEFAULT C
HARACTER SET utf8 */
2017-06-24T01:06:22.932975Z 64 Init DB dbtest
2017-06-24T01:06:22.933111Z 64 Query DROP TABLE IF EXISTS `t`
2017-06-24T01:06:22.933728Z 64 Query /*!40101 SET @saved_cs_client = @@character_set_client */
2017-06-24T01:06:22.933859Z 64 Query /*!40101 SET character_set_client = utf8 */
2017-06-24T01:06:22.934023Z 64 Query CREATE TABLE `t` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-06-24T01:06:22.939020Z 64 Query /*!40101 SET character_set_client = @saved_cs_client */
2017-06-24T01:06:22.939213Z 64 Query LOCK TABLES `t` WRITE
2017-06-24T01:06:22.939616Z 64 Query /*!40000 ALTER TABLE `t` DISABLE KEYS */
2017-06-24T01:06:22.940164Z 64 Query INSERT INTO `t` VALUES (1)
2017-06-24T01:06:22.941111Z 64 Query /*!40000 ALTER TABLE `t` ENABLE KEYS */
2017-06-24T01:06:22.941871Z 64 Query UNLOCK TABLES
2017-06-24T01:06:22.942023Z 64 Query DROP TABLE IF EXISTS `t1`
2017-06-24T01:06:22.942558Z 64 Query /*!40101 SET @saved_cs_client = @@character_set_client */
2017-06-24T01:06:22.942702Z 64 Query /*!40101 SET character_set_client = utf8 */
2017-06-24T01:06:22.942879Z 64 Query CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-06-24T01:06:22.946848Z 64 Query /*!40101 SET character_set_client = @saved_cs_client */
2017-06-24T01:06:22.946986Z 64 Query LOCK TABLES `t1` WRITE
2017-06-24T01:06:22.947344Z 64 Query /*!40000 ALTER TABLE `t1` DISABLE KEYS */
2017-06-24T01:06:22.947867Z 64 Query INSERT INTO `t1` VALUES (1,'name')
2017-06-24T01:06:22.948877Z 64 Query /*!40000 ALTER TABLE `t1` ENABLE KEYS */
2017-06-24T01:06:22.949727Z 64 Query UNLOCK TABLES
2017-06-24T01:06:22.949847Z 64 Query DROP TABLE IF EXISTS `t2`
2017-06-24T01:06:22.950384Z 64 Query /*!40101 SET @saved_cs_client = @@character_set_client */
2017-06-24T01:06:22.950488Z 64 Query /*!40101 SET character_set_client = utf8 */
2017-06-24T01:06:22.950632Z 64 Query CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-06-24T01:06:22.954865Z 64 Query /*!40101 SET character_set_client = @saved_cs_client */
2017-06-24T01:06:22.955009Z 64 Query LOCK TABLES `t2` WRITE
2017-06-24T01:06:22.955370Z 64 Query /*!40000 ALTER TABLE `t2` DISABLE KEYS */
2017-06-24T01:06:22.955873Z 64 Query INSERT INTO `t2` VALUES (1,'name'),(1,'name')
2017-06-24T01:06:22.957048Z 64 Query /*!40000 ALTER TABLE `t2` ENABLE KEYS */
2017-06-24T01:06:22.957872Z 64 Query UNLOCK TABLES
2017-06-24T01:06:22.958010Z 64 Query DROP TABLE IF EXISTS `t3`
2017-06-24T01:06:22.958512Z 64 Query /*!40101 SET @saved_cs_client = @@character_set_client */
2017-06-24T01:06:22.958618Z 64 Query /*!40101 SET character_set_client = utf8 */
2017-06-24T01:06:22.958778Z 64 Query CREATE TABLE `t3` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-06-24T01:06:22.972610Z 64 Query /*!40101 SET character_set_client = @saved_cs_client */
2017-06-24T01:06:22.972767Z 64 Query LOCK TABLES `t3` WRITE
2017-06-24T01:06:22.973184Z 64 Query /*!40000 ALTER TABLE `t3` DISABLE KEYS */
2017-06-24T01:06:23.001988Z 64 Query INSERT INTO `t3` VALUES (1,'name1'),(2,'name2'),(3,'name3')
四、mysqldump備份恢復總結
1、mysqldump是透過select * from table_name來獲取表的資料進而完成備份。
2、START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */必須放到FLUSH TABLES WITH READ LOCK和UNLOCK TABLES之間，放到之前會造成START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */和FLUSH TABLES WITH READ LOCK之間執行的DML語句丟失，放到之後，會造成從庫重複插入資料。
3、mysqldump只適合放到業務低峰期做，如果備份的過程中資料操作很頻繁，會造成Undo表空間越來越大，undo表空間預設是放到共享表空間中的，而ibdata的特性是一旦增大，就不會收縮。（可重複讀的資料會在undo中儲存，dml也會產生相關的redo/undo）
4、--single-transaction開啟事務一致性快照，開始savepoint，每次備份完執行rollback to savepoint為了防止表的DDL阻塞，而調整事務隔離界別RR為了保障會話的可重複讀，從而達到備份資料的一致性。
一、不加--single-transaction備份
mysqldump --default-character-set=utf8mb4 -uroot --triggers -R -E -A --master-data=2 |gzip >dbbackup.sql.gz
1、連線資料庫
2、調整sql_mode、time_zone
3、重新整理tables(關閉所有開啟的表並清空快取資料)
4、FLUSH TABLES WITH READ LOCK(執行全域性讀鎖)
5、記錄相關變數、引數、binlog位置等
6、備份資料檔案、表空間等
7、備份業務表結構、表資料、觸發器
8、備份系統表結構、表資料、觸發器
9、備份函式、儲存過程
10、備份結束退出，全域性鎖自動釋放
2017-06-24T00:48:48.622170Z 56 Connect root@localhost on using Socket
2017-06-24T00:48:48.622372Z 56 Query /*!40100 SET @@SQL_MODE='' */
2017-06-24T00:48:48.622500Z 56 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-06-24T00:48:48.622654Z 56 Query FLUSH /*!40101 LOCAL */ TABLES
2017-06-24T00:48:48.623539Z 56 Query FLUSH TABLES WITH READ LOCK
2017-06-24T00:48:48.623669Z 56 Query SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema =
'performance_schema' AND table_name = 'session_variables'
2017-06-24T00:48:48.624191Z 56 Query SELECT COUNT(*) FROM performance_schema.session_variables WHERE VARI
ABLE_NAME LIKE 'rocksdb\_skip\_fill\_cache'
2017-06-24T00:48:48.625417Z 56 Query SHOW VARIABLES LIKE 'gtid\_mode'
2017-06-24T00:48:48.627542Z 56 Query SHOW MASTER STATUS
2017-06-24T00:48:48.627743Z 56 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, E
NGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_
NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_
NAME
2017-06-24T00:48:48.628321Z 56 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTE
NT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, L
OGFILE_GROUP_NAME
2017-06-24T00:48:48.628778Z 56 Query SHOW DATABASES
2017-06-24T00:48:48.629166Z 56 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2017-06-24T00:48:48.631175Z 56 Init DB dbtest
2017-06-24T00:48:48.631282Z 56 Query SHOW CREATE DATABASE IF NOT EXISTS `dbtest`
2017-06-24T00:48:48.631415Z 56 Query show tables
2017-06-24T00:48:48.631704Z 56 Query show table status like 't'
2017-06-24T00:48:48.632269Z 56 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:48:48.632372Z 56 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:48:48.632462Z 56 Query show create table `t`
2017-06-24T00:48:48.632642Z 56 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:48:48.632810Z 56 Query show fields from `t`
2017-06-24T00:48:48.633252Z 56 Query show fields from `t`
2017-06-24T00:48:48.633664Z 56 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t`
2017-06-24T00:48:48.633894Z 56 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:48:48.634000Z 56 Query use `dbtest`
2017-06-24T00:48:48.634136Z 56 Query select @@collation_database
2017-06-24T00:48:48.634281Z 56 Query SHOW TRIGGERS LIKE 't'
2017-06-24T00:48:48.634750Z 56 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:48:48.634887Z 56 Query show table status like 't1'
2017-06-24T00:48:48.635388Z 56 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:48:48.635483Z 56 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:48:48.635575Z 56 Query show create table `t1`
2017-06-24T00:48:48.635719Z 56 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:48:48.635852Z 56 Query show fields from `t1`
2017-06-24T00:48:48.636268Z 56 Query show fields from `t1`
2017-06-24T00:48:48.636699Z 56 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2017-06-24T00:48:48.636911Z 56 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:48:48.637030Z 56 Query use `dbtest`
2017-06-24T00:48:48.637132Z 56 Query select @@collation_database
2017-06-24T00:48:48.637264Z 56 Query SHOW TRIGGERS LIKE 't1'
2017-06-24T00:48:48.637719Z 56 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:48:48.637856Z 56 Query show table status like 't2'
2017-06-24T00:48:48.638347Z 56 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:48:48.638441Z 56 Query SET SESSION character_set_results = 'binary'
二、加--single-transaction備份
mysqldump --default-character-set=utf8mb4 -uroot --triggers -R -E -A --master-data=2 --single-transaction |gzip >dbbackup.sql.gz
1、連線資料庫
2、調整sql_mode,time_zone
3、調整事務隔離界別為RR（SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ）
4、開啟事務一致性快照（START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */）
5、記錄gtid_mode、binlog位置等資訊
6、釋放全域性鎖
7、備份資料檔案、表空間資料
8、備份業務資料庫表結構
9、設定savepoint sp(SAVEPOINT sp)
10、備份資料表結構、表資料、觸發器、事件(每備份完一個表，做一次rollback to savepoint sp，釋放DDL鎖)
11、備份系統資料庫、表結構、觸發器
12、備份儲存過程、函式
13、釋放savepoint
14、備份事件
15、備份結束會話退出
2017-06-24T00:58:05.650060Z 58 Connect root@localhost on using Socket
2017-06-24T00:58:05.650336Z 58 Query /*!40100 SET @@SQL_MODE='' */
2017-06-24T00:58:05.650491Z 58 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-06-24T00:58:05.650703Z 58 Query SHOW STATUS LIKE 'binlog_snapshot_%'
2017-06-24T00:58:05.660129Z 58 Query SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema =
'performance_schema' AND table_name = 'session_variables'
2017-06-24T00:58:05.660477Z 58 Query SELECT COUNT(*) FROM performance_schema.session_variables WHERE VARI
ABLE_NAME LIKE 'rocksdb\_skip\_fill\_cache'
2017-06-24T00:58:05.662214Z 58 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-06-24T00:58:05.662314Z 58 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-06-24T00:58:05.663369Z 58 Query SHOW VARIABLES LIKE 'gtid\_mode'
2017-06-24T00:58:05.667436Z 58 Query SHOW STATUS LIKE 'binlog_snapshot_%'
2017-06-24T00:58:05.670160Z 58 Query UNLOCK TABLES
2017-06-24T00:58:05.670343Z 58 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, E
NGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_
NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_
NAME
2017-06-24T00:58:05.671210Z 58 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTE
NT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, L
OGFILE_GROUP_NAME
2017-06-24T00:58:05.671971Z 58 Query SHOW DATABASES
2017-06-24T00:58:05.672341Z 58 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2017-06-24T00:58:05.675964Z 58 Init DB dbtest
2017-06-24T00:58:05.676080Z 58 Query SHOW CREATE DATABASE IF NOT EXISTS `dbtest`
2017-06-24T00:58:05.676223Z 58 Query SAVEPOINT sp
2017-06-24T00:58:05.676343Z 58 Query show tables
2017-06-24T00:58:05.676641Z 58 Query show table status like 't'
2017-06-24T00:58:05.677297Z 58 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:58:05.677404Z 58 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:58:05.677525Z 58 Query show create table `t`
2017-06-24T00:58:05.677659Z 58 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:58:05.677821Z 58 Query show fields from `t`
2017-06-24T00:58:05.678319Z 58 Query show fields from `t`
2017-06-24T00:58:05.678746Z 58 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t`
2017-06-24T00:58:05.678967Z 58 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:58:05.679059Z 58 Query use `dbtest`
2017-06-24T00:58:05.679211Z 58 Query select @@collation_database
2017-06-24T00:58:05.679357Z 58 Query SHOW TRIGGERS LIKE 't'
2017-06-24T00:58:05.679851Z 58 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:58:05.679958Z 58 Query ROLLBACK TO SAVEPOINT sp
2017-06-24T00:58:05.680059Z 58 Query show table status like 't1'
2017-06-24T00:58:05.680589Z 58 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:58:05.680702Z 58 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:58:05.680807Z 58 Query show create table `t1`
2017-06-24T00:58:05.680945Z 58 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:58:05.681071Z 58 Query show fields from `t1`
2017-06-24T00:58:05.681579Z 58 Query show fields from `t1`
2017-06-24T00:58:05.682021Z 58 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2017-06-24T00:58:05.682244Z 58 Query SET SESSION character_set_results = 'binary'
2017-06-24T00:58:05.682369Z 58 Query use `dbtest`
2017-06-24T00:58:05.682477Z 58 Query select @@collation_database
2017-06-24T00:58:05.682609Z 58 Query SHOW TRIGGERS LIKE 't1'
2017-06-24T00:58:05.683147Z 58 Query SET SESSION character_set_results = 'utf8mb4'
2017-06-24T00:58:05.683268Z 58 Query ROLLBACK TO SAVEPOINT sp
2017-06-24T00:58:05.683366Z 58 Query show table status like 't2'
2017-06-24T00:58:05.683899Z 58 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-06-24T00:58:05.684005Z 58 Query SET SESSION character_set_results = 'binary'
三、資料庫恢復
gunzip <dbbackup.sql.gz |mysql -uroot --default-character-set=utf8mb4 -o dbtest
2017-06-24T01:06:22.926385Z 64 Connect root@localhost on dbtest using Socket
2017-06-24T01:06:22.926645Z 64 Query select @@version_comment limit 1
2017-06-24T01:06:22.927001Z 64 Query /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
2017-06-24T01:06:22.927161Z 64 Query /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
2017-06-24T01:06:22.927286Z 64 Query /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */
2017-06-24T01:06:22.927426Z 64 Query /*!40101 SET NAMES utf8mb4 */
2017-06-24T01:06:22.927580Z 64 Query /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */
2017-06-24T01:06:22.927735Z 64 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-06-24T01:06:22.927856Z 64 Query /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */
2017-06-24T01:06:22.927986Z 64 Query /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_K
EY_CHECKS=0 */
2017-06-24T01:06:22.928099Z 64 Query /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZE
RO' */
2017-06-24T01:06:22.928234Z 64 Query /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
2017-06-24T01:06:22.928337Z 64 Query /*!50717 SET @rocksdb_bulk_load_var_name='rocksdb_bulk_load' */
2017-06-24T01:06:22.928527Z 64 Query /*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FRO
M INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */
2017-06-24T01:06:22.928995Z 64 Query /*!50717 SET @rocksdb_get_is_supported = IF (@rocksdb_has_p_s_sessio
n_variables, 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NA
ME=?', 'SELECT 0') */
2017-06-24T01:06:22.929105Z 64 Query /*!50717 PREPARE s FROM @rocksdb_get_is_supported */
2017-06-24T01:06:22.929259Z 64 Prepare SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.s
ession_variables WHERE VARIABLE_NAME=?
2017-06-24T01:06:22.929346Z 64 Query /*!50717 EXECUTE s USING @rocksdb_bulk_load_var_name */
2017-06-24T01:06:22.929370Z 64 Execute SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.s
ession_variables WHERE VARIABLE_NAME='rocksdb_bulk_load'
2017-06-24T01:06:22.931165Z 64 Query /*!50717 DEALLOCATE PREPARE s */
2017-06-24T01:06:22.931306Z 64 Query /*!50717 SET @rocksdb_enable_bulk_load = IF (@rocksdb_is_supported,
'SET SESSION rocksdb_bulk_load = 1', 'SET @rocksdb_dummy_bulk_load = 0') */
2017-06-24T01:06:22.931426Z 64 Query /*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */
2017-06-24T01:06:22.931463Z 64 Prepare SET @rocksdb_dummy_bulk_load = 0
2017-06-24T01:06:22.931558Z 64 Query /*!50717 EXECUTE s */
2017-06-24T01:06:22.931573Z 64 Execute SET @rocksdb_dummy_bulk_load = 0
2017-06-24T01:06:22.931666Z 64 Query /*!50717 DEALLOCATE PREPARE s */
2017-06-24T01:06:22.931805Z 64 Query CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dbtest` /*!40100 DEFAULT C
HARACTER SET utf8 */
2017-06-24T01:06:22.932975Z 64 Init DB dbtest
2017-06-24T01:06:22.933111Z 64 Query DROP TABLE IF EXISTS `t`
2017-06-24T01:06:22.933728Z 64 Query /*!40101 SET @saved_cs_client = @@character_set_client */
2017-06-24T01:06:22.933859Z 64 Query /*!40101 SET character_set_client = utf8 */
2017-06-24T01:06:22.934023Z 64 Query CREATE TABLE `t` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-06-24T01:06:22.939020Z 64 Query /*!40101 SET character_set_client = @saved_cs_client */
2017-06-24T01:06:22.939213Z 64 Query LOCK TABLES `t` WRITE
2017-06-24T01:06:22.939616Z 64 Query /*!40000 ALTER TABLE `t` DISABLE KEYS */
2017-06-24T01:06:22.940164Z 64 Query INSERT INTO `t` VALUES (1)
2017-06-24T01:06:22.941111Z 64 Query /*!40000 ALTER TABLE `t` ENABLE KEYS */
2017-06-24T01:06:22.941871Z 64 Query UNLOCK TABLES
2017-06-24T01:06:22.942023Z 64 Query DROP TABLE IF EXISTS `t1`
2017-06-24T01:06:22.942558Z 64 Query /*!40101 SET @saved_cs_client = @@character_set_client */
2017-06-24T01:06:22.942702Z 64 Query /*!40101 SET character_set_client = utf8 */
2017-06-24T01:06:22.942879Z 64 Query CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-06-24T01:06:22.946848Z 64 Query /*!40101 SET character_set_client = @saved_cs_client */
2017-06-24T01:06:22.946986Z 64 Query LOCK TABLES `t1` WRITE
2017-06-24T01:06:22.947344Z 64 Query /*!40000 ALTER TABLE `t1` DISABLE KEYS */
2017-06-24T01:06:22.947867Z 64 Query INSERT INTO `t1` VALUES (1,'name')
2017-06-24T01:06:22.948877Z 64 Query /*!40000 ALTER TABLE `t1` ENABLE KEYS */
2017-06-24T01:06:22.949727Z 64 Query UNLOCK TABLES
2017-06-24T01:06:22.949847Z 64 Query DROP TABLE IF EXISTS `t2`
2017-06-24T01:06:22.950384Z 64 Query /*!40101 SET @saved_cs_client = @@character_set_client */
2017-06-24T01:06:22.950488Z 64 Query /*!40101 SET character_set_client = utf8 */
2017-06-24T01:06:22.950632Z 64 Query CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-06-24T01:06:22.954865Z 64 Query /*!40101 SET character_set_client = @saved_cs_client */
2017-06-24T01:06:22.955009Z 64 Query LOCK TABLES `t2` WRITE
2017-06-24T01:06:22.955370Z 64 Query /*!40000 ALTER TABLE `t2` DISABLE KEYS */
2017-06-24T01:06:22.955873Z 64 Query INSERT INTO `t2` VALUES (1,'name'),(1,'name')
2017-06-24T01:06:22.957048Z 64 Query /*!40000 ALTER TABLE `t2` ENABLE KEYS */
2017-06-24T01:06:22.957872Z 64 Query UNLOCK TABLES
2017-06-24T01:06:22.958010Z 64 Query DROP TABLE IF EXISTS `t3`
2017-06-24T01:06:22.958512Z 64 Query /*!40101 SET @saved_cs_client = @@character_set_client */
2017-06-24T01:06:22.958618Z 64 Query /*!40101 SET character_set_client = utf8 */
2017-06-24T01:06:22.958778Z 64 Query CREATE TABLE `t3` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-06-24T01:06:22.972610Z 64 Query /*!40101 SET character_set_client = @saved_cs_client */
2017-06-24T01:06:22.972767Z 64 Query LOCK TABLES `t3` WRITE
2017-06-24T01:06:22.973184Z 64 Query /*!40000 ALTER TABLE `t3` DISABLE KEYS */
2017-06-24T01:06:23.001988Z 64 Query INSERT INTO `t3` VALUES (1,'name1'),(2,'name2'),(3,'name3')
四、mysqldump備份恢復總結
1、mysqldump是透過select * from table_name來獲取表的資料進而完成備份。
2、START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */必須放到FLUSH TABLES WITH READ LOCK和UNLOCK TABLES之間，放到之前會造成START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */和FLUSH TABLES WITH READ LOCK之間執行的DML語句丟失，放到之後，會造成從庫重複插入資料。
3、mysqldump只適合放到業務低峰期做，如果備份的過程中資料操作很頻繁，會造成Undo表空間越來越大，undo表空間預設是放到共享表空間中的，而ibdata的特性是一旦增大，就不會收縮。（可重複讀的資料會在undo中儲存，dml也會產生相關的redo/undo）
4、--single-transaction開啟事務一致性快照，開始savepoint，每次備份完執行rollback to savepoint為了防止表的DDL阻塞，而調整事務隔離界別RR為了保障會話的可重複讀，從而達到備份資料的一致性。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27067062/viewspace-2141194/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【mysqldump】mysqldump及備份恢復示例MySql
- mysqldump備份技巧分享MySql
- MySQLDump的備份方法MySql
- Mysqldump的備份流程MySql
- mysqldump 備份指令碼MySql指令碼
- mysqldump備份指令碼MySql指令碼
- 邏輯備份--mysqldumpMySql
- windows mysqldump備份指令碼WindowsMySql指令碼
- mysql 邏輯備份 （mysqldump）MySql
- mysqldump備份不輸入密碼直接備份MySql密碼
- mysqldump 資料庫備份程式MySql資料庫
- MySQL 5.5 mysqldump備份說明MySql
- mysqldump常用備份恢復方法MySql
- mysql 備份資料庫 mysqldumpMySql資料庫
- mysqldump 進行資料備份MySql
- 簡單的mysqldump備份（windows）MySqlWindows
- MySQL主從配置及mysqldump備份MySql
- mysql5.6 mysqldump備份報錯MySql
- Mysqldump備份的基本流程介紹MySql
- mysqldump備份指令碼一例MySql指令碼
- mysqldump如何只備份表結構MySql
- mysql備份恢復mysqldump面面觀MySql
- mysqldump 全量和增量備份指令碼MySql指令碼
- mysql之 mysqldump 備份恢復詳解MySql
- Mysql增量備份之Mysqldump& MylvmbackupMySqlLVM
- mysqldump 備份單張表和恢復MySql
- mysql的常用備份工具：mysqldump和mysqlhotcopyMySql
- Mysql備份系列（2）--mysqldump備份（全量+增量）方案操作記錄MySql
- mysqldump全量備份+mysqlbinlog二進位制日誌增量備份MySql
- mysqldump壓縮備份匯出匯入（含定期備份shell指令碼）MySql指令碼
- Mysql備份和還原資料庫-mysqldumpMySql資料庫
- MySQL 5.5 Slave節點備份指令碼（mysqldump）MySql指令碼
- mysqldump來備份MYSQL資料庫(指令碼)MySql資料庫指令碼
- 熱備份原理
- RMAN備份原理
- 初探MySQL資料備份及備份原理MySql
- XtraBackup完整備份與增量備份的原理
- MySQL · 物理備份 · Percona XtraBackup 備份原理MySql