MySQL Enterprise Backup 4.0 User's Guide  /  ...  /  Limitations of MySQL Enterprise Backup

B.1 Limitations of MySQL Enterprise Backup

  • In Linux, Unix, and OS X systems, mysqlbackup 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 now 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 combination of su, umask, chown, and 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 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 by, for example, a Unix kill -9 command, a FLUSH TABLES WITH READ LOCK operation might remain running. In this case, use the KILL QUERY statement from the mysql command line to kill the FLUSH TABLES WITH READ LOCK statement. This issue is more likely to occur if the FLUSH TABLES operation is stalled by a long-running query or transaction. Refer to Section 7.1, “Optimizing Backup Performance” for guidelines about backup timing and performance.

  • Do not run the DDL operations ALTER TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, REPAIR TABLE, RESTORE TABLE or CREATE INDEX while a backup operation is going on. The resulting backup might become corrupted.

    The only 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.

  • 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 they have been used to update or insert into normal tables during the backup, 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 the mysql.backup_history table does not correctly reflect the storage engines of the backed-up databases.

  • 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 4.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 known issue with the InnoDB storage engine (see Bug# 72851 on the MySQL Bug System).

  • 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.

  • Backup of a partitioned table using transportable tablespace (TTS) would fail when any (or all) of its partitions was created in a shared tablespace.

  • A copy-back process of a partitioned table backed up using transportable tablespace (TTS) would fail if any of the partitions was created outside of the backed-up server's data directory, even when the --force option is used.

  • If Data Definition Language (DDL) statements are executed on the server when a backup is being created using transportable tablespace (TTS), the backup might fail. It is because tables not being backed up are not locked during the backup process, but mysqlbackup still checks the status of those tables at the end of the process, and an error might occur if definitions of those tables have been changed. To avoid the problem, do not perform any DDL operation, especially DROP TABLE, when a TTS backup is going on.

  • If a table containing full-text search (FTS) index is backed up using transportable tablespace (TTS), after it is restored, the FTS index will be corrupted. Users will need to recreate the index with the following command:

    mysql> ALTER TABLE mytable ENGINE = INNODB;

    Then, check that there are no more errors with the table:

    mysql> CHECK TABLE mytable;

  • Tables created on the MySQL server with the ANSI_QUOTES SQL mode cannot be backed up using transportable tablespace (TTS).

  • Currently, MySQL Enterprise Backup cannot backup a multi-source replication slave.

  • MySQL Enterprise Backup does not include the .pem files from the server into the backup. The files are part of the server instance when SSL connections are enabled.

  • When using the backup-and-apply-log command without a connection to the MySQL server, mysqlbackup cannot know the correct binary log file name and binary log position for the backup; yet, at the end of the backup-and-apply-log operation, mysqlbackup still prints out some values for the binary log file name and position, which are random in nature and should be ignored by users.

  • When backing up an instance of MySQL 5.7, if a CREATE INDEX statement with ALGORITHM = INPLACE is issued when the backup process is going on, because the statement will not go into the redo log of the MySQL 5.7 server (see Sorted Index Builds for details), it cannot be recorded in the backup, and the index will not be recreated by mysqlbackup when the backup is restored.

Download this Manual
PDF (US Ltr) - 1.1Mb
PDF (A4) - 1.1Mb
EPUB - 241.5Kb
HTML Download (TGZ) - 189.4Kb
HTML Download (Zip) - 218.7Kb
User Comments
Sign Up Login You must be logged in to post a comment.