Top 5 Considerations While Setting Up Your MySQL Backup

Security and Database Administration
Location: Ballroom G Level: Novice

In this session we provide a list of the top 5 considerations while planning a solution for backing up MySQL databases in your environment and implementing that plan.

1) How fast and how easy do you want the MySQL Recovery process to be?

Just like planning any other backup implementation, the most important consideration is the recovery process. Here are two key recovery objectives:

  • To what point in time will you need to recover your database?
    How much time will you have to recover your database?

2) What will be the impact of MySQL Backup process on your Application?

Backup window refers to the time it takes for backups to complete. During the backup window your MySQL applications will be either offline or degraded (in performance or functionality or both).

  • Some backup methods, e.g. offline backup make MySQL databases totally unavailable.
  • Others, e.g. mysqldump make the database unavailable for update.
  • Still others (replication, snapshots) have very minimal impact on the MySQL databases and applications
    The size of your database and rate of transaction activity will determine how long your backup window will be. Depending on these factors and the business requirements of your MySQL based applications you will need to decide on the scheduling of your backup window. You will also need to plan on potentially wide gyrations of the backup window duration—e.g., if some business activity results in a huge number of updates to your database. Ideally your implementation should also be aware of the current activity on your database—e.g., if your database is presently loaded with transactions, your implementation can intelligently postpone a scheduled backup run for a few hours.

Your applications may have data stored in files outside of MySQL database. E.g. the configuration files for your application. While implementing a MySQL backup solution make sure to create a process of backing up and recovering these files, which will be needed to completely recover your MySQL based applications.

3) What will your backup configuration look like?
What, Where, When, and How of MySQL Backup.

Your MySQL backup implementation will depend on the specific server and database configuration in your environment. You will need to take an inventory of the following:

  • Number of MySQL servers
  • Size of MySQL databases
  • Storage engines in use (InnoDB, MyISAM, NDB-MySQL Cluster). Note that you may introduce a new MySQL storage engine in the future. Your backup implementation should be flexible to incorporate a new storage engine.
  • Database activity

While deciding on what databases and tables to backup, make sure to include all databases and tables needed to be backed up together to ensure data consistency at your application level.

Choosing the right location for backup images is also a critical decision. Store backups as far away from the original data as possible! You will be surprised how many DBAs keep the backups on the same RAID as the original data.

A related decision is where you want to recover your database during the recovery process. If recovery is initiated after a simple user error, you may choose to recover the database at its original location. Keep in mind that hardware failure and disaster might require recovering to a different host that could have a different location, hardware, operating system, or version of MySQL. In ideal case, you should have a standby server where you periodically audit your recovery process.

Depending on the size of your databases and your recovery location criteria (i.e. whether you will recover at the same location as your current database or a different location) you will need to plan for allocation of space for backup and recovery purposes. If space is at a premium you can also choose to compress your backup images – although this uses computation resources both at backup and recovery stages.

If you are using MySQL replication for high availability or load balancing, it can also be leveraged for backup purposes – backups can be performed on a slave server without effecting the master server.

4) How will you manage your backup process and backed up data?

Backups should almost always be automated. Backups must be taken consistently and regularly and relying on human intervention should be avoided. Backup catalog should be automatically kept up-to-date keeping track of all copies of backup images.

In typical backup implementations, important pre-backup and post-backup procedures need to be performed. E.g. A pre-backup procedure can check whether needed storage will be available for the upcoming backup run. At the other end, a post-backup procedure can remove no longer needed binary logs after a successful backup has finished. Your backup implementation should seamlessly integrate such pre-backup and post-backup procedures.

Security of your MySQL database is a key consideration while implementing a backup solution. While backing up your MySQL database you need to consider whether encryption of your data is required. Just like compression, keep in mind that encryption has a computation cost both during backup and recovery. Also, if you are backing up your data remotely—say from your service provider site to your local data center or vice versa—you should make sure that the transportation mechanism being used is secure against eavesdroppers.

Proper planning should be done as to which personnel in your organization can perform the recovery of the MySQL database. These personnel need to have appropriate (technical and business) permissions to recover, for example, the Accounting database. Proper training for the personnel is essential before performing a recovery.

Appropriate security and access to backup images also plays into the compliance requirements that your organization may have, which leads us to the next top consideration.

5) What kind of tracking, reporting and compliance requirements does your business have from your MySQL backup implementation?

Your MySQL backup implementation should provide timely notifications for critical events such as backup failures. Mechanisms may include email, SMS, or RSS feed captured on an administrator’s dashboard.

It is likely that multiple personnel in your organization would be interested in receiving reports of MySQL backups. This is especially true if your environment has multiple entities using MySQL databases for different purposes. MySQL Backup reports can also be used for other interesting purposes, e.g. identifying durations of high MySQL update activity.

Your MySQL backup implementation should automatically implement your Retention Policy—i.e., how long you want keep to your backed up MySQL data. Your backup procedures should account for the possibility that different types of data may have different retention policies—depending on compliance and business requirements. The expired backups should be automatically purged.

Photo of Dmitri Joukovski

Dmitri Joukovski


Dmitri Joukovski is the Vice President of Product Management at Zmanda, the leader in open source backup and recovery. Dmitri has been solving backup and recovery challenges since the early ‘90s working for startups and large companies such as Legato and EMC. He wrote multiple articles and contributed to several books about backup and recovery published by O’Reilly. Dmitri lives in Silicon Valley, CA with his beautiful wife and two children.

  • Kickfire
  • Zmanda, Inc.
  • Continuent
  • EDS
  • JasperSoft
  • Sun Microsystems
  • Symantec Anti-Virus Software
  • XAware
  • Data Direct Technologies
  • Dolphin Interconnect Solutions
  • Hewlett Packard
  • Infobright, Inc
  • Linagora
  • Microsoft
  • OpSource
  • Oracle
  • Pentaho
  • R1Soft
  • Red Hat
  • Ticketmaster
  • TechRepublic

Contact Us

View a complete list of MySQL contacts.