Skip all databases and run only specific one

Question

I have a sql file generated by "mysqldump --all-databases" . There are many databases in it. What I want to do is to update my local database but only a specific one, not all. I tried to use "mysql -database=db_name < file.sql" but it updated all databases. Is there a way to skip all databases except the one that I want.

Answer 1

You can try doing:

mysql -D example_database -o < dump.sql

This will only execute the SQL commands for the specified database and will skip the commands for all other databases. The -o ("one database") option is critical to this working as expected (it tells mysql to ignore statements related to other databases).

dump.sql is the result of executing mysqldump --all-databases

Answer 2

it's getting dirty, stop reading if or be warned ;)

It's not pretty and maybe there is a better (correct) way to achive this. But assuming the dumps you are working with aren't to big, you might be quicker by importing the full dump into temporary DBs and creating a fresh dump of the database you'd like to import. (As I said, not pretty)

Additionally, you should really make sure that you're able to restore backups you make (in any imaginable way). This could get really embarrassing the day you need them urgently.

Answer 3

A couple of ideas:

1. You can edit the dump file (it's just a text file, after all), extracting only the items related to the database you want to restore. I think each database is written to the file individually, so this is a matter of identifying the one big block related to the one you want and deleting the bits before and after it -- e.g., not as much of a chore as it sounds. Look for the create database and use statements (there's also -- at least in my version, with my options) a banner comment "Current Database: foo" at the top of each section). This would be pretty easy to do with vi or anything else that lets you do large operations easily. You can then search through the result ensuring that there are no cross-references to the DBs you don't want updated.

2. You can back up the databases you don't want updated, do the update, then restore them. I mean, before you do this sort of thing, you have a backup anyway, right? ;-)

3. You can restore to a blank MySQL instance, then grab the backup of the one you want. Blech.

4. (Variation on #3) You can do a rename on all of your current databases, import the file, then drop the ones you don't want and rename the originals back. Also blech.

I'd probably go with #1 (after a full backup). Good luck with it.

Edit No, I'd go with codaddict's -D databasename -o solution (after a full backup). Nice one