[MYSQL] 怎么并发导入数据?

导读

通常我们会使用 mysqldump 导出数据, 然后使用mysql命令导入. 我们可以根据 上一篇文章 提供的脚本来查看进度, 但是该等的时间还是不能少.

mysql导入是单线程的, 很慢. 那么我们可以把.sql文件拆分为多个文件, 然后并发导入, 这样就快很多了.
其实之前也测试过的, 但是效果不佳, 开32并发速度都是差不多的… 因为当时是把每个INSERT语句都均匀的分在每个文件. 这样并不会提高导入速度.

原理

吸取了上次的教训, 这次就按照 每张表一个文件 来拆分. 然后并发导入, 这样同时导入, 速度就会提示.

MYSQLDUMP 文件格式

mysql 5.7 和 8.0 的mysqldump导出的数据是差不多的, 只有一点点区别
格式如下:

客户端和服务端 版本信息

GLOBAL.GTID_PURGED 如果是5.7的话
字符集等变量设置(改回去)


关键词匹配

那我们就可以根据这个格式来写相关脚本了.
官方可能也考虑到了这一点, 还是提供了相关的关键字的.
关键字关系如下

GTID 8.0 (开头的)
--
-- GTID state at the beginning of the backup
--

并发导入原理

并发导入的原理比较简单, 其实就是把进程放后台就行. 主要是注意导入顺序
如果是 5.7 导入到8.0 的话, 需要注意统计信息表是的DROP和CREATE是无法执行的, 可以人工注释掉,然后导入, 或者人工收集统计信息.

脚本说明

.sql文件拆分脚本

说明

MysqlDumpSplitSQL.py使用python2编写的(python3有点编码问题). 可以将mysqldump导出的.sql文件拆分为多个文件, 按照如下结构分布:
如果是8.0的话, 还有dbs/special.sql记录统计信息

splitByddcw_20240229_165143
├── dbs
│   ├── chartest
│   │   ├── appmap.sql
│   │   └── app.sql
│   ├── create.sql
│   ├── gtid.sql
│   ├── master_info.txt
│   ├── T20240227_2
│   └── test
│   └── test.sql
├── events
│   ├── chartest.sql
│   └── test.sql
├── routines
│   ├── chartest.sql
│   ├── db1.sql
└── views
├── db1.sql
└── t20240227.sql


速度还是很快的(嘎嘎快), 1.7GB的文件只要4秒左右就能拆分完.

使用方法

python MysqlDumpSplitSQL.py t20240228_alldb.sql


如果只要某张表的话, 还可以使用 --table tablename 来匹配需要的表. 支持正则表达式
详细用法如下

python MysqlDumpSplitSQL.py -h
usage: MysqlDumpSplitSQL.py [-h] [--version] [--database DATABASE]
[--table TABLE] [--output-dir OUTPUT_DIR]
[--presql PRESQL] [--file FILENAME]
[--log-file LOG_FILENAME]
[files [files ...]]

导入脚本说明

说明

testparallel.sh 按照mysqldump导出的顺序做导入操作, 还额外检查了下 某些特殊参数, 然后开了并发.差不多就这些. 并发逻辑就是 放后台, 然后循环检查 如果跑完了, 就下一个导入开始. 由于是基于文件级别的, 所以存在短板效应.

使用方法

修改脚本中的连接信息 和 并发度 等信息, 然后执行脚本,后面跟上上面拆分的路径就行. 剩下的脚本自己去识别.

sh testparallel.sh splitByddcw_20240229_165143


修改的信息参考如下:

#可以修改的参数
CONCURRENCY=4 #并发数量
SLEEP_INTERNAL="0.01" #每隔 SLEEP_INTERNAL 秒, 检查一次 是否有导入完成的进程
IGNORE_GTID_CHECK="0" #如果为1, 表示不检查GTID是否存在
IGNORE_FUCNTION_CREATOR="0" #如果为1, 表示不检查log_bin_trust_function_creators是否为1
IGNORE_DISABLE_ENGINE="0" #如果为1, 表示不检查disabled_storage_engines是否含MyISAM
LOGFILE="import.log" #导入日志. 和控制台输出的内容一样
DIRNAME=$1 #已经拆分了的 mysqldump 导出的SQL文件目录


脚本演示

说这么多, 还不如直接演示一下, 由于打印的日志太长了, 这里就把中间的信息省略了.
为了验证结果正确性, 我们要在导入前后使用如下命令来记录相关表的校验值, 然后验证我们的脚本确实没毛病!
校验命令参考如下:

导入前数据校验:
mysql -h127.0.0.1 -P3314 -p123456 -NB -e "select concat('CHECKSUM TABLE \`',TABLE_SCHEMA,'\`.\`',TABLE_NAME,'\`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN('sys','mysql','information_schema','performance_schema');" | sort | mysql -h127.0.0.1 -P3314 -p123456 > /tmp/before_check.txt

导入后数据校验:
mysql -h127.0.0.1 -P3314 -p123456 -NB -e "select concat('CHECKSUM TABLE \`',TABLE_SCHEMA,'\`.\`',TABLE_NAME,'\`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN('sys','mysql','information_schema','performance_schema');" | sort | mysql -h127.0.0.1 -P3314 -p123456 > /tmp/after_check.txt

前后数据比较
diff /tmp/before_check.txt /tmp/after_check.txt


导出

导出没啥好说的, 直接全库导出即可

17:05:28 [root@ddcw21 mysqldump_t20240226]#mysqldump -h127.0.0.1 -P3314 -p123456 --events --triggers --single-transaction --routines --master-data=2 -A > t20240229_alldb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

17:06:48 [root@ddcw21 mysqldump_t20240226]#
17:06:48 [root@ddcw21 mysqldump_t20240226]#ll -ahrlt t20240229_alldb.sql
-rw-r--r-- 1 root root 1.7G Feb 29 17:06 t20240229_alldb.sql


拆分

直接使用脚本拆即可

17:07:55 [root@ddcw21 mysqldump_t20240226]#python MysqlDumpSplitSQL.py t20240229_alldb.sql
2024-02-29 17:08:03 CLIENT_VERSION: 8.0.28, SERVER_VERSION: 8.0.28FILE_HEADER:
-- AUTO SPLIT MYSQLDUMP FILE BY DDCW @https://github.com/ddcw
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

就 4 秒 -_-

并发导入

直接导入的话, 由于GTID问题, 会导不进去

17:09:54 [root@ddcw21 mysqldump_t20240226]#sh testparallel.sh splitByddcw_20240229_170803

********** BEGIN CHECK **************
2024-02-29 17:10:03 CONNCT SUCCESS.
2024-02-29 17:10:03 CHECK_CONN OK
2024-02-29 17:10:03 CURRENT GTID: b68e2434-cd30-11ec-b536-000c2980c11e:1-35 FAILED!
17:10:03 [root@ddcw21 mysqldump_t20240226]#


所以我们要修改脚本, 把忽略GTID给打开, 把IGNORE_FUCNTION_CREATOR也打开吧.

IGNORE_GTID_CHECK="1"
IGNORE_FUCNTION_CREATOR="1"


然后再次导入

17:11:38 [root@ddcw21 mysqldump_t20240226]#sh testparallel.sh splitByddcw_20240229_170803

耗时 217秒 , 还行… 主要是那种大表太慢了(170秒)…
这里有个ERROR, 是gtid.sql导入失败了. 我们不用管它. 因为是我们自己忽略的. 当然也可以选择reset master然后再导入这个gtid.sql也是可以的

正常导入

直接导入会报错, 有gtid问题, 所以要先reset master

17:19:53 [root@ddcw21 mysqldump_t20240226]#mysql -h127.0.0.1 -P3314 -p123456 < t20240229_alldb.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3546 (HY000) at line 26: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

reset master后, 开始导入 (加个time, 不然看不到时间, 不好比较)

17:21:22 [root@ddcw21 mysqldump_t20240226]#time mysql -h127.0.0.1 -P3314 -p123456 < t20240229_alldb.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

real 4m39.067s
user 0m11.893s
sys 0m4.000s
17:26:02 [root@ddcw21 mysqldump_t20240226]#


感觉上就比较慢了.

验证

我这里忘了校验前后数据一致性了…
之前做测试的时候 校验过的, 是一致的.

时间对比

拆分时间4秒 加上 导入217秒, 耗时3min37s

效果还是有的, 但是有短板效应.

总结

1. mysql并发导入确实能提升速度, 但是存在短板效应, 如果有一张表占比特别大的话, 并发导入的优势就不明显.
2. mysql 5.7和8.0 的mysqldump命令导出的文件还是有区别的. 对于统计信息表, 5.7 是含有DROP和CREATE的, 但 8.0 只有 INSERT IGNORE INTO
3. MySQL 5.7和8.0的GTID位置也不一样.
4. 4并发就够了, 并发多了, 提升不是很明显(IO瓶颈了)

附相关源码

源码太多了, 这里就直接放相关链接了.
github地址: https://github.com/ddcw/ddcw/tree/master/python/MySQL%E5%B9%B6%E5%8F%91%E5%AF%BC%E5%85%A5
墨天轮地址: https://www.modb.pro/doc/125805