I have a database of about 6GB in size and it has a table with 12.6 million rows. I tried to export the database into a SQL dump by:
mysqldump -u root -p db_name > db_name.sql
When the command finishes, the exported SQL dump file is just about 2GB and the primary table got exported only about 1 million rows.
What could possibly be wrong?
解决方案
There is a 2GB filesize limit for some reason, the easiest way to get around this is using split:
mysqldump ... | split -b 250m - filename.sql-
You can also compress the files like this:
mysqldump ... | gzip -9c | split -b 250m - filename.sql.gz-
To restore from a non-compressed file, do this:
cat filename.sql-* | mysql ...
For a compressed file:
cat filename.sql-* | zcat | mysql ...
Of course if you want a single file, you can then tar the result.
Obviously you can replace the 250m with a different size if you wish.