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/”.
  • 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 '' 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 Add the following code to this file.

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

Running the file

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

[server]$ 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 PST.