文章目录

概述一.mysqldumper安装二.参数介绍2.1 mydumper参数详解2.2 myloader参数详解 三.使用案例3.1 测试数据准备3.2 备份test库下的t1和t2两张表3.3 恢复数据 四.mydumper正则拓展参考文献:

概述

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

二.参数介绍

2.1 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加密的允许密码列表

2.2 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 使用指定的配置文件

三.使用案例

3.1 测试数据准备

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;

3.2 备份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

3.3 恢复数据

删除数据

[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\.)'

参考文献:

1.https://blog.csdn.net/vkingnew/article/details/83010053 2.https://blog.csdn.net/weixin_30445169/article/details/96141753