1.4 Files that Are Backed Up

DBA and development work typically involves logical structures such as tables, rows, columns, the data dictionary, and so on. For backups, you must understand the physical details of how these structures are represented by files.

Table 1.1 Files in a MySQL Enterprise Backup Output Directory

File Name, Pattern, or Extension

Relation to Original Data Files

Notes

ibdata*

The InnoDB system tablespace, containing multiple InnoDB tables and associated indexes.

Because the original files might change while the backup is in progress, the apply-log step applies the same changes to the corresponding backup files.

*.ibd

InnoDB file-per-table tablespaces, each containing a single InnoDB table and associated indexes.

Used for tables created under the innodb_file_per_table. Because the original files might change while the backup is in progress, the apply-log step applies the same changes to the corresponding backup files.

*.ibz

Compressed form of InnoDB data files from the MySQL data directory.

Produced instead of .ibd files in a compressed backup. The ibdata* files representing the InnoDB system tablespace also receive this extension in a compressed backup.

The .ibz files are uncompressed for the apply-log step.

*.frm

Hold metadata about all MySQL tables.

The database is put into a read-only state while these files are copied. These files are copied without changes.

*.MYD

MyISAM table data.

The database is put into a read-only state while these files are copied. These files are copied without changes.

*.MYI

MyISAM index data.

The database is put into a read-only state while these files are copied. These files are copied without changes.

*.CSM

Metadata for CSV tables.

These files are copied without changes. The backup_history and backup_progress tables created by mysqlbackup use the CSV format, so the backup always includes some files with this extension.

*.CSV

Data for CSV tables.

These files are copied without changes. The backup_history and backup_progress tables created by mysqlbackup use the CSV format, so the backup always includes some files with this extension.

*.MRG

MERGE storage engine references to other tables.

The database is put into a read-only state while these files are copied. These files are copied without changes.

*.TRG

Trigger parameters.

The database is put into a read-only state while these files are copied. These files are copied without changes.

*.TRN

Trigger namespace information.

The database is put into a read-only state while these files are copied. These files are copied without changes.

*.opt

Database configuration information.

The database is put into a read-only state while these files are copied. These files are copied without changes.

*.par

Definitions for partitioned tables.

The database is put into a read-only state while these files are copied. These files are copied without changes.

*.ARM

Archive storage engine metadata.

The database is put into a read-only state while these files are copied. These files are copied without changes.

*.ARZ

Archive storage engine data.

The database is put into a read-only state while these files are copied. These files are copied without changes.

backup-my.cnf

Records the configuration parameters that specify the layout of the MySQL data files.

Used in restore operations to reproduce the same layout as when the backup was taken.

ibbackup_logfile

A condensed version of the ib_logfile* files from the MySQL data directory.

The InnoDB log files (ib_logfile*) are fixed-size files that are continuously updated during database operation. For backup purposes, only the changes that are committed while the backup is in progress are needed. These changes are recorded in ibbackup_logfile, and used to re-create the ib_logfile* files during the apply-log phase.

ibbackup_redo_log_only

Used instead of ibbackup_logfile for incremental backups taken with the --incremental-with-redo-log-only option.

ib_logfile*

Created in the backup directory during the apply-log phase after the initial backup.

These files are not copied from the original data directory, but rather re-created in the backup directory during the apply-log phase after the initial backup, using the changes recorded in the ibbackup_logfile file.

Timestamped directory, such as 2011-05-26_13-42-02

Created by the --with-timestamp option. All the backup files go inside this subdirectory.

Use the --with-timestamp option whenever you intend to keep more than one set of backup data available under the same main backup directory.

datadir directory

A subdirectory that stores all the data files and database subdirectories from the original MySQL instance.

Created under the backup directory by the mysqlbackup command.

image file

A single-file backup produced by the backup-to-image option, with a name specified by the --backup-image option.

If your backup data directory consists only of zero-byte files, with a single giant data file in the top-level directory, you have a single-file backup. You can move the image file without losing or damaging the contents inside it, then unpack it with the mysqlbackup command using the extract option and specifying the same image name with the --backup-image option. Although some extra files such as backup-my.cnf and the meta subdirectory are present in the backup directory, these files are also included in the image file and do not need to be moved along with it.

any other files

Copied from the MySQL data directory.

By default, any unrecognized files in the MySQL data directory are copied to the backup. To omit such files, specify the --only-known-file-types option.

meta directory

A subdirectory that stores files with metadata about the backup.

Created under the backup directory by the mysqlbackup command. All files listed below go inside the meta subdirectory.

backup_variables.txt

Holds important information about the backup. For use by the mysqlbackup command only. Prior to MySQL Enterprise Backup 3.6, this information was in a file named ibbackup_binlog_info.

The mysqlbackup command consults and possibly updates this file during operations after the initial backup, such as the apply-log phase or the restore phase.

image_files.xml

Contains the list of all the files (except itself) that are present in the single-file backup produced by the backup-to-image or backup-dir-to-image options. For details about this file, see Section 7.5, “Using the MySQL Enterprise Backup Manifest”.

This file is not modified at any stage once generated.

backup_create.xml

Lists the command line arguments and environment in which the backup was created. For details about this file, see Section 7.5, “Using the MySQL Enterprise Backup Manifest”.

This file is not modified once it is created. You can prevent this file from being generated by specifying the --disable-manifest option.

backup_content.xml

Essential metadata for the files and database definitions of the backup data. For details about this file, see Section 7.5, “Using the MySQL Enterprise Backup Manifest”.

This file is not modified once created. You can prevent this file from being generated by specifying the --disable-manifest option.

comments.txt

Produced by the --comments or --comments-file option.

The comments are specified by you to document the purpose or special considerations for this backup job.


InnoDB Data

Data managed by the InnoDB storage engine is always backed up. The primary InnoDB-related data files that are backed up include the ibdata* files that represent the system tablespace and possibly the data for some user tables; any .ibd files, containing data from user tables created with the file-per-table setting enabled; data extracted from the ib_logfile* files (the redo log information representing changes that occur while the backup is running), which is stored in a new backup file ibbackup_logfile.

If you use the compressed backup feature, the .ibd files are renamed in their compressed form to .ibz files.

The files, as they are originally copied, form a raw backup that requires further processing before it is ready to be restored. You then run the apply step, which updates the backup files based on the changes recorded in the ibbackup_logfile file, producing a prepared backup. At this point, the backup data corresponds to a single point in time. The files are now ready to be restored to their original location, or for some other use, such as testing, reporting, or deployment as a replication slave.

To restore InnoDB tables to their original state, you must also have the corresponding .frm files along with the backup data. Otherwise, the table definitions could be missing or outdated, if someone has run ALTER TABLE or DROP TABLE statements since the backup. By default, the mysqlbackup command automatically copies the .frm files during a backup operation and restores the files during a restore operation.

Data from MyISAM and Other Storage Engines

The mysqlbackup command can also back up the .MYD files, .MYI files, and associated .frm files for MyISAM tables. The same applies to files with other extensions, as shown in this list.

MyISAM tables and these other types of files cannot be backed up in the same non-blocking way as InnoDB tables can. This phase is a warm backup: changes to these tables are prevented while they are being backed up, possibly making the database unresponsive for a time, but no shutdown is required during the backup.

Note

To avoid concurrency issues during backups of busy databases, you can use the --only-innodb or --only-innodb-with-frm option to back up only InnoDB tables and associated data.

Generated Files Included in the Backup

The backup data includes some new files that are produced during the backup process. These files are used to control later tasks such as verifying and restoring the backup data. The files generated during the backup process include:

For details about all the files in the backup directory, see Table 1.1, “Files in a MySQL Enterprise Backup Output Directory”.

Single-File Backups

Depending on your workflow, you might perform a single-file backup rather than the typical backup that produces a separate file for every file in the original instance. The single-file format is easier to transfer to a different system, compress and uncompress, and ensure that no backed-up files are deleted later by mistake. It is just as fast as a multi-file backup to do a full restore; restoring individual files can be slower than in a multi-file backup. For instructions, see Section 3.3.5, “Making a Single-File Backup”.