Is there a limit to how large a database can be when executing a .sql backup using mysqldump, or will you run into server problems before that - will the server not be able to take such a large backup file?

I'm thinking in the levels of:

  • 1 - 10GB
  • 10 - 100GB
  • 100 - 500GB

2 Answers 2


I can only think of three(3) things that can cause a mysqldump to be too large

PROBLEM #1 : Disabling Extended Inserts

Extended Inserts (--extended-insert) is on by default via the --opt option. If you issue --skip-opt or --skip-extended-insert, every INSERT command will quickly become hundreds or every thousands. Such a mysqldump can still be loaded but takes longer because of the absence of batch inserts.


When dumping a table with BLOB data, there is a small possibility that the dump may not be portable. To make such data portable, some resort to the --hex-blob option. This write the BLOB as text-represented hexadecimal. When using this, you should expect a more bloated mysqldump.

CAVEAT : Don't use --hex-blob and --skip-extended-insert together

PROBLEM #3 : MySQL Packet

Most people take the MySQL Packet for granted. Some mysqldump could time out if you not paying attention. mysqldump's default for max-allowed-packet in 24M. Extended inserts can benefit from a larger packet. It could possibly increase the number of rows per extended INSERT command. The savings in size might be nominal at best. Notwithstanding, the larger the dump, the slightly better the reduction of size might be.

UPDATE 2015-02-13 12:37 EST

If your main concern is just sheer size vs. hardware constraints, you dump like this:

mysqldump -uroot -ppasswword ... | gzip > mybackup.sql.gz

and restore like this

gzip -d < mybackup.sql.gz | mysql -uroot -ppasswword

You could use bzip2 instead of gzip (Pros and cons of bzip vs gzip?)

  • Great. Then I shouldn't worry about our database and growing out of mysqldump as the basic export option yet. Feb 13, 2015 at 17:31
  • +1 for compression. You could also take a look at a parallel implementation of your favorite compression utility, for example: pigz.
    – vschum
    Feb 18, 2015 at 23:46

There are a lot of factors that could come into play. Size of RAM, whether your software is using any timeouts (e.g.: phpMyAdmin), your own patience.

In general, though, I would expect a server that can generate a backup should be able to restore it.

  • So mysqldump can generate a dump file for a 500gb database as long as the hardware is enough? Feb 13, 2015 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.