Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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:
I can only think of three(3) things that can cause a mysqldump to be too large
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.
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.
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.
