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.