Ok, here is what I am trying to accomplish:

I want to backup each database on my server to a different file. I also want to be able to add databases without having to modify the script.

I have seen scripts that do a dump of all databases into one file, but I am looking for some way to do it individually so that if I need to restore a small 1M database I don't have to sift through a 1-2G db dump to find that part.


Thanks in advance.

#!/bin/sh

##########################
# mysqldump backup script
# Written by: Dan
# www.serve-you.net
##########################


BACKUP_DIR=/root/mysql
BACKUP_MODE=600
MAX_BACKUPS=3

MYSQLDUMP=/usr/local/psa/mysql/bin/mysqldump
MYSQL_USER=admin
MYSQL_PASSWORD=adminpass
DATE=`date +%d%b%Y-%H%M`
ARCHIVE_FILE="mysqlbackup-$DATE.sql.tar.gz"
ARCHIVE_DIR=/usr/local/archive/mysql
SQL_BACKUP_FILE="$BACKUP_DIR/mysqlbackup-$DATE.sql"

$MYSQLDUMP -lv -u$MYSQL_USER -p$MYSQL_PASSWORD -A > $SQL_BACKUP_FILE

tar cfpsz $ARCHIVE_DIR/$ARCHIVE_FILE $SQL_BACKUP_FILE

chmod $BACKUP_MODE $ARCHIVE_DIR/$ARCHIVE_FILE
chown root:root $ARCHIVE_DIR/$ARCHIVE_FILE

# remove old backupz

cd $BACKUP_DIR
rm mysqlbackup*

# remove old archivez

cd $ARCHIVE_DIR

DONE=0
until [ $DONE -eq 1 ]
do
FILES=`ls -1rc`
TOTAL_FILES=`echo "$FILES" | wc -l`

if [ $TOTAL_FILES -gt $MAX_BACKUPS ]
then
OLD_ARCHIVE=`echo "$FILES" | head -1`
rm $OLD_ARCHIVE
else
DONE=1
fi
done
scp -o "Protocol=1" -pC $ARCHIVE_DIR/$ARCHIVE_FILE authuser@backup.domain.com:/path/to/mysql/backup/dir

#end


This what you looking for??


Just read closer.. Hmm I don't think this does indivdual files.. hmmm

Thanks for the post. I have something like that...but it puts everything in one file.

Anyone else?

You could add each mysqldump to cron, but it would mean having to do each database manually, but they would be saved to individual files that way...

Yes, I have considered that, I even have a script that I can list them individually in, but as users are able to add/delete their own databases, I would like for this to be automatic

Something like this might work assuming you run it as root and have root's .my.cnf file configured appropriately.

#!/usr/bin/perl

my $dir = "/var/lib/mysql";
my $dumpdir = "/path/to/backup/sql/directory";

opendir(D, $dir) || die "Can't open $dir: $!";
my @files = readdir(D);
close D;

foreach my $file (@files) {
next if ($file =~ /\.+/);
print "${file}...\n";
system("mysqldump $file > ${dumpdir}/$file.sql");
}That will dump all of the databases into database_name.sql in the backup directory you specify. Note that running this more than once will overwrite previous backups in your backup directory. No guarantees, no warranties, etc. :)