如何将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)，因为无法将文件分类(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倍，因此我需要找出多少行sql代码大致对应于40MB.

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屋！