How MySQL Enterprise Backup (MEB) uses locking while making a backup

MySQL Enterprise Backup performs the backup in several steps:

• First the InnoDB data is copied.
• FLUSH TABLES WITH READ LOCK is executed.
• All other files are copied and other tasks requiring the lock are performed. See also below.
• The lock is released.

If you only need to backup InnoDB tables, you can use one of the following options to skip steps 2. through 4. and thus also avoid the locking steps:

• –only-innodb – Back up only InnoDB data and log files. All .frm files and files created by other storage engines are excluded.
• –only-innodb-with-frm[={all|related}] – Back up only InnoDB data, log files, and the .frm files associated with the InnoDB tables. Note this option is only available in MySQL Enterprise Backup 3.7 and later until 4.x.

While copying the InnoDB data MySQL Enterprise Backup does not lock any tables. This allows you to make a hot backup ensuring the database is fully available for your application for the entire duration of this step.

The second step however will per default lock the entire database as that is the only way to ensure a consistent backup between InnoDB and non-InnoDB tables as well as getting the correct binary log coordinates. When MySQL Enterprise Backup locks the tables, the following lines will be printed:

...
mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
111124 9:05:31 mysqlbackup: INFO: Starting to lock all the tables....
111124 9:05:31 mysqlbackup: INFO: All tables are locked and flushed to disk
mysqlbackup: INFO: Opening backup source directory '/var/lib/mysql/'
111124 9:05:31 mysqlbackup: INFO: Starting to backup all files in subdirectories of '/var/lib/mysql/'
...

The exact tasks performed while the lock is held depends on the version of MySQL Enterprise Backup being used. For version 3.12.3, 4.0.3, and 4.1.0, the tasks are:

• FLUSH ENGINE LOGS; This causes InnoDB to flush its logs to disk.
• If the –suspend-at-end option was given, the backup is suspended.
• If binary logs are copied, copy binlog files used during the backup, starting from the one that was current at the begin of the backup operation.
• Copy non-InnoDB files.
• If the –exec-when-locked option was given, execute the script given as argument.
• In versions earlier than 4.1.0: Copy the InnoDB buffer pool (MySQL Server 5.6 and later only).
• Write the meta files:
  • server-all.cnf
  • server-my.cnf
• Rescan the InnoDB tablespace files for schema changes. In MySQL Enterprise Backup 4.1.0 and later, this can be skipped using the –skip-final-rescan option; in this case it is your responsibility to ensure no DDL changes are made to InnoDB tables during the backup.
• Remove dropped InnoDB tablespace files.
• For image backups, copy the InnoDB native symbolic link files (.isl) to the image.
• Stop to copy the InnoDB redo log. This can take up to 1 second.
• Create the meta/backup_variables.txt, meta/ibbackup_slave_info, meta/backup_gtid_executed.sql files. This includes:
  • SHOW MASTER STATUS; to get the binary log coordinates for the instance being backed up.
  • If the –slave-info option was specified, SHOW SLAVE STATUS;
  • If GTIDs are enabled on the instance, SELECT @@GLOBAL.GTID_EXECUTED;

In versions 4.1.0 and later, the files themselves are written outside the lock (but the data is retrieved inside the lock to get consistent data).