Mysqldump

From DreamHost
Jump to: navigation, search

Overview

This article is a quick overview on how to perform a MySQL database backup using the mysqldump command. The mysqldump can be performed via command line through a shell user on your web server or any computer that is allowed to connect to your database.

Note2 icon.png Note: You must first create/enable a shell user to connect to your web server. To create a shell user, visit the following article:


Backing up databases

Visit the following article which explains how to perform a backup using the mysqldump command:

Using a mysqldump script in a cron job

You can also create a cron job that will utilize mysqldump to create database backups for you through a script.

The following script:

  • dumps a database in the format "yourusername_mm-dd-yyyy.sql" to the directory "/home/username/backup/”.
  • assumes that all the included databases run on the same host, and use the same user.
  • has been tested on large MySQL databases (600MB+), and has worked very well.

In the script below, you must fill in the first six variables, namely: "domains", "sqldbs", "opath", "mysqlhost", "username", and "password". Then, simply copy and paste it into cron.

Note2 icon.png Note: In the following example, the opath varilable points to $HOME/backup/. Make sure you have created this backup directory first before proceeding.


domains=( example.com example2.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

See also