MySQL Backups and Indexes: a Guide

It’s not a secret that if you back up your MySQL data, you back up all of the data related to MySQL storage engines including the database schema, the data itself and also the indexes. In this blog post we will tell you what kinds of issues you can run into when backing up and restoring data with indexes in MySQL.

What Does Your MySQL Backup Consist Of?

If you have taken your MySQL backup using mysqldump, your backup was probably taken in a .sql format and it probably consists of the following:

• The name of the database your backup was taken from.
• The table structures of each of the tables and views in the specific database.
• The data derived from each of the tables in the database.
• The character sets and collations pertaining to the database.
• The indexes pertaining to each of the tables in the database.

If you take logical backups of MySQL, the indexes are also backed up together with the data itself – the indexes will most likely be added before the data itself is loaded into the database so your backed up data of your tables can look like this:

DROP TABLE IF EXISTS `demo`;
 
CREATE TABLE IF NOT EXISTS `demo` (
 
  `id` int(255) NOT NULL AUTO_INCREMENT,
 
  `column` varchar(255) NOT NULL DEFAULT '',
 
  PRIMARY KEY (`id`),
 
  KEY `data` (`data`)
 
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

Or, if the table is bigger, indexes are added after loading all of the data into it. Here’s how the process of adding indexes to a table looks like then:

ALTER TABLE `demo_table`
 
  ADD KEY `demo_index1` (`demo_column1`),
 
  ADD KEY `demo_index2` (`demo_column2`),
 
  ADD KEY `demo_index3` (`demo_column3`);

Performance Implications of Adding Indexes

In MySQL, indexes are added using the ALTER TABLE statement – the statement is used to change the structure of a table. To use the statement, you need to have the ALTER, CREATE and INSERT privileges for a specific table you’re modifying. The syntax of the ALTER TABLE statement usually looks like this:

ALTER TABLE demo_table [OPTIONS]

To add an index, you would usually use a query like so:

ALTER TABLE demo_table ADD INDEX demo_name(column_name);

What we would like you to keep in mind is that MySQL does not perform ALTER TABLE operations in memory in any case. When any ALTER TABLE queries are run, MySQL creates a new table (with a new schema if a new column is added, for example), it copies all rows “on the disk”, modifies all the data and then atomically swaps the old and new table. If you have a lot of data, such ALTER TABLE behavior can cause problems because you have to keep in mind that your disk must have the amount of free space that amounts to how big the table is and how big the index is (i.e if your table is 10GB and the index weighs 5GB, you actually need 15GB of free space on the disk instead of 5GB), so that’s very useful to keep in mind.

Also keep in mind that in MySQL 5.6 and higher, the table that you run the operations on is available for read and write operations while the operations with the index are being performed. We should also tell you that the ALTER TABLE queries on InnoDB have been redesigned for MySQL 5.6:

In MySQL 5.6, you can also request the operation to run online (and thus avoid accidentally making the table unavailable for reads and (or) writes, specify a LOCK statement like so:

ALTER TABLE demo_table ADD INDEX index_name(column_name) LOCK=NONE;

The LOCK clause could prove to be invaluable if you want to fine-tune the degree of concurrent access to your tables. If you want to permit reads, consider using the SHARED option:

ALTER TABLE demo_table ADD INDEX index_name(column_name) LOCK=SHARED;

If you want to enable the old table-copying approach, consider running SET old_alter_table=0.

MySQL Backups with Backup Ninja

Backup Ninja provides you with a single location to manage backups for multiple databases with different database vendors and backup methods. You can also manage your entire database backup environment if you use a variety of databases:

Backup Ninja also enables you to make automated backups for MySQL with rich feature settings and compression methods – backups can also be restored either locally or in the cloud. With Backup Ninja you can back up your databases without using any backup scripts:

Backup Ninja also enables you to store your backups in multiple storage locations – with that being said, it also supports archiving for cloud storage. You can choose to upload your backup to one or more S3 storage endpoints. It supports over 60 regions around the world while also supporting your own custom S3 endpoint.

If you feel like you might benefit from Backup Ninja’s offerings, be sure to give it a try.

Summary

MySQL backups is a pretty complex and wide topic, in this blog post we have touched a certain aspect of it regarding the usage of indexes. If your MySQL data uses indexes, be sure to take away from this blog post what you will and use it to your advantage.