mysql ftwrl_BACKUP LOCK解决备份时FTWRL引入的SQL阻塞问题

1 问题描述

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

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存在的问题。

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 forSELECTqueries to complete regardless of the storage engines involved;

never blocksSELECTs, or updates to InnoDB, Blackhole and Federated tables.