Backing Up a Large Database Without Timeout; A Practical Guide

You have a three-gigabyte database, you run the mysqldump command, and after twenty minutes of waiting, the terminal disconnects. The output file is half-finished, or completely empty. If you're on SSH you get the message Connection to server closed, and if you ran it from the panel, you usually see something like 504 Gateway Timeout. The problem is neither the database nor the server; the problem is that the default command was written for a small database, not for something with several gigabytes of data.

Why a large database backup gets cut off midway

Three separate factors are behind this cutoff, and you need to distinguish between them, because the solution for each is different.

First, the web layer timeout. If you're backing up from phpMyAdmin or a PHP script, max_execution_time is usually set to 30 or 60 seconds, and max_input_time is something similar. Your database needs ten minutes. The result is an incomplete file that doesn't even leave an error in the PHP log, because the script considers itself successful.

Second, the network timeout. If you connect to a remote MySQL from your own system, net_read_timeout and net_write_timeout on the server side default to 30 and 60 seconds. A heavy query that takes 90 seconds will close the connection.

Third, and the one that's most underestimated: table locking. mysqldump by default works with --opt, which includes --lock-tables. On InnoDB this lock is almost useless, but on MyISAM it locks the entire table and your site won't respond for the whole duration of the backup. If your site is high-traffic, users will see 500 errors while you were just taking a backup.

The correct mysqldump command for a multi-gigabyte database

This is the command I use on 5 to 20 gigabyte databases:

mysqldump --single-transaction --quick --skip-lock-tables \
  --routines --triggers --events \
  --default-character-set=utf8mb4 \
  -u dbuser -p dbname | gzip -1 > backup-$(date +%F).sql.gz

Every option has a reason. --single-transaction opens a transaction with a repeatable isolation level on InnoDB and, without locking tables, gives a consistent snapshot. --quick makes rows get written to the output one by one instead of collecting the entire result set in server memory; on a table with ten million rows, this is the difference between success and an OOM kill. --skip-lock-tables removes that same MyISAM lock.

A point many people don't know: --single-transaction only makes sense on InnoDB. If you have MyISAM tables, this option won't give you a consistent snapshot and your backup may be logically inconsistent. First check the table types with SHOW TABLE STATUS.

Let me also explain gzip -1, since people usually set -9. Level 9 on a text database gives a file about 15 to 20 percent smaller, but multiplies CPU usage and lengthens the backup time. On a server that's simultaneously serving users, -1 is the wiser choice. If the backup is at night and the server is idle, -6 is a good balance.

If the database is on another server

When you connect remotely, run these two lines before the dump:

SET GLOBAL net_read_timeout = 600;
SET GLOBAL net_write_timeout = 600;

And on the client side, also add --max-allowed-packet=512M if you have a table with a large BLOB. Without this, you'll get the MySQL server has gone away error, which is a misleading message; the real problem was a packet larger than the allowed limit, not the server disconnecting.

Chunked restore; the way that bypasses the timeout

Taking the backup is half the story. Restoring an 8-gigabyte file with mysql -u user -p db < backup.sql on shared hosting usually fails, because running a long SQL script exceeds the panel timeout.

The solution is to split the file. If you have a gzip file, first decompress it and then:

split -l 50000 backup.sql part_

This splits the file into fifty-thousand-line chunks. Now import each chunk separately:

for f in part_*; do mysql -u dbuser -p dbname < "$f" || break; done

The hidden advantage of this method is that when the seventh chunk errors out, you know where the problem is and you don't have to start from scratch. With a single monolithic file, an error at minute forty means starting over again.

A practical warning: split splits by line and may cut in the middle of a multi-line INSERT. If your dump was taken with --extended-insert (which is the default), each row is usually on one line and there's no problem. But if you took it with --skip-extended-insert, first re-take it with --complete-insert.

This is where they make mistakes

The mistake I really see a lot: they take the backup with --single-transaction, the file is fine, but during restore onto a destination database that already has tables with the same names, they just run mysql db < backup.sql. The result is that CREATE TABLE throws a Table already exists error, the script continues, and in the end you have a database where half its tables have old data and half have new data. The site comes up but the numbers are wrong and you may not notice for weeks.

And the sign is exactly this: the restore is reported as "successful," there's no clear error in the output, but the row counts don't match your expectations. Always empty the destination database before restoring, or use --add-drop-table at dump time.

Alternatives; where mysqldump doesn't cut it

My choice for databases under 20 gigabytes is always mysqldump, because the output file is text and you can pull a specific table out of it. Above that size, I prefer XtraBackup; a physical backup on a 100-gigabyte database takes a few minutes, not a few hours. But the condition is that you have enough free disk space, because XtraBackup effectively makes a copy of the data files.

If you're on Linux hosting and don't have root access, you're practically limited to the same mysqldump. In that case, break the backup into smaller chunks and each night back up only the high-churn tables, not the entire database.

A few tips that will save you time

• Before every backup, check the database size with SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024) AS mb FROM information_schema.tables GROUP BY table_schema;. Know the real number, not a guess.
• Don't keep the backup on the same server. If the disk fills up, you lose both the database and the backup.
• Once a month, try a restore on a test database. A backup that hasn't been tested isn't a backup.
• If you have WordPress and your scheduled backups aren't running, the problem is usually cron, not the database; see WordPress wp-cron; why it doesn't run and how to replace it with real cron.

For more commands and ready-made examples, ServerNet's documentation and knowledge base is a good reference. If you want to make sure before backing up that the server responds healthily from the outside, free tools are also available on this same site.

Frequently asked questions

Why is mysqldump so slow on a large database?

Because by default it works single-threaded and reads all the data from the server, writes it to disk, and compresses it. On a 10-gigabyte database, this can take one to two hours. With --quick you reduce memory usage but it doesn't multiply the speed. For real speed you need to migrate to mydumper or XtraBackup.

Can I back up just one table?

Yes. Write the table name after the database name: mysqldump -u user -p dbname table_name > table.sql. This is very useful for high-churn tables like the orders or logs table, and it drastically reduces the size of your daily backup.

What does the MySQL server has gone away error during backup mean?

It means the packet exchanged between the client and server was larger than max_allowed_packet, or the connection was closed due to a network timeout. Raise the max_allowed_packet value on both the server and the client, and increase net_write_timeout. If you have a table with a large BLOB, this error is almost certain unless you tune the parameters.

How can I restore a gzipped backup without fully decompressing it?

You can pipe it directly: gunzip < backup.sql.gz | mysql -u user -p dbname. This method doesn't create an intermediate file on disk and is suitable for limited space. But if it errors out midway, pinpointing the error location is harder; for large databases, the chunked method is more reliable.