1.4. Files that Are Backed Up

1.4. Files that Are Backed Up
Prev	Chapter 1. Introduction to MySQL Enterprise Backup	Next

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.
`*.bl`	Renamed version of each .isl file from the backed-up server.	A `.isl` file is created when you specify the location of an `InnoDB` table using the syntax `CREATE TABLE ... DATA DIRECTORY = ...`, to act like a symbolic link pointing to the tablespace file. (See Specifying the Location of a Tablespace for details.) The `.bl` files might or might not be turned back into `.isl` files during the `copy-back` operation. If the specified directory does not exist on the server where the backup is restored, the mysqlbackup command attempts to create it. If the directory cannot be created, the restore operation fails. Thus, if you use the `DATA DIRECTORY` clause to put tables in different locations, and restore to a server with a different file structure where the corresponding directories cannot be created, edit the `.bl` files before restoring to point to directories that do exist on the destination server.
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 9.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 9.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 9.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.
`gtid_executed.sql`	Signifies the backup came from a server with GTIDs enabled.	GTIDs are a replication feature in MySQL 5.6 and higher. See Replication with Global Transaction Identifiers for details. When you back up a server with GTIDs enabled, the file `gtid_executed.sql` is created in the backup directory. Edit and execute this file after restoring the backup data on a slave server.

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. The mysqlbackup command automatically copies the .frm files back and forth during backup and restore operations.

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:

backup-my.cnf: Records the crucial configuration parameters that apply to the backup. These parameter values are used during a restore operation, so that the original values are used regardless of changes to your my.cnf file in the meantime.
meta/backup_create.xml: Lists the command line arguments and environment in which the backup was created.
meta/backup_content.xml: Essential metadata for the files and database definitions of the backup data.

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”.

Prev	Up	Next
1.3. Overview of Backup Performance and Capacity Considerations	Home	1.5. Overview of Restoring a Database