MySQL入门篇-MySQL 工具之mysqldumper介绍
郭辉
概述
mysqldump无法并行，这点与Oracle的expdp相比，存在一定的劣势，但是开源的mysqldumper是一个很好的补充。
mydumper（http://www.mysqldumper.org/）是一个在GNU GPLv3许可下发布的高性能MySQL备份和恢复工具集。mydumper是多线程的，他创建一个mysql备份就比随mysql发布的mysqldump工具要快得多。
mydumper也有从源端服务器恢复二进制日志的能力。
mysqldumper优点
1.多线程，可以是转存数据快很多
2.mydumper的输出已于管理和分析，因为他的表和元数据是分开的单独文件。
3. 所有线程都维护有一直的快照，这边提供了精准的主从位置。
4.Mydumper支持Perl正则表达式，这样就既可以包括是数据库名和报名的模式匹配，也可以配置这种匹配。
5.通过名为myloader的多线程工具，mydumper工具集也可以从mydumper备份中恢复数据。
主要备份步骤概括
1.主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，以阻止DML语句写入，保证数据的一致性
2.读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用
3.N个（线程数可以指定，默认是4）dump线程 START TRANSACTION WITH CONSISTENT SNAPSHOT; 开启读一致的事物
4.dump non-InnoDB tables, 首先导出非事物引擎的表
5.主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁
6.dump InnoDB tables, 基于事物导出InnoDB表
7.事务结束
mysqldumper安装
数据库版本 : MySQL 5.7
操作系统版本: CentOS 7.8
软件地址:https://github.com/maxbube/mydumper/releases
yum -y install cmake glib2 pcre pcre-devel mysql-devel
-- 通过浏览器进行下载
yum -y install mydumper-0.9.5-2.el7.x86_64.rpm
[root@10-31-1-119 src]# yum -y install mydumper-0.9.5-2.el7.x86_64.rpm
已加载插件：fastestmirror, langpacks
正在检查 mydumper-0.9.5-2.el7.x86_64.rpm: mydumper-0.9.5-2.x86_64
mydumper-0.9.5-2.el7.x86_64.rpm 将被安装
正在解决依赖关系
--> 正在检查事务
---> 软件包 mydumper.x86_64.0.0.9.5-2 将被 安装
--> 解决依赖关系完成
依赖关系解决
============================================================================================================================================================================================================
Package 架构 版本 源 大小
============================================================================================================================================================================================================
正在安装:
mydumper x86_64 0.9.5-2 /mydumper-0.9.5-2.el7.x86_64 7.6 M
事务概要
============================================================================================================================================================================================================
安装 1 软件包
总计：7.6 M
安装大小：7.6 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : mydumper-0.9.5-2.x86_64 1/1
验证中 : mydumper-0.9.5-2.x86_64 1/1
已安装:
mydumper.x86_64 0:0.9.5-2
完毕！
[root@10-31-1-119 src]#
[root@10-31-1-119 src]# which mydumper
/usr/bin/mydumper
[root@10-31-1-119 src]#
[root@10-31-1-119 src]# which myloader
/usr/bin/myloader
参数介绍
mydumper参数详解
-B, --database 需要备份的数据库
-T, --tables-list 备份的表，使用英文逗号分隔
-O, --omit-from-file 包含要跳过的database.table条目列表的文件，每行一个（在应用正则表达式选项之前跳过）
-o, --outputdir 备份文件指定的目录
-s, --statement-size INSERT语句的字节数, 默认 1000000
-r, --rows 尝试将表的数据按多少行分隔为块，当使用--chunk-filesize时此参数关闭。
-F, --chunk-filesize 表的数据按照多少M分隔，输出为备份文件，单位为M
-c, --compress 压缩输出文件
-e, --build-empty-files 若表没有数据仍然生成一个空文件
-x, --regex 使用正则表达式
-i, --ignore-engines 忽略存储引擎，使用英文逗号分隔
-N, --insert-ignore 使用--insert-ignore备份行数
-m, --no-schemas 不导出表结构
-d, --no-data 卜导出表数据
-G, --triggers 备份触发器
-E, --events 备份事件(mysql的定时任务)
-R, --routines 备份自定义的存储过程和函数
-W, --no-views 不导出视图
-k, --no-locks 不执行临时共享锁。警告：此操作会引起不一致的备份。
--no-backup-locks 不使用percona的备份锁
--less-locking 在InnoDB表使用最小的锁表时间
-l, --long-query-guard 设置备份时候慢查询的计时器，单位妙，默认值60.
-K, --kill-long-queries 杀掉长查询语句
-D, --daemon 是以daemon模式使用
-I, --snapshot-interval dump快照间隔时间，默认60分钟，需要在daemon模式下
-L, --logfile 备份日志文件路径，默认是stdout输出
--tz-utc 在备份文件的顶部标注SET TIME_ZONE='+00:00'，当服务器位于不同的时区或者在不同时区的服务器之前迁移timestamp数据，默认是禁用的
--skip-tz-utc 默认值
--use-savepoints 使用保存点减少数据锁定的问题，需super权限
--success-on-1146 如表不存在则不增加错误计数，使用告警替换严重级别。
--lock-all-tables 使用lock table 锁定所有的表，替代FTWRL
-U, --updated-since 仅备份过去几天更新的表数据
--trx-consistency-only 仅事务一致性
--complete-insert insert语句是完整的，包括列名
-h, --host 连接的主机
-u, --user 连接的用户，需要必需的权限
-p, --password 连接用户的密码
-a, --ask-password 提示输入用户的密码
-P, --port 连接使用的端口
-S, --socket 连接使用的socket文件
-t, --threads 备份使用的并发线程，默认是4
-C, --compress-protocol mysql连接上使用压缩协议
-V, --version 展示mydumper程序的版本号
-v, --verbose 输出信息的等级 0 = silent, 1 = errors, 2 = warnings, 3 = info默认为2
--defaults-file 使用指定的配置文件
--ssl 连接使用SSL
--key SSL连接使用的秘钥文件路径
--cert SSL连接使用的认证文件路径
--ca SSL连接使用证书颁发机构文件的路径名
--capath 包含PEM格式的受信任SSL CA证书的目录的路径名
--cipher 用于SSL加密的允许密码列表
myloader参数详解
-d, --directory 数据恢复的目录
-q, --queries-per-transaction 每个事务的查询数，默认1000
-o, --overwrite-tables 若表存在则删除
-B, --database 需要恢复的数据库
-s, --source-db 恢复的数据
-e, --enable-binlog 恢复数据的时候开启binlog
-h, --host 连接的主机
-u, --user 连接的用户
-p, --password 连接的用户密码
-a, --ask-password 提示输入用户
-P, --port 连接的端口
-S, --socket 连接使用的socket文件
-t, --threads 恢复使用的线程数，默认是4
-C, --compress-protocol 在mysql连接的时候使用压缩协议
-V, --version 显示myloader的版本新
-v, --verbose 输出日志的等级, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2
--defaults-file 使用指定的配置文件
使用案例
测试数据准备
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 10.31.1.119 -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 10.31.1.119 -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 10.31.1.119 -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
mydumper正则拓展
-- 1.不备份 mysql和test两个数据库的数据
mydumper --regex '^(?!(mysql\.|test\.))'
-- 2.备份 mysql和test两个数据库的数据
mydumper --regex '^(mysql\.|test\.)'
参考
类似资料：