Effective MySQL: Backup and Recovery (2012)

Chapter 1. The Five Minute DBA

You have just inherited a production MySQL system and there is no confirmation that an existing MySQL backup strategy is in operation. What is the least you need to do? Before undertaking any backup strategy there are some necessary prerequisites about your database size and storage engine usage that have a direct effect on your system availability during any backup approach.

In this chapter we will discuss the approach necessary to identify a minimum functionality backup, including:

• Determine your database size

• Determine your storage engine usage

• Locking and downtime implications

Approaching a MySQL Backup

There is more than one strategy to back up a MySQL environment. These strategies also depend on the number of servers in the MySQL topology. There are a number of various open source and commercial tools available to perform backups. In Chapter 2 we will be discussing in detail all these possible options.

At this time you have an environment with a single server and you want to create a consistent backup. You have at your disposal for all MySQL environments two immediate options. The first option is to stop your MySQL instance and take a full filesystem cold backup. This would result in your system being unavailable for an undetermined time, and you would need to ensure you make a copy of all the right information including MySQL data, transaction and binary logs if applicable, and the current MySQL configuration.

Your second option is to use a client tool included with the standard MySQL installation. The mysqldump command can produce a consistent MySQL backup without stopping the MySQL instance. However, before running mysqldump, several important decisions are required to make an informed decision of the best options to use. These are:

• What is the size of the database to backup?

• What locking strategy is necessary to produce a consistent backup?

• How long will the backup take?

Determining Your Database Size

An important consideration for performing a MySQL backup is the size of your backup when backing up to local disk. This is required to ensure you have available diskspace to store your backup file.

The following SQL statement provides the total size in MB of your current data and indexes:

Your mysqldump backup will be approximately the same size as your data with an appropriate safety margin of 10 to 15 percent. There is no precise calculation; however, your backup produces a text based output of your data. For example, a 4 byte integer in the database may be 10 character bytes long in a mysqldump backup file. It is possible to compress your backup concurrently or to transfer to a different network device. These options and their limitations are discussed in Chapters 2 and 8.

From this SQL statement the database data size is 847MB. For later reference, the size of the backup file as described in the section running mysqldump reports a size of 818MB using the common default options. The example database in Chapter 8 with a data size of 4.5GB produces a backup file of 2.9GB.

Choosing a Locking Strategy

The locking strategy chosen will determine if your application can perform database write operations during the execution of a backup. By default, mysqldump performs a table level lock to ensure a consistent version of all data using the LOCK TABLES command. This occurs with the --lock-tables command line option, which is not enabled by default. This option is part of the --opt option that is enabled by default. You can elect to not lock tables; however, this may not ensure a consistent backup. When using the MyISAM storage engine, --lock-tables is necessary to ensure a consistent backup.

Alternatively, mysqldump provides the --single-transaction option that creates a consistent version snapshot of all tables in a single transaction. This option is only applicable when using a storage engine that supports multiversioning. InnoDB is the only storage engine included in a default MySQL installation that is applicable. When specified, this option automatically turns off --lock-tables.

The following SQL statement will confirm the storage engines in use for your MySQL instance:

In this example, the MySQL instance has several different schemas that support various functions including a shopping cart, newsletter, and administration tool. An all InnoDB application may look like:

As you see in the example the mysql meta-schema uses MyISAM. There is no ability to change this. If your database is all InnoDB you will have two options regarding the MyISAM mysql tables that we will discuss later in this chapter.

Execution Time

The most important requirement is to determine how long your backup will take. There is no calculation that can give an accurate answer. The size of your database, the amount of system RAM, the storage engine(s) in use, the MySQL configuration, the hard drive speed, and the current workload all contribute in the calculation. What is important when performing a backup is that you collect this type of information for future reference. The execution time is important, as this is an effective maintenance window for your database. During a database backup there may be a limitation of application functionality, a performance overhead during the backup, and your backup may limit other operations including batch processing or software maintenance.

Combining Information

The following is a recommended SQL statement that combines all information for an audit of your database size:

Performing a MySQL Backup

Now that you have gathered prerequisite information, you have the details necessary to make an informed decision.

The choice of how to perform a backup, when to perform, and how you monitor and verify is a more complex process that is discussed in more detail starting with Chapter 2.

One additional consideration during a backup process is to disable any cron or batch processes during the backup to minimize additional workload. This can minimize database contention and shorten the window of time needed.

Running mysqldump

In the simplest form, you can perform a backup using mysqldump with the following syntax:

• The first command runs the mysqldump for all databases producing an ASCII dump in the backup.sql file.

• The second command confirms the exit status of the first command. A non-zero result is an indication of a problem during the backup process. If any errors occur, these are generally shown in the screen output.

• The third command shows the size of your backup file for later reference.

For example:

This is a successful backup file totaling 818MB that took 35 seconds to execute. The original size of the database data as shown previously for this MySQL instance was 847MB.

TIP Prefixing the mysqldump command with the time command will provide valuable information on the actual time taken. Recording your backup time and size is an important administration step all DBAs should do. This time is useful for scheduling other system requirements, for an additional verification step if a successful backup has a significantly different time, and is helpful in benchmarking using different arguments, MySQL configuration settings, or changes in physical hardware.

An example of an error condition may look like:

A backup file as per this example may in isolation appear to be completely valid. That is, this file contains valid and complete SQL statements and can be successfully used to restore data in one or more schemas; however, it is incomplete as a full backup of all data. The execution time, error status, and size are all important information for verification of a successful backup.

Creating a backup is only the first step in a suitable strategy. It is important this backup file can be used successfully in recovery. This is discussed in Chapter 5.

Securing Your Backup

The final step in a minimal backup approach is to ensure the security of your data. The backup is currently on the same system as your data. A loss of this system would include the data and your backup. The minimum you should undertake is to copy your backup to a secondary location. For example:

Benefits with mysqldump

The mysqldump command provides a SQL based backup file. This can be ideal for creating a backup that can be executed on different versions of MySQL, and on different operating systems. You can, for example, view this file directly and see SQL statements. For example:

More Information

For more information about the various options with mysqldump, you can obtain a list of valid options with the following syntax:

You can find detailed information in the MySQL Reference Manual at http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html.

Other Options

If your database uses all InnoDB tables, the default locking strategy is restrictive. You have to consider the impact of the mysql schema on MyISAM tables. Under normal circumstances you can generally ignore the consistency requirement providing you do not perform operations that change the meta-data. This includes adding or changing users and privileges, as well as creating or dropping database schemas. Alternatively you may elect to perform two separate backups. The first backup excludes the mysql schema using the --single-transaction option. The second backup only includes the mysql schema and uses the default locking approach. This will be discussed more in Chapter 2.

Conclusion

An appropriate MySQL backup strategy is an essential component for any running production system. For a simple installation, the implementation of a backup strategy can occur in minutes as demonstrated in this chapter. However, a backup strategy is only as good as the process to perform a successful, timely, and complete recovery using the backup strategy. Chapter 5 will provide a detailed explanation for a successful recovery.

There are a number of important considerations when using the output of mysqldump for recovery that may affect how you execute your backup command. Chapter 2 will discuss these points.

This chapter also introduced a number of common terms including consistent, valid, complete, and point in time. We will define these terms in greater detail in Chapter 2.

The SQL statements and web links listed in this chapter can be downloaded from http://effectivemysql.com/book/backup-recovery/.