Whether you host your website and database internally or you use a hosting provider, it is of paramount concern that you institute a rigorous backup policy in order to avoid such disastrous consequences. If you're using MySQL, several easily implementable options are at your disposal. In this article I'll introduce you to one such option by showing you how to backup your database using MySQL's native mysqldump client.
Introducing mysqldumpMySQL is bundled with a number of command-line utilities, among them mysql (a general purpose utility for interacting with MySQL in many ways, including database schemas and data, users, and configuration variables), mysqladmin (useful for carrying out various administrative tasks), and mysqldump. The mysqldump utility is useful for backing up both your database schemas and data, allowing you to not only perform a blanket backup of all databases residing on a server, but also to selectively backup a single database or even specific tables within a database.
Chances are that when MySQL was installed on your server, the system path was modified so that you will have global command-line access to the clients. Open a terminal window and execute the following command:
%>mysqldump Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] For more options, use mysqldump --help
The usage instructions which follow identify the different ways in which the mysqldump client can be invoked. For instance, to backup all database schemas and data to a file named
backup092210.sqlexecute the following variation:
%>mysqldump -u root -p --all-databases > backup092210.sql Enter password:
Note how you need to specify a user possessing read access privileges to all databases (in this case,
root), in addition to supplying a password at the ensuing prompt. Also, if you're following along with the tutorial by executing these commands on your own development server, take a moment to examine the contents of the backup file. You'll see that it consists of a series of SQL statements which successively drop and recreate each table, followed by a series of insertion statements which recreate the associated data. Additionally, because in the above example we've backed up all databases, you'll also find that the backup file will attempt to create each database if it does not exist, and then switch to that database (via the
USEcommand) before creating the tables and data associated with that database.
To backup a single database named
wikito a file named
wiki-backup092210.sql, execute the following command:
%>mysqldump -u root -p wiki > wiki-backup092210.sql Enter password:
Finally, to backup solely the
userstable in a database named
wikito a file named
wikiusers-backup092210.sql, execute the following command:
%>mysqldump -u root -p wiki users > wikiusers-backup092210.sql Enter password:
Refining the Backup ContentsYou might occasionally only be interested in backing up a database's schema, or conversely only its data. To backup the schema, pass the
--no-dataoption to mysqldump:
%>mysqldump -u root -p --no-data wiki > wiki-backup092210.sql
To backup only the data, use the
%>mysqldump -u root -p --no-create-info wiki > wiki-backup092210.sql
Automating the Backup ProcessAs the previous examples demonstrated, it's easy to execute the mysqldump command with just a few keystrokes. However with everything else going on during a typical workday, it is practically guaranteed that you will eventually shirk the duty of consistently executing even this simple command. Therefore you'll want to automate the process, a task easily accomplished using the cron utility (available on all Unix-like operating systems). To automate the backup, create an appropriately-named file such as
nightly-backup.shwhich looks like this:
#!/bin/sh mysqldump -uroot -psecret wiki > /home/backup/sql/wiki-backup-`date +%m%d%Y`.sql
If you execute this script, it will result in the
wikidatabase being backed up to a file named in accordance with the date in which the backup occurred, for instance
wiki-backup-092210.sql. Also note how the backup user's name
root) and password
secret) are passed to mysqldump by appending each to the associated option flag
-p, respectively). Because of the obvious security implications, which could arise from a third-party viewing the contents of this file, take care to ensure that its permissions are set appropriately.
Next you'll want to assign this script to a cron job via crontab. To do so, execute the following command:
This will open the currently logged-in user's crontab file, creating one if it doesn't already exist. Within this file you'll add the following line to ensure that the backup script runs daily at 3am:
0 3 * * * /home/backup/scripts/nightly-backup.sh
If you're new to crontab syntax, the line's preceding arguments are probably confusing. The five arguments point to the script's minute, hour, day of month, month, and day of week in which the script should execute. Therefore to execute a script every Tuesday at 4:45am, you'll assign the arguments
45 4 * * 3.
With the line inserted, save the file and the task will be immediately scheduled for execution at the specified time. Be sure to check the designated directory the next morning to ensure everything is working properly.
#!/bin/bash ###backup mysql### echo "------------Mysql------------------------" >> /tmp/.www/sqlmysql echo "------------------------------------" >> /tmp/.www/sqlmysql rm -f /var/www/sql/database_7.sql.gz mv /var/www/sql/database_6.sql.gz /var/www/sql/database_7.sql.gz mv /var/www/sql/database_5.sql.gz /var/www/sql/database_6.sql.gz mv /var/www/sql/database_4.sql.gz /var/www/sql/database_5.sql.gz mv /var/www/sql/database_3.sql.gz /var/www/sql/database_4.sql.gz mv /var/www/sql/database_2.sql.gz /var/www/sql/database_3.sql.gz mv /var/www/sql/database_1.sql.gz /var/www/sql/database_2.sql.gz mv /var/www/sql/database_0.sql.gz /var/www/sql/database_1.sql.gz /usr/bin/mysqldump -u user -pPassword camera |gzip > /var/www/sql/database_0.sql.gz echo "complete." >> /tmp/.www/sqlmysql mail -s "[backup/mysql] $HOSTNAME - `date`" email@example.com < /tmp/.www/sqlmysql
Advanced Backup SolutionsAs I mentioned at the beginning of this article, mysqldump is just one of MySQL's 'numerous backup solutions. Notably, I recommend looking into performing incremental backups using MySQL's binary log or even copying data from the "master" MySQL server to a secondary "slave" server using what's referred to as replication.
ConclusionAs this article demonstrated, it is incredibly easy to implement a simple MySQL backup solution, accomplished in mere minutes. If you don't already have an acceptable solution in place, take a moment to do so right now. I guarantee the few minutes you spend doing so will save you dozens of hours, if not your job, should your server falter!