Backup MySQL for all clients automatically

[cawheele]

Hello everyone,

I was just wondering how I would go about backing up the databases for all of my clients without logging in to each account manually. I have a reseller account.

Thanks,

Chris

[sawbuck]

One possibility. http://restkultur.ch/personal/wolf/scripts/db_backup/

[sleddog]

Here's another possibility, a little Bash shell script I wrote and which I've been using for a couple years. It could be nicer and fancier, but it does what I need

- Provide it the root MySQL username/password
- Enter a list of databases
- Set the number of days you want to keep backups around
- Chown it to a trusted user, or root.
- Chmod it 700
- Create the backup directory, chown/chmod own it the same as the script
- Run it manually once (as the owner or root) to check things out
- Schedule a cronjob (as the owner or root, as appropriate) to run once nightly.

#!/bin/bash
# Script to backup mySQL databases by [sleddog]

# --- User options - must be edited ---

# A space-delimited list of databases to be backed up:
db_list="database1 database2 database3"

db_username=root
db_password=password
backup_dir=/some/directory/backup
days_keep=7

# --- Do not edit below here, unless of course you know what you're doing ---

today=`date '+%Y%m%d'`
oldday=`date '+%Y%m%d' --date "$days_keep days ago"`

if [ -e $backup_dir ];
  then
    cd $backup_dir
    for dbname in $db_list;
      do mysqldump -u $db_username -p$db_password --opt $dbname > $dbname.sql ;
    done
    tar -cf $today.tar *.sql && gzip -9 $today.tar
    if [ -e $oldday.tar.gz ];
      then
        rm -f $oldday.tar.gz
    fi
    rm -f *.sql
  else
    echo Backup directory does not exist!
fi

chmod 600 $backup_dir/*.gz