I'm trying to restore single database from mysqldump all-databases with different name

Question

I'm running mysql-server-5.1.73-3.el6_5.x86_64:

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

mysql>

I created mysqldump with option --all-databases, to create database backup of all databases located on that server.

I need to restore SINGLE database out of this whole mysqldump and restore it with different (from original) database name.

I usually use something like this: mysql --one-database --database=db1 but that's assuming I'm restoring under SAME name.

Could someone give me a hand with this please?

Thanks in advance!

score 0 · Accepted Answer · edited May 23 '17 at 12:40

After much Googling and messing about :-) I succeeded in getting it done ultimately due to this post here. It's quite simple and logical really - I just went down a few blind allies. Check out the answer by user1201232 and not the accepted answer - with a slight modification from me (experimenting!).

You get your dump of your entire server as you have done.

./bin/mysqldump -S ./mysql.sock -u root -pdba --all-databases > serverdump.sql

Take a backup of this original dump (see why later).

Then, you perform a sed on the file. I have a schema called gnarly which I renamed to xyzg in the dump file. This is a good reason to take a backup, otherwise, if your schema name is some common word, it might mess up other restores that you may wish to perform.

sed -i 's/gnarly/xyzg/g' serverdump.sql

Then create a schema called xyzg on the server

./bin/mysqladmin -S ./mysql.sock -u root -pdba create xyzg

Then restore the backup "fooling" mysql into thinking it's restoring an already made one.

./bin/mysql -S ./mysql.sock -u root -pdba xyzg < serverdump.sql

Connect and check your tables.

+--------------------+
| Tables_in_xyzg     |
+--------------------+
| customer_addresses |
| customers          |
| order_items        |
| order_payments     |
| order_status       |
| orders             |
| recurring_orders   |
| status_types       |
+--------------------+

A couple of selects showed that my data was indeed present. Et voilà!

Forgot to mention that MySQL Workbench has a way of doing this - go through the Data Import/Restore and there's a "New" button you can use to select a different schema name. My setup is custom and I wasn't able to get this to work, but if you have a default setup, it should work (I believe).

Of course, there's always the good old manual editing of the dump file. I did some further reading and found this is not as difficult as it might appear.

Using the example above, changing schema gnarly to xyzg.

Get your dump (serverdump.sql) of all schemas as per above.

From the shell prompt

$> grep "USE \`gnarly\`"  
USE `gnarly`;  
USE `gnarly`;  
$>

only occurs twice. Use vi (or the editor of your choice) to change gnarly to the string of your choice, i.e. xyzg.

[EDIT]

Better way again - no need to edit anything.

Just do

sed -i 's/USE \`gnarly\`/USE \`xyzg\`/g' serverdump.sql

No need to use vi - or worry about changing anything else in the file.

Create a schema xyzg as per above and follow the other steps.

Might be useful if sed is overkill. From here.

I'm trying to restore single database from mysqldump all-databases with different name

1 Answers1