How do I backup a MySQL database?

Night data (photo by tiseb - CC-BY)

Night data (photo by tiseb - CC-BY)

The open-source MySQL database is widely used for web applications such as WordPress and phpBB. Like any database, it needs to be backed up to protect the content of the website from failure of the database or the server on which it runs. There are a multitude of different ways to backup MySQL.

If you prefer to backup from your browser, you can install phpMyAdmin, a web-based maintenance utility for MySQL databases. From the phpMyAdmin home screen click “Export”. Select all the databases, make sure “Structure” and “Data” are selected, select “Save as file” and provide a file name (or a file name template pattern). If your databases are large, select a compression option. Then click “Go”.

You may prefer to work from the command line using the mysqldump utility that should be installed alongside MySQL. Here is how to perform a backup using mysqldump from the bash shell on Linux or Unix. The basic command is:

mysqldump --user=root --password=???? --opt --all-databases
 > filename.sql

Replace “????” with your MySQL root password, and replace “filename.sql” by a filename of your choice. It can be a security risk to include your MySQL password on the command line because, for example, the command line history would record it. If you omit the –password argument, you will be prompted for your password after you issue the command. The “--opt” option sets a standard group of options that will be suitable for many users, but you can set options one-by-one if you need finer control.

If you don’t have root MySQL access, you can’t backup all the databases but you can backup any that you have access to:

mysqldump --user=username --opt --databases databasename
 > filename.sql

Replace “username” by your own MySQL user name, and replace “databasename” by the MySQL name of the database that you wish to backup.

If you have a large database, you may want to compress the output by piping the output of mysqldump through a compression utility such as gzip (or bzip2 with the -c option).

If you just want to backup the table structure, not the data stored in those tables, add the “--no-data” option.

You could put your backup command into a shell script, and have it executed regularly by means of a “cron job”. On Fedora Linux a “cron job” is set up by copying the shell script into one of the cron directories, such as /etc/cron.daily (don’t forget to make the script file executable).

Other ways to backup a MySQL database include the mysqlhotbackup utility, or by replicating your database to another server.

Of course you must test your backup before you can assume that it is working. You could do this by restoring it into a different MySQL server. Vinu Thomas has written a nice overview of MySQL backup and restore.

Related questions:

  Need research? Quezi's researchers can answer your questions at uclue.com

Written by | 3,950 views | Tags: , , , , , , , ,

No Comments »

RSS feed for comments on this post. TrackBack URL

Leave a Reply

Privacy Policy | Acknowledgements