• 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. “Another MySQL daemon already running with the same unix socket” – error while starting MYSQL
  2. How to Create a MySQL Docker Container for Testing
  3. How To Create a Local Yum Repository for MySQL Enterprise Packages
  4. What are Reserved User Accounts in MySQL
  5. MySQL: How to kill a Long Running Query using max_execution_time
  6. MySQL – How to undo (rollback) a set of SQL statements
  7. How to use a Wrapper Script to set Custom Per-Process Attributes for MySQL Server
  8. How To Restore an NDBCluster Backup Using ndb_restore
  9. What is the MySQL Enterprise Monitor?
  10. MySQL 8.0 : Persisted Variables

You May Also Like

Primary Sidebar

Recent Posts

  • pw-cat Command Examples in Linux
  • pvs: command not found
  • pulseaudio: command not found
  • pulseaudio Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright