暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / [MYSQL] 怎么并发导入数据?

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

原创 大大刺猬 2024-02-29
861

导读

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

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

原理

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

MYSQLDUMP 文件格式

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

客户端和服务端 版本信息

字符集等变量设置
SQL_LOG_BIN

GLOBAL.GTID_PURGED  如果是8.0的话
CHANGE MASTER 

db1
	create table 建表
	insert into  插入数据
	trigger      触发器
	v1 view      视图
db2 ....


db1
	event
	routin
db2
	...
	

db1 
	view
db2 ....

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

关键词匹配

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

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


GTID 5.7 (结尾的)
--
-- GTID state at the end of the backup 
--



CHANGE MASTER
--
-- Position to start replication or point-in-time recovery from
--


建库语句
--
-- Current Database: `ibd2sql`
--

表结构
--
-- Table structure for table `AllTypesExample`
--


INSERT
--
-- Dumping data for table `AllTypesExample`
--


视图(和表放一起的) 8.0
--
-- Temporary view structure for view `v1`
--

视图(和表放一起的) 5.7
--
-- Temporary table structure for view `v_1`
--

EVENT
--
-- Dumping events for database 'db2'
--


ROUTINES
--
-- Dumping routines for database 'db2'
--


VIEW (最后的)
--
-- Final view structure for view `v_1`
--

并发导入原理

并发导入的原理比较简单, 其实就是把进程放后台就行. 主要是注意导入顺序
如果是 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 ...]]

拆分 mysqldump 导出的.sql文件.

positional arguments:
  files                 要拆分的 mysqldump.sql 文件

optional arguments:
  -h, --help            show this help message and exit
  --version, -v, -V     版本信息
  --database DATABASE   只导入的数据库
  --table TABLE         只导入的表
  --output-dir OUTPUT_DIR
                        输出的目录
  --presql PRESQL       每个.sql文件开头部分, 比如 set
                        sql_log_bin=off; set names utf8mb4
  --file FILENAME       要拆分的 mysqldump.sql 文件
  --log-file LOG_FILENAME
                        日志

导入脚本说明

说明

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;



2024-02-29 17:08:03 READ TABLE FOR mysql.columns_priv BEGIN
2024-02-29 17:08:03 READ TABLE FOR mysql.columns_priv FINISH. COST TIME: 0.0 seconds
2024-02-29 17:08:03 READ TABLE FOR mysql.component BEGIN
2024-02-29 17:08:03 READ TABLE FOR mysql.component FINISH. COST TIME: 0.0 seconds
2024-02-29 17:08:03 READ TABLE FOR mysql.db BEGIN
2024-02-29 17:08:03 READ TABLE FOR mysql.db FINISH. COST TIME: 0.0 seconds

........................................
日志太多就省略中间部分了
........................................
2024-02-29 17:08:07 READ ROUTINE FOR DATABASE t20240227_2 FINISH COST TIME: 0.0 seconds
2024-02-29 17:08:07 READ TABLE FOR test.test BEGIN
2024-02-29 17:08:07 READ TABLE FOR test.test FINISH. COST TIME: 0.0 seconds
2024-02-29 17:08:07 READ EVENT FOR DATABASE test BEGIN
2024-02-29 17:08:07 READ EVENT FOR DATABASE test FINISH COST TIME: 0.0 seconds
2024-02-29 17:08:07 READ ROUTINE FOR DATABASE test BEGIN
2024-02-29 17:08:07 READ ROUTINE FOR DATABASE test FINISH COST TIME: 0.0 seconds
2024-02-29 17:08:07 READ VIEW FOR DATABASE db1 BEGIN
2024-02-29 17:08:07 READ VIEW FOR DATABASE db1 FINISH COST TIME: 0.0 seconds
2024-02-29 17:08:07 READ VIEW FOR DATABASE t20240227 BEGIN
2024-02-29 17:08:07 READ VIEW FOR DATABASE t20240227 FINISH COST TIME: 0.0 seconds
2024-02-29 17:08:07 READ VIEW FOR DATABASE t20240227_2 BEGIN
2024-02-29 17:08:07 READ VIEW FOR DATABASE t20240227_2 FINISH COST TIME: 0.0 seconds
2024-02-29 17:08:07 READ ALL FINISH
2024-02-29 17:08:07 FILENAME     : /root/mysqldump_t20240226/t20240229_alldb.sql
2024-02-29 17:08:07 OUTPUT_DIR   : /root/mysqldump_t20240226/splitByddcw_20240229_170803
2024-02-29 17:08:07 LOG_FILENAME : /root/mysqldump_t20240226/SplitMysqlDumpSQL.log
2024-02-29 17:08:07 COST TIME    : 4.02 SECONDS.  TABLES COUNT: 412
2024-02-29 17:08:07 WARNING      : 0
17:08:07 [root@ddcw21 mysqldump_t20240226]#

就 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


********** BEGIN CHECK **************
2024-02-29 17:11:39 CONNCT SUCCESS.
2024-02-29 17:11:39 CHECK_CONN OK
2024-02-29 17:11:39 CHECK_GTID OK
2024-02-29 17:11:39 MYSQL VERSION: 8 0 28
2024-02-29 17:11:39 CHECK_VERSION OK
2024-02-29 17:11:39 disabled_storage_engines= OK
2024-02-29 17:11:39 log_bin_trust_function_creators=1 OK
........................................
日志太多就省略中间部分了
........................................
#################### IMPORT APP VIEWS #####################
2024-02-29 17:15:16 IMPORT splitByddcw_20240229_170803/views/db1.sql BEGIN...
2024-02-29 17:15:16 IMPORT splitByddcw_20240229_170803/views/db1.sql SUCCESS.
2024-02-29 17:15:16 IMPORT splitByddcw_20240229_170803/views/db1.sql FINISH. cost 0 seconds
2024-02-29 17:15:16 IMPORT splitByddcw_20240229_170803/views/t20240227.sql BEGIN...
2024-02-29 17:15:16 IMPORT splitByddcw_20240229_170803/views/t20240227.sql SUCCESS.
2024-02-29 17:15:16 IMPORT splitByddcw_20240229_170803/views/t20240227.sql FINISH. cost 0 seconds
2024-02-29 17:15:16 IMPORT splitByddcw_20240229_170803/views/t20240227_2.sql BEGIN...
2024-02-29 17:15:16 IMPORT splitByddcw_20240229_170803/views/t20240227_2.sql SUCCESS.
2024-02-29 17:15:16 IMPORT splitByddcw_20240229_170803/views/t20240227_2.sql FINISH. cost 0 seconds
2024-02-29 17:15:16 APP DATABASE COUNT: 24 APP TABLE COUNT: 377 APP DATA IMPORT COST_TIME: 215 SECONDS.
2024-02-29 17:15:16 IMPORT ALL FINISH. TOTAL COST TIME 217 SECONDS. FAILED COUNT: 1
2024-02-29 17:15:16 ERROR COUNT: 1
2024-02-29 17:15:16 
 splitByddcw_20240229_170803/dbs/gtid.sql IMPORT FAILED

耗时 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

导入类型 时间
原生导入 4min39s
4并发 3min37s
8并发 3min12s

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

总结

  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

mysqlpythonshell数据导入墨力计划
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

PYTHON解析MYSQL（共61篇）
用python解析mysql协议,模拟mysql连接,解析binlog,redolog,ibd文件等

评论