Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a scheduled mysqldump task that uses the --all-databases option. This includes all databases in one file.

How can I tell mysql to import ONE of the databases contained in the dump file?


Thanks for the answer guys. Much appreciated... I ended up not using the --all-databases option. Here's an alternative script:

#! /bin/bash
date=`date +%A`
for i in /var/lib/mysql/*/; do
dbname=`basename $i`
mysqldump -u <whatever username> --password=<whatever pw>--single-transaction $dbname > <path>/$date/$dbname-backup.sql
share|improve this question
Your alternative script is flawed because it will miss at the least all InnoDB databases! Their names won't show up in /var/lib/mysql/* because they're stored in a ibdata1 file. Let MySQL tell you which databases it knows instead, with the 'show databases' command. See womble's example below. – Martijn Heemels Dec 29 '09 at 13:27
Good catch. Thanks! – Mike B Dec 30 '09 at 4:19
up vote 3 down vote accepted

Edit the dump file to extract out the statements related to the database of interest, or else load the whole dump into a scratch database server and dump just the database of interest.

This is why people in the know dump their databases to separate files.

share|improve this answer
Thanks womble. Question... is there a way to tell mysql to dump all databases to separate files? I'd like to set something up that won't require me to know the names of all databases ahead of time. – Mike B Dec 19 '09 at 21:03
for i in $(mysql -N -e 'show databases'); do mysqldump $i >/var/backups/mysql/$i.sql; gzip /var/backups/mysql/$i.sql; done should do the trick, assuming your credentials file is in order. – womble Dec 19 '09 at 23:06

Here is another modificatio.

for i in $(mysql -N -e 'show databases' -uroot -p'password' ); do mysqldump -uroot -p'password' $i | gzip -9c > /root/backups/$i\_$(date +\%Y-\%m-\%d_\%Hh\%M).sql.gz; done
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.