3

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
7

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.

PROBLEM #2 : BLOB Data

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. – naboovalley Feb 13 '15 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 '15 at 23:46
1

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? – naboovalley Feb 13 '15 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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