[MySQL] BACKUP LOCK解决备份时FTWRL引入的SQL阻塞问题

1 问题描述

我们备份用的是Percona Xtrabackup工具做全量备份(关于Xtrabackup工作原理:https://www.cnblogs.com/olinux/p/5207887.html ) ,这个备份工具在2.2版本之前备份都需要执行FTWRL(flush tables with read lock)语句。这个语句会关闭所有表,所以如果没有结束的SELECT语句,会等待FTWRL语句结束,这时其它SQL都会被阻塞。一线曾出现过用户的长查询语句的情况(几个小时都没有结束),触发这个问题导致整个数据库用户业务SQL都无法执行。

image.png


2 解决方案

我们引入了backup lock锁,以及LOCK TABLES FOR BACKUP,LOCK BINLOG FOR BACKUP,UNLOCK BINLOG三条语句,以作为用于物理和逻辑备份的FLUSH TABLES WITH READ LOCK的轻量级替代。这个backup lock锁不会阻塞Innodb表的DML语句和SELECT语句。

2.1 backup lock锁特性新增三条语句

1. LOCK TABLES FOR BACKUP语句:用来阻塞对于非事物表(MyISAM,CSV,MEMORY和ARCHIVE表等)的update和所有的DDL语句,针对所有表的SELECT查询和针对Innodb/Blackhole/Federated表的INSERT/REPLACE/UPDATE/DELETE不受LOCK TABLES FOR BACKUP影响。执行该语句不会刷新表,即存储引擎不会强制关闭表,并且表不会从表缓存中排出,所以仅等待冲突的语句完成(即DDL和更新到非事务性表)。


2. LOCK BINLOG FOR BACKUP语句:使用另一种新的MDL锁定类型来阻止所有可能会更改二进制日志位置或Exec_Master_Log_Po或Exec_Gtid_Set的操作,如SHOW MASTER / SLAVE状态。即用来阻塞所有对binary log的修改。


3. UNLOCK BINLOG语句:释放当前链接获取的LOCK BINLOG FOR BACKUP锁。

2.2有Backup lock前的备份逻辑

# copy InnoDB data

FLUSH TABLES WITH READ LOCK;

# copy .frm, MyISAM, etc.

# get the binary log coordinates

# finalize the background copy of REDO log

UNLOCK TABLES;

2.3有Backup lock后的备份逻辑

# copy InnoDB data

LOCK TABLES FOR BACKUP;

# copy .frm, MyISAM, etc

LOCK BINLOG FOR BACKUP;

# finalize the background copy of REDO log

UNLOCK TABLES;

# get the binary log coordinates

UNLOCK BINLOG;

2.4 Backup lock源码

          sql_parse.cc

        switch (lex->sql_command) {        
        …
        //处理UNLOCK BINLOG语句,这里释放全局的binlog lock
          case SQLCOM_UNLOCK_BINLOG:
            if (thd->backup_binlog_lock.is_acquired())
              thd->backup_binlog_lock.release(thd);
         
            my_ok(thd);
            break;
        …
        //处理LOCK TABLES FOR BACKUP语句,这里会拿到backup lock
          case SQLCOM_LOCK_TABLES_FOR_BACKUP:
            if (!lock_tables_for_backup(thd))
              my_ok(thd);
         
        break;
        //处理LOCK BINLOG FOR BACKUP语句
          case SQLCOM_LOCK_BINLOG_FOR_BACKUP:
        //这里会拿到一个全局的binlog lock,在MYSQL_BIN_LOG::commit()时新增了代码要获取到这个锁才能执行ordered_commit()。
        //这样就当执行了LOCK BINLOG FOR UPDATE后,就可以阻止binlog的更新。
            if (!lock_binlog_for_backup(thd))
              my_ok(thd);
         
        break;
        …
        }


3 关于FTWRL

3.1 FTWRL的问题:

Percona官方明确给出的FTWRL存在的问题。

image.png

3.2 权限及与其他全局锁的交互

1. FLUSH TABLES WITH READ LOCK与LOCK TABLES FOR BACKUP和LOCK BINLOG FOR BACKUP一样,都需要RELOAD权限。

2. 与其他全局锁的交互:如果当前连接已经拥有FLUSH TABLES WITH READ LOCK锁,LOCK TABLES FOR BACKUP和LOCK BINLOG FOR BACKUP都不起作用,因为FTWRL是一个更严格的锁。 如果FLUSH TABLES WITH READ LOCK在已获取LOCK TABLES FOR BACKUP或LOCK BINLOG FOR BACKUP的连接中执行,则FLUSH TABLES WITH READ LOCK将失败并显示错误。如果服务器以只读模式运行(即read_only设置为1),则对于备份不安全的语句将被阻塞或失败,并显示错误,取决于它们是否在拥有LOCK TABLES FOR BACKUP锁的相同连接或其他连接中执行.

 

3.3 FTWRL相关源码

    sql_parse.cc

        switch (lex->sql_command) {        
        …
            // Fall through.
          case SQLCOM_FLUSH:
          {
            int write_to_binlog;
            if (check_global_access(thd,RELOAD_ACL))
              goto error;
         
            if (first_table && lex->type & REFRESH_READ_LOCK)
            {
              /* Check table-level privileges. */
              if (check_table_access(thd, LOCK_TABLES_ACL | SELECT_ACL, all_tables,
                                     FALSE, UINT_MAX, FALSE))
                goto error;
              if (flush_tables_with_read_lock(thd, all_tables))
                goto error;
              my_ok(thd);
              break;
            }
        …
        }

          sql_reload.cc文件

        bool flush_tables_with_read_lock(THD *thd, TABLE_LIST *all_tables)        
        {
        …
          //这里会拿到表的SNW锁(MDL_SHARED_NO_WRITE, SNW 可升级锁,其它线程能读metadata,数据可读不能读,
          //持锁者可以读写,可以升级成X锁)
          if (lock_table_names(thd, all_tables, NULL,
                               thd->variables.lock_wait_timeout,
                               MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK))
        goto error;
         
          //清理表缓存
          for (table_list= all_tables; table_list;
               table_list= table_list->next_global)
          {
            /* Request removal of table from cache. */
            tdc_remove_table(thd, TDC_RT_REMOVE_UNUSED,
                             table_list->db,
                             table_list->table_name, FALSE);
            /* Reset ticket to satisfy asserts in open_tables(). */
            table_list->mdl_request.ticket= NULL;
          }
          //打开表,并获取表锁
          if (open_and_lock_tables(thd, all_tables, MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK,
                                   &lock_tables_prelocking_strategy) ||
              thd->locked_tables_list.init_locked_tables(thd))
          {
            goto error;
          }
          thd->variables.option_bits|= OPTION_TABLE_LOCK;
        …
        }


3.4 Backup Lock与FTWRL的区别

        1)相同点

    blocks updates to MyISAM, MEMORY, CSV and ARCHIVE tables;

    blocks DDL against any tables;

    does not block updates to temporary and log tables.

2不同点

    does not invalidate the Query Cache;

    never waits for SELECT queries to complete regardless of the storage engines involved;

    never blocks SELECTs, or updates to InnoDB, Blackhole and Federated tables.