Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

-M


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
done
share|improve this question
1  
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
add comment

2 Answers

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
1  
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
add comment

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
add comment

Your Answer

 
discard

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.