Manage Learn to apply best practices and optimize your operations.

Book excerpt: Effective MySQL Backup and Recovery

As a DBA, you need to create an effective strategy. How? Read this excerpt from Ronald Bradford's 'Effective MySQL: Backup and Recovery' to find out.

Effective MySQL: Backup and RecoveryYou 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, find out about the necessary prerequisites for your database size and storage engine usage. That will have a direct effect on your system availability during any backup approach.

In this chapter we will discuss the approach necessary to identify a mini­mum 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 pos­sible options.

Read more of this book

Excerpted from Effective MySQL: Backup and Recovery by Ronald Bradford (McGraw-Hill/Oracle Press; 2012) with permission by McGraw-Hill. Read the entire chapter here. To purchase a copy of this book, please visit

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 environ­ments 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?

For more info on backup and recovery:

What's the best way to do an Oracle backup and recovery?

Oracle backup and recovery concepts

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 refer­ence, 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 data­base 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 data­base. 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 informa­tion for an audit of your database size.

Ronald Bradford is an Oracle ACE Director and MySQL Community Member of the Year. He is also the visionary force behind the Effective MySQL series. Ronald is a respected industry expert with more than 20 years of experience in the relational database field, with 12 years of experience working with MySQL. He combines his consulting expertise with numerous public speaking events at conferences worldwide. Ronald is the all-time top individual MySQL blogger.

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.