如何解析mysqldump备份原理

69次阅读
没有评论

共计 14251 个字符,预计需要花费 36 分钟才能阅读完成。

本篇文章给大家分享的是有关如何解析 mysqldump 备份原理,丸趣 TV 小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着丸趣 TV 小编一起来看看吧。

这里通过开启 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 为了保障会话的可重复读,从而达到备份数据的一致性。

以上就是如何解析 mysqldump 备份原理,丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-19发表,共计14251字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)