Search code examples
mysqlsqlbackupmysqlimport

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

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

Solution

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