How do I use a mysqldump script to back up my database?

You can create a cron job to utilize mysqldump in order to create a database backup.

This script does the following:

  • dumps a database in the format "yourusername_mm-dd-yyyy.sql" to the directory "/home/username/backup/example.com”.
  • 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.

What you need to change in the script

In the script below, you must fill in the first six variables that are highlighted:

  • domains — The script uses this field to create a directory under $HOME/backups/. This is the directory that will store your database. It makes sense to name it after the domain that uses the database.
  • sqldbs — The name of your database.
  • opath — The directory you want to back up to. You can change this if you like.
  • mysqlhost — Your MySQL database hostname.
  • username — Your MySQL username.
  • password — Your MySQL password.

Then, simply copy and paste it into a cron job.

The script shows how to backup two databases at once that are under the same MySQL username. If you're only backing up a single database, just remove 'example2.com' and 'yourdb2'.

In the following example, the opath variable points to $HOME/backup/. Make sure you have created this backup directory first before proceeding.

Creating the file

Log into your server via SSH and create a file named something like backup.sh. Add the following code to this file.

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

Running the file

You can now run the file manually with the following command:

[server]$ sh backup.sh

You can also create a cron job to run this automatically at specific times.

See also

External links

Did this article answer your questions?

Article last updated .