• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

by admin

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

MySQL 8.0.16+: In MySQL 8.0.16 and later, MySQL Enterprise Backup no longer includes InnoDB tables in FLUSH TABLES WITH READ LOCK. Instead the MySQL Server 8.0 backup and log locks are used to ensure consistency.

Filed Under: mysql

Some more articles you might also be interested in …

  1. MySQL Server 8.0 – How to create a REPLICATION SLAVE using MEB
  2. How to Generate Unique IDs For MySQL Cluster Backups
  3. Examples of mysqldump partial backups
  4. CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)
  5. How to Backup a MySQL Cluster
  6. MySQL Backup stuck at “Starting to lock all the tables”
  7. Configuring mysqld to log slow queries
  8. How to change the audit log path in the MySQL Docker
  9. How to configure resource groups for MySQL Server running on Linux
  10. “Access denied for user ‘username’@’hostname’ (using password: YES)” – Error while connecting MySQL with PHP

You May Also Like

Primary Sidebar

Recent Posts

  • fprintd-delete Command Examples in Linux
  • fprintd-delete: command not found
  • foreman: command not found
  • foreman Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright