Search code examples
mysqlmigration

How do I split the output from mysqldump into smaller files?

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.

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?

Edit

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.

Solution

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

    mysqldump -d --all-databases

    and restore it.

    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