如何剖析mysqldump备份原理

本篇文章给大家分享的是有关如何解析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为了保障会话的可重复读，从而达到备份数据的一致性。

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!