MySQL Enterprise Backup Glossary

These terms are commonly used in information about the MySQL Enterprise Backup product.

A

.ARM file

Metadata for ARCHIVE tables. Contrast with .ARZ file. Files with this extension are always included in backups produced by the mysqlbackup command of the MySQL Enterprise Backup product.

See Also .ARZ file.

.ARZ file

Data for ARCHIVE tables. Contrast with .ARM file. Files with this extension are always included in backups produced by the mysqlbackup command of the MySQL Enterprise Backup product.

See Also .ARM file.

apply

The operation that transforms a raw backup into a prepared backup by incorporating changes that occurred while the backup was running, using data from the log.

See Also log, prepared backup, raw backup.

MySQL Enterprise Backup

A licensed products that performs hot backups of MySQL database servers. It offers the most efficiency and flexibility when backing up InnoDB tables; it can also back up MyISAM and other kinds of tables. It is included as part of the MySQL Enterprise Edition subscription.

See Also InnoDB.

B

backup

The process of copying some or all table data and metadata from a MySQL instance, for safekeeping. Can also refer to the set of copied files. This is a crucial task for DBAs. The reverse of this process is the restore operation.

With MySQL, physical backups are performed by the MySQL Enterprise Backup product, and logical backups are performed by the mysqldump command. These techniques have different characteristics in terms of size and representation of the backup data, and speed (especially speed of the restore operation).

Backups are further classified as hot, warm, or cold depending on how much they interfere with normal database server operation. (Hot backups have the least interference, cold backups the most.)

See Also logical backup, mysqldump, physical backup, warm backup.

backup directory

The directory under which the backup data and metadata are stored, permanently or temporarily. It is used in most kinds of backup and restore operations, including single-file backups and restores. See the description of the --backup-dir option on how the backup directory is used for different purposes and for different operations.

backup repository

Contrast with server repository.

See Also repository, server repository.

backup-my.cnf

A small configuration file generated by MySQL Enterprise Backup, containing a minimal set of configuration parameters. This file records the settings that apply to this backup data. Subsequent operations, such as the apply process, read options from this file to determine how the backup data is structured. This file always has the extension .cnf, rather than .cnf on Unix-like systems and .ini on Windows systems.

See Also apply, configuration file.

binlog

An informal name for the binary log file. For example, you might see this abbreviation used in e-mail messages or forum discussions.

C

compression

A technique that produces smaller backup files, with size reduction influenced by the compression level setting. Suitable for keeping multiple sets of non-critical backup files. (For recent backups of critical data, you might leave the data uncompressed, to allow fast restore speed in case of emergency.)

MySQL Enterprise Backup can apply compression to the contents of InnoDB tables during the backup process, turning the .ibd files into .ibz files.

Compression adds CPU overhead to the backup process, and requires additional time and disk space during the restore process.

See Also backup, compression level, .ibd file, InnoDB, restore.

compression level

A setting that determines how much compression to apply to a compressed backup. This setting ranges from 0 (none), 1 (default level when compression is enabled) to 9 (maximum). The amount of compression for a given compression level depends on the nature of your data values. Higher compression levels do impose additional CPU overhead, so ideally you use the lowest value that produces a good balance of compression with low CPU overhead.

See Also compression.

configuration file

The file that holds the startup options of the MySQL server and related products and components. Often referred to by its default file name, my.cnf on Linux, Unix, and macOS systems, and my.ini on Windows systems. The MySQL Enterprise Backup stores its default configuration settings in this file, under a [mysqlbackup] section. For convenience, MySQL Enterprise Backup can also read settings from the [client] section, for configuration options that are common between MySQL Enterprise Backup and other programs that connect to the MySQL server.

See Also my.cnf, my.ini.

crash recovery

The cleanup activities for InnoDB tables that occur when MySQL is started again after a crash. Changes that were committed before the crash, but not yet written to the tablespace files, are reconstructed from the doublewrite buffer. When the database is shut down normally, this type of activity is performed during shutdown by the purge operation.

D

data dictionary

A set of tables, controlled by the InnoDB storage engine, that keeps track of InnoDB-related objects such as tables, indexes, and table columns. These tables are part of the InnoDB system tablespace.

Because the MySQL Enterprise Backup product always backs up the system tablespace, all backups include the contents of the data dictionary.

See Also MySQL Enterprise Backup, system tablespace.

database

A set of tables and related objects owned by a MySQL user. Equivalent to “schema” in Oracle Database terminology. MySQL Enterprise Backup can perform a partial backup that includes some databases and not others. The full set of databases controlled by a MySQL server is known as an instance.

See Also instance.

E

exclude

In a partial backup, to select a set of tables, databases, or a combination of both to be omitted from the backup. Contrast with include.

extract

The operation that retrieves some content from an image file produced by a single-file backup. It can apply to a single file (unpacked to an arbitrary location) or to the entire backup (reproducing the original directory structure of the backup data). These two kinds of extraction are performed by the mysqlbackup options extract and image-to-backup-dir, respectively.

See Also image, single-file backup.

F

file format

The format used by InnoDB for its data files named ibdata1, ibdata2, and so on. Each file format supports one or more row formats.

See Also ibdata file, row format.

full backup

A backup that includes all the tables in each MySQL database, and all the databases in a MySQL instance. Contrast with partial backup and incremental backup. Full backups take the longest, but also require the least amount of followup work and administration complexity. Thus, even when you primarily do partial or incremental backups, you might periodically do a full backup.

See Also backup, incremental backup, table.

H

history table

The table mysql.backup_history that holds details of completed backup operations. While a backup job is running, the details (especially the changing status value) are recorded in the progress table.

See Also backup, progress table.

I

.ibd file

Each InnoDB tablespace created using the file-per-table setting has a filename with a .ibd extension. This extension does not apply to the system tablespace, which is made up of files named ibdata1, ibdata2, and so on.

See Also system tablespace, tablespace.

ibdata file

A set of files with names such as ibdata1, ibdata2, and so on, that make up the InnoDB system tablespace. These files contain metadata about InnoDB tables, and can contain some or all of the table and index data also (depending on whether the file-per-table option is in effect when each table is created). For backward compatibility these files always use the Antelope file format.

See Also system tablespace.

image

The file produced as part of a single-file backup operation. It can be a real file that you store locally, or standard output (specified as -) when the backup data is streamed directly to another command or remote server. This term is referenced in several mysqlbackup options such as backup-dir-to-image and image-to-backup-dir.

See Also single-file backup, streaming.

include

In a partial backup, to select a set of tables, databases, or a combination of both to be backed up. Contrast with exclude.

incremental backup

A backup that captures only data changed since the previous backup. It has the potential to be smaller and faster than a full backup. The incremental backup data must be merged with the contents of the previous backup before it can be restored. See Section 4.3.3, “Making a Differential or Incremental Backup” for usage details. Related mysqlbackup options are --incremental, --incremental-with-redo-log-only, --incremental-backup-dir, --incremental-base, and --start-lsn.

See Also full backup.

InnoDB

The type of MySQL table that works best with MySQL Enterprise Backup. These tables can be backed up using the hot backup technique that avoids interruptions in database processing. For this reason, and because of the higher reliability and concurrency possible with InnoDB tables, most deployments should use InnoDB for the bulk of their data and their most important data. In MySQL 5.5 and higher, the CREATE TABLE statement creates InnoDB tables by default.

See Also table.

instance

The full contents of a MySQL server, possibly including multiple databases. A backup operation can back up an entire instance, or a partial backup can include selected databases and tables.

See Also database.

L

locking

See Also warm backup.

log

Several types of log files are used within the MySQL Enterprise Backup product. The most common is the InnoDB redo log that is consulted during incremental backups.

See Also incremental backup, redo log.

log sequence number

See LSN.

logical backup

A backup that reproduces table structure and data, without copying the actual data files. For example, the mysqldump command produces a logical backup, because its output contains statements such as CREATE TABLE and INSERT that can re-create the data. Contrast with physical backup.

See Also backup, physical backup.

LSN

Acronym for log sequence number. This arbitrary, ever-increasing value represents a point in time corresponding to operations recorded in the redo log. (This point in time is regardless of transaction boundaries; it can fall in the middle of one or more transactions.) It is used internally by InnoDB during crash recovery and for managing the buffer pool.

In the MySQL Enterprise Backup product, you can specify an LSN to represent the point in time from which to take an incremental backup. The relevant LSN is displayed by the output of the mysqlbackup command. Once you have the LSN corresponding to the time of a full backup, you can specify that value to take a subsequent incremental backup, whose output contains another LSN for the next incremental backup.

See Also crash recovery, incremental backup, redo log.

M

.MRG file

A file containing references to other tables, used by the MERGE storage engine. Files with this extension are always included in backups produced by the mysqlbackup command of the MySQL Enterprise Backup product.

.MYD file

A file that MySQL uses to store data for a MyISAM table.

See Also .MYI file.

.MYI file

A file that MySQL uses to store indexes for a MyISAM table.

See Also .MYD file.

manifest

The record of the environment (for example, command-line arguments) and data files involved in a backup, stored in the files meta/backup_create.xml and meta/backup_content.xml, respectively. This data can be used by management tools during diagnosis and troubleshooting procedures.

master

See source.

media management software

A class of software programs for managing backup media, such as libraries of tape backups. One example is Oracle Secure Backup. Abbreviated MMS.

See Also Oracle Secure Backup.

my.cnf

The typical name for the MySQL configuration file on Linux, Unix, and macOS systems.

See Also configuration file, my.ini.

my.ini

The typical name for the MySQL configuration file on Windows systems.

See Also configuration file, my.cnf.

MyISAM

A MySQL storage engine, formerly the default for new tables. In MySQL 5.5 and higher, InnoDB becomes the default storage engine. MySQL Enterprise Backup can back up both types of tables, and tables from other storage engines also. The backup process for InnoDB tables (hot backup) is less disruptive to database operations than for MyISAM tables (warm backup).

See Also InnoDB, warm backup.

mysqlbackup

The primary command of the MySQL Enterprise Backup product. Different options perform backup and restore operations.

See Also backup, restore.

mysqldump

A MySQL command that performs logical backups, producing a set of SQL commands to recreate tables and data. Suitable for smaller backups or less critical data, because the restore operation takes longer than with a physical backup produced by MySQL Enterprise Backup.

See Also logical backup, physical backup, restore.

N

non-TTS backup

A backup that is NOT created using transportable tablespace (TTS), that is, not with the --use-tts option.

See Also transportable tablespace, TTS backup.

O

.opt file

A file containing database server configuration information. Files with this extension are always included in backups produced by the backup operations of the MySQL Enterprise Backup product.

Oracle Secure Backup

An Oracle product for managing backup media, and so classified as media management software (MMS). Abbreviated OSB. For MySQL Enterprise Backup, OSB is typically used to manage tape backups.

See Also backup, media management software, OSB.

OSB

Abbreviation for Oracle Secure Backup, a media management software product (MMS).

See Also Oracle Secure Backup.

P

.par file

A file containing partition definitions. Files with this extension are always included in backups produced by the mysqlbackup command of the MySQL Enterprise Backup product.

parallel backup

The default processing mode in MySQL Enterprise Backup 3.8 and higher, employing multiple threads for different classes of internal operations (read, process, and write). See Section 1.2, “Overview of Backup Types” for an overview, Section 20.10, “Performance / Scalability / Capacity Options” for the relevant mysqlbackup options, and Chapter 13, Performance Considerations for MySQL Enterprise Backup for performance guidelines and tips.

physical backup

A backup that copies the actual data files. For example, the MySQL Enterprise Backup command produces a physical backup, because its output contains data files that can be used directly by the mysqld server. Contrast with logical backup.

See Also backup, logical backup.

point in time

The time corresponding to the end of a backup operation. A prepared backup includes all the changes that occurred while the backup operation was running. Restoring the backup brings the data back to the state at the moment when the backup operation completed.

See Also backup, prepared backup, restore.

prepared backup

The set of backup data that is entirely consistent and ready to be restored. It is produced by performing the apply operation on the raw backup.

See Also apply, raw backup.

progress table

The table mysql.backup_progress that holds details of running backup operations. When a backup job finishes, the details are recorded in the history table.

See Also backup, history table.

R

raw backup

The initial set of backup data, not yet ready to be restored because it does not incorporate changes that occurred while the backup was running. The apply operation transforms the backup files into a prepared backup that is ready to be restored.

See Also apply, prepared backup.

redo log

A set of files, typically named ib_logfile0 and ib_logfile1, that record statements that attempt to change data in InnoDB tables. These statements are replayed automatically to correct data written by incomplete transactions, on startup following a crash. The passage of data through the redo logs is represented by the ever-increasing LSN value. The 4GB limit on maximum size for the redo log is raised in MySQL 5.6.

See Also LSN.

regular expression

Some MySQL Enterprise Backup features use POSIX-style regular expressions, for example to specify tables, databases, or both to include or exclude from a partial backup. Regular expressions require escaping for dots in filenames, because the dot is the single-character wildcard; no escaping is needed for forward slashes in path names. When specifying regular expressions on the command line, surround them with quotation marks as appropriate for the shell environment, to prevent expansion of characters such as asterisks by the shell wildcard mechanism.

See Also exclude, include.

replica

In a replication configuration, a database server that receives updates from a source server. Typically used to service user queries, to minimize the query load on the source server. With MySQL Enterprise Backup, you might take a backup on one server, and restore on a different system to create a new replica server with the data already in place. You might also back up data from a replica server rather than the source, to minimize any slowdown of the overall system.

See Also replication, source.

replication

A common configuration for MySQL deployments, with data and DML operations from a source server synchronized with a set of replica servers. With MySQL Enterprise Backup, you might take a backup on one server, and restore on a different system to create a new replica server with the data already in place. You might also back up data from a replica server rather than the source, to minimize any slowdown of the overall system.

See Also replica, source.

repository

We distinguish between the server repository and the backup repository.

See Also backup repository, server repository.

restore

The converse of the backup operation. The data files from a prepared backup are put back into place to repair a data issue or bring the system back to an earlier state.

See Also backup, prepared backup.

row format

The disk storage format for a row from an InnoDB table. As InnoDB gains new capabilities such as compression, new row formats are introduced to support the resulting improvements in storage efficiency and performance.

Each table has its own row format, specified through the ROW_FORMAT option. To see the row format for each InnoDB table, issue the command SHOW TABLE STATUS. Because all the tables in the system tablespace share the same row format, to take advantage of other row formats typically requires setting the innodb_file_per_table option, so that each table is stored in a separate tablespace.

S

SBT

Acronym for system backup to tape.

See Also system backup to tape.

selective backup

Another name for partial backup

See Also selective restore.

selective restore

Another name for partial restore

See Also selective backup.

server

A MySQL instance controlled by a mysqld daemon. A physical machine can host multiple MySQL servers, each requiring its own backup operations and schedule. Some backup operations communicate with the server through a connection.

See Also instance.

server repository

Contrast with backup repository.

See Also backup repository, repository.

single-file backup

A backup technique that packs all the backup data into one file (the backup image), for ease of storage and transfer. The streaming backup technique requires using a single-file backup.

See Also image, streaming.

slave

See replica.

source

In a replication configuration, a database server that sends updates to a set of replica servers. It typically dedicates most of its resources to write operations, leaving user queries to the replicas. With MySQL Enterprise Backup, typically you perform backups on the replica servers rather than the source, to minimize any slowdown of the overall system.

See Also replica, replication.

streaming

A backup technique that transfers the data immediately to another server, rather than saving a local copy. Uses mechanisms such as Unix pipes. Requires a single-file backup, with the destination file specified as - (standard output).

See Also single-file backup.

system backup to tape

An API for media management software. Abbreviated SBT. Several mysqlbackup options (with sbt in their names) pass information to media management software products such as Oracle Secure Backup.

See Also Oracle Secure Backup, SBT.

system tablespace

By default, this single data file stores all the table data for a database server, as well as all the metadata for InnoDB-related objects (the data dictionary).

Turning on the innodb_file_per_table option causes each newly created table to be stored in its own tablespace, reducing the size of, and dependencies on, the system tablespace.

Keeping all table data in the system tablespace has implications for the MySQL Enterprise Backup product (backing up one large file rather than several smaller files), and prevents you from using certain InnoDB features that require the newer Barracuda file format. on the

See Also data dictionary, file format, ibdata file, tablespace.

T

.TRG file

A file containing trigger parameters. Files with this extension are always included in backups produced by the mysqlbackup command of the MySQL Enterprise Backup product.

table

Although a table is a distinct, addressable object in the context of SQL, for backup purposes we are often concerned with whether the table is part of the system tablespace, or was created under the file-per-table setting and so resides in its own tablespace.

See Also backup, system tablespace, tablespace.

tablespace

For InnoDB tables, the file that holds the data and indexes for a table. Can be either the system tablespace containing multiple tables, or a table created with the file-per-table setting that resides in its own tablespace file.

See Also InnoDB, system tablespace.

transportable tablespace

A feature that allows a tablespace to be moved from one instance to another. Traditionally, this has not been possible for InnoDB tablespaces because all table data was part of the system tablespace. In MySQL 5.6 and higher, the FLUSH TABLES ... FOR EXPORT syntax prepares an InnoDB table for copying to another server; running ALTER TABLE ... DISCARD TABLESPACE and ALTER TABLE ... IMPORT TABLESPACE on the other server brings the copied data file into the other instance. A separate .cfg file, copied along with the .ibd file, is used to update the table metadata (for example the space ID) as the tablespace is imported. See Importing InnoDB Tables for usage information.

Use the --use-tts option to create a backup with transportable tablespace. See also Section 5.1.5, “Restoring Backups Created with the --use-tts Option”.

TTS

Short form for transportable tablespace.

See Also transportable tablespace.

TTS backup

A backup that is created using transportable tablespace (TTS), that is, with the --use-tts option.

See Also non-TTS backup, transportable tablespace.

W

warm backup

A backup taken while the database server is running, but that restricts some database operations during the backup process. For example, tables might become read-only. For busy applications and websites, you might prefer a hot backup.

See Also backup.