Sometimes you may need to restore a specific database, but the only available backup contains all databases. Restoring everything from a backup may not be an option. Instead, you will need the ability to restore only a specific database. Using the –one-database option allows you to restore a single database from a backup of multiple databases.
In order to restore a single database, use the –one-database or -o option when replaying the backup:
mysql -uroot -p[pwd] --one-database [db1] < [/path/backup.sql]
The above command uses the root user account with a password of pwd, and restores only the target_db database. Replace these values with appropriate values for your needs.
WARNING: Do NOT use this method if the backup includes DROP DATABASE statements such as those generated by mysqldump when the --add-drop-database parameter has been set. The --one-database parameter considers only the database which is set as default, not which database is actually affected by an executed statement. Consider a series of statements like the following:
DROP DATABASE IF EXISTS db1; CREATE DATABASE db1; USE db1; -- various other statements DROP DATABASE db2; CREATE DATABASE db2; USE db2; -- various other statements
Since the "DROP DATABASE db2;" appears while db1 is the default database, using "--one-database db1" will cause db2 to be dropped and then recreated, but none of the statements which follow USE db2; will be executed. So db2 will be empty.