1. Toy模板网首页
  2. >Toy博客

mysql 超大 sql 文件导入过程

1年前作者：哈哈虎123分类：Toy博客阅读(8)违法举报

这篇具有很好参考价值的文章主要介绍了mysql 超大 sql 文件导入过程。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

问题

最近遇到 2 个超大 sql 文件导入，好一通折腾

文档在哪里

调优参数太多，文档都看不过来
找到这些参数也费劲,

  1. ubuntu 在 /etc/mysql/mysql.conf.d/mysqld.cnf 中找到这个链接

    ......
#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

  2. centos 7 在 /etc/my.cnf 中找到这个链接

     # For advice on how to change settings please see
 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

  3. 官方调优文档
    14.15 InnoDB Startup Options and System Variables

  4. 耐心去阅读文档中那么多参数，需要勇气！
    最终我也是从这里 mysql使用多cpu抄来的

     # wzh 20230814
 # Set the number of CPUs to be used by MySQL 
 #  # to half of the total available CPUs
 innodb_thread_concurrency = 2
 #
 # Set the number of buffer pool instances
 innodb_buffer_pool_instances = 2
 # #  
 # Set the size of the query cache
 query_cache_size = 64M
 #
 # wzh 20230814 
 bulk_insert_buffer_size = 2G
 innodb_log_buffer_size = 2G
 innodb_autoinc_lock_mode = 2
 #

调优后 三种导入方式对比

  1. 方式一: 使用 mysql source 命令，直接导入一个大文件 (18G)

    为了统计用时和绘画内部优化，将实际mysqldump 文件包含到 all.sql

     # cat all.sql
 SET GLOBAL innodb_lru_scan_depth=256;
 set session sql_mode = 'ALLOW_INVALID_DATES';
 SET autocommit=0;
 SET unique_checks=0;
 SET foreign_key_checks=0;
 
 show global variables like 'innodb_lru_scan_depth';
 show variables like '%sql_mode%';  
 show variables like '%autocommit';
 show variables like '%unique_checks';
 show variables like '%foreign_key_checks';
 select now();
 -- one file 18G
 source /home/XXXdata/20191230_135112.sql;
 
 show global variables like 'innodb_lru_scan_depth';
 show variables like '%sql_mode%';
 show variables like '%autocommit';
 show variables like '%unique_checks';
 show variables like '%foreign_key_checks';
 select now();

    导入过程

     mysql > tee out-20230816.txt
 
 mysql > source /home/XXXdata/all.sql

    查看结果

     # cat out-20230816.txt
 ......
 | 2023-08-16 08:17:02 |
 ......
 | 2023-08-16 14:26:40 |

    总共耗时 6 小时 +

  2. 方式2⃣️: 使用shell 命令行后台任务，直接导入一个大文件 (18G)

    为了统计用时和绘画内部优化，将实际mysqldump 文件包含到 all.sql

     # cat all.sql 
 
 同前一个方式

    shell 后台任务

     # nohup  mysql -uroot -pPassword@123 --default-character-set=utf8 --force zXXX< /home/zXXX/all.sql > /home/out-20230817.txt 2>&1 &

    查看执行结果

     # cat out-20230817.txt
 ......
 2023-08-16 21:08:52
 ......
 2023-08-17 01:43:14

    用时 大约5 小时

  3. 方式三：SQLDumpSplitter切割 sql 文件 ( 每个 2G)，使用 mysql source 命令，

    切割后的结果

     # ls *.sql -l
 -rw-r--r--. 1 root root 2147482476 8月  10 14:13 20191230_135112_0.sql
 -rw-r--r--. 1 root root 2147482521 8月  10 14:15 20191230_135112_1.sql
 -rw-r--r--. 1 root root 2147482263 8月  10 14:17 20191230_135112_2.sql
 -rw-r--r--. 1 root root 2147482371 8月  10 14:19 20191230_135112_3.sql
 -rw-r--r--. 1 root root 2147481971 8月  10 14:21 20191230_135112_4.sql
 -rw-r--r--. 1 root root 2147481699 8月  10 14:24 20191230_135112_5.sql
 -rw-r--r--. 1 root root 2147482612 8月  10 14:25 20191230_135112_6.sql
 -rw-r--r--. 1 root root 2147482594 8月  10 14:27 20191230_135112_7.sql
 -rw-r--r--. 1 root root  959044232 8月  10 14:28 20191230_135112_8.sql
 -rw-r--r--. 1 root root       1096 8月  16 14:14 all.sql

    all.sql

     # cat all.sql 
 SELECT DATABASE();
 select now();
 
 SET GLOBAL innodb_lru_scan_depth=256;
 set session sql_mode = 'ALLOW_INVALID_DATES';
 SET autocommit=0;
 SET unique_checks=0;
 SET foreign_key_checks=0;
 
 show global variables like 'innodb_lru_scan_depth';
 show variables like '%sql_mode%';  
 show variables like '%autocommit';
 show variables like '%unique_checks';
 show variables like '%foreign_key_checks';
 
 -- 00
 source /home/XXXdata/20191230_135112_0.sql ;
 -- 01
 source /home/XXXdata/20191230_135112_1.sql ;
 -- 02
 source /home/XXXdata/20191230_135112_2.sql ;
 -- 03
 source /home/XXXdata/20191230_135112_3.sql ;
 -- 04
 source /home/XXXdata/20191230_135112_4.sql ;
 -- 05
 source /home/XXXdata/20191230_135112_5.sql ;
 -- 06
 source /home/XXXdata/20191230_135112_6.sql ;
 -- 07
 source /home/XXXdata/20191230_135112_7.sql ;
 -- 08
 source /home/XXXdata/20191230_135112_0.sql ;
 -- ALL OK
 
 show global variables like 'innodb_lru_scan_depth';
 show variables like '%sql_mode%';
 show variables like '%autocommit';
 show variables like '%unique_checks';
 show variables like '%foreign_key_checks';
 
 SELECT DATABASE();
 select now();

    导入过程

     mysql > tee out-20230816.txt
 
 mysql > source /home/XXXdata/all.sql

    查看结果

     cat out-20230816.txt
 ......
 | 2023-08-16 08:17:02 |
 ......
 | 2023-08-16 13:15:21 |

    总共耗时大约 5 小时

不调优，使用shell 命令行后台任务，直接导入一个大文件 (18G)

  1. 注释掉 my.cnf 中的调优， restart mysqld 服务

     # cat /etc/my.cnf
 
 # wzh 20230814
 # Set the number of CPUs to be used by MySQL 
 #  # to half of the total available CPUs
 # innodb_thread_concurrency = 2
 #
 # Set the number of buffer pool instances
 #innodb_buffer_pool_instances = 2
 #  
 # Set the size of the query cache
 # query_cache_size = 64M
 #
 # wzh 20230814 
 # bulk_insert_buffer_size = 2G
 # innodb_log_buffer_size = 2G
 # innodb_autoinc_lock_mode = 2
 # wzh 20230817
 # default-time-zone='Asia/Shanghai'
 explicit_defaults_for_timestamp=true
 log_timestamps=SYSTEM

    这就和安装完 mysql 5.7 之后，全部使用缺省值一样了

  2. shell 后台任务

     nohup  mysql -uroot -pPassword@123 --default-character-set=utf8 --force zXXX< /home/zXXX/all.sql > /home/out-20230817.txt 2>&1 &

  3. 查看结果

    cat /home/out-20230817.txt

    …
    2023-08-18 14:50:11
    …
    2023-08-18 19:43:37

    大约 5 小时，对比前面调优没有多大改进？

原因分析

决定实际完成快慢的，是该进程的 CPU 占用时间（ cputime ） ？

以下是中途记录的一段

#  ps -eo pid,euid,euser,lstart,etime,cputime,cmd | grep mysql
...
 1877     0 root     Fri Aug 18 08:22:21 2023    01:20:06 00:00:59 mysql -uroot -px xxxxxxxxxx --default-character-set=utf8 --force zXXX
...

可以看到，本次运行时间 01:20:06 ，CPU 占用时间 00:00:59 ，说明效率很高！

之前曾经有过运行一晚上，实际 cputime 才 2 个小时，效率太低了！

总结

  1. 切割 SQL 文件并不能显著改善导入速度 （也许我切割到 2G 还是太大了，感觉应该 1G ）

  2. 使用 shell 命令行 和 mysql source 命令要快大约 20%-30%

  3. 使用多 CPU 和增加缓存等办法，没有测出有明显效果

  4. 影响导入速度的还是导入过程中的错误忽略 sql_mode = ‘ALLOW_INVALID_DATES’ 和 autocommit 等优化

  5. shell 命令行 加上 --force

  6. 不要同时执行 2 个或以上大任务，互相影响！

  7. 提前判断好需要的硬盘空间，不要等最后才知道 disk full ，前功尽弃！

    查看磁盘空间

    df -h

    如果可以找到原始的数据库来源，查看数据库文件大小

    $ sudo du -sh /var/lib/mysql/zXXX
    26G /var/lib/mysql/zXXX文章来源地址https://www.toymoban.com/news/detail-664356.html

到了这里，关于mysql 超大 sql 文件导入过程的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包赞助服务器费用

相关文章

  • navicat导入sql数据库文件的简单操作步骤

    navicat导入sql数据库文件的简单操作步骤

    目录 前言必读 一、概念 二、操作步骤 （一）新建连接  （二）新建数据库 （三）数据库导入sql文件 读者手册（必读）_云边的快乐猫的博客-CSDN博客 在很多项目当中都有sql文件导入到MySQL数据库的需要，因为有sql数据库文件，这个项目才能正常运行起来，那么现在就来学习

    2024年02月03日
    浏览(11)
  • MySQL 数据库文件的导入导出

    MySQL 数据库文件的导入导出

    目录 数据库的导出 导出整个数据库 导出数据库中的数据表 导出数据库结构 导出数据库中表的表结构 导出多个数据库 导出所有数据库 数据库的导入 mysqldump -h IP地址 -P 端口 -u 用户名 -p 数据库名 导出的文件名 用管理员权限打开cmd进入MySQL的bin目录下，根据自己情况调整  

    2024年02月13日
    浏览(14)
  • 【SQL Server】---SQL Server 2008 R2数据库导入（还原）.bak文件

    【SQL Server】---SQL Server 2008 R2数据库导入（还原）.bak文件

    以下是简述SQL Server把备份的数据.bak文件还原、导入的方法。 一、新建数据库、数据文件夹；         1.新建数据库Cola：打开SQL Server-数据库-新建数据库；                            2.新建.bak数据文件夹：新建一个文件夹，把.bak数据文件放到该文件夹下；      

    2024年02月13日
    浏览(39)
  • Linux 使用bash创建MYSQL数据库并导入数据库文件

    Linux 使用bash创建MYSQL数据库并导入数据库文件

    主要是杠一下的数据库123-456.sql，这个神经病一样，试了很多写法

    2024年01月22日
    浏览(13)

  • 用sql将excel文件导入数据库（Microsoft.ACE.OLEDB.12.0）

    1、将xlsx（或者xls文件，需要将文件后缀修改）文件数据插入数据库中 2、将csv文件数据插入数据库中 其中， \\\"HDR=yes;\\\"是说Excel文件的第一行是列名而不是数据，\\\"HDR=no;\\\"正好与前面的相反。 \\\"IMEX=1 \\\"如果列中的数据类型不一致，使用\\\"IMEX=1\\\"可必免数据类型冲突。 3、判断路径下是

    2024年02月12日
    浏览(8)
  • MySQL数据库自动生成Models文件导入Django

    MySQL数据库自动生成Models文件导入Django

    在Django项目使用数据库时，可以有三种方式：           1.直接编写app内的models文件，然后自动生成MySQL数据库          2.直接在MySQL中将数据库编写好，然后不使用models，而是在views中利用PyMySQL编写数据库链接等操作         3.MySQL库自动生成models文件进行连接使用 这

    2023年04月08日
    浏览(11)
  • 【MySQL】探索MySQL存储过程的魔力，初学者的数据库编程秘笈（内含实战SQL脚本）

    【MySQL】探索MySQL存储过程的魔力，初学者的数据库编程秘笈（内含实战SQL脚本）

    🧑‍💻作者名称：DaenCode 🎤作者简介：啥技术都喜欢捣鼓捣鼓，喜欢分享技术、经验、生活。 😎人生感悟：尝尽人生百味，方知世间冷暖。 📖所属专栏：重温MySQL MySQL存储过程作为一种服务器端的 数据库编程方式 ，提供了高效、可重用的方法来执行相对复杂的数据库操

    2024年02月15日
    浏览(37)
  • 【网络安全---sql注入（2）】如何通过SQL注入getshell？如何通过SQL注入读取文件或者数据库数据？一篇文章告诉你过程和原理。

    【网络安全---sql注入（2）】如何通过SQL注入getshell？如何通过SQL注入读取文件或者数据库数据？一篇文章告诉你过程和原理。

    分享一个非常详细的网络安全笔记，是我学习网安过程中用心写的，可以点开以下链接获取： 超详细的网络安全笔记 本篇博客主要是通过piakchu靶场来讲解如何通过SQL注入漏洞来写入文件，读取文件。通过SQL输入来注入木马来getshell等，讲解了比较详细的过程； 如果想要学习

    2024年02月07日
    浏览(11)
  • 【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件，Excel表格导入数据库的接口

    【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件，Excel表格导入数据库的接口

    最近由于学校压力，心情不太好，没咋写博客； 但最近做数据库实验的时候，数据是xlsx文件展示的，要求将这些导入数据库，我懒得去下载MySQL WorkBench等等可视化的工具，就想着写个程序来实现这个功能~ 只要访问这个接口，输入xlsx表格文件的路径，就会向程序设置好的数

    2024年02月07日
    浏览(9)
  • ELK（Elasticsearch、Kibana、Logstash）以及向ES导入mysql数据库数据或CSV文件数据，创建索引和可视化数据

    ELK（Elasticsearch、Kibana、Logstash）以及向ES导入mysql数据库数据或CSV文件数据，创建索引和可视化数据

    地址：Past Releases of Elastic Stack Software | Elastic 在Products和version处分别选择需要下载的产品和版本，E（elasticsearch）L（logstash）K（kibana）三者版本必须相同 将下载好的elk分别解压到相同路径下 本文中elasticsearch=E=ES=es；L=logstash；K=kibana 一般情况下使用默认配置即可，下面对我的

    2024年02月15日
    浏览(40)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包