1

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
Improve this question
1
  • 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, 2009 at 13:27

2 Answers 2

Reset to default
3

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.

Improve this answer
2
  • 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, 2009 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, 2009 at 23:06
1

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
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .