Most people backup their web site files. But with so many sites being database-driven it is equally important to make regular MySQL database backups. If you are running a blog, a forum or any database-driven web site and your database gets nuked, well there goes all your content. How long will it take to restore it and get your site back online? If you have a backup, the data loss should be minimal and restoration of your backup should take no time at all. If you don’t have a backup you are probably screwed and all your work has disappeared into the ether.
What options do you have for creating a MySQL database backup? There are plenty and many of them can be automated using shell scripting or cronjobs so you can set them and forget them (until you need them!). Let’s look at a few manual backup methods. In a later post I will talk a little bit about automating backups.
PhpMyAdmin
Most of you are probably familiar with PhpMyAdmin, the PHP web-based administration backend for MySQL. PhpMyAdmin has a tab called export where by you can backup your entire database or just selected tables. The default export settings are usually perfect, although you can tweak them according to your needs, although you should always export as SQL, as that is the format your database will expect if you ever need to restore it. Lastly, you want to save the file to your local computer and to save space and transfer time, compress it in the format of your choice.
mysqldump
mysqldump is a command line utility which pretty much does the same thing as PhpMyAdmin except it’s shell based and not web based. So you will need a shell account on the server where your database resides. The basic syntax for using mysqldump is:
mysqldump -u [user name] -p[password] [database name] > [dump file]
So you will need to provide your database username, database password, database name and finally a filename which will be the schema file containing the entire database structure and data.
If your database is large you might want to compress it to save space and transfer time. You can backup the database and compress the database in one swoop like so:
mysqldump –-user [user name] –-password=[password] [database name] > [dump file] | gzip > [dump file]
Now, to restore a database with mysqldump you would issue do something like this:
mysql -u [username ] -p[password] [database name] < [dump_file]
This same command could also be used for a schema file which you created with PhpMyAdmin. You would of course need to upload the schema file to your account. I find working with schema files through the shell a lot faster than working with PhpMyAdmin. PhpMyAdmin can also choke on your schema, particularly if you are saving binary info to your database.
mysqlhotcopy
mysqlhotcopy is another command line utility for creating MySQL database backups. It has many advantages over mysqldump:
- It’s very fast. So it’s suitable for large databases which would be both time consuming to backup as well as restore.
- It makes exact copies of the actual files which make up the database tables.
- It ensure data integrity by locking and flushing the tables while they are being copied. Other database backup methods can export a backup while a record is being written which can lead to a corruption in the backup.
Now, despite it’s overwhelming positives, mysqlhotcopy comes with one large downfall which makes it not practical for everyone. To restore the files, you need to have access to the actual files which make up your database. If you are on a shared web host or don’t have root access on your database server you won’t be able to restore the files yourself. You might be able to ask the web host to copy the files and flush the database for you. But you should check before you use this method, lest you get stuck with a backup which you can’t restore.
So how do you use mysqlhotcopy? It’s a shell command similar to mysqldump:
mysqlhotcopy -u [username] -p[password] db_name [/path/to/new_directory]
What this will do is lock and flush the tables in your database and make an exact copy of those files to the directory your specified. To restore your database, you simply copy these files into your database directory and flush or restart MySQL. The location on my server would be /var/lib/mysql/table_name/, but yours might be different.
So this covers some manual backup methods. Manual backups are better than nothing, but they still leave something to be desired. It can be time consuming to run backups manually and would be better to have them run automatically at regular intervals. It would also be ideal to have some sort of backup rotation where you could choose between restoring yesterday’s backup, last week’s backup or anything in between. This is a topic I will try to cover in a future post. But for now, just start backing up your databases.
