When restoring an individual InnoDB table, as described in
Section 5.6, “Restoring a Single
.ibd File”, the table must not
have been dropped or truncated in the MySQL server after the
backup. Dropping or truncating an InnoDB table changes its
internal table ID, and when the table is re-created the ID
will not match the table ID from the backup data.
In Linux, Unix, and OS X systems, the
mysqlbackup command does not record file
ownership or permissions of the files that are backed up. Upon
restore, these files might have different ownership, for
example being owned by
root rather than
mysql. They might also have different
read/write permissions, for example being readable by anyone
rather than just the file owner. When planning your backup
strategy, survey the files in the MySQL data directory to
ensure they have consistent owner and permission settings.
When executing a restore operation, use an appropriate
chmod on the restored files to set up the
same owners and privileges as on the original files.
In some cases, backups of non-transactional tables such as
MyISAM tables could contain
additional uncommitted data. If
autocommit is turned
off, and both
InnoDB tables and
non-transactional tables are modified within the same
transaction, data can be written to the non-transactional
table before the binlog position is updated. The binlog
position is updated when the transaction is committed, but the
non-transactional data is written immediately. If the backup
occurs while such a transaction is open, the backup data
contains the updates made to the non-transactional table.
If the mysqlbackup process is interrupted,
such as by a Unix
kill -9 command, a
FLUSH TABLES WITH READ LOCK operation might
remain running. In this case, use the
QUERY statement from the mysql
command line to kill the
FLUSH TABLES WITH READ
LOCK statement. This issue is more likely to occur
FLUSH TABLES operation is stalled by
a long-running query or transaction. Refer to
Chapter 4, mysqlbackup Command Reference for guidelines about backup
timing and performance.
Do not run the DDL operations
REPAIR TABLE, or
RESTORE TABLE while a backup operation is
going on. The resulting backup might be corrupted.
ALTER TABLE operations that can be
safely run in parallel with a backup are those that do not
influence the physical representation of records on disk, such
as changing column names or default column values.
The maximum number of subdirectories allowed in the
--backup-dir path is 21. This limit could be
exceeded by a deeply nested backup directory, or by an
anomalous condition such as symbolic links forming an infinite
If you take a backup when there are temporary tables in the
database, and you use those temporary tables to update or
insert into normal tables, then applying the MySQL binlog to a
backup can fail. That is, you might not be able to roll
forward the backup to a particular point in time using the
MySQL binlog. Temporary tables are not copied to the backup
because the physical filenames
not correspond to the logical table names that MySQL writes to
the binlog. This problem might be removed in the future, if
MySQL implements “row-level binlogging”.
Currently, if the regular expression for the
--include option does not match any table
names, all file-per-table
tables are included in the backup.