MySQL · 物理备份 · Percona XtraBackup 备份原理
MySQL · 物理备份 · Percona XtraBackup 备份原理
LOCK TABLES FOR BACKUP
LOCK TABLES FOR BACKUP使用新的类型的mdl锁来阻塞非事务表的更新以及所有的DDL。如果会话持有一个LOCK TABLES FOR BACKUP锁，那么所有MyISAM, CSV, MEMORY, ARCHIVE, TokuDB, and MyRocks表都会被阻塞，通过performance_schema或者是processlist可以看到这些连接处于Waiting for backup lock状态。
无论什么存储引擎，LOCK TABLES FOR BACKUP对select操作都没有影响。对InnoDB, Blackhole and Federated表，LOCK TABLES FOR BACKUP对INSERT, REPLACE, UPDATE, DELETE 语句并不适用。
不像FLUSH TABLES WITH READ LOCK，LOCK TABLES FOR BACKUP不会flush tables，存储引擎不会强制关闭表，也不会将表强制刷出table cache。结果，LOCK TABLES FOR BACKUP仅仅只会等待冲突语句的完成（DDL和非事务表的更新），不会等待select或者innodb表的update完成
LOCK BINLOG FOR BACKUP
不同于LOCK TABLES FOR BACKUP，LOCK BINLOG FOR BACKUP采用的是另外一种mdl锁，旨在阻塞所有可能导致binlog pos点或者SHOW MASTER/SLAVE STATUS结果中Exec_Master_Log_Pos or Exec_Gtid_Set(当前slave的sql线程执行位点对应的master binlog位点)值的变化，更为特别的是，如果开启了binlog(不管是global级别，或者是session级别设置sql_log_bin)，他只会阻塞事务commit，或者再slave上执行了commit导致Exec_Master_Log_Pos or Executed_Gtid_Set变化，Connections that are currently blocked on the global binlog lock can be identified by the Waiting for binlog lock status in PROCESSLIST
从percona server 5.6.26-74.0起，LOCK TABLES FOR BACKUP会刷新innodb当前的binlog位点，因此，innodb中的binlog位点与redo log是一致的，对于非事务表的更新binlog是不变化的(因为非事务表的更新会被LOCK TABLES FOR BACKUP阻塞)，这将会避免在某些场景下执行更不安全的LOCK BINLOG FOR BACKUP命令
unlock binlog
如果当前连接获得了binlog备份锁，那么UNLOCK BINLOG将会释放LOCK BINLOG FOR BACKUP锁。
LOCK TABLES FOR BACKUP ... copy .frm, MyISAM, CSV, etc. ... LOCK BINLOG FOR BACKUP UNLOCK TABLES ... get binlog coordinates ... ... wait for redo log copying to finish ... UNLOCK BINLOG
Interaction with other global locks
Both LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP have no effect if the current connection already owns a FLUSH TABLESWITH READ LOCK lock, as it’s a more restrictive lock. If FLUSH TABLES WITH READ LOCK is executed in a connection that has acquired LOCK TABLES FOR BACKUP or LOCK BINLOG FOR BACKUP, FLUSH TABLES WITH READ LOCK fails with an error.
If the server is operating in the read-only mode (i.e. read_only set to 1), statements that are unsafe for backups will be either blocked or fail with an error, depending on whether they are executed in the same connection that owns LOCK TABLES FOR BACKUPlock, or other connections.
mysqldump
mysqldump新增lock-for-backup选项(默认禁用)。如果与single-transaction同时存在的话，mysqldump在启动dump操作前会执行LOCK TABLES FOR BACKUP，目的是防止不安全的语句执行导致数据不一致
如果没有single-transaction，lock-for-backup会自动转换成lock-all-tables锁，lock-for-backup与lock-all-tables是互斥的，如果在命令行中同时出现这两个参数的话，会报出错误
如果备份锁这个功能在备份的实例上不被支持的话，而在命令中又有lock-for-backup选项的话，mysqldump会被终止掉并报出对应的错误。
在一个备份命令中，如果同时出现master-data和single-transaction，那么lock-for-backup将不会产生任何作用，任然会用FLUSH TABLES WITH READLOCK来获取binlog位点。为了实现Start transaction with consistent snapshot功能，这个限制在 Percona Server for MySQL 5.6.17-66.0中已经被移除
