如何将 mysqldump 的输出拆分为较小的文件? [英] How do I split the output from mysqldump into smaller files?

问题描述

我需要将整个表从一个 MySQL 数据库移动到另一个.我没有第二个的完全访问权限，只有 phpMyAdmin 访问权限.我只能上传(压缩)小于 2MB 的 sql 文件.但是第一个数据库表的 mysqldump 的压缩输出大于 10MB.

I need to move entire tables from one MySQL database to another. I don't have full access to the second one, only phpMyAdmin access. I can only upload (compressed) sql files smaller than 2MB. But the compressed output from a mysqldump of the first database's tables is larger than 10MB.

有没有办法将 mysqldump 的输出拆分成更小的文件?我无法使用 split(1)，因为我无法将文件 cat(1) 放回到远程服务器上.

Is there a way to split the output from mysqldump into smaller files? I cannot use split(1) since I cannot cat(1) the files back on the remote server.

或者我错过了其他解决方案吗?

Or is there another solution I have missed?

编辑

第一个发布者建议的 mysqldump 的 --extended-insert=FALSE 选项会生成一个 .sql 文件，然后可以将其拆分为可导入的文件，前提是使用合适的 --lines 选项调用 split(1).通过反复试验，我发现 bzip2 将 .sql 文件压缩了 20 倍，因此我需要计算大约 40MB 对应的 sql 代码行数.

The --extended-insert=FALSE option to mysqldump suggested by the first poster yields a .sql file that can then be split into importable files, provided that split(1) is called with a suitable --lines option. By trial and error I found that bzip2 compresses the .sql files by a factor of 20, so I needed to figure out how many lines of sql code correspond roughly to 40MB.

推荐答案

首先转储模式(它肯定适合 2Mb，不是吗?)

First dump the schema (it surely fits in 2Mb, no?)

mysqldump -d --all-databases 

并恢复它.

之后只转储单独插入语句中的数据，因此您可以拆分文件并恢复它们，而无需在远程服务器上连接它们

Afterwards dump only the data in separate insert statements, so you can split the files and restore them without having to concatenate them on the remote server

mysqldump --all-databases --extended-insert=FALSE --no-create-info=TRUE

这篇关于如何将 mysqldump 的输出拆分为较小的文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！