Considerations when designing a MySQL backup and recovery system

Seasoned IT managers know that it’s best to prepare for worst-case scenarios and when it comes to data management, this usually means a plan for regular backups in the event of a catastrophe – or simply some lost emails that were mistakenly deleted by the CEO.

That’s why it is important to plan ahead of time, because doing so later can mean that the best options are no longer viable. Practicing recovery operations regularly is also important to ensure that things go smoothly when it’s the real deal.

The first things you’ll hear when DBAs are talking about MySQL backup and restore are the following terms: hot, warm and cold backups. These adjectives reflect the impact that the backups will have to the organization - much like a thermometer. But in this case, “hot” is good because hot backups do not require any server downtime.

What exactly does a MySQL restore operation involve? Let’s first examine the meaning of “restore” in this context. Restore and recover are not the same yet those two terms are often used incorrectly. To restore means to retrieve data from a backup and putting them where MySQL can use them. Recovery, on the other hand, involves the process of rescuing a system after something has become broken.

In addition to disaster recovery, backups should be part of your IT department’s planning for the following common requirements: “Opps!” This is when people accidentally delete data – or delete it then realize that they need it back again. Audits. You never know when you might be called upon to present that your data or schema looked like at a certain point in time. This might occur during a lawsuit, an internal investigation – or simply to track down an elusive bug.

Before you can create an effective recovery system the MySQL backup and restore requirements must first be defined. Why? Because backups won’t be of much use without a system in place to restore everything to where it was before the catastrophe.

Backing up MySQL is not easy because while a basic copy of the data is fairly straightforward, the following make for the headache: your app requirements; MySQL’s storage engine architecture; your system configuration.

Many DBAs mistakenly believe that their “replica” is their backup. No! And a RAID array is also not a backup. With that in mind, here are some recommended things to consider when designing your MySQL restore system:

Raw backups

Keep several generations of backups

Periodic logical backups

Keep the binary logs for point-in-time recovery.

Don’t depend on backup tools: monitor backups independently

Test the backup and recovery process regularly

Don’t take security for granted

Another thing to consider is whether your organization would be best served by online or offline backups. Most DBAs weigh the following when making this decision: Lock time; backup time; backup load; and recovery time. There are tools to consider, too, such as Percona XtraBackup and MySQL Enterprise Backup.

In terms of backing up MySQL’s data, there are two paths you can take, either “logical” (sometimes referred to as a “dump”) or “raw” backups. Each one has its pros can cons. Another question will be deciding what to backup. Generally your organization’s recovery requirements will dictate what exactly is on this list.

If you are dealing with massive data then a good strategy is implementing incremental and differential backups. The latter entails backing everything up that has changed since the previous full backup. Incremental backups include everything that has changed since the last backup – of any sort.

The bottom line: Everyone needs backups but not all see the need “recoverable” backups. There are many tools that can help, but perhaps the best course of action is to seek out a mentor or a MySQL expert consultant when in doubt.

Author Box

Percona is the oldest and largest independent MySQL Consulting, Support, Remote DBA, Training, and Software Development Company serving more than 1,900 customers in 50+ countries since 2006 with MySQL performance as their primary focus.