mysqldump做部分备份-不完整的表转储 [英] mysqldump doing a partial backup - incomplete table dump

问题描述

我有一个大约6GB的数据库，并且有一个包含1260万行的表.我试图通过以下方式将数据库导出到SQL转储中:

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

mysqldump -u root -p db_name > db_name.sql

命令完成后，导出的SQL转储文件只有2GB，而主表只导出了约100万行.

When the command finishes, the exported SQL dump file is just about 2GB and the primary table got exported only about 1 million rows.

可能有什么问题吗?

推荐答案

出于某些原因，文件大小限制为2GB，解决此问题的最简单方法是使用split:

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 ...

对于压缩文件:

cat filename.sql-* | zcat | mysql ...

当然，如果只需要一个文件，则可以tar结果.

Of course if you want a single file, you can then tar the result.

很显然，您可以根据需要将250m替换为其他大小.

Obviously you can replace the 250m with a different size if you wish.

这篇关于mysqldump做部分备份-不完整的表转储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！