文章目录
mydumper
mydumper 介绍
mysqldump 是单线程进行数据库备份的工具,在数据量比较大时,备份速度下降十分严重,mydumper 支持并行备份,备份速度更快,备份耗费时间更少。
特性:
- 并行,高性能的
MySQL
逻辑导入导出工具 - 非常方便管理导出的文件(文件拆分成单表存储, metadata 信息)
- 导入导出时支持正则表达式
mydumper
备份机制
mydumper
备份原理示意图
time | master_thread | worker_thread |
---|---|---|
t1 | FLUSH TABLES WITH READ LOCK |
|
t2 | START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ |
|
t3 | SHOW MASTER STATUS |
|
t4 | SHOW SLAVE STATUS |
|
t5 | Connect db_instance using TCP/IP |
|
t6 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |
|
t7 | START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ |
|
t8 | SHOW DATABASES |
|
t9 | Init DB |
|
t10 | backup all tables of myisam and some innodb tables |
|
t11 | UNLOCK TABLES /* FTWRL */ |
|
t12 | Quit |
|
t13 | backup innodb tables |
|
t14 | Quit |
mydumper
备份过程
mydumper 备份期间 general log
15180 Connect root@127.0.0.1 on db3 using TCP/IP
15180 Query FLUSH TABLES WITH READ LOCK
15180 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
15180 Query SHOW MASTER STATUS
15180 Query SHOW SLAVE STATUS
15181 Connect root@127.0.0.1 on using TCP/IP
15181 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
15181 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
15182 Connect root@127.0.0.1 on using TCP/IP
15182 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
15182 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
15183 Connect root@127.0.0.1 on using TCP/IP
15183 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
15183 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
15184 Connect root@127.0.0.1 on using TCP/IP
15184 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
15184 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
15181 Init DB db3
15182 Query SHOW CREATE DATABASE `db3`
15181 Query SHOW TABLE STATUS
15182 Query select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='db3' and TABLE_NAME='t_myisam' and extra like '%GENERATED%'
15184 Query select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='db3' and TABLE_NAME='t2' and extra like '%GENERATED%'
15181 Query select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='db3' and TABLE_NAME='t3' and extra like '%GENERATED%'
15183 Query select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='db3' and TABLE_NAME='t1' and extra like '%GENERATED%'
15184 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `db3`.`t2`
15181 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `db3`.`t3`
15182 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `db3`.`t_myisam`
15184 Query select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='db3' and TABLE_NAME='t5' and extra like '%GENERATED%'
15181 Query select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='db3' and TABLE_NAME='t4' and extra like '%GENERATED%'
15183 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `db3`.`t1`
15180 Query UNLOCK TABLES /* FTWRL */
15182 Query SHOW CREATE TABLE `db3`.`t1`
15184 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `db3`.`t5`
15183 Query SHOW CREATE TABLE `db3`.`t2`
15180 Quit
15184 Query SHOW CREATE TABLE `db3`