MySQL备份工具之mydumper

1.yum安装

github地址：maxbube/mydumper
安装依赖工具和软件：
yum install -y cmake gcc gcc-c++ git make
yum install -y glib2-devel mysql-devel openssl-devel pcre-devel zlib-devel
yum install -y mysql-devel
yum install -y Percona-Server-devel-57
yum install -y mariadb-devel

yum install https://github.com/maxbube/mydumper/releases/download/v0.10.5/mydumper-0.10.5-1.el7.x86_64.rpm

2.使用方法

mydumper常用参数:
-B, --database #备份数据库
-T, --tables-list #备份的表
-o, --outputdir #备份保存的目录
-r, --rows #多少行输出一个文件
-c, --compress #使用压缩
-G, --triggers #备份触发器
-E, --events #备份事件
-R, --routines #备份routines
--use-savepoints #使用savepoints备份，以释放mdl锁
--trx-consistency-only #开启一个事务进行备份
-h, --host
-u, --user
-p, --password
-P, --port
-S, --socket
-t, --threads #备份并行线程，默认4
myloader常用参数：
-d, --directory #从哪里恢复数据
-B, --database #恢复到哪个数据库
-h, --host
-u, --user
-p, --password
-P, --port
-S, --socket
-t, --threads #并行线程数，默认4

3.备份流程

打开general_log,输出log到table
mysql> set global log_output='table';
mysql> set global general_log=1;
执行备份：
mydumper --trx-consistency-only --use-savepoints -c -B sbtest -o backup_sbtest -t 4 --rows 100000 -S /tmp/mysql.sock57
关闭general_log:
mysql> set global general_log=0;
查看general_log输出：
mysql> select thread_id,left(argument,64) from general_log;
+-----------+------------------------------------------------------------------+
| thread_id | left(argument,64)                                                |
+-----------+------------------------------------------------------------------+
| 13        | root@localhost on sbtest using Socket                            |
| 13        | SET SESSION wait_timeout = 2147483                               |
| 13        | SET SESSION net_write_timeout = 2147483                          |
| 13        | SHOW PROCESSLIST                                                 |
| 13        | SELECT @@version_comment, @@version                              |
| 13        | FLUSH TABLES WITH READ LOCK                                      |
| 13        | START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */           |
| 13        | /*!40101 SET NAMES binary*/                                      |
| 13        | SHOW MASTER STATUS                                               |
| 13        | SHOW SLAVE STATUS                                                |
| 14        | root@localhost on  using Socket                                  |
| 14        | SET SESSION wait_timeout = 2147483                               |
| 14        | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ          |
| 14        | START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */           |
| 14        | /*!40103 SET TIME_ZONE='+00:00' */                               |
| 14        | /*!40101 SET NAMES binary*/                                      |
| 15        | root@localhost on  using Socket                                  |
| 15        | SET SESSION wait_timeout = 2147483                               |
| 15        | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ          |
| 15        | START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */           |
| 15        | /*!40103 SET TIME_ZONE='+00:00' */                               |
| 15        | /*!40101 SET NAMES binary*/                                      |
| 16        | root@localhost on  using Socket                                  |
| 16        | SET SESSION wait_timeout = 2147483                               |
| 16        | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ          |
| 16        | START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */           |
| 16        | /*!40103 SET TIME_ZONE='+00:00' */                               |
| 16        | /*!40101 SET NAMES binary*/                                      |
| 17        | root@localhost on  using Socket                                  |
| 17        | SET SESSION wait_timeout = 2147483                               |
| 17        | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ          |
| 17        | START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */           |
| 17        | /*!40103 SET TIME_ZONE='+00:00' */                               |
| 17        | /*!40101 SET NAMES binary*/                                      |
| 13        | UNLOCK TABLES /* trx-only */                                     |
| 15        | sbtest                                                           |
| 14        | SHOW CREATE DATABASE IF NOT EXISTS `sbtest`                      |
| 15        | SHOW TABLE STATUS                                                |
| 13        | SHOW INDEX FROM `sbtest`.`sbtest1`                               |
| 13        | SELECT /*!40001 SQL_NO_CACHE */ MIN(`id`),MAX(`id`) FROM `sbtest |
| 13        | EXPLAIN SELECT `id` FROM `sbtest`.`sbtest1`                      |
| 14        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 16        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 13        | SHOW INDEX FROM `sbtest`.`sbtest2`                               |
| 17        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 13        | SELECT /*!40001 SQL_NO_CACHE */ MIN(`id`),MAX(`id`) FROM `sbtest |
| 15        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 13        | EXPLAIN SELECT `id` FROM `sbtest`.`sbtest2`                      |
| 13        | SHOW INDEX FROM `sbtest`.`sbtest3`                               |
| 13        | SELECT /*!40001 SQL_NO_CACHE */ MIN(`id`),MAX(`id`) FROM `sbtest |
| 14        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE  |
| 16        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE  |
| 17        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE  |
| 15        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE  |
| 13        | EXPLAIN SELECT `id` FROM `sbtest`.`sbtest3`                      |
| 13        | SHOW INDEX FROM `sbtest`.`sbtest4`                               |
| 13        | SELECT /*!40001 SQL_NO_CACHE */ MIN(`id`),MAX(`id`) FROM `sbtest |
| 13        | EXPLAIN SELECT `id` FROM `sbtest`.`sbtest4`                      |
| 13        |                                                                  |
| 16        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 16        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE  |
| 14        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 14        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE  |
| 15        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 15        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE  |
| 17        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 17        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE  |
| 16        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 16        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE  |
| 14        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 14        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest2` WHERE  |
| 15        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 15        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest2` WHERE  |
| 17        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 17        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest2` WHERE  |
| 14        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 14        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest2` WHERE  |
| 16        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 16        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest2` WHERE  |
| 15        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 15        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest2` WHERE  |
| 17        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 17        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest2` WHERE  |
| 14        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 14        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest2` WHERE  |
| 16        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 16        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest2` WHERE  |
| 15        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 15        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest3` WHERE  |
| 17        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 17        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest3` WHERE  |
| 14        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 14        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest3` WHERE  |
| 16        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 16        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest3` WHERE  |
| 15        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 15        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest3` WHERE  |
| 17        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 17        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest3` WHERE  |
| 14        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 14        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest3` WHERE  |
| 16        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 16        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest3` WHERE  |
| 17        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 17        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest3` WHERE  |
| 15        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 15        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest4` WHERE  |
| 14        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 14        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest4` WHERE  |
| 16        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 16        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest4` WHERE  |
| 15        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 15        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest4` WHERE  |
| 17        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 17        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest4` WHERE  |
| 14        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 14        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest4` WHERE  |
| 16        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 16        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest4` WHERE  |
| 15        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 15        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest4` WHERE  |
| 17        | select COLUMN_NAME from information_schema.COLUMNS where TABLE_S |
| 17        | SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest4` WHERE  |
| 16        | SHOW CREATE TABLE `sbtest`.`sbtest1`                             |
| 16        | SHOW CREATE TABLE `sbtest`.`sbtest2`                             |
| 16        | SHOW CREATE TABLE `sbtest`.`sbtest3`                             |
| 16        | SHOW CREATE TABLE `sbtest`.`sbtest4`                             |
| 16        |                                                                  |
| 14        |                                                                  |
| 15        |                                                                  |
| 17        |                                                                  |
| 11        | set global general_log=0                                         |
+-----------+------------------------------------------------------------------+

4.恢复

查看备份文件,每张表10000行生成一个压缩文件。表结构文件和数据文件分开存储。
 shell>ll backup_sbtest/
total 375688
-rw-r--r-- 1 root root      272 Jun 10 17:23 metadata
-rw-r--r-- 1 root root 10676267 Jun 10 17:23 sbtest.sbtest1.00000.sql.gz
-rw-r--r-- 1 root root 10684748 Jun 10 17:23 sbtest.sbtest1.00001.sql.gz
-rw-r--r-- 1 root root 10683895 Jun 10 17:23 sbtest.sbtest1.00002.sql.gz
-rw-r--r-- 1 root root 10684709 Jun 10 17:23 sbtest.sbtest1.00003.sql.gz
-rw-r--r-- 1 root root 10684778 Jun 10 17:23 sbtest.sbtest1.00004.sql.gz
-rw-r--r-- 1 root root 10685078 Jun 10 17:23 sbtest.sbtest1.00005.sql.gz
-rw-r--r-- 1 root root 10683451 Jun 10 17:23 sbtest.sbtest1.00006.sql.gz
-rw-r--r-- 1 root root 10684622 Jun 10 17:23 sbtest.sbtest1.00007.sql.gz
-rw-r--r-- 1 root root 10684016 Jun 10 17:23 sbtest.sbtest1.00008.sql.gz
-rw-r--r-- 1 root root      261 Jun 10 17:23 sbtest.sbtest1-schema.sql.gz
-rw-r--r-- 1 root root 10676463 Jun 10 17:23 sbtest.sbtest2.00000.sql.gz
-rw-r--r-- 1 root root 10685153 Jun 10 17:23 sbtest.sbtest2.00001.sql.gz
-rw-r--r-- 1 root root 10684257 Jun 10 17:23 sbtest.sbtest2.00002.sql.gz
-rw-r--r-- 1 root root 10684568 Jun 10 17:23 sbtest.sbtest2.00003.sql.gz
-rw-r--r-- 1 root root 10684937 Jun 10 17:23 sbtest.sbtest2.00004.sql.gz
-rw-r--r-- 1 root root 10684110 Jun 10 17:23 sbtest.sbtest2.00005.sql.gz
-rw-r--r-- 1 root root 10685353 Jun 10 17:23 sbtest.sbtest2.00006.sql.gz
-rw-r--r-- 1 root root 10685418 Jun 10 17:23 sbtest.sbtest2.00007.sql.gz
-rw-r--r-- 1 root root 10684071 Jun 10 17:23 sbtest.sbtest2.00008.sql.gz
-rw-r--r-- 1 root root      261 Jun 10 17:23 sbtest.sbtest2-schema.sql.gz
-rw-r--r-- 1 root root 10676522 Jun 10 17:23 sbtest.sbtest3.00000.sql.gz
-rw-r--r-- 1 root root 10683953 Jun 10 17:23 sbtest.sbtest3.00001.sql.gz
-rw-r--r-- 1 root root 10684802 Jun 10 17:23 sbtest.sbtest3.00002.sql.gz
-rw-r--r-- 1 root root 10684851 Jun 10 17:23 sbtest.sbtest3.00003.sql.gz
-rw-r--r-- 1 root root 10684288 Jun 10 17:23 sbtest.sbtest3.00004.sql.gz
-rw-r--r-- 1 root root 10684398 Jun 10 17:23 sbtest.sbtest3.00005.sql.gz
-rw-r--r-- 1 root root 10684992 Jun 10 17:23 sbtest.sbtest3.00006.sql.gz
-rw-r--r-- 1 root root 10684743 Jun 10 17:23 sbtest.sbtest3.00007.sql.gz
-rw-r--r-- 1 root root 10683578 Jun 10 17:23 sbtest.sbtest3.00008.sql.gz
-rw-r--r-- 1 root root      261 Jun 10 17:23 sbtest.sbtest3-schema.sql.gz
-rw-r--r-- 1 root root 10676289 Jun 10 17:23 sbtest.sbtest4.00000.sql.gz
-rw-r--r-- 1 root root 10684658 Jun 10 17:23 sbtest.sbtest4.00001.sql.gz
-rw-r--r-- 1 root root 10685005 Jun 10 17:23 sbtest.sbtest4.00002.sql.gz
-rw-r--r-- 1 root root 10684927 Jun 10 17:23 sbtest.sbtest4.00003.sql.gz
-rw-r--r-- 1 root root 10685821 Jun 10 17:23 sbtest.sbtest4.00004.sql.gz
-rw-r--r-- 1 root root 10685278 Jun 10 17:23 sbtest.sbtest4.00005.sql.gz
-rw-r--r-- 1 root root 10684259 Jun 10 17:23 sbtest.sbtest4.00006.sql.gz
-rw-r--r-- 1 root root 10685283 Jun 10 17:23 sbtest.sbtest4.00007.sql.gz
-rw-r--r-- 1 root root 10683806 Jun 10 17:23 sbtest.sbtest4.00008.sql.gz
-rw-r--r-- 1 root root      261 Jun 10 17:23 sbtest.sbtest4-schema.sql.gz
-rw-r--r-- 1 root root      109 Jun 10 17:23 sbtest-schema-create.sql.gz
metadata文件存储了binlog位置点和备份开始结束的时间
shell>cat metadata
Started dump at: 2021-06-10 17:23:10
SHOW MASTER STATUS:
	Log: mysql-bin.000001
	Pos: 472
	GTID:03d52d94-c6a7-11eb-9322-000c29b99c14:1-1534082,
71557f9f-c9c3-11eb-9a33-000c29b99c14:1-2,
c58301f7-f8ad-11ea-b9f5-000c29bcd489:1-3259357

Finished dump at: 2021-06-10 17:23:25
数据恢复
myloader -d backup_test/  -B test1 -S /tmp/mysql.sock57
注：可以将某张表的备份文件全部放在指定目录下，从而只恢复某一张指定的表。
posted @ 2021-06-10 17:28  yang417  阅读(832)  评论(0)    收藏  举报