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.

A few notes on 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.

In the following example, the opath variable 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

Troubleshooting

Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1'

When running mysqldump, you may see the following error message:

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

This means your mysqldump version is too old compared to your MySQL server version.

To update your mysqldump version, you'll have to compile a new version of MySQL (which first requires compiling dependencies such as cmake).

Any commands that require root/sudo access can only be ran on a Dedicated server. Please note that DreamHost support is unable to assist with any such custom changes to a server. Be prepared to troubleshoot any installation errors yourself.

See also

External links