Want to back up your Mysql database? This is how it works

Do you use a  Mysql database? It is of vital importance to create backups on a regular interval. This way, data can be restored in case of an emergency. But, a backup of just a server is not sufficient. It is better to back up the database as well. To do so, read the steps below.

What is Mysql and Mysqldump?

MySQL is an open source SQL relational database management system, developed and supported by Oracle. It is just one of the many systems that store and manage data for you. And it is a very popular database solution, also used for corporate websites.

However, there is something to look out for: when creating a back-up of a server running MySQL, it's not sufficient to copy the data files of MySQL. Only copying the data files might lead to a corrupted back-up, leaving you in the cold in case of emergency.

To avoid the corrupt back-up, you can use Mysqldump. Mysqldump is a command-line utility that can be used to create backups of a Mysql database: it exports the internal structure of the database and the data inside of it to a plain readable file, which can not get corrupted.

Mysqldump is a command-line utility that can be used to create backups of a Mysql database.

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

Schedule regular backups with cron

To create backups on a regular interval, Mysqldump backups can be scheduled using cron.

The following cron example creates a backup every 15 minutes of the 'employees' database, compresses it with gzip and stores it with a date/timestamp.

*/15 * * * * mysqldump -u root -p'password' employees | gzip > /root/db_backup/employees_`date +\%Y\%m\%d_\%H\%M`.sql.gz

💡 Note that this is a very straight forward example of mysqldump. We recommend exploring the additional options available. A simple search on the internet will return plenty results to work with.

Make sure to test first!

We would recommend you to test restoring the data, so you are certain a succesful restore can be done whenever it is needed.