Lost connection to MySQL server during query,MySQL设置session,global变量及网络IO与索引

时间:2022-03-23 11:05:37

Navicat导出百万级数据时,报错:2013 - Lost connection to MySQL server during query

Lost connection to MySQL server during query,MySQL设置session,global变量及网络IO与索引

网上一番搜索,修改mysql如下几处配置文件即可:

Lost connection to MySQL server during query,MySQL设置session,global变量及网络IO与索引


  1. select @@sql_mode;
  2. SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
  3. 4194304 -- 2013 - Lost connection to MySQL server during query
  4. select @@max_allowed_packet;
  5. show global variables like 'max_allowed_packet';
  6. -- SET max_allowed_packet = 524288000
  7. SET GLOBAL max_allowed_packet = 524288000

最好修改配置文件my.cnf或my.ini,然后重启服务。

操作成功结果如下:

Lost connection to MySQL server during query,MySQL设置session,global变量及网络IO与索引

另一个VPN远端数据库(MySQL5.6.x)导出结果csv:

Lost connection to MySQL server during query,MySQL设置session,global变量及网络IO与索引

最近做数据处理时的一点感触:

1、网络IO对程序的影响真的很大,通过VPN插入更新数据比本机操作MySQL速度要慢数十倍;

如19114条数据,通过VPN网络操作DB用了164.2s,本地数据库操作使用了2.9s。(50+倍

Lost connection to MySQL server during query,MySQL设置session,global变量及网络IO与索引

Lost connection to MySQL server during query,MySQL设置session,global变量及网络IO与索引

2、索引真的很重要,百万级别的操作速度可相差好几百倍;

创建索引前,查询一百多万条数据用了481.3s,创建增加索引用了12.7s,创建索引后用了0.9s。(500+倍

Lost connection to MySQL server during query,MySQL设置session,global变量及网络IO与索引
Lost connection to MySQL server during query,MySQL设置session,global变量及网络IO与索引Lost connection to MySQL server during query,MySQL设置session,global变量及网络IO与索引

PS:一些设置方法:


  1. SELECT version();
  2. SHOW engines;
  3. SHOW variables like '%storage_engine%';
  4. MySQL设置session,global变量的方法
  5. SHOW VARIABLES LIKE '%auto_increment%';
  6. -- SET GLOBAL auto_increment_offset = 1;
  7. -- SET GLOBAL auto_increment_increment = 1;
  8. -- -- 设置或修改系统日志有效期
  9. SHOW VARIABLES LIKE '%expire_logs_days%';
  10. -- SET GLOBAL expire_logs_days=8;
  11. SHOW VARIABLES LIKE '%max_connections%'
  12. -- SET GLOBAL max_connections = 2648;
  13. 如果要修改全局变量, 必须要显示指定"GLOBAL"或者"@@global.", 同时必须要有SUPER权限
  14. set global wait_timeout=100;
  15. set @@global.wait_timeout=100;
  16. select @@global.wait_timeout
  17. show global variables like 'wait_timeout';
  18. 其中show variables的话就等同于使用show session variables, 查询的是会话session变量, 只有使用show global variables查询的才是全局变量.
  19. 设置会话变量方法: 在命令行里通过SET来设置
  20. set wait_timeout=10;
  21. set session wait_timeout=10;
  22. set @@wait_timeout=10;
  23. set @@session.wait_timeout=10;
  24. 查看设置是否成功:
  25. select @@wait_timeout;
  26. select @@session.wait_timeout;
  27. show variables like 'wait_timeout';
  28. show session variables like 'wait_timeout';
  29. 将会话变量值设置为对应的全局变量值:
  30. set @@session.wait_timeout=@@global.wait_timeout;
  31. SHOW KEYS FROM user_csv_bak;
  32. SHOW INDEX FROM user_csv_bak;