Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 273
The Pixel Developer Avatar asked Aug 29 '10 20:08

The Pixel Developer


2 Answers

You can use the mysql command line --one-database option.

mysql> mysql -u root -p --one-database YOURDBNAME < YOURFILE.SQL

Of course be careful when you do this.

You can also use a mysql dumpsplitter.

like image 166
Tim Avatar answered Sep 28 '22 15:09

Tim


You can pipe the dumped SQL through sed and have it extract the database for you. Something like:

cat mysqldumped.sql | \
sed -n -e '/^CREATE DATABASE.*`the_database_you_want`/,/^CREATE DATABASE/ p' | \
sed -e '$d' | \
mysql

The two sed commands:

  1. Only print the lines matching between the CREATE DATABASE lines (including both CREATE DATABASE lines), and
  2. Delete the last CREATE DATABASE line from the output since we don't want mysqld to create a second database.

If your dump does not contain the CREATE DATABASE lines, you can also match against the USE lines.

like image 35
rjk Avatar answered Sep 28 '22 14:09

rjk