Split SQL file with multiple databases in it

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP











up vote
0
down vote

favorite












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?







share|improve this question

























    up vote
    0
    down vote

    favorite












    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?







    share|improve this question























      up vote
      0
      down vote

      favorite









      up vote
      0
      down vote

      favorite











      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?







      share|improve this question













      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?









      share|improve this question












      share|improve this question




      share|improve this question








      edited May 2 at 17:41









      Jeff Schaller

      31.1k846105




      31.1k846105









      asked May 2 at 15:19









      Sam

      65118




      65118




















          1 Answer
          1






          active

          oldest

          votes

















          up vote
          1
          down vote



          accepted










          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:




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






          share|improve this answer























          • I'm getting an error stating invalid statement (P).
            – Sam
            May 2 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 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 at 15:46










          • Is it actually freezing or is it simply taking a long time to parse a very large file?
            – DopeGhoti
            May 2 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 at 16:47










          Your Answer







          StackExchange.ready(function()
          var channelOptions =
          tags: "".split(" "),
          id: "106"
          ;
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function()
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled)
          StackExchange.using("snippets", function()
          createEditor();
          );

          else
          createEditor();

          );

          function createEditor()
          StackExchange.prepareEditor(
          heartbeatType: 'answer',
          convertImagesToLinks: false,
          noModals: false,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          );



          );








           

          draft saved


          draft discarded


















          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f441346%2fsplit-sql-file-with-multiple-databases-in-it%23new-answer', 'question_page');

          );

          Post as a guest






























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          up vote
          1
          down vote



          accepted










          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:




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






          share|improve this answer























          • I'm getting an error stating invalid statement (P).
            – Sam
            May 2 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 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 at 15:46










          • Is it actually freezing or is it simply taking a long time to parse a very large file?
            – DopeGhoti
            May 2 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 at 16:47














          up vote
          1
          down vote



          accepted










          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:




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






          share|improve this answer























          • I'm getting an error stating invalid statement (P).
            – Sam
            May 2 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 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 at 15:46










          • Is it actually freezing or is it simply taking a long time to parse a very large file?
            – DopeGhoti
            May 2 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 at 16:47












          up vote
          1
          down vote



          accepted







          up vote
          1
          down vote



          accepted






          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:




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






          share|improve this answer















          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:




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







          share|improve this answer















          share|improve this answer



          share|improve this answer








          edited May 2 at 15:51









          derobert

          68.4k8147203




          68.4k8147203











          answered May 2 at 15:33









          DopeGhoti

          40k54779




          40k54779











          • I'm getting an error stating invalid statement (P).
            – Sam
            May 2 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 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 at 15:46










          • Is it actually freezing or is it simply taking a long time to parse a very large file?
            – DopeGhoti
            May 2 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 at 16:47
















          • I'm getting an error stating invalid statement (P).
            – Sam
            May 2 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 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 at 15:46










          • Is it actually freezing or is it simply taking a long time to parse a very large file?
            – DopeGhoti
            May 2 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 at 16:47















          I'm getting an error stating invalid statement (P).
          – Sam
          May 2 at 15:40




          I'm getting an error stating invalid statement (P).
          – Sam
          May 2 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 at 15:44




          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 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 at 15:46




          Still no luck. It contains 3 databases: on localhost I can't import it correctly without the MySQL server freezing.
          – Sam
          May 2 at 15:46












          Is it actually freezing or is it simply taking a long time to parse a very large file?
          – DopeGhoti
          May 2 at 16:46




          Is it actually freezing or is it simply taking a long time to parse a very large file?
          – DopeGhoti
          May 2 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 at 16:47




          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 at 16:47












           

          draft saved


          draft discarded


























           


          draft saved


          draft discarded














          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f441346%2fsplit-sql-file-with-multiple-databases-in-it%23new-answer', 'question_page');

          );

          Post as a guest













































































          Popular posts from this blog

          How to check contact read email or not when send email to Individual?

          Displaying single band from multi-band raster using QGIS

          How many registers does an x86_64 CPU actually have?