mydumper

---

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`