The group commit feature in MySQL 5.6 and higher changes the frequency of flush operations for the
InnoDB
redo log, which could affect the point in time associated with the backup data fromInnoDB
tables. See Section B.4, “Compatibility Notes for Specific MySQL Versions” for details.If a table on the server is dropped while mysqlbackup is backing up that table, mysqlbackup throws an error and exits.
For MySQL 5.5 and earlier, when restoring an individual InnoDB table, as described in Section 4.4, “Backing Up and 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. This restriction does not apply to MySQL 5.6 and later, as long as the restoration is made from one Generally Available (GA) version to another in the same series of MySQL servers.
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 thanmysql
. 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 combination ofsu
,umask
,chown
, andchmod
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 bothInnoDB
tables and non-transactional tables are modified within the same transaction, data can be written to the non-transactional table before the binary log position is updated. The binary log 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, aFLUSH TABLES WITH READ LOCK
operation might remain running. In this case, use theKILL QUERY
statement from the mysql command line to kill theFLUSH TABLES WITH READ LOCK
statement. This issue is more likely to occur if theFLUSH TABLES
operation is stalled by a long-running query or transaction. Refer to Chapter 5, mysqlbackup Command Reference for guidelines about backup timing and performance.Do not run the DDL operations (for example,
ALTER TABLE
,TRUNCATE TABLE
,OPTIMIZE TABLE
,REPAIR TABLE
,RESTORE TABLE
orCREATE INDEX
) while a backup operation is going on. The resulting backup might become corrupted.When statement-based binary log format is used on the MySQL server (which is the default behavior), 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, application of the MySQL binary log to a backup could then fail—that is, you might not be able to roll forward the backup to a particular point in time using the MySQL binary log. This is because temporary tables are not copied to the backup, as the physical filenames
#sql*.frm
do not correspond to the logical table names that MySQL writes to the binary log. To avoid the problem, use row-based or mixed format for the binary log by setting the value for the--binlog-format
option to “ROW” or “MIXED” on the server.The
engines
column in themysql.backup_history
table does not correctly reflect the storage engines of the backed-up databases.When saving any tables into the data directory, the MySQL server performs conversions for any special characters in any database or table names to come up with the file names for storage. Here are two examples of MySQL commands including special characters (“.” and “-”) in their database and file names:
mysql> CREATE TABLE `db.2`.`t.2` (c1 INT) ENGINE=InnoDB; mysql> CREATE TABLE `db-3`.`t-3` (c1 INT) ENGINE=InnoDB;
The tables created by these commands are stored in the file system as these files:
db@002e2/t@002e2.ibd db@002d3/t@002d3.ibd
Currently, mysqlbackup does not perform the same conversion with database and table names supplied using the
--include-tables
and--exclude-tables
options. Using, for example,--include-tables="db\.2\.t\.2|db-3\.t-3"
will not select the tables described above.As a workaround, for a backup that does not use transportable tablespace (TTS) (that is, the
--use-tts
option is not used during the creation of the backup), users can look up the file names for the desired tables in the data directories of the databases and, in the arguments for the--include-tables
and--exclude-tables
options, use the converted names for the database directories as database names and the converted file names (without the file extension) as table names . For example, for the tables mentioned above, select them for inclusion in a backup with the option argument--include-tables="db@002e2\.t@002e2|db@002d3\.t@002d3"
. Using the same argument with the option--exclude-tables
excludes the two tables in the backup.However, the suggested workaround does not work for TTS backups; therefore, do not use TTS if you want to do a partial backup that includes tables with special characters in their names.
Hot backups for large databases with heavy writing workloads (say, in the order of gigabytes per minute) can take a very long time to complete, due to the huge redo log files that are generated on the server when the backup is in progress. And if the redo log files grow faster than they can be processed by mysqlbackup, the backup operation can actually fail when mysqlbackup cannot catch up with the redo log cycles and LSNs get overwritten by the server before they are read by mysqlbackup. The problem is intensified when the I/O resources available for reading and writing the redo logs are scarce during the backup process. However, if only a small number of tables of the database are modified frequently, the Optimistic Backup feature might alleviate the problem. See Section 3.3.6, “Making an Optimistic Backup” for details.
Compressed InnoDB tables from MySQL server 5.6.10 and earlier cannot be restored with MySQL Enterprise Backup 3.9.0 or later, due to a bug with the InnoDB storage engine (see Bug# 72851 on the MySQL Bug System).
The attempt to copy the binary log files from a server into a backup (which is the default behavior of MySQL Enterprise Backup since 3.11.0) will cause an incremental backup to fail if the full backup it is based on was created with the
--no-locking
option. As a workaround, users should use the--skip-binlog
option for an incremental backup performed in that situation.The attempt to copy the binary log files from a server into a backup (which is the default behavior of MySQL Enterprise Backup since 3.11.0) will cause an offline backup to fail. As a workaround, always use the
--skip-binlog
option for offline backups.While it is possible to backup to or restore from a Network Attached Storage (NAS) device using MySQL Enterprise Backup, due to networking issues that might arise, the consistency of the backups and the performance of the backup or restore operations might be compromised.
When creating a backup using transportable tablespace (TTS) for a server containing tables with a mix of the Antelope and Barracuda file formats, do not apply full locking on the tables (that is, do not specify
--use-tts=with-full-locking
). Instead, just specify--use-tts
or--use-tts=with-minimum-locking
, both of which will apply minimum locking to the tables (Bug #20583946).Tables created on the MySQL server with the
ANSI_QUOTES
SQL mode cannot be backed up using transportable tablespace (TTS).Due to some issues, cloud backup and restore using Amazon S3 is currently not supported by MySQL Enterprise Backup 3.11.