Keeping Data Backed Up

Backing up your MySQL databases is key to ensuring your application is available and user data isn't lost due to a programming error or some other mishap. Mistakes happen and it's always good to know you have backups to rely on. Here are some of the tools I use for backing up and recoverying data:

• XtraBackup - Command line tool that can be used to backup the entire MySQL data directory and stream it to an external storage service such as AWS S3
• mysqldump - Command that will backup a single database into a file containing SQL commands
• Amazon Relational Database Service (RDS) - A service offered by AWS that can be configured to provide point-in-time recover for MySQL databases

In addition to backing up data, you should also have some way of validating each backup by running tests against the data.

How I Use MySQL

Whenever I set up MySQL for an application I always use the default engine (InnoDB) when creating new databases and tables. InnoDB supports transactions which allows me to safely perform atomic operations across multiple tables.

Although I'm familiar with the SQL commands to create tables, I typically use PHP and Laravel's migration feature. Typically each table gets an auto-incrementing ID as the primary key unless the data that the table will hold will already have a unique identifier for each row. I add as many columns as I need to each table, however I avoid having duplicate columns in multiple tables. I mainly stick to integer, boolean, varchar, text, timestamp and datetime fields and will occasionally use a JSON field for when I need the data schema to be more fluid and less constrained. When dealing with dollar amounts, I usually use whole numbers and store the value as an integer to avoid rounding errors that can occur when using floats.

For backing up databases I use XtraBackup by Percona. This utility allows me to perform daily backups of my entire system's data directory unlike mysqldump which only generates SQL commands to recreate a single database. For multi-tenant applications I have a database for each organization so it's easier to backup the entire directory. When the data is backed up it is compressed and then sent to an S3 bucket on AWS where it is stored. When I need to perform a restore, I have a couple of options. The first option is to completely replace the data directory of the production server which typically requires some downtime. The second option is to perform the restore on a separate but identical server, then use mysqldump to grab just the database I need and restore that database on the production server. The second option is usually the preferred method as it allows me to minimize the impact of the restore process. If there's a system-wide issue that requires the entire database to be restored then I have no choice but to go with option one.

Naturally when performing restores from snapshots data loss occurs as you are restoring a version of the data from before the incident occurred whether it's from 1 hour ago or 7 days ago. I find that backing up my own projects once a day is enough and I keep a month or more of backups. For projects where a shorter recovery window is needed, I would use something like AWS RDS that offers point-in-time recovery at 5 minute intervals.