Most modern websites use MySQL databases to store and manage their content. A database can be used to hold data for anything from a simple list of someone’s favourite DVDs, the login details of site users, or the catalogue of complex e-commerce site. The loss or corruption of such a database could then prove either merely inconvenient or potentially financially disastrous to your business. Because of this, it is highly recommended that database backups are taken regularly.

Backing up databases:

There are many web-based applications that be used to create backups, but by connecting to your server using SSH you can backup your database quite simply using the following command:

MYSQLDUMP -u [username] -p [password] [databasename] > [backupfile.sql]

[username] – this is your database username
[password] – this is the password for your database
[databasename] – the name of your database
[backupfile.sql] – the file to which the backup should be written.

This will then create a dump file which will contain all the SQL statements required to create the tables and re-populate your database .

You can then use FTP to transfer the file to your local computer for safe keeping.

Restoring databases:

When you wish to restore the database, you will need to first remove and then recreate the original database (which is presumably now missing or corrupt). Log onto MySQL and first check whether the database exists by running the command:

SHOW DATABASES;

If it does exist, remove it using the command:

DROP DATABASE [databasename];

And then (re)create the database by running the command:

CREATE DATABASE [databasename];

Note: remember to end these commands with a semi-colon.

Exit MySQL and run the following command from the directory that contains the dump file:

MYSQL-u [username] -p [password] -D [databasename] < backupfile.sql

This will send the contents of the dump file into the blank database, where it will be used to create and re-populate the contents of the database.

Binary Logging:

To keep a record of changes made to the database between backups, enable binary logging. This keeps a log of any SQL statements that change the data stored in the database, such as UPDATE, INSERT and DELETE commands. The binary log can then be used to restore the lost data.

You will need to edit the file my.cnf, which will usually live in the /etc directory. Add the line:

log-bin=/var/mysql/mysqlbin.log
Save the changes and restart MySQL.