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
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?)