My company’s website runs on a MySQL database. How do I back this up?

Cain, who works for a real estate agent, sent in a question about MySQL, specifically how to take and restore backups. Now, we all know that backups are really, really important; at least, if you’ve ever suffered a system failure you know exactly how important they are. Unfortunately most people learn this the hard way, so, if you’ve never been bitten, try and imagine you went to work one day and all your systems were down because your database server had been stolen; how would you recover your systems?

Ok, that’s a bit of a far fetched scenario, as most people probably won’t encounter it, but hard disk crashes are more common than that, and sometimes backups are useful to recover a system to a previous known state because of bad code or bad data; so knowing how to backup and restore is extremely important.

So, back to the matter at hand. Backing up a MySQL database is quite simple. It comes with a command line utility called mysqldump (or mysqldump.exe if you’re on a Windows platform). What this does is create a text file will all commands necessary to recreate the database and populate all the tables with the data they contain. The beauty of this is that you can open the backup in a text file and see exactly what it contains.

You can execute the command on the command line in this way:
mysqldump --user=username --password=password databasename >path/backupfilename


where username and password are the credentials of a user who has access to the database and databasename is the name of the database you want to backup. If you do this in Windows, you’ll need to use mysqldump.exe instead of mysqldump.

Restoring the backup is done via the command line again. You can use this command:

mysql -u username -p databasename < path/backupfilename

where username is the name of the user to run the command as and databasename is the name of the database where you want the backup file restored.

Let’s hope you never need it!