mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table

Written by - 0 comments

Published on - last updated on January 28th 2022 - Listed in MySQL MariaDB Database Linux Backup


A nightly mysqldump script recently started to fail on a customer's LAMP server with the following error:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `wp_wfHoover` at row: 458317
mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)" when trying to connect
mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)" when trying to connect

max allowed packet size limit

Although "Lost connection to MySQL server" sounds more like a communication problem on a network level or that MySQL was unavailable during the mysqldump process, it is (most likely) rather a "packet size" problem. A much better (and self-explaining) error would actually be something such as "got too big packet".

To retrieve large data from a table, the default "max allowed packet" size may need to be adjusted. The mysqldump (client) command sets a default value of 24 MB for the --max-allowed-packet parameter. For this table (wp_wfHoover) this was not enough.

Note: Check out this article to find out the data size of MariaDB / MySQL tables.

After appending a --max-allowed-packet parameter with a higher value to the mysqldump command, the backup script worked again, without throwing any errors:

root@db:~# mysqldump --routines --events --single-transaction --quick --max-allowed-packet=256m DBNAME

Other potential reasons causing the "Error 2013" could be timeout-related problems. See this article for additional hints.

MySQL is indeed "gone"

The above error can also show up, if during the mysqldump process the database server is indeed gone. Check your MySQL error logs (usually in /var/log/mysql/error.log) and system logs, including dmesg. When you see events with "oom", your system ran out of memory:

root@db:~# dmesg
[...]
[Thu Jan 27 23:02:33 2022] Memory cgroup out of memory: Kill process 12509 (mysqld) score 279 or sacrifice child
[Thu Jan 27 23:02:33 2022] Killed process 12509 (mysqld) total-vm:5527388kB, anon-rss:2048568kB, file-rss:0kB, shmem-rss:0kB
[Thu Jan 27 23:02:33 2022] oom_reaper: reaped process 12509 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB
[Fri Jan 28 00:04:38 2022] mysqld invoked oom-killer: gfp_mask=0x6200ca(GFP_HIGHUSER_MOVABLE), nodemask=(null), order=0, oom_score_adj=0



Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder