Documentation Home
MySQL Enterprise Backup 3.7 User's Guide
Download this Manual
PDF (US Ltr) - 0.8Mb
PDF (A4) - 0.8Mb

MySQL Enterprise Backup 3.7 User's Guide  /  ...  /  Limitations of mysqlbackup Command

A.1 Limitations of mysqlbackup Command

  • When restoring an individual InnoDB table, as described in Section 5.6, “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.

  • 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 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 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 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 Chapter 4, mysqlbackup Command Reference for guidelines about backup timing and performance.

  • Do not run the DDL operations ALTER TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, REPAIR TABLE, or RESTORE TABLE while a backup operation is going on. The resulting backup might be 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.

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

  • 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 #sql*.frm do 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.

  • The engines column in the mysql.backup_history table does not correctly reflect the storage engines of the backed-up databases.

User Comments
Sign Up Login You must be logged in to post a comment.