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 and other important information about the MySQL data files.	The file is created during a backup, and it contains crucial parameters describing the backed-up data like innodb_data_file_path, innodb_log_file_size, innodb_log_files_in_group, and so on. It might also contain other InnoDB parameters like innodb_data_home_dir and innodb_undo_directory if some of the backup repository options were used during the backup. mysqlbackup uses the parameters stored in this file to understand the structure of the backup and to perform various operations. You might need to supply some of these parameters to mysqlbackup during a restore and to mysqld when you start the target server if the target server and the backup are configured differently.
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 Creating Tables Externally 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.
binary log files	Binary log files from the server, which are included in a backup by default (except when the backup is created with the --use-tts option). They allow a snapshot of the server to be taken, so a server can be cloned to its exact state. Using a full backup as a basis, the binary log files that are included with an incremental backup can be used for a point-in-time recovery (PITR), which restores a database to its state at a certain point in time after the last full backup. See Section 4.3, “Point-in-Time Recovery” for details.	Saved under the datadir directory under the backup directory. Use the --skip-binlog option to exclude binary logs in the backup. For MySQL 5.5 and earlier, as well as all offline backups, use the --log-bin-index option to specify the absolute path of the index file on the MySQL server that lists all the used binary log files, if it is different from the default value of the option, for mysqlbackup to find the binary log files and include them in the backups. Notes Due to some known issues, users should always use the --skip-binlog option when creating offline backups, or when creating an incremental backup that is based on a full backup created with the --no-locking option. See Appendix A, MySQL Enterprise Backup Limitations for details. If any binary log files are missing on the server you are backing up, you should use the --skip-binlog option to avoid mysqlbackup throwing an error for the missing files. No binary log files are copied into the incremental backup if the --use-tts option or the --start-lsn option is used. To include binary log files for the period covered by the incremental backup, do not use the --use-tts option and, instead of --start-lsn, use the --incremental-base option, which provides the necessary information for mysqlbackup to ensure that no gap exists between binary log data included in the previous backup and the current incremental backup.
relay log files	Relay log files from a slave server, which are included in a backup of a slave server by default (except when the backup is created with the --use-tts option). Their inclusion saves the time and resources required for fetching the relay logs from the master when the slave is being restored.	Saved under the datadir directory under the backup directory. Use the --skip-relaylog option to exclude relay logs in the backup. For offline backup, use the --relay-log-index option to specify the absolute path of the index file on the MySQL server that lists all the used relay log files, if it is different from the default value of the option, for mysqlbackup to find the relay log files and include them in the backups.
slave status log files	Usually named master.info and relay-log.info, they are included by default in a backup of a slave database in a replication setup. See Replication Metadata Repositories, for details.	Saved under the datadir directory under the backup directory. For an offline backup, use the--master-info-file and --relaylog-info-file options to specify the absolute paths of the information files, if they are different from the default values of the options, for mysqlbackup to find those files and include them in the backups.
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 in the database subdirectories under the datadir directory (that is, under backup-dir/datadir/data-base-name)	Copied from the database subdirectories under the MySQL data directory.	By default, any unrecognized files in the database subdirectories under 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 11.4, “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 11.4, “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. It also contains details of all the plugins defined on the backed-up server, by which users should make sure the same plugins are defined in the same manner on the target server for restoration. For details about this file, see Section 11.4, “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.
backup_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.
server-my.cnf	Contains values of the backed-up server's global variables that are set to non-default values. Use this file or server-all.cnf to start the target server for restoration.	During a copy-back or copy-back-and-apply-log operation, the server repository options values (e.g., --datadir, --innodb_data_home_dir, etc.) in the file are modified if the command makes changes to them through the command options. However, during an --apply-incremental-backup operation, the values already saved in the file take precedence and they are not modified by the option values supplied through the command. Warning When using the file to restart the target server, change parameters like --tmpdir, --general-log, etc., and any global variable that uses an absolute filepath to avoid the accidental usage of the wrong file locations by the target server.
server-all.cnf	Contains values of all the global variables of the backed-up server. Use this file or server-my.cnf to start the target server for restoration.	During a copy-back or copy-back-and-apply-log operation, the server repository options values (e.g., --datadir, --innodb_data_home_dir, etc.) in the file are modified if the command makes changes to them through the command options. However, during an --apply-incremental-backup operation, the values already saved in the file take precedence and they are not modified by the option values supplied through the command. Warning When using the file to restart the target server, change parameters like --tmpdir, --general-log, etc., and any global variable that uses an absolute filepath to avoid the accidental usage of the wrong file locations by the target 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. 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.

Note

While MySQL Enterprise Backup can back up non-InnoDB data (like MYISAM tables), the MySQL server to be backed up must support InnoDB (i.e., the backup process will fail if the server was started up with the --innodb=OFF or --skip-innodb option), and the server must contain at least one InnoDB table.

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:

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

• backup-my.cnf: Records the crucial configuration parameters that apply to the backup. These configuration parameters are read by mysqlbackup during operations like apply-log to determine how the backup data is structured. These parameters are also checked during a restore operation for their compatibility with your target server's configuration.

• server-my.cnf: Contains values of the backed-up server's global variables that are set to non-default values.

• server-all.cnf: Contains values of all the global variables of the backed-up server.

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