Morning,

Doing mysql db dump on a cron job from a file that has something like:
mysqldump --databases db1 db2 db3 > /var/www/Backup/dbs.txt --opt -p

Which is run once a week.

What i'd like is rather than it overwrite the file everyweek, to do it by date so I get a list of:
31_3_04_dbs.txt
24_3_04_dbs.txt

etc

any ideas?

Here is what I'm using. Format the mydate command to however you like by changing the %X variables.

If you don't want to FTP take that part out or install LFTP or whatever you like. LFTP needs the /usr/local/bin in your path or in the script like I have and exported (otherwise take that out).

--opt adds a bunch of options (--lock-tables, --add-drop-table, --add-locks, etc...)

PLEASE notice the spacing in the -p and -u options in mysqldump and the options inside the bracket in the IF



#! /bin/bash
mydate=$(date +%m%d%Y%l%M%p)
PATH=$PATH:/usr/local/bin
export PATH

/usr/local/mysql/bin/mysqldump --flush-logs --opt -u SQLUSER -pSQLPWD dbname > /dir1/dir2/dir3/backup.sql
cd /dir1/dir2/dir3/
if [ -e backup.sql ]
then
tar -czvf backup-$mydate.tar.gz backup.sql
fi
if [ -e backup-$mydate.tar.gz ]
then
lftp <<EOF
open FTPSERVER
user FTPUSER FTPPWD
pwd
put backup-$mydate.tar.gz
bye
EOF
fi
if [ -e backup-$mydate.tar.gz ]
then rm /dir1/dir2/dir3/backup.sql
fi

how about using ssh (scp?) instead of ftp? anyone know how to do with scp?