From DreamHost
Revision as of 05:13, 4 December 2010 by Jasdeepharibhajan (Talk | contribs) (added a note about script being tested on database of 600mb+)

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=( )
sqldbs=( yourdb1 yourdb2 )
suffix=$(date +%m-%d-%Y)
for (( i = 0 ; i < ${#domains[@]} ; i++ ))
	if [ -d $cpath ]
		filler="just some action to prevent syntax error"
		echo Creating $cpath
		mkdir -p $cpath
	mysqldump -c -h $mysqlhost --user $username --password=$password ${sqldbs[$i]} > ${cpath}/${sqldbs[$i]}_$suffix.sql

External Links