Mydumper 介绍
Mydumper是一个针对MySQL和Drizzle的高性能多线程备份和恢复工具。
Mydumper主要特性:
- 轻量级C语言写的
- 多线程备份,备份后会生成多个备份文件
- 事务性和非事务性表一致的快照(适用于0.2.2以上版本)
- 快速的文件压缩
- 支持导出binlog
- 多线程恢复(适用于0.2.1以上版本)
- 以守护进程的工作方式,定时快照和连续二进制日志(适用于0.5.0以上版本)
- 开源 (GNU GPLv3)
下载地址:
https://launchpad.net/mydumper
-B, --database 要备份的数据库,不指定则备份所有库
-T, --tables-list 需要备份的表,名字用逗号隔开
-o, --outputdir 备份文件输出的目录
-s, --statement-size 生成的insert语句的字节数,默认1000000
-r, --rows 将表按行分块时,指定的块行数,指定这个选项会关闭 --chunk-filesize
-F, --chunk-filesize 将表按大小分块时,指定的块大小,单位是 MB
-c, --compress 压缩输出文件
-e, --build-empty-files 如果表数据是空,还是产生一个空文件(默认无数据则只有表结构文件)
-x, --regex 是同正则表达式匹配 'db.table'
-i, --ignore-engines 忽略的存储引擎,用都厚分割
-m, --no-schemas 不备份表结构
-k, --no-locks 不使用临时共享只读锁,使用这个选项会造成数据不一致
--less-locking 减少对InnoDB表的锁施加时间(这种模式的机制下文详解)
-l, --long-query-guard 设定阻塞备份的长查询超时时间,单位是秒,默认是60秒(超时后默认mydumper将会退出)
--kill-long-queries 杀掉长查询 (不退出)
-b, --binlogs 导出binlog
-D, --daemon 启用守护进程模式,守护进程模式以某个间隔不间断对数据库进行备份
-I, --snapshot-interval dump快照间隔时间,默认60s,需要在daemon模式下
-L, --logfile 使用的日志文件名(mydumper所产生的日志), 默认使用标准输出
--tz-utc 跨时区是使用的选项,不解释了
--skip-tz-utc 同上
--use-savepoints 使用savepoints来减少采集metadata所造成的锁时间,需要 SUPER 权限
--success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist
-h, --host 连接的主机名
-u, --user 备份所使用的用户
-p, --password 密码
-P, --port 端口
-S, --socket 使用socket通信时的socket文件
-t, --threads 开启的备份线程数,默认是4
-C, --compress-protocol 压缩与mysql通信的数据
-V, --version 显示版本号
-v, --verbose 输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为 2
myloader 参数解释
-d, --directory 备份文件的文件夹
-q, --queries-per-transaction 每次事物执行的查询数量,默认是1000
-o, --overwrite-tables 如果要恢复的表存在,则先drop掉该表,使用该参数,需要备份时候要备份表结构
-B, --database 需要还原的数据库
-e, --enable-binlog 启用还原数据的二进制日志
-h, --host 主机
-u, --user 还原的用户
-p, --password 密码
-P, --port 端口
-S, --socket socket文件
-t, --threads 还原所使用的线程数,默认是4
-C, --compress-protocol 压缩协议
-V, --version 显示版本
-v, --verbose 输出模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2
mydumper的主要工作步骤
1 主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁,以阻止DML语句写入,保证数据的一致性
2 读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中,以供即使点恢复使用
3 START TRANSACTION WITH CONSISTENT SNAPSHOT; 开启读一致事务
4 启用N个(线程数可以指定,默认是4)dump线程导出表和表结构
5 备份非事务类型的表
6 主线程 UNLOCK TABLES,备份完成非事务类型的表之后,释放全局只读锁
7 dump InnoDB tables, 基于事物导出InnoDB表
8 事物结束
使用方法
#导出整个库
mydumper -u root -S /srv/my3308/run/mysql.sock -B trade_platform -o /data/trade_platform
#仅仅导出platform的ddl语句不包含数据到指定的目录 /data/platform
mydumper -u root -S /srv/my3308/run/mysql.sock -B platform -m -o /data/platform
#以压缩的方式导出的文件
mydumper -u root -S /srv/my3308/run/mysql.sock -B trade_platform -c -o /data/trade_platform
备份文件以.gz 的格式压缩
#ls
metadata trade_platform.config.sql.gz trade_platform.trade_order-schema.sql.gz
trade_platform.config-schema.sql.gz trade_platform-schema-create.sql.gz trade_platform.trade_order.sql.gz
#使用正则表达式
mydumper -u root -S /srv/my3308/run/mysql.sock --regex='^(?!(mysql|test))' -o /data/bk20170120
其中正则表达式可以是
--regex=order.* 导出所有order 开头的表
mydumper 导出的文件
# ls
metadata platform.config.sql platform.order.sql
mydumper 导出的文件 分为
metadata :包含导出时刻的binlog 位点信息 ,如果启用gtid ,则记录gtid信息。
Started dump at: 2017-01-20 17:26:53
SHOW MASTER STATUS:
Log: mysql-bin.000025
Pos: 505819083
GTID:
Finished dump at: 2017-01-20 17:27:02
db.table.sql :数据文件,insert语句
db.table-schema.sql :包含建表语句
db-schema.sql :包含建库语句
注意 0.9.1 版本去掉了 --binlogs 参数,故会少了 启用binlogs参数相关的文件。
有兴趣的朋友可以继续阅读 这里 ,有专门针对mydumper与5.7 新出的mysqlpump 工具的讨论。
使用案例:
测试数据准备:
create database test character set utf8;
use test;
create table t1(id int,name varchar(1000));
DELIMITER //
CREATE PROCEDURE p5()
BEGIN
declare l_n1 int default 1000000;
while l_n1 > 0 DO
insert into t1 select l_n1,repeat('w',1000);
set l_n1 = l_n1 - 1;
end while;
select l_n1;
END;
//
DELIMITER ;
-- 往测试表录入100w条数据,执行时间比较长,可以优化下,分批提交
call p5;
--拷贝一个表t2
create table t2 as select * from t1;
备份test库下的t1和t2两张表
备份进程是放在后台执行,查看日志才知道什么时候导出完成
/usr/bin/mydumper -h 47.99.1.254-u root -p abc123 -B test -T t1,t2 -t 4 -r 100000 -c --less-locking -v 3 -D -L /var/log/mydumper.log -o /backup/mydumper/20200826
查看备份日志
[root@10-31-1-119 20200826]# tail -100f /var/log/mydumper.log
2020-08-26 16:39:16 [INFO] - Connected to a MySQL server
2020-08-26 16:39:16 [INFO] - Started dump at: 2020-08-26 16:39:16
2020-08-26 16:39:16 [INFO] - Written master status
2020-08-26 16:39:16 [INFO] - Thread 5 connected using MySQL connection ID 2468
2020-08-26 16:39:16 [INFO] - Thread 6 connected using MySQL connection ID 2469
2020-08-26 16:39:16 [INFO] - Thread 7 connected using MySQL connection ID 2470
2020-08-26 16:39:16 [INFO] - Thread 8 connected using MySQL connection ID 2471
2020-08-26 16:39:16 [INFO] - Thread 1 connected using MySQL connection ID 2472
2020-08-26 16:39:16 [INFO] - Thread 2 connected using MySQL connection ID 2473
2020-08-26 16:39:16 [INFO] - Thread 3 connected using MySQL connection ID 2474
2020-08-26 16:39:16 [INFO] - Thread 4 connected using MySQL connection ID 2475
2020-08-26 16:39:16 [INFO] - Thread 7 shutting down
2020-08-26 16:39:16 [INFO] - Thread 5 shutting down
2020-08-26 16:39:16 [INFO] - Thread 8 shutting down
2020-08-26 16:39:16 [INFO] - Thread 4 dumping data for `test`.`t1`
2020-08-26 16:39:16 [INFO] - Thread 6 shutting down
2020-08-26 16:39:16 [INFO] - Thread 3 dumping data for `test`.`t2`
2020-08-26 16:39:16 [INFO] - Thread 1 dumping schema for `test`.`t1`
2020-08-26 16:39:16 [INFO] - Thread 2 dumping schema for `test`.`t2`
2020-08-26 16:39:16 [INFO] - Non-InnoDB dump complete, unlocking tables
2020-08-26 16:39:16 [INFO] - Thread 2 shutting down
2020-08-26 16:39:16 [INFO] - Thread 1 shutting down
2020-08-26 16:39:28 [INFO] - Thread 3 shutting down
2020-08-26 16:39:28 [INFO] - Thread 4 shutting down
2020-08-26 16:39:28 [INFO] - Finished dump at: 2020-08-26 16:39:28
备份文件如下:
[root@10-31-1-119 20200826]# ll
总用量 0
drwx------. 2 root root 157 8月 26 16:39 0
drwx------. 2 root root 6 8月 26 16:39 1
lrwxrwxrwx. 1 root root 1 8月 26 16:39 last_dump -> 0
[root@10-31-1-119 20200826]#
[root@10-31-1-119 20200826]# du -sh *
11M 0
0 1
0 last_dump
[root@10-31-1-119 20200826]# cd 0
[root@10-31-1-119 0]# ls -lrth
总用量 11M
-rw-rw-rw-. 1 root root 83 8月 26 16:39 test-schema-create.sql.gz
-rw-rw-rw-. 1 root root 195 8月 26 16:39 test.t1-schema.sql.gz
-rw-rw-rw-. 1 root root 195 8月 26 16:39 test.t2-schema.sql.gz
-rw-rw-rw-. 1 root root 5.1M 8月 26 16:39 test.t2.sql.gz
-rw-rw-rw-. 1 root root 5.1M 8月 26 16:39 test.t1.sql.gz
-rw-rw-rw-. 1 root root 142 8月 26 16:39 metadata
恢复数据:
删除数据
[root@10-31-1-119 src]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2484
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop table t1;
Query OK, 0 rows affected (0.19 sec)
mysql> drop table t2;
Query OK, 0 rows affected (0.19 sec)
mysql>
数据恢复
[root@10-31-1-119 0]# /usr/bin/myloader -h 47.99.1.254 -u root -p abc123 -B test -e -t 8 -d /backup/mydumper/20200826/ --overwrite-tables -v 3
** (myloader:21232): CRITICAL **: 16:46:37.885: the specified directory is not a mydumper backup
[root@10-31-1-119 0]#
[root@10-31-1-119 0]# /usr/bin/myloader -h 47.99.1.254 -u root -p abc123 -B test -e -t 8 -d /backup/mydumper/20200826/0/ --overwrite-tables -v 3
** Message: 16:46:58.957: 8 threads created
** Message: 16:46:58.957: Dropping table or view (if exists) `test`.`t1`
** Message: 16:46:58.959: Creating table `test`.`t1`
** Message: 16:46:58.967: Dropping table or view (if exists) `test`.`t2`
** Message: 16:46:58.968: Creating table `test`.`t2`
** Message: 16:46:58.975: Thread 2 restoring `test`.`t1` part 0
** Message: 16:46:58.975: Thread 7 restoring `test`.`t2` part 0
** Message: 16:46:58.975: Thread 6 shutting down
** Message: 16:46:58.975: Thread 4 shutting down
** Message: 16:46:58.975: Thread 1 shutting down
** Message: 16:46:58.975: Thread 5 shutting down
** Message: 16:46:58.975: Thread 8 shutting down
** Message: 16:46:58.975: Thread 3 shutting down
** Message: 16:48:09.018: Thread 7 shutting down
** Message: 16:48:09.668: Thread 2 shutting down