These terms are commonly used in information about the MySQL Enterprise Backup product.
If your application could benefit from InnoDB table
compression, or uses BLOBs or
large text columns that could benefit from the dynamic row
format, you might switch some tables to Barracuda format. You
select the file format to use by setting the
innodb_file_format option before creating the
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
Backups are further classified as hot, warm, or cold depending on how much they interfere with normal database operation. (Hot backups have the least interference, cold backups the most.)
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 on Unix-like systems and
.ini on Windows systems.
The code name for an InnoDB file
format that supports compression for table data. This
file format was first introduced in the InnoDB Plugin. It
supports the compressed row
format that enables InnoDB table compression, and the
dynamic row format that
improves the storage layout for BLOB and large text columns. You
can select it through the
Because the InnoDB system tablespace is stored in the original Antelope file format, to use the Barracuda file format you must also enable the file-per-table setting, which puts newly created tables in their own tablespaces separate from the system tablespace.
The MySQL Enterprise Backup product version 3.5 and above supports backing up tablespaces that use the Barracuda file format.
A file containing a record of all statements that attempt to change table data. These statements can be replayed to bring slave servers up to date in a replication scenario, or to bring a database up to date after restoring table data from a backup. The binary logging feature can be turned on and off, although Oracle recommends always enabling it if you use replication or perform backups.
You can examine the contents of the binary log, or replay those statements during replication or recovery, by using the mysqlbinlog command. For full information about the binary log, see The Binary Log. For MySQL configuration options related to the binary log, see Binary Log Options and Variables.
For the MySQL Enterprise Backup
product, the file name of the binary log and the current
position within the file are important details. To record this
information for the master server when taking a backup in a
replication context, you can specify the
Prior to MySQL 5.0, a similar capability was available, known as the update log. In MySQL 5.0 and higher, the binary log replaces the update log.
See Also binary log.
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.
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.
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 OS X 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.
The mechanism used by certain backup operations to communicate with a running MySQL server. For example, the mysqlbackup command can log into the server being backed up to insert and update data in the progress table and the history table. A hot backup typically uses a database connection for convenience, but can proceed anyway if the connection is not available. A warm backup always uses a database connection, because it must put the server into a read-only state. A cold backup is taken while the MySQL server is shut down, and so cannot use any features that require a connection.
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.
Because the MySQL Enterprise Backup product always backs up the system tablespace, all backups include the contents of the data dictionary.
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.
A period when the database is unresponsive. The database might be entirely shut down, or in a read-only state when applications are attempting to insert, update, or delete data. The goal for your backup strategy is to minimize downtime, using techniques such as hot backup for InnoDB tables, cold backup using slave servers in a replication configuration, and minimizing the duration of the suspend stage where you run customized backup logic while the MySQL server is locked.
See Also partial backup.
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
For backups, you must always keep the full set of
.frm files along with the backup data to be
able to restore tables that are altered or dropped after the
Although each InnoDB table has a
InnoDB maintains its own table metadata in the system
.frm files are not needed for
InnoDB to operate on InnoDB tables.
These files are backed up by the MySQL
Enterprise Backup product. These files must not be
modified by an
ALTER TABLE operation while
the backup is taking place, which is why backups that include
non-InnoDB tables perform a
FLUSH TABLES WITH READ
LOCK operation to freeze such activity while backing
.frm files. Restoring a backup can
.frm files being created, changed,
or removed to match the state of the database at the time of the
See Also MySQL Enterprise 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.
A hot backup involves more than simply copying data files: it must include any data that was inserted or updated while the backup was in process; it must exclude any data that was deleted while the backup was in process; and it must ignore any changes started by transactions but not committed.
The Oracle product that performs hot backups, of InnoDB tables especially but also tables from MyISAM and other storage engines, is MySQL Enterprise Backup.
The hot backup process consists of two stages. The initial copying of the InnoDB data files produces a raw backup. The apply step incorporates any changes to the database that happened while the backup was running. Applying the changes produces a prepared backup; these files are ready to be restored whenever necessary.
A full backup consists of a hot backup phase that copies the InnoDB data, followed by a warm backup phase that copies any non-InnoDB data such as MyISAM tables and .frm files.
Each InnoDB tablespace created
using the file-per-table
setting has a filename with a
This extension does not apply to the
system tablespace, which is
made up of files named
ibdata2, and so on.
A set of files with names such as
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.
The compression applied during backup is distinct from the compressed row format that keeps table data compressed during normal operation. An InnoDB tablespace that is already in compressed row format is not compressed a second time, because that would save little or no space.
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
See Also partial 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 3.3.2, “Making an Incremental Backup” for usage details.
Related mysqlbackup options are
See Also full backup.
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
statement creates InnoDB tables by default.
A backup that reproduces table
structure and data, without copying the actual data files. For
mysqldump command produces a
logical backup, because its output contains statements such as
CREATE TABLE and
that can re-create the data. Contrast with
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.
The record of the environment (for example, command-line
arguments) and data files involved in a backup, stored in the
meta/backup_content.xml, respectively. This
data can be used by management tools during diagnosis and
In a replication configuration, a database server that sends updates to a set of slave servers. It typically dedicates most of its resources to write operations, leaving user queries to the slaves. With MySQL Enterprise Backup, typically you perform backups on the slave servers rather than the master, to minimize any slowdown of the overall system.
See Also Oracle Secure Backup.
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
See Also MySQL Enterprise Backup.
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).
A licensed products that performs hot backups of MySQL databases. 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.
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.
A type of operation performed while the database server is stopped. With the MySQL Enterprise Backup product, the main offline operation is the restore step. You can optionally perform a cold backup, which is another offline operation. Contrast with online.
A type of operation performed while the database server is running. A hot backup is the ideal example, because the database continues to run and no read or write operations are blocked. For that reason, sometimes “hot backup” and “online backup” are used as synonyms. A cold backup is the opposite of an online operation; by definition, the database server is shut down while the backup happens. A warm backup is also a kind of online operation, because the database server continues to run, although some write operations could be blocked while a warm backup is in progress. Contrast with offline.
See Also MySQL Enterprise Backup.
See Also Oracle Secure Backup.
See Also MySQL Enterprise 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.3, “Making Backups Faster and Smaller” for an overview, Section 4.1.11, “Capacity Options” for the relevant mysqlbackup options, and Performance Considerations for MySQL Enterprise Backup for performance guidelines and tips.
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
mysqld server. Contrast with
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.
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.
A set of files, typically named
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.
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.
A common configuration for MySQL deployments, with data and DML operations from a master server synchronized with a set of slave servers. With MySQL Enterprise Backup, you might take a backup on one server, and restore on a different system to create a new slave server with the data already in place. You might also back up data from a slave server rather than the master, to minimize any slowdown of the overall system.
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
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.
See Also system backup to tape.
A MySQL instance controlled by
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
In a replication configuration, a database server that receives updates from a master server. Typically used to service user queries, to minimize the query load on the master. With MySQL Enterprise Backup, you might take a backup on one server, and restore on a different system to create a new slave server with the data already in place. You might also back up data from a slave server rather than the master, to minimize any slowdown of the overall system.
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.
An optional stage within the backup where the MySQL Enterprise Backup processing stops, to allow for user-specific operations to be run. The mysqlbackup command has options that let you specify commands to be run while the backup is suspended. Most often used in conjunction with backups of InnoDB tables only, where you might do your own scripting for handling .frm files.
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
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.
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 MySQL Enterprise Backup.