The backup is done by querying the MySQL server to obtain
database structure and content information.

Backup is slower than physical methods because the server must
access database information and convert it to logical format.
If the output is written on the client side, the server must
also send it to the backup program.

Output is larger than for physical backup, particularly when
saved in text format.

Backup and restore granularity is available at the server
level (all databases), database level (all tables in a
particular database), or table level. This is true regardless
of storage engine.

The backup does not include log or configuration files, or
other database-related files that are not part of databases.

To restore logical backups, SQL-format dump files can be
processed using the mysql client. To load
delimited-text files, use the
LOAD DATA
INFILE statement or the
mysqlimport client.

Physical backup methods have these characteristics:

The backup consists of exact copies of database directories
and files. Typically this is a copy of all or part of the
MySQL data directory. Data from MEMORY
tables cannot be backed up this way because their contents are
not stored on disk.

Physical backup methods are faster than logical because they
involve only file copying without conversion.

Output is more compact than for logical backup.

Backup and restore granularity ranges from the level of the
entire data directory down to the level of individual files.
This may or may not provide for table-level granularity,
depending on storage engine. (Each MyISAM
table corresponds uniquely to a set of files, but an
InnoDB table shares file storage with other
InnoDB tables.)

In addition to databases, the backup can include any related
files such as log or configuration files.

Backups are portable only to other machines that have
identical or similar hardware characteristics.

Backups can be performed while the MySQL server is not
running. If the server is running, it is necessary to perform
appropriate locking so that the server does not change
database contents during the backup.

For restore, files copied at the file system level or with
mysqlhotcopy can be copied back to their
original locations with file system commands;
ibbackup restores InnoDB
tables, and ndb_restore restores
NDB tables.

Online Versus Offline Backups

Online backups take place while the MySQL server is running so
that the database information can be obtained from the server.
Offline backups take place while the server is stopped. This
distinction can also be described as “hot” versus
“cold” backups; a “warm” backup is one
where the server remains running but locked against modifying data
while you access database files externally.

Online backup methods have these characteristics:

The backup is less intrusive to other clients, which can
connect to the MySQL server during the backup and may be able
to access data depending on what operations they need to
perform.

Care must be taken to impose appropriate locking so that data
modifications do not take place that would compromise backup
integrity.

Offline backup methods have these characteristics:

Clients can be affected adversely because the server is
unavailable during backup.

The backup procedure is simpler because there is no
possibility of interference from client activity.

A similar distinction between online and offline applies for
recovery operations, and similar characteristics apply. However,
it is more likely that clients will be affected for online
recovery than for online backup because recovery requires stronger
locking. During backup, clients might be able to read data while
it is being backed up. Recovery modifies data and does not just
read it, so clients must be prevented from accessing data while it
is being restored.

Local Versus Remote Backups

A local backup is performed on the same host where the MySQL
server runs, whereas a remote backup is done from a different
host. For some types of backups, the backup can be initiated from
a remote host even if the output is written locally on the server.
host.

mysqldump can connect to local or remote
servers. For SQL output (CREATE and
INSERT statements), local or
remote dumps can be done and generate output on the client.
For delimited-text output (with the
--tab option), data files
are created on the server host.

mysqlhotcopy performs only local backups:
It connects to the server to lock it against data
modifications and then copies local table files.

SELECT ... INTO
OUTFILE can be initiated from a local or remote
client host, but the output file is created on the server
host.

Physical backup methods typically are initiated locally on the
MySQL server host so that the server can be taken offline,
although the destination for copied files might be remote.

Snapshot Backups

Some file system implementations enable “snapshots”
to be taken. These provide logical copies of the file system at a
given point in time, without requiring a physical copy of the
entire file system. (For example, the implementation may use
copy-on-write techniques so that only parts of the file system
modified after the snapshot time need be copied.) MySQL itself
does not provide the capability for taking file system snapshots.
It is available through third-party solutions such as Veritas,
LVM, or ZFS.

Full Versus Incremental Backups

A full backup includes all data managed by a MySQL server at a
given point in time. An incremental backup consists of the changes
made to the data during a given time span (from one point in time
to another). MySQL has different ways to perform full backups,
such as those described earlier in this section. Incremental
backups are made possible by enabling the server's binary log,
which the server uses to record data changes.

Full Versus Point-in-Time (Incremental)
Recovery

A full recovery restores all data from a full backup. This
restores the server instance to the state that it had when the
backup was made. If that state is not sufficiently current, a full
recovery can be followed by recovery of incremental backups made
since the full backup, to bring the server to a more up-to-date
state.

Incremental recovery is recovery of changes made during a given
time span. This is also called point-in-time recovery because it
makes a server's state current up to a given time. Point-in-time
recovery is based on the binary log and typically follows a full
recovery from the backup files that restores the server to its
state when the backup was made. Then the data changes written in
the binary log files are applied as incremental recovery to redo
data modifications and bring the server up to the desired point in
time.

Backup scheduling is valuable for automating backup procedures.
Compression of backup output reduces space requirements, and
encryption of the output provides better security against
unauthorized access of backed-up data. MySQL itself does not
provide these capabilities. ibbackup can
compress InnoDB backups, and compression or
encryption of backup output can be achieved using file system
utilities. Other third-party solutions may be available.