加快mydumper与myloader导出导入

最新推荐文章于 2023-08-28 09:50:14 发布
最新推荐文章于 2023-08-28 09:50:14 发布
阅读量5.7k 收藏 4
点赞数
分类专栏： -----MySQL相关特性 文章标签： Database SQL dba mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/leshami/article/details/48348481
版权

    mydumper与myloader是一个优秀的第三方mysql数据库逻辑备份恢复工具，使用多线程的导出与导入。弥补了mysqldump单线程的不足。本文描述的是如何加快mydumper与myloader的导出与导入供大家参考。

    有关mydumper与myloader其他事项可以参考：
        mydumper安装及安装故障汇总
        mydumper备份mysql数据库示例
        myloader恢复mysql数据库示例


1、基于MyIsam引擎导出导入
a、表不分块导出及导入
[root@GZAPP tmp]# mydumper -u inno -p xxx -B bsom -T tb_access_log -o /backup/tmp/
[root@GZAPP tmp]# ls -hltr   ###导出的数据文件为单个文件，大小在2.6GB
total 2.6G
-rw-r--r-- 1 root root 1.6K Jul 24 08:51 bsom.tb_access_log-schema.sql
-rw-r--r-- 1 root root  214 Jul 24 08:52 metadata
-rw-r--r-- 1 root root 2.6G Jul 24 08:52 bsom.tb_access_log.sql

###基于缺省线程数导入，且设定每个事务查询数为10000，此参数此时其实作用不大，因为表为myisam引擎
[root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -d /backup/tmp -v 3 -q 10000
[root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -d /backup/tmp -v 3 -q 10000
** Message: 4 threads created
** Message: Creating table `tempdb`.`tb_access_log`
** Message: Thread 4 shutting down
** Message: Thread 1 restoring `bsom`.`tb_access_log` part 0
** Message: Thread 3 shutting down
** Message: Thread 2 shutting down

root@localhost[tempdb]> show processlist;
+---------+---------+----------+--------+---------+--------+---------+------------------------------------------------+
| Id      | User    | Host     | db     | Command | Time   | State   | Info                                           |
+---------+---------+----------+--------+---------+--------+---------+------------------------------------------------+
| 4452079 | root    | localhost| tempdb | Query   |      0 | init    | show processlist                               |
| 4453793 | inno    | localhost| tempdb | Sleep   |    420 |         | NULL                                           |
| 4453794 | inno    | localhost| tempdb | Query   |      4 | update  | INSERT INTO `tb_access_log` VALUES (506873,"325|
+---------+---------+----------+--------+---------+--------+---------+------------------------------------------------+
###从上面的线程数可以看出只有一个单线程在执行insert操作


b、表分块导出及导入
###下面的示例中使用500MB进行分块
[root@GZAPP tmp]# mydumper -u inno -p xxx -B bsom -T tb_access_log -F 500 -o /backup/tmp/
[root@GZAPP tmp]# ls -hltr
total 2.6G     
-rw-r--r-- 1 root root 1.6K Jul 24 08:21 bsom.tb_access_log-schema.sql
-rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00001.sql
-rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00002.sql
-rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00003.sql
-rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00004.sql
-rw-r--r-- 1 root root 478M Jul 24 08:22 bsom.tb_access_log.00005.sql
-rw-r--r-- 1 root root  214 Jul 24 08:22 metadata
-rw-r--r-- 1 root root 241M Jul 24 08:22 bsom.tb_access_log.00006.sql
###由上可知，大表tb_access_log按接近500M被分割成了多个文件

[root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -t 6 -d /backup/tmp -v 3
** Message: 6 threads created
** Message: Creating database `tempdb`
** Message: Creating table `tempdb`.`tb_access_log`
** Message: Thread 1 restoring `bsom`.`tb_access_log` part 3
** Message: Thread 2 restoring `bsom`.`tb_access_log` part 5
** Message: Thread 5 restoring `bsom`.`tb_access_log` part 4
** Message: Thread 3 restoring `bsom`.`tb_access_log` part 6
** Message: Thread 4 restoring `bsom`.`tb_access_log` part 1
** Message: Thread 6 restoring `bsom`.`tb_access_log` part 2

#在下面的processlist可以看到，存在表级锁等待
+---------+-------+-----------+---------+---------+--------+-----------------------------+------------------------------------------------+
| Id      | User  | Host      | db      | Command | Time   | State                       | Info                                           |
+---------+-------+-----------+---------+---------+--------+-----------------------------+------------------------------------------------+
| 4452079 | root  | localhost | bsom    | Query   |      0 | init                        | show processlist                               |
| 4452167 | inno  | localhost | tempdb  | Sleep   |    769 |                             | NULL                                           |
| 4452168 | inno  | localhost | tempdb  | Query   |     36 | update                      | INSERT INTO `tb_access_log` VALUES (6367402,"0,|
| 4452169 | inno  | localhost | tempdb  | Query   |     21 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (12593865," |
| 4452170 | inno  | localhost | tempdb  | Query   |     26 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (15643029,""|
| 4452171 | inno  | localhost | tempdb  | Query   |      6 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (173947,"70 |
| 4452172 | inno  | localhost | tempdb  | Query   |     15 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (9490507,"7 |
| 4452173 | inno  | localhost | tempdb  | Query   |     30 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (3271602,"4 |
+---------+---------+-----------+---------+---------+--------+-----------------------------+----------------------------------------------+


c、调整myisam有关参数后导入
[root@GZ-APP-BAK01 tmp]# time myloader -u innobk -p InnoBK -B tempdb -t 6 -d /backup/tmp -v 3
** Message: 6 threads created
** Message: Creating table `tempdb`.`tb_mobile_access_log`
** Message: Thread 1 restoring `blossom`.`tb_mobile_access_log` part 3
** Message: Thread 6 restoring `blossom`.`tb_mobile_access_log` part 6
** Message: Thread 2 restoring `blossom`.`tb_mobile_access_log` part 5
** Message: Thread 3 restoring `blossom`.`tb_mobile_access_log` part 4
** Message: Thread 4 restoring `blossom`.`tb_mobile_access_log` part 1
** Message: Thread 5 restoring `blossom`.`tb_mobile_access_log` part 2
** Message: Thread 6 shutting down
** Message: Thread 5 shutting down
** Message: Thread 1 shutting down
** Message: Thread 2 shutting down
** Message: Thread 4 shutting down
** Message: Thread 3 shutting down

real    266m28.903s
user    0m6.008s
sys     0m1.681s

###调整以下相关参数，后尝试再次导入，
concurrent_insert  AUTO 改成 ALWAYS
bulk_insert_buffer_size 8388608 改成 256M
myisam_sort_buffer_size 67108864 改成 128M

[root@GZ-APP-BAK01 tmp]# time myloader -u innobk -p InnoBK -B tempdb -t 6 -o -d /backup/tmp -v 3
** Message: 6 threads created
** Message: Dropping table (if exists) `tempdb`.`tb_mobile_access_log`
** Message: Creating table `tempdb`.`tb_mobile_access_log`
** Message: Thread 1 restoring `blossom`.`tb_mobile_access_log` part 3
** Message: Thread 2 restoring `blossom`.`tb_mobile_access_log` part 6
** Message: Thread 3 restoring `blossom`.`tb_mobile_access_log` part 5
** Message: Thread 4 restoring `blossom`.`tb_mobile_access_log` part 4
** Message: Thread 6 restoring `blossom`.`tb_mobile_access_log` part 1
** Message: Thread 5 restoring `blossom`.`tb_mobile_access_log` part 2
** Message: Thread 2 shutting down
** Message: Thread 1 shutting down
** Message: Thread 6 shutting down
** Message: Thread 5 shutting down
** Message: Thread 3 shutting down
** Message: Thread 4 shutting down

real    253m42.460s   ###此时导入时间并无明显减少
user    0m5.924s
sys     0m1.637s


2、基于innodb引擎的导出导入
a、表未分块导出，数据文件大小为3.9GB
[root@GZAPP tmp]# ls -hltr
total 3.9G
-rw-r--r-- 1 root root 1.8K Jul 24 00:09 bscom.tb_message-schema.sql
-rw-r--r-- 1 root root 3.9G Jul 24 00:25 bscom.tb_message.sql
-rw-r--r-- 1 root root  215 Jul 24 09:14 metadata

###下面使用6个线程导入，实际上可以看到，只有1个线程在工作，因为数据文件只有1个
[root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -t 6 -d /backup/tmp -v 3
** Message: 6 threads created
** Message: Creating table `tempdb`.`tb_message`
** Message: Thread 1 restoring `bscom`.`tb_message` part 0
** Message: Thread 5 shutting down
** Message: Thread 2 shutting down
** Message: Thread 6 shutting down
** Message: Thread 3 shutting down
** Message: Thread 4 shutting down


b、表分块导出
[root@GZAPP tmp]# mydumper -u inno -p xxx -B bscom -T tb_message -F 500 -o /backup/tmp/
[root@GZAPP tmp]# ls -hltr
total 3.9G
-rw-r--r-- 1 root root 1.8K Jul 24 09:55 bscom.tb_message-schema.sql
-rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00001.sql
-rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00002.sql
-rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00003.sql
-rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00004.sql
-rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00005.sql
-rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00006.sql
-rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00007.sql
-rw-r--r-- 1 root root 481M Jul 24 09:55 bscom.tb_message.00008.sql
-rw-r--r-- 1 root root  135 Jul 24 09:55 metadata
-rw-r--r-- 1 root root  93M Jul 24 09:55 bscom.tb_message.00009.sql

###下面尝试使用6线程导入，可以看到有6个线程在并发导入
[root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -t 6 -d /backup/tmp/ -v 3
** Message: 6 threads created
** Message: Creating database `tempdb`
** Message: Creating table `tempdb`.`tb_message`
** Message: Thread 2 restoring `bscom`.`tb_message` part 5
** Message: Thread 1 restoring `bscom`.`tb_message` part 9
** Message: Thread 3 restoring `bscom`.`tb_message` part 1
** Message: Thread 4 restoring `bscom`.`tb_message` part 8
** Message: Thread 5 restoring `bscom`.`tb_message` part 4
** Message: Thread 6 restoring `bscom`.`tb_message` part 6
** Message: Thread 1 restoring `bscom`.`tb_message` part 7
** Message: Thread 6 restoring `bscom`.`tb_message` part 3
** Message: Thread 2 restoring `bscom`.`tb_message` part 2
** Message: Thread 3 shutting down
** Message: Thread 5 shutting down
** Message: Thread 4 shutting down
** Message: Thread 1 shutting down
** Message: Thread 2 shutting down
** Message: Thread 6 shutting down


3、小结
a、mydumper在导出的时候可以根据服务器可用资源来合理地设置线程数。
b、mydumper在导出的时候尽可能地指定chunk-filesize或者rows参数以分块导出。
c、myloader在针对myisam引擎时建议调整相关参数至合理值以提高无法提高性能，主要是表级锁的问题。
d、myloader在针对innodb引擎时建议调整参数至合理值以提高性能，如以下参数等：
  innodb_buffer_pool_size
  innodb_flush_log_at_trx_commit
  innodb_log_buffer_size
e、通过使用分块导出与导入可以显著利用并发来加快inndbo表导入。  
f、注意mydumper导出时不会导出存储过程，函数，触发器等。

Leshami
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
go-mydumper：一种多线程MySQL备份和还原工具，比mysqldump更快
02-02
go-dumper go-mydumper是一个多线程MySQL备份和还原工具，它与布局中的兼容。 建立 $git clone https://github.com/xelabs/go-mydumper $cd go-mydumper $make build $./bin/mydumper -h $./bin/myloader -h 测试 $make test 用法 mydumper ./bin/mydumper -h Usage: ./bin/mydumper -c conf/mydumper.ini.sample -c string config file Examples: $./bin/mydumper -c conf/mydumper.ini.sample 2017/10/25 13:12:52.933391 dumper.go:35: [INFO] dumping.database[sbtest].schema... 2017/10/25 13:12:52.937743 dumper.go:45:
9、Mydumper和Myloader多线程备份恢复
weixin_33912453的博客
06-19 250
<--目录-->1）mydumper介绍2）mydumper特性3）mydumper参数4）mydumper安装方法5) mydumper效率测试6）mydumper导出实战7）mydumper导出的文件介绍8）myloader参数9）myloader导入实战【mydumper介绍】mydumper是一个针对 MySQL 和 Drizzle 的高性能多线程备份和...
myloader导入更快吗？并没有......
最新发布
GreatSQL2021的博客
08-28 133
前几天我用MySQL官网提供的airportdb库中的weatherdata表做测试，结论是相比原生快了约5倍。群里有小伙伴反驳说用myloader更香，于是就有了本次测试。
mydumper及myloader参数说明
云淡风轻
07-27 4001
mydumper Usage: mydumper [OPTION...] multi-threaded MySQL dumping Help Options: -?, --help Show help options Application Options: -B, --database 需要备份的数据库，一个数据库一条命...
MySQL多线程导入导出 —— mydumper & myloader 简介及常见用法
Hehuyi_In的博客
04-07 7787
一、 简介 之前介绍了mysqldump和mysqlpump两种逻辑备份工具，但还是各有缺点。这里介绍一个更强大的逻辑备份工具mydumper & myloader，前者用于备份或者用于恢复。 mysqldumphttps://blog.csdn.net/Hehuyi_In/article/details/90045274 mysqlpumphttps://blog.csdn...
MySQL5.7数据导入/导出、TiDB数据回迁移
Mr.Wang的博客
05-12 1386
本文灵感来与现实中项目需求：TiDB分布式数据库全量迁移至TDSQL 用到的迁移工具如下： 1.Dumpling（mydumper 的全新升级版） 2.Myloader（mydumper附带的导入命令,需要安装mydumper） 注意：鉴于TDSQL基于MySQL5.7.x系列二开，所以本文迁移目标采用MySQL5.7.x来完成！
MySQL逻辑备份工具mysqldump,mysqlpump,mydumper详细介绍，建议收藏
白话机器学习
08-23 1153
mysqlpump的备份是基于表并行的，对于每张表的导出只能是单个线程的，这里会有个限制是如果某个数据库有一张表非常大，可能大部分的时间都是消耗在这个表的备份上面，并行备份的效果可能就不明显。该参数默认开启，表示快递启动–add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset选项，通过 --skip-opt 关闭。
mysql文件导出NULL值处理_Mysql select into outfile NULL值导出的处理方法
weixin_39904612的博客
01-19 1445
目录1简介... 12 准备... 12.1 环境说明... 13 安装... 24 配置... 25 使用... 25.1 验证实验... 25.2 问题分析... 46 延展... 47 总结... 4Mysql select into outfile NULL值导出的处理方法1简介基于mysql select into outfile的方式导出文本文件的处理方法，这里主要针对里面NULL的处...
Mydumper的使用.pdf
04-17
包括前提环境的配置，软件的原理&安装&使用，实验截图
mydumper：mydumper官方项目
02-22
什么是mydumper？ 为什么？ 并行性（因此，速度）和性能（避免了昂贵的字符集转换例程，整体上高效的代码） 易于管理输出（用于表的单独文件，转储元数据等，易于查看/解析数据） 一致性-维护所有线程的快照，提供准确的主从日志位置等 可管理性-支持PCRE用于指定数据库和表的包含和排除 如何安装mydumper / myloader？ 首先从获取正确的URL，然后： 红帽/ Centos yum install https://github.com/maxbube/mydumper/releases/download/v0.10.1/mydumper-0.10.1-2.el7.x86_64.rpm yum install https://github.com/maxbube/mydumper/releases/download/v0.10.1/mydumper-0.10.1-2.
mydumper-0.9.5-2.el7.x86_64.rpm
12-26
centos7 64位mydumper工具安装包，安装后myloader和mydumper命令在/usr/bin/下。 供mysql5.7版本数据迁移使用 [root@localhost ~]# myloader -V myloader 0.9.5, built against MySQL 5.7.21-21 [root@localhost ~]# mydumper -V mydumper 0.9.5, built against MySQL 5.7.21-21
myloader
03-13
myloader
MySQL [ERROR] Table 'mysql.user' doesn't exist
热门推荐
乐沙弥的世界
12-08 11万+
一次源码新装的mysql,由于没有复制my-default.cnf到/etc/my.cnf位置，在启动mysql的时候碰到了无法打开mysql.user表的错误。具体错误为：[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist。下面是该错误的描述及其解决办法。
MySQL 修改用户密码及重置root密码
乐沙弥的世界
10-05 8万+
为数据库用户修改密码是DBA比较常见的工作之一。对于MySQL用户账户的密码修改，有几种不同的方式，推荐的方式使用加密函数来修改密码。本文主要描述了通过几种不同的方式来修改用户密码以及mysql root账户密码丢失(重置root密码)的处理方法。1、密码修改的几种方法a、可以在创建用户的时候指定密码，以及直接使用grant创建用户的时候指定密码。   对于已经存在的用户直接使用grant方式也可
MySQL 错误日志(Error Log)
乐沙弥的世界
10-03 7万+
同大多数关系型数据库一样，日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助我们定义mysqld内部发生的事情，数据库性能故障，记录数据的变更历史，用户恢复数据库等等。本文主要描述错误日志文件。1、MySQL日志文件系统的组成  a、错误日志：记录启动、运行或停止mysqld时出现的问题。  b、
MySQL 慢查询日志(Slow Query Log)
乐沙弥的世界
10-06 6万+
同大多数关系型数据库一样，日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助我们定位mysqld内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等。本文主要描述通用查询日志。1、MySQL日志文件系统的组成   a、错误日志：记录启动、运行或停止mysqld时出现的问题。
MySQL 通用查询日志(General Query Log)
乐沙弥的世界
10-04 4万+
同大多数关系型数据库一样，日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助我们定义mysqld内部发生的事情，数据库性能故障，记录数据的变更历史，用户恢复数据库等等。本文主要描述通用查询日志。1、MySQL日志文件系统的组成   a、错误日志：记录启动、运行或停止mysqld时出现的问题。
MySQL create table as与create table like对比
乐沙弥的世界
07-08 3万+
在MySQL数据库中，关于表的克隆有多种方式，比如我们可以使用create table ..as .. ，也可以使用create table .. like ..方式。然而这2种不同的方式还是有些差异的，他的差异到底在哪里呢，本文通过演示对此展开描述。1、mysql sakila表上的结构--actor表状态 robin@localhost[sakila]> show table
Mysql的into outfile如何并行导出
05-29
Mysql的INTO OUTFILE命令并不支持并行导出，因为它是在单个线程中执行的。但是，你可以通过多个线程同时导出多个文件来实现并行导出。 一种可行的方法是使用shell脚本和Mysql命令行工具。你可以编写一个脚本，该脚本在不同的Mysql连接中运行多个导出命令，每个命令导出不同的数据集。这些导出命令可以在不同的终端窗口中执行，以确保它们同时运行。 例如，你可以编写一个脚本，该脚本使用Mysql命令行工具同时连接到不同的数据库，然后在每个连接中运行导出命令。这将使你能够同时导出多个数据集，从而实现并行导出。 除此之外，你也可以使用第三方工具，例如Mydumper和Myloader，这些工具支持并行导出和导入。这些工具可以帮助你更高效地导出和导入大量数据。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值