getting Lost connection to mysql when using mysqldump even with max_allowed_packet parameter

Try adding the --quick option to your mysqldump command; it works better with large tables. It streams the rows from the resultset to the output rather than slurping the whole table, then writing it out.

 mysqldump -uroot -h my.host -p'mypassword' --quick --max_allowed_packet=512M db_name table_name | \
 gzip  > dump_test.sql.gz

You can also try adding the --compress option to your mysqldump command. That makes it use the more network-friendly compressed connection protocol to your MySQL server. Notice that you still need the gzip pipe; MySQL's compressed protocol doesn't cause the dump to come out of mysqldump compressed.

It's also possible the server is timing out its connection to the mysqldump client. You can try resetting the timeout durations. Connect to your server via some other means and issue these queries, then run your mysqldump job.

These set the timeouts to one calendar day.

    SET GLOBAL wait_timeout=86400;
    SET GLOBAL interactive_timeout=86400;

Finally, if your server is far away from your machine (through routers and firewalls) something may be disrupting mysqldump's connection. Some inferior routers and firewalls have time limits on NAT (network address translation) sessions. They're supposed to keep those sessions alive while they are in use, but some don't. Or maybe you're hitting a time or size limit configured by your company for external connections.

Try logging into a machine closer to the server and running mysqldump on it. Then use some other means (sftp?) to copy your gz file to your own machine.

Or, you may have to segment the dump of this file. You can do something like this (not debugged).

mysqldump  -uroot -h my.host -p'mypassword'  \ 
          db_name table_name --skip-create-options --skip-add-drop-table \
          --where="id>=0 AND id < 1000000" | \
          gzip....

Then repeat that with these lines.

          --where="id>=1000000 AND id < 2000000" | \

          --where="id>=2000000 AND id < 3000000" | \
          ...

until you get all the rows. Pain in the neck, but it will work.

For me, all worked fine when I skip lock tables

 mysqldump -u xxxxx --password=xxxxx --quick --max_allowed_packet=512M --skip-lock-tables --verbose   -h xxx.xxx.xxx.xxx > db.sql

I may create problems with consistency but allowed me to backup a 5GB database without any issue.

other option to try:

net_read_timeout=3600 
net_write_timeout=3600

on my.ini/my.cnf or via SET GLOBAL ...

Using JohnBigs comment above, the --compress flag was what worked for me.

I had previously tried --single-transaction, --skip-extended-insert, and --quick the w/o success.

I had a similar problem on my server, where MySQL would apparently restart during the nightly backups. It was always the same database, but the actual table sometimes varied.

Tried several from the other answers here, but in the end it was just some cronjob executing queries that didn't finish. This caused not so much CPU and RAM usage that it triggered the monitoring, but apparently enough that compressing the dump caused the OOM killer to become active. Fixed the cronjob and the next backup was ok again.

Things to look for:

• OOM? dmesg | grep invoked
• Process killed? grep killed /var/log/kern.log

Also, make sure you MYSQL.EXE client is the same version as your mysql server.

So, if you're mysql version is 8.0.23 but your client version is 8.0.17 or 8.0.25, you may have issues. I ran into this problem using a version 8.0.17 on a mysql server 8.0.23 - changing the client version to match the server version resolved the issue.