FLUSH TABLES WITH READ LOCK简称(FTWRL),这个命令通常热备份时使用，也是瞬时命令，因为这个命令的特殊性，执行命令时一不留神容易导致穷住。目前在热备份当中 xtrabackup 和 mysqldump 非常常见。

##xtrabackup 
shell# xtrabackup --defaults-file=/etc/my.cnf --no-server-version-check --backup -uroot -p --socket=/tmp/mysql.sock --target-dir=/tmp/back
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
220819 11:26:05  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3381;mysql_socket=/opt/data8.1/data/mysql.sock' as 'root'  (using password: YES).
。。。
2022-08-19T11:26:06.058537+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (2677710324)
2022-08-19T11:26:06.226237+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./undo_001 to /tmp/back/undo_001
2022-08-19T11:26:08.834591+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH TABLES WITH READ LOCK...

##mysqldump
shell# mysqldump -uroot -p -S /tmp/mysql.sock --set-gtid-purged=OFF  --all-databases --master-data=2 --single-transaction >  /tmp/full.sql
2022-08-19T16:33:11.447763+08:00  120 Connect root@localhost on  using Socket
2022-08-19T16:33:11.448073+08:00  120 Query   /*!40100 SET @@SQL_MODE='' */
2022-08-19T16:33:11.448357+08:00  120 Query   /*!40103 SET TIME_ZONE='+00:00' */
2022-08-19T16:33:11.448512+08:00  120 Query   /*!80000 SET SESSION information_schema_stats_expiry=0 */
2022-08-19T16:33:11.448706+08:00  120 Query   SET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 86400
2022-08-19T16:33:11.448866+08:00  120 Query   FLUSH /*!40101 LOCAL */ TABLES
2022-08-19T16:33:11.451661+08:00  120 Query   FLUSH TABLES WITH READ LOCK
。。。
复制

备注：在mysqldump的–master-data、–lock-all-tables参数引发FLUSH TABLES和FLUSH TABLES WITH READ LOCK的输出。

FTWRL穷住现象

下面两个案例中FLUSH TABLES WITH READ LOCK 导致数据库出现穷住情况。

案例1：

#session1
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              32 |
+-----------------+
1 row in set (0.00 sec)

#session2
mysql> INSERT INTO tmr_tzsy...

复制

Waiting for global read lock
正在等待全局读锁,这种情况是执行FLUSH TABLES WITH READ LOCK命令 或 正在设置全局read_only系统变量的时候出现。

案例2：

Waiting for table flush：
线程正在执行FLUSH TABLES，等待所有线程关闭它们的表，或者线程得到一个通知，表的底层结构已经改变，它需要重新打开表来获得新的结构。但是，要重新打开表，必须等到所有其他线程都关闭了这个表。
当线程使用FLUSH TABLES或其他语句之一:FLUSH TABLES tbl_name、ALTER table、RENAME table、REPAIR table、ANALYZE table或OPTIMIZE table，则会发生此通知。

FTWRL机制

关闭所有打开的表，并使用全局读锁锁定所有数据库的所有表。

• 该操作需要FLUSH_TABLES或RELOAD权限
• FLUSH TABLES WITH READ LOCK和read_only变量：不会阻止服务器向日志表插入行，所以对于日志表没影响。

从相关描述中不难理解，操作表缓存，那就必须获取元表相关的锁，因为每个表在内存中都有一个table_cache，不同表的cache对象通过hash链表维护。

关闭表过程中，如果有大查询导致关闭表等待，那么所有访问这个表的查询和更新都需要等待；
上全局读锁会导致所有更新操作都会被堵塞；

引擎影响

• 对于xtrabackup来说 MySQL8.0版本的 innodb和myisam引擎做了优化：
  

在8.0版本中如实例中没有MyISAM引擎，就不会上FTWRL锁。5.6版本和5.7版本 系统表本身就存在MyISAM引擎，所以FTWRL锁 避免不了。

#Mysql5.7
mysql> SELECT TABLE_SCHEMA, ENGINE, COUNT(*)
     FROM INFORMATION_SCHEMA.TABLES
     WHERE  TABLE_TYPE='BASE TABLE'
     GROUP BY TABLE_SCHEMA,  ENGINE;
+--------------------+--------------------+----------+
| TABLE_SCHEMA       | ENGINE             | COUNT(*) |
+--------------------+--------------------+----------+
| db1                | InnoDB             |       14 |
| mysql              | CSV                |        2 |
| mysql              | InnoDB             |       19 |
| mysql              | MyISAM             |       10 |
| performance_schema | PERFORMANCE_SCHEMA |       87 |
| sbtest             | InnoDB             |     3000 |
| sys                | InnoDB             |        1 |
+--------------------+--------------------+----------+
16 rows in set (0.87 sec)
复制

备注：上述备份执行命令行里其实还存在FLUSH NO_WRITE_TO_BINLOG BINARY LOGS命令。默认情况下，服务器将FLUSH语句写入二进制日志，以便将它们复制到副本。要禁止日志记录，可以指定可选的NO_WRITE_TO_BINLOG关键字或别名LOCAL。

LOCK INSTANCE FOR BACKUP

MySQL8.0采用 LOCK INSTANCE FOR BACKUP获取一个实例级备份锁，该锁允许在联机备份期间进行DML操作，同时防止可能导致快照不一致的操作。

• 防止：文件被创建、重命名或删除。

• 阻塞：REPAIR TABLE， TRUNCATE TABLE, OPTIMIZE TABLE，以及账户管理语句。

• 阻止：对InnoDB重做日志中没有记录的修改InnoDB文件的操作。

• 阻止：发出PURGE BINARY LOGS命令

• 允许：只影响用户创建的临时表的DDL操作。实际上，当持有备份锁时，属于用户创建的临时表的文件可以被创建、重命名或删除。

• 允许：创建binary log二进制日志文件。

• UNLOCK INSTANCE释放当前会话持有的备份锁。如果会话终止，会话持有的备份锁也会被释放。其中lock_wait_timeout定义LOCK INSTANCE FOR BACKUP语句在放弃之前等待获得锁的时间。

• 执行需要BACKUP_ADMIN权限，从早期版本到MySQL 8.0的本地升级时，BACKUP_ADMIN特权会自动授予具有RELOAD特权的用户。

LOCK INSTANCE FOR BACKUP;
FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
UNLOCK TABLES;
UNLOCK INSTANCE;


FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
LOCK INSTANCE FOR BACKUP;
UNLOCK INSTANCE;
UNLOCK TABLES;
复制

总结

对于FLUSH TABLES WITH READ LOCK命令导致“Waiting for global read lock”，“Waiting for table flush”现象出现，最好的应对方式，就是把发出FTWRL命令的进程kill掉，释放资源。

FTWRL目前很难避免，应该减少对数据库影响：

1. 不管5.7版本还是8.0版本，引擎采取innodb引擎。
2. 备份一般放在从库就行，当然有需要主库也可以。
3. 备份期间选择负载低，如存在长时间SQL语句，暂停一下。后期需要对长时间SQL进行优化。
4. 对于8.0版本备份机制，LOCK INSTANCE FOR BACKUP限制要遵守。
5. DDL肯定禁止。
6. 合理利用相关参数优化