Question :
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
Answer :
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?)
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.