Mysqldump
From DreamHost
The mysqldump client is a backup program that can be used to dump a database or a collection of databases for backup, or for transferring the data to another SQL server (not necessarily a MySQL server). The dump contains SQL statements to create the table or populate it, or both.
Backing up Databases
You can write a script to backup multiple databases and place the generate sql statements into sorted paths.
mysqldump script
I just jotted this script to backup my tables. You may call it via a crontab or Dreamhost's Cron job Interface (depending on your comfort with linux) based on the frequency you need to perform backups.
This script assumes that all the included DBs run on the same host, and use the same user
#!/bin/bash
#@author Eddie Webb edwardawebb.com January 11, 2009
#
# Feel free to make it betta!
#####
# Set these values!
####
# space separated list of domain names (will be used as part of the output path)
domains=( domain1.com domain2.org )
#list of corresponding DB names
sqldbs=( domain1_app domain2_app )
#Directory to save generated sql files (domain name is appended)
opath=$HOME/sql_dumps/
# your mysql host
mysqlhost=mysql.yourdomain.com
#username for host
username=YOURUSERNAME
#corresponding password
password=ANDTHEPASSSWORD
#####
# End of config values
#####
#date to append
suffix=$(date +%m-%d-%Y)
#run on each domain
for (( i = 0 ; i < ${#domains[@]} ; i++ ))
do
#set current output path
cpath=$opath${domains[$i]}
#check if we need to make path
if [ -d $cpath ]
then
# direcotry exists, we're good to continue
filler="just some action to prevent syntax error"
else
#we need to make the directory
echo Creating $cpath
mkdir -p $cpath
fi
#now do the backup
mysqldump -c -h $mysqlhost --user $username --password=$password ${sqldbs[$i]} > ${cpath}/${sqldbs[$i]}_$suffix.sql
done
