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
Mike B
  • 11,871
  • 42
  • 107
  • 168
  • 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

2 Answers2

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.

womble
  • 96,255
  • 29
  • 175
  • 230
  • 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
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
Ura
  • 248
  • 1
  • 2
  • 12