Import single database from --all-databases dump
Solution 1
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
Solution 2
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)
Solution 3
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"
).
savageguy
Updated on September 29, 2021Comments
-
savageguy over 2 years
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.
-
savageguy about 14 yearsThanks for the fast answer! Awesome.
-
davidselo over 11 yearsVery useful for me, Thanks!! you can add --disable-keys for avoid errors of foreign keys ;) mysql -u user -D --disable-keys database -o <dump.sql
-
RolandoMySQLDBA almost 10 yearsThis is answer is very reckless and incredibly dangerous. If you launch
mysqldump --all-databases
, the mysqldump output will containDROP DATABASE IF EXISTS dbname; CREATE DATABASE dbname; USE dbname;
for every database in the MySQL instance, including the mysql schema. Please look at the mysqldump documentation: dev.mysql.com/doc/refman/5.5/en/…. That means every database will mercilessly get overwritten. Can you provide proof that it will be skipping all database but one ??? NOTE: You could do this to binary logs using mysqlbinlog. -
topher over 9 yearsFor the paranoid, I first created a user who only had rights to the specific database and then ran this command with that user's credentials. I got an error when it tried to switch databases but the data for the specific database was retained.
-
neuro over 9 yearsGreat solution ! This said I had to add the original dump header to avoid some errors ...
-
jah over 8 yearsThis answer is incorrect. The -D option has no effect when reading MySQL statements from a file generated by mysqldump --all-databases. Furthermore it will DROP and CREATE tables in the mysql schema, including the users table.
-
Bysander almost 5 yearsThis should be the accpted answer
-
Somnium almost 5 yearsNote that this does not work in Powershell, but works in cmd
-
George Chalhoub almost 5 yearsThe sed thing is really helpful!
-
Abdull about 4 yearsHow come you say Another option which is IMHO much safer? Are there any risks in using
--one-database
? -
João Pimentel Ferreira almost 4 years@RolandoMySQLDBA I tested now and with the option
-o
I asure you my other databases were not overwritten, thus reconsider your comment -
João Pimentel Ferreira almost 4 yearsI tested now and it does work. The other databases were kept
-
Daniel over 2 yearsMaybe I'm wrong, but wouldn't the
sed
extraction fail if the DB in question happens to be the last one exported (or the only one), as there wouldn't be a secondCurrent Database
to match?