I've recently received a SQL-dump file, but it contains multiple databases in it. Is there any tool I can use to easily split this up into multiple .sql files, one per database?


Presuming that this is a plaintext .sql file with CREATE SCHEMA or CREATE DATABASE statements, you can effectively do this with split:

$ split -p 'CREATE DATABASE' bigfile.sql splitsql_

This will create a set of new files, e. g. splitsql_aa, splitsql_ab, and so forth, wherein each file will start with the found CREATE statements.

From split's manual:

     split -- split a file into pieces
     -p pattern
             The file is split whenever an input line matches pattern,
             which is interpreted as an extended regular expression.
             The matching line will be the first line of the next
             output file.  This option is incompatible with the -b and
             -l options.
|improve this answer|||||
  • I'm getting an error stating invalid statement (P). – Sam May 2 '18 at 15:40
  • That, unfortunately, doesn't actually parse SQL — so it could be fooled by a CREATE DATABASE hidden in a text field somewhere. Might help to put a ^ in front of it... – derobert May 2 '18 at 15:44
  • Still no luck. It contains 3 databases: on localhost I can't import it correctly without the MySQL server freezing. – Sam May 2 '18 at 15:46
  • Is it actually freezing or is it simply taking a long time to parse a very large file? – DopeGhoti May 2 '18 at 16:46
  • Also, it might be helpful, if you can still create the original DB dump, to separate the schema dump (i. e. the tables and other underlying structures) from the data dump (i. e. a metric tonne of INSERT statements). – DopeGhoti May 2 '18 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.