Mysqldump

From DreamHost
Jump to: navigation, search

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

External Links