Import a single database from a dump of -all-databases

Question

Is it possible to import one database from mysqldump -all databases? I think I can change the file manually, but I wonder if there are any command line options for this.

I move servers and have many databases, most of which I do not need or do not need at the moment, but if necessary, I need to be able to restore one.

Answer 1

mysqldump output is just a collection of SQL .

You can provide the desired database on the command line and skip commands against other databases using:

 mysql -D mydatabase -o < dump.sql 

This will only execute the commands when mydatabase used

Answer 2

You can use the following command:

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

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

Another option, which is IMHO much safer, is to extract the database from the dump --all-databases . 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. This way you will have a separate database in the file, and then you can restore it with a simple mysql command.

Good luck.

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