Import single database from --all-databases dump

mysql database import mysqldump
savageguy picture savageguy · Feb 26, 2010 · Viewed 64.9k times · Source

Is it possible to import a single database from an --all-databases mysqldump? I guess I can modify the file manually but wondering if there are any command line options to do this.

I am moving servers and have a lot of databases, most of which I don't currently need or want at the moment but would like to have the option to restore a single one if need be.

Answer

Hetzbh picture Hetzbh · Sep 22, 2014

You can use the following command:

mysql -u root -p --one-database destdbname < alldatabases.sql

Where destdbname is your desired database which you want to restore.

Another option which is IMHO much safer, is to extract the DB from an --all-databases dump. Example:

sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' alldatabases.sql > output.sql

Replace dbname with the desired database name. alldatabases.sql is the name of your sql-dump file. That way you'll have the seperated DB on file, and then you can restore using a simple mysql command.

(Credits goes to: Darren Mothersele - see his page)

Related questions

How do I restore one database from a mysqldump containing multiple databases?

I have a mysql dump with 5 databases and would like to know if there is a way to import just one of those (using mysqldump or other). Suggestions appreciated.

Read More
Import SQL file into mysql

I have a database called nitm. I haven't created any tables there. But I have a SQL file which contains all the necessary data for the database. The file is nitm.sql which is in C:\ drive. This file has …

Read More
MySQL: ignore errors when importing?

I am importing a fairly large database. The .sql file has almost 1,000,000 lines in it. Problem is that I am getting a syntax error when trying to import the database. It says: ERROR 1064 (42000) at line 8428420: You have an error in …

Read More