Mysqldump
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
You may call the following script via Dreamhost's Cron job Interface based on the frequency you need to perform backups. The script will dump a database in the format "yourusername_mm-dd-yyyy.sql" to the directory "/home/username/backup/". The script assumes that all the included databases run on the same host, and use the same user.
This script has been tested on large MySQL databases (600MB+), and has worked very well.
In the script below you need to fill in the first six variables, namely, "domains", "sqldbs", "opath", "mysqlhost", "username" and "password", then just copy and paste it into cron:
domains=( yourdomain.com yourdomain2.com )
sqldbs=( yourdb1 yourdb2 )
opath=$HOME/backup/
mysqlhost=mysqlhostname
username=mysqlusername
password=mysqlpassword
suffix=$(date +%m-%d-%Y)
for (( i = 0 ; i < ${#domains[@]} ; i++ ))
do
cpath=$opath${domains[$i]}
if [ -d $cpath ]
then
filler="just some action to prevent syntax error"
else
echo Creating $cpath
mkdir -p $cpath
fi
mysqldump -c -h $mysqlhost --user $username --password=$password ${sqldbs[$i]} > ${cpath}/${sqldbs[$i]}_$suffix.sql
done
