让mysqldump变成并发导出导入的魔法

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

1. 简介

取名mypumpkin,是python封装的一个让mysqldump以多线程的方式导出库表,再以mysql命令多线程导入新库,用于成倍加快导出,特别是导入的速度。这一切只需要在 mysqldump 或 mysql 命令前面加上 mypumpkin.py 即可,所以称作魔法。

项目地址https://github.com/seanlook/mypumpkin

该程序源于需要对现网单库几百G的数据进行转移到新库,并对中间进行一些特殊操作(如字符集转换),无法容忍mysqldump导入速度。有人可能会提到为什么不用 mydumper,其实也尝试过它但还是放弃了,原因有:

  1. 不能设置字符集
    mydumper强制使用 binary 方式来连接库以达到不关心备份恢复时的字符集问题,然而我的场景下需要特意以不同的字符集导出、再导入。写这个程序的时候正好在公众号看到网易有推送的一篇文章 (解密网易MySQL实例迁移高效完成背后的黑科技),提到他们对mydumper的改进已支持字符集设置,可是在0.9.1版本的patch里还是没找到。
  2. 没有像 mysqldump 那样灵活控制过滤选项(导哪些表、忽略哪些表)
    因为数据量之巨大,而且将近70%是不变更的历史表数据,这些表是可以提前导出转换的;又有少量单表大于50G的,最好是分库导出转换。mydumper 不具备 mysqldump 这样的灵活性
  3. 对忽略导出gtid信息、触发器等其它支持
    阿里云rds 5.6 导出必须要设置 set-gtid-purged=OFF

另外有人还可能提到 mysqlpump —— 它才是我认为mysqldump应该具有的模样,语法兼容,基于表的并发导出。但是只有 mysql服务端 5.7.9 以上才支持,这就是现实和理想的距离。。。

2. 实现方法

首先说明,mysqldump的导出速度并不慢,经测试能达到50M/s的速度,10G数据花费3分钟的样子,可以看到瓶颈在于网络和磁盘IO,再怎样的导出工具也快不了多少,但是导入却花了60分钟,磁盘和网络大概只用到了20%,瓶颈在目标库写入速度(而一般顺序写入达不到IOPS限制),所以mypumpkin就诞生了 —— 兼顾myloader的导入速度和mysqldump导出的灵活性。

用python构造1个队列,将需要导出的所有表一次放到队列中,同时启动N个python线程,各自从这个Queue里取出表名,subprocess调用操作系统的mysqldump命令,导出数据到以 dbname.tablename.sql 命名的文件中。load in 与 dump out 类似,根据指定的库名或表名,从dump_dir目录找到所有sql文件,压进队列,N个线程同时调用mysql构造新的命令,模拟 < 操作。

参数解析从原来自己解析,到改用argparse模块,几乎做了一次重构。
对于没有指定--tables的情况,程序会主动去库里查询一下所有表名,然后过滤进队列。

load in目标库,选项做到与dump out一样丰富,可以指定导入哪些db、哪些表、忽略哪些表。

其中的重点是做到与原mysqldump兼容,因为需要对与表有关的选项(-B, -A, --tables, --ignore=),进行分析并组合成新的执行命令,考虑的异常情况非常多。

3. 限制

  1. 重要:导出的数据不保证库级别的一致性

    1. 对历史不变表,是不影响的
    2. 具体到一个表能保证一致性,这是mysqldump本身采用哪些选项决定的
    3. 不同表导出动作在不同的mysqldump命令中,无法保证事务。
      在我的案例场景下,是有开发同学辅助使用一套binlog解析程序,等完成后重放所有变更,来保证最终一致性。

另,许多情况下我们导数据,并不需要完整的或者一致的数据,只是用于离线分析或临时导出,重点是快速拿数据给到开发。

  1. 不寻常选项识别
    程序已经尽力做到与mysqldump命令兼容,只需要加上 mypumpkin.py、指定dump-dir,就完成并发魔法,但有些情况的参数不方便解析,暂不支持格式:
db1 table1 table2
db2 db3

即以上无法在命令行下判断 db1、table1 是库名还是表面,用的时候只需记住“[-A|-B], [--tables], [--ignore-table]”三组,必须出现一个:db1 table1 table2改成db1 --tables table1 table2db2改成-B db2 db3

  1. 密码暂只能显式输入

4. 使用说明

安装基于python 2.7 开发,其它版本没测。需要按 MySQLdb 库。

4.1 help

./mypumpkin.py --help
Only mysqldump or mysql allowed after mypumpkin.py

usage: mypumpkin.py {mysqldump|mysqls} [--help]

This's a program that wrap mysqldump/mysql to make them dump-out/load-in
concurrently. Attention: it can not keep consistent for whole database(s).

optional arguments:
  --help                show this help message and exit
  -B db1 [db1 ...], --databases db1 [db1 ...]
                        Dump one or more databases
  -A, --all-databases   Dump all databases
  --tables t1 [t1 ...]  Specifiy tables to dump. Override --databases (-B)
  --ignore-table db1.table1 [db1.table1 ...]
                        Do not dump the specified table. (format like
                        --ignore-table=dbname.tablename). Use the directive
                        multiple times for more than one table to ignore.
  --threads =N          Threads to dump out [2], or load in [CPUs*2].
  --dump-dir DUMP_DIR   Required. Directory to dump out (create if not exist),
                        Or Where to load in sqlfile

At least one of these 3 group options given: [-A,-B] [--tables] [--ignore-table]
  • --dump-dir,必选项,原来用的shell标准输入输出 > or < 不允许使用。dump-dir指定目录不存在时会尝试自动创建。
  • --threads=N,N指定并发导出或导入线程数。dump out 默认线程数2, mypumpkin load in 默认线程数是 cpu个数 * 2。
    注:线程数不是越大越好,这里主要的衡量指标是网络带宽、磁盘IO、目标库IOPS,最好用 dstat 观察一下。
  • -B, --tables--ignore-table,使用与mysqldump相同,如:

    1. 在mysqldump里面,--tables会覆盖--databases/-B选项
    2. 在mysqldump里面,--tables--ignore-table不能同时出现
    3. 在mysqldump里面,如果没有指定-B,则--tables--ignore-table必须紧跟db名之后
  • 其它选项,mypumpkin会原封不动的保留下来,放到shell去执行。所以如果其它选项有错误,检查是交给原生mysqldump去做的,执行过程遇到一个失败则会退出线程。

4.2 example

导出:

## 导出源库所有db到visit_dumpdir2目录 (不包括information_schema和performance_schema)
$ ./mypumpkin.py mysqldump -h dbhost_name -utest_user -pyourpassword -P3306 \
 --single-transaction --opt -A --dump-dir visit_dumpdir2

## 导出源库db1,db2,会从原库查询所有表名来过滤
$ ./mypumpkin.py mysqldump -h dbhost_name -utest_user -pyourpassword -P3306 \
 --single-transaction --opt -B db1 db2 --dump-dir visit_dumpdir2

## 只导出db1库的t1,t2表,如果指定表不存在则有提示
$ ./mypumpkin.py mysqldump -h dbhost_name -utest_user -pyourpassword -P3306 \
 --single-transaction --opt -B db1 --tables t1 t2 --dump-dir visit_dumpdir2

## 导出db1,db2库,但忽略 db1.t1, db2.t2, db2.t3表
## mysqldump只支持--ignore-table=db1.t1这种,使用多个重复指令来指定多表。这里做了兼容扩展
$ ./mypumpkin.py mysqldump -h dbhost_name -utest_user -pyourpassword --single-transaction \
 --opt -B db1 db2 --ignore-table=db1.t1 --ignore-table db2.t2 db2.t3 --dump-dir visit_dumpdir2 (如果-A表示全部db)

## 不带 -A/-B
$ ./mypumpkin.py mysqldump -h dbhost_name -utest_user -pyourpassword -P3306 \
 --single-transaction --opt db1 --ignore-table=db1.t1 --dump-dir=visit_dumpdir2

## 其它选项不做处理
$ ./mypumpkin.py mysqldump -h dbhost_name -utest_user -pyourpassword -P3306 \
 --single-transaction --set-gtid-purged=OFF --no-set-names --skip-add-locks -e -q -t -n --skip-triggers \
 --max-allowed-packet=134217728 --net-buffer-length=1638400 --default-character-set=latin1 \
 --insert-ignore --hex-blob --no-autocommit \
 db1 --tables t1 --dump-dir visit_dumpdir2

导入:
-A, -B, --tables, --ignore-table, --threads, --dump-dir用法与作用与上面完全相同,举部分例子:

## 导入dump-dir目录下所有表
$ ./mypumpkin.py mysql -h dbhost_name -utest_user -pyourpassword --port 3307 -A \
 --dump-dir=visit_dumpdir2

## 导入db1库(所有表)
$ ./mypumpkin.py mysql -h dbhost_name -utest_user -pyourpassword --port 3307 -B db1 \
 --dump-dir=visit_dumpdir2

## 只导入db.t1表
$ ./mypumpkin.py mysql -h dbhost_name -utest_user -pyourpassword --port 3307 \
 --default-character-set=utf8mb4 --max-allowed-packet=134217728 --net-buffer-length=1638400 \
 -B db1 --tables t1 --dump-dir=visit_dumpdir2

## 导入db1,db2库,但忽略db1.t1表(会到dump-dir目录检查db1,db2有无对应的表存在,不在目标库检查)
$ ./mypumpkin.py mysql -h dbhost_name -utest_user -pyourpassword --port 3307 \
 -B db1 db2 --ignore-table=db1.t1 --dump-dir=visit_dumpdir2

5.速度对比

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5月前
|
SQL 关系型数据库 MySQL
|
SQL 关系型数据库 MySQL
MySQL数据库的数据导入和导出操作指南
MySQL数据库的数据导入和导出操作指南
|
3月前
|
SQL 关系型数据库 MySQL
Navicate,数据库,Mysql,改表,4月29日Finished - Unsuccessfully,导出数据不妨,右键,备份一下Mysql数据库的内容,你想导入和导出数据不如,用查询的方式去做
Navicate,数据库,Mysql,改表,4月29日Finished - Unsuccessfully,导出数据不妨,右键,备份一下Mysql数据库的内容,你想导入和导出数据不如,用查询的方式去做
|
SQL 关系型数据库 MySQL
mysql 导出查询结果/导入.sql文件数据/mysqldump导出表or数据
mysql 导出查询结果/导入.sql文件数据/mysqldump导出表or数据
760 0
mysql 导出查询结果/导入.sql文件数据/mysqldump导出表or数据
|
SQL 搜索推荐 关系型数据库
MySQL导出sql脚本文件
sql脚本文件在我们做项目时,特别是学习别人的开源项目时经常需要进行导入导出操作,才能在自己的系统上跑起来,这篇文章主要介绍如何导出sql脚本文件,具体操作如下,附带截图详解。
620 0
MySQL导出sql脚本文件
|
SQL 关系型数据库 MySQL
MySQL 导出 表结构,执行 .sql 文件导入结构或者数据
MySQL 导出 表结构,执行 .sql 文件导入结构或者数据
|
SQL Shell 数据库
shell 自动导出导入数据库,将导出的格式为 : 数据库名+时间.sql
mysqldump 自动导出mysql8 or mariadb10x 数据库工具,导入数据库工具,mysql 创建于删除数据库
176 0
|
SQL 关系型数据库 MySQL
|
SQL 关系型数据库 MySQL
用户指南—数据导入和导出—使用mysqldump导入导出数据
本文介绍了通过mysqldump工具将PolarDB-X数据导入导出的几种常见场景和详细操作步骤。 PolarDB-X支持MySQL官方数据导出工具mysqldump。mysqldump命令的详细说明请参见MySQL 官方文档。
141 0
|
数据库
Msql 导出导入数据库
导出数据库mysqldump eatfun_only -uroot -p123456 > eatfun_only.sql mysqldump eatfun_new -ureadonly -h127.0.
1175 0