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 mysqldump
MySQL 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 backup092210.sql execute the following variation:%>mysqldump -u root -p --all-databases > backup092210.sql Enter password: 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 USE command) before creating the tables and data associated with that database.To backup a single database named
wiki to a file named wiki-backup092210.sql, execute the following command:%>mysqldump -u root -p wiki > wiki-backup092210.sql Enter password: users table in a database named wiki to a file named wikiusers-backup092210.sql, execute the following command:%>mysqldump -u root -p wiki users > wikiusers-backup092210.sql Enter password:Refining the Backup Contents
You might occasionally only be interested in backing up a database's schema, or conversely only its data. To backup the schema, pass the--no-data option to mysqldump:%>mysqldump -u root -p --no-data wiki > wiki-backup092210.sql --no-create-info option:%>mysqldump -u root -p --no-create-info wiki > wiki-backup092210.sqlAutomating the Backup Process
As 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 asnightly-backup.sh which looks like this:#!/bin/sh mysqldump -uroot -psecret wiki > /home/backup/sql/wiki-backup-`date +%m%d%Y`.sqlIf you execute this script, it will result in the
wiki database 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 -u and -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:
%>crontab -e 0 3 * * * /home/backup/scripts/nightly-backup.sh 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`" admin@example.com < /tmp/.www/sqlmysql
Advanced Backup Solutions
As 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.Conclusion
As 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!REFERENCES
http://www.databasejournal.com/features/mysql/article.php/3905786/article.htm
