Split SQL file with multiple databases in it
Clash 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?
text-processing files sql
edited May 2 at 17:41
Jeff Schaller
31.1k846105
asked May 2 at 15:19
Sam
65118
add a comment |Â
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?
text-processing files sql
edited May 2 at 17:41
Jeff Schaller
31.1k846105
asked May 2 at 15:19
Sam
65118
add a comment |Â
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?
text-processing files sql
edited May 2 at 17:41
Jeff Schaller
31.1k846105
asked May 2 at 15:19
Sam
65118
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?
text-processing files sql
edited May 2 at 17:41
Jeff Schaller
31.1k846105
asked May 2 at 15:19
Sam
65118
edited May 2 at 17:41
Jeff Schaller
31.1k846105
edited May 2 at 17:41
Jeff Schaller
31.1k846105
edited May 2 at 17:41
Jeff Schaller
31.1k846105
31.1k846105
asked May 2 at 15:19
Sam
65118
asked May 2 at 15:19
Sam
65118
asked May 2 at 15:19
Sam
65118
65118
add a comment |Â
add a comment |Â
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.
edited May 2 at 15:51
derobert
68.4k8147203
answered May 2 at 15:33
DopeGhoti
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 ofINSERTstatements).
– DopeGhoti
May 2 at 16:47
add a comment |Â
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.
edited May 2 at 15:51
derobert
68.4k8147203
answered May 2 at 15:33
DopeGhoti
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 ofINSERTstatements).
– DopeGhoti
May 2 at 16:47
add a comment |Â
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.
edited May 2 at 15:51
derobert
68.4k8147203
answered May 2 at 15:33
DopeGhoti
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 ofINSERTstatements).
– DopeGhoti
May 2 at 16:47
add a comment |Â
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.
edited May 2 at 15:51
derobert
68.4k8147203
answered May 2 at 15:33
DopeGhoti
40k54779
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.
edited May 2 at 15:51
derobert
68.4k8147203
answered May 2 at 15:33
DopeGhoti
40k54779
edited May 2 at 15:51
derobert
68.4k8147203
edited May 2 at 15:51
derobert
68.4k8147203
edited May 2 at 15:51
derobert
68.4k8147203
68.4k8147203
answered May 2 at 15:33
DopeGhoti
40k54779
answered May 2 at 15:33
DopeGhoti
40k54779
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 ofINSERTstatements).
– DopeGhoti
May 2 at 16:47
add a comment |Â
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 ofINSERTstatements).
– 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
add a comment |Â
Â
draft saved
draft discarded
Â
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
