Import single database from --all-databases dump

Is it possible to import a single database from an --all-databases mysqldump? I guess I can modify the file manually but wondering if there are any command line options to do this.

I am 开发者_开发百科moving servers and have a lot of databases, most of which I don't currently need or want at the moment but would like to have the option to restore a single one if need be.

You can use the following command:

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

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

Another option which is IMHO much safer, is to extract the DB from an --all-databases dump. 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. That way you'll have the seperated DB on file, and then you can restore using a simple mysql command.

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

mysqldump output is just a set of SQL statements.

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

mysql -D mydatabase -o mydatabase < dump.sql

This will only execute the commands when mydatabase is in use

When using the sed-approach suggested by Hetzbh, be sure to manually copy the initial and final lines from the original dump, such as e.g.

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

and

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

to the start and end respectively of the stripped file produced by sed. Otherwise import may fail due to foreign constraints not respected by the alphabetic order of the tables in the dump (i.e. errno: 150 "Foreign key constraint is incorrectly formed").

Here is the command that would restore only the database named $DB_FROM from $BACKUP_FILE backup file:

(sed '/^-- Current Database: `/q' "$BACKUP_FILE"; sed -n "/^-- Current Database: \`$DB_FROM\`/,/^-- Current Database: \`/p" "$BACKUP_FILE") | mysql -u root -p

If you like at the same time to restore the new database as $DB_TO (instead of $DB_FROM), that means restoring the database to a different name, here is the full command:

(sed '/^-- Current Database: `/q' "$BACKUP_FILE"; sed -n "/^-- Current Database: \`$DB_FROM\`/,/^-- Current Database: \`/p" "$BACKUP_FILE") | sed "s/\`$DB_FROM\`/\`$DB_TO\`/g" | mysql -u root -p

My answer was inspired by this one as well as this one and this webpage.

The method suggested by MariaDB docs is to restore just one object (table or database) by creating temporarily a user with just the privileges for the object to restore, like so:

GRANT SELECT
ON db1.* TO 'admin_restore_temp'@'localhost' 
IDENTIFIED BY 'its_pwd';

GRANT ALL ON db1.table1
TO 'admin_restore_temp'@'localhost';

After that, the restore can be done safely:

mysql --user admin_restore_temp --password --force < /data/backup/db1.sql