How do I import a single database from a .sql file that contains multiple databases

Question

How do I import a single database from a backup file that has multiple databases on it?

The main issue is that the file is 921mb so I can't successfully open it in notepad or any of the IDE's I have. If I could do that, I would get the SQL that I need and just manually copy it into phpMyAdmin.

It would be good if I could just import it straight from the backup file.

I guess it would be something like this but I can't get it to work

mysql -u root -p --database1 database1 < backup.sql

Can anybody help? Thanks

Answer 1

I had a problem like this with some data loading scripts. The scripts were in the form:

insert into table(a,b,c) values((a0,b0,c0),(a1,b1,c1),...(a50000,b50000,c50000));

and contained from 5 to several dozen of these hyper-long statements. This format wasn't recognizable by the system I wanted to import the data into. That needed the form:

insert into table(a,b,c) values(a0,b0,c0);
insert into table(a,b,c) values(a1,b1,c1);
...
insert into table(a,b,c) values(a50000,b50000,c50000);

Even the smaller scripts were several MB and took up to an hour to load into a text editor. So making these changes in a standard text editor was out of the question. I wrote a quick little Java app that read in the first format and created a text file consisting of the second format. Even the largest scripts took less than 20 seconds total. Yes, that's seconds not minutes. That's even when a lot of the data was quoted text so I had to make the parser quote-aware.

You can write your own app (Java, C#, Perl, whatever) to do something similar. Write to a separate script file only those lines that pertain to the database you want. It will take a few hours or days to write and test the app, but it has probably taken you more than that just to research text editors that work with very large files -- just to find out they don't really.

Answer 2

Does the target database already exist?

if so:

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

otherwise:

mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]