• 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

MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?

by admin

MySQL 8.0.11 supports lock instance for backup but MySQL Enterprise Backup (MEB) not support that still uses FTWRL (FLUSH TABLES WITH READ LOCK). Support for avoiding FLUSH TABLES WITH READ LOCK for InnoDB tables was added in MySQL Enterprise Backup 8.0.16.

Before 8.0.16, 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.

MySQL Server 8.0 introduced support for a specialized lock that is useful for backups:

“Backup lock. A new type of backup lock permits DML during an online backup while preventing operations that could result in an inconsistent snapshot. The new backup lock is supported by LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE syntax. The BACKUP_ADMIN privilege is required to use these statements.”

However, currently MySQL Enterprise Backup does not support this new lock.

Avoid locking in earlier versions

It is possible to avoid locking the tables. To enable this, you will have to specify the –no-locking option which disables locking while copying the non-InnoDB files. The option can also be specified in the configuration file:

[mysqlbackup]
no-locking
Warning!: With –no-locking, if data is changed while copying the files, there will be inconsistencies in the non-InnoDB data. Schema changes (DDL statements) are not guaranteed to be consistent either. It will also not be possible for MySQL Enterprise Backup to get the binary log file and position in a consistent manner.

When locking is disabled, the output when changing from the first to the second stage looks like:

...
mysqlbackup: INFO: Opening backup source directory '/var/lib/mysql/'
111124 10:20:44 mysqlbackup: INFO: Starting to backup all files in subdirectories of '/var/lib/mysql/'
...

Filed Under: mysql

Some more articles you might also be interested in …

  1. Understanding MySQL Query Cache
  2. How to List and Set SELinux Context for MySQL Server
  3. MySQL: How To Find Queries Taking Longer Than N Seconds
  4. MySQL 8.0 : Persisted Variables
  5. How to Restore a Cluster Slave Using its Own Backups
  6. How to Rollback RPM Upgrade of the MySQL Server
  7. Understanding the REVOKE statement in MySQL
  8. Which Character Set Should Be Used To Store Emojis in MySQL Database
  9. MySQL ‘show processlist’ statement
  10. What’s a good process to find and eliminate slow queries in MySQL

You May Also Like

Primary Sidebar

Recent Posts

  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found
  • macof: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright