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.

Dh-kb-note-icon.fw.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 to 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.

Dh-kb-note-icon.fw.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

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).

Dh-kb-important-icon.fw.png Important:

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