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 |
---|---|---|
| The InnoDB system tablespace, containing multiple InnoDB tables and associated indexes. |
Because the original files might change while the backup
is in progress, the
|
| InnoDB file-per-table tablespaces, each containing a single InnoDB table and associated indexes. |
Used for tables created under the
|
| Compressed form of InnoDB data files from the MySQL data directory. |
Produced instead of
The |
| 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. |
| MyISAM table data. | The database is put into a read-only state while these files are copied. These files are copied without changes. |
| MyISAM index data. | The database is put into a read-only state while these files are copied. These files are copied without changes. |
| Metadata for CSV tables. |
These files are copied without changes. The
|
| Data for CSV tables. |
These files are copied without changes. The
|
| 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. |
| Trigger parameters. | The database is put into a read-only state while these files are copied. These files are copied without changes. |
| Trigger namespace information. | The database is put into a read-only state while these files are copied. These files are copied without changes. |
| Database configuration information. | The database is put into a read-only state while these files are copied. These files are copied without changes. |
| Definitions for partitioned tables. | The database is put into a read-only state while these files are copied. These files are copied without changes. |
| Archive storage engine metadata. | The database is put into a read-only state while these files are copied. These files are copied without changes. |
| Archive storage engine data. | The database is put into a read-only state while these files are copied. These files are copied without changes. |
| 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
|
|
A condensed version of the
|
The InnoDB log files ( |
|
Used instead of | |
| 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
|
| Renamed version of each .isl file from the backed-up server. |
A |
Timestamped directory, such as
|
Created by the
|
Use the |
| 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 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
Notes
| |
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 |
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. |
|
A single-file backup produced by the
|
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
|
Any other files in the database subdirectories under the
| 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
|
| 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 |
|
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 | 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. |
|
Contains the list of all the files (except itself) that
are present in the single-file backup produced by the
| This file is not modified at any stage once generated. |
| 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
|
| 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
|
|
Produced by the | The comments are specified by you to document the purpose or special considerations for this backup job. |
| 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
|
server-my.cnf |
Contains values of the backed-up server's global
variables that are set to non-default values. Use this
file or |
During a
Warning
When using the file to restart the target server,
change parameters like |
server-all.cnf |
Contains values of all the global variables of the
backed-up server. Use this file or
|
During a
Warning
When using the file to restart the target server,
change parameters like |
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.
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.
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 likeapply-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”.