怎么理解mysql中FLUSH TABLES和FLUSH TABLES WITH READ LOCK

共计 11620 个字符，预计需要花费 30 分钟才能阅读完成。

这篇文章主要讲解了“怎么理解 mysql 中 FLUSH TABLES 和 FLUSH TABLES WITH READ LOCK”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“怎么理解 mysql 中 FLUSH TABLES 和 FLUSH TABLES WITH READ LOCK”吧！

1、FLUSH TABLES 关闭所有打开的表，强制关闭所有正在使用的表，并刷新查询缓存和预准备语句缓存，不会刷新脏块
2、FLUSH TABLES WITH READ LOCK 关闭所有打开的表并使用全局读锁锁定所有数据库的所有表，不会刷新脏块
3、如果一个会话中使用 LOCK TABLES tbl_name lock_type 语句对某表加了表锁，在该表锁未释放前，那么另外一个会话如果执行 FLUSH TABLES 语句会被阻塞，执行 FLUSH TABLES WITH READ LOCK 也会被堵塞

4、如果一个会话正在执行 DDL 语句，那么另外一个会话如果执行 FLUSH TABLES 语句会被阻塞，执行 FLUSH TABLES WITH READ LOCK 也会被堵塞

5、如果一个会话正在执行 DML 大事务（DML 语句正在执行，数据正在发生修改，而不是使用 lock in share mode 和 for update 语句来显式加锁），那么另外一个会话如果执行 FLUSH TABLES 语句会被阻塞，执行 FLUSH TABLES WITH READ LOCK 也会被堵塞

6、FLUSH TABLES WITH READ LOCK 语句不会阻塞日志表的写入，例如：查询日志，慢查询日志等

7、mysqldump 的 –master-data、–lock-all-tables 参数引发 FLUSH TABLES 和 FLUSH TABLES WITH READ LOCK
8、FLUSH TABLES tbl_name [, tbl_name] … FOR EXPORT 会刷新脏块
9、FLUSH TABLES WITH READ LOCK 可以针对单个表进行锁定，比如只锁定 table1 则 flush tables table1 with read lock;

FLUSH TABLES
https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables
 Closes all open tables, forces all tables in use to be closed, and flushes the query cache and prepared statement cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.

  关闭所有打开的表，强制关闭所有正在使用的表，并刷新查询缓存和预准备语句缓存。FLUSH TABLES 还会从查询缓存中删除所有查询结果，例如 RESET QUERY CACHE 语句。

RESET QUERY CACHE
https://dev.mysql.com/doc/refman/5.7/en/query-cache.html
 The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

 The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.

 The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

  查询缓存存储 SELECT 语句的文本以及发送到客户端的相应结果。如果稍后收到相同的语句，则服务器从查询缓存中检索结果，而不是再次解析和执行语句。查询缓存在会话之间共享，因此可以发送由一个客户端生成的结果集以响应由另一个客户端发出的相同查询。
  查询缓存在您拥有不经常更改且服务器接收许多相同查询的表的环境中非常有用。这是许多基于数据库内容生成许多动态页面的 Web 服务器的典型情况。
  查询缓存不返回过时数据。修改表时，将刷新查询缓存中的所有相关条目。

FLUSH TABLES is not permitted when there is an active LOCK TABLES … READ. To flush and lock tables, use FLUSH TABLES tbl_name … WITH READ LOCK instead.

当有表正处于 LOCK TABLES … READ 语句加锁状态时，不允许使用 FLUSH TABLES 语句（另外一个会话执行 FLUSH TABLES 会被阻塞），如果已经使用 LOCK TABLES … READ 语句对某表加读锁的情况下要对另外的表执行刷新，可以在另外一个会话中使用 FLUSH TABLES tbl_name … WITH READ LOCK 语句

会话 1 先执行
mysql lock tables table1 read ;
会话 2，堵塞
mysql flush tables ;
会话 3，堵塞
mysql flush tables table1 with read lock;
会话 4，不堵塞
mysql flush tables table2 with read lock;

FLUSH TABLES tbl_name [, tbl_name] …

With a list of one or more comma-separated table names, this statement is like FLUSH TABLES with no names except that the server flushes only the named tables. If a named table does not exist, no error occurs.

使用一个或多个逗号分隔的表名列表，表示只刷新这些表名的表，如果命名表不存在，则不会发生错误。

FLUSH TABLES WITH READ LOCK

 Closes all open tables and locks all tables for all databases with a global read lock. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock.

  关闭所有打开的表并使用全局读锁锁定所有数据库的所有表。如果您具有可以及时拍摄快照的 Veritas 或 ZFS 等文件系统，则这是一种非常方便的备份方式。使用 UNLOCK TABLES 释放锁定。(你可以及时使用支持快照的文件系统进行快照备份，备份完成之后，使用 UNLOCK TABLES 语句释放锁。)

 FLUSH TABLES WITH READ LOCK acquires a global read lock rather than table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits:

 UNLOCK TABLES implicitly commits any active transaction only if any tables currently have been locked with LOCK TABLES. The commit does not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCK because the latter statement does not acquire table locks.

 Beginning a transaction causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK TABLES. Beginning a transaction does not release a global read lock acquired with FLUSH TABLES WITH READ LOCK.

 FLUSH TABLES WITH READ LOCK 获取全局读锁而不是表锁，因此在表锁定和隐式提交方面，表现行为不会像 LOCK TABLES 和 UNLOCK TABLES 语句：
  当前任何表已被 LOCK TABLES tbl_name lock_type 语句锁定时，UNLOCK TABLES 会隐式提交任何活动事务。但是执行 FLUSH TABLES WITH READ LOCK 之后，再执行 UNLOCK TABLES 不会发生提交，因为后一个语句没有获取表锁。
  开始事务会导致释放使用 LOCK TABLES tbl_name lock_type 语句获取的表锁，就像您已经执行了 UNLOCK TABLES 一   样。  开始事务不会释放使用 FLUSH TABLES WITH READ LOCK 获取的全局读锁定。

FLUSH TABLES WITH READ LOCK is not compatible with XA transactions.

FLUSH TABLES WITH READ LOCK does not prevent the server from inserting rows into the log tables
FLUSH TABLES WITH READ LOCK 与 XA 事务不兼容。
FLUSH TABLES WITH READ LOCK 不会阻止服务器将行插入日志表，例如：查询日志，慢查询日志等

FLUSH TABLES tbl_name [, tbl_name] … WITH READ LOCK

 This statement flushes and acquires read locks for the named tables. The statement first acquires exclusive metadata locks for the tables, so it waits for transactions that have those tables open to complete. Then the statement flushes the tables from the table cache, reopens the tables, acquires table locks (like LOCK TABLES … READ), and downgrades the metadata locks from exclusive to shared. After the statement acquires locks and downgrades the metadata locks, other sessions can read but not modify the tables.

 Because this statement acquires table locks, you must have the LOCK TABLES privilege for each table, in addition to the RELOAD privilege that is required to use any FLUSH statement.

 This statement applies only to existing base (non-TEMPORARY) tables. If a name refers to a base table, that table is used. If it refers to a TEMPORARY table, it is ignored. If a name applies to a view, an ER_WRONG_OBJECT error occurs. Otherwise, an ER_NO_SUCH_TABLE error occurs.

 Use UNLOCK TABLES to release the locks, LOCK TABLES to release the locks and acquire other locks, or START TRANSACTION to release the locks and begin a new transaction.

 This FLUSH TABLES variant enables tables to be flushed and locked in a single operation. It provides a workaround for the restriction that FLUSH TABLES is not permitted when there is an active LOCK TABLES … READ.

 This statement does not perform an implicit UNLOCK TABLES, so an error results if you use the statement while there is any active LOCK TABLES or use it a second time without first releasing the locks acquired.

 If a flushed table was opened with HANDLER, the handler is implicitly flushed and loses its position.

  此语句刷新并获取指定表的读锁定。该语句首先获取表的独占元数据锁，因此它等待打开这些表的事务完成。然后语句从表缓存中刷新表，重新打开表，获取表锁（如 LOCK TABLES … READ），并将元数据锁从独占降级为共享。在语句获取锁并降级元数据锁后，其他会话可以读取但不能修改表。
  由于此语句获取表锁，因此除了使用任何 FLUSH 语句所需的 RELOAD 权限外，还必须为每个表具有 LOCK TABLES 权限。
    此语句仅适用于现有的基本（非 TEMPORARY）表。如果名称引用基本表，则使用该基本表。如果它引用 TEMPORARY 表，则忽略它。如果名称适用于视图，则会发生 ER_WRONG_OBJECT 错误。否则，发生 ER_NO_SUCH_TABLE 错误。
  使用 UNLOCK TABLES 释放锁，使用 LOCK TABLES 释放该锁并获取其他锁，或使用 START TRANSACTION 释放锁并开始新的事务。
  此 FLUSH TABLES 变量使表能够在单个操作中刷新和锁定。它提供了一个解决方法，当有一个活动的 LOCK TABLES … READ 时，不允许 FLUSH TABLES。
  此语句不执行隐式 UNLOCK TABLES，因此如果在有任何活动的 LOCK TABLES 时使用该语句，或者在没有首先释放获取的锁的情况下再次使用该语句，则会导致错误。
  如果使用 HANDLER 打开已刷新的表，则会隐式刷新处理程序并丢失其位置。

FLUSH TABLES tbl_name [, tbl_name] … FOR EXPORT

 This FLUSH TABLES variant applies to InnoDB tables. It ensures that changes to the named tables have been flushed to disk so that binary table copies can be made while the server is running.

The statement works like this:

 a.It acquires shared metadata locks for the named tables. The statement blocks as long as other sessions have active transactions that have modified those tables or hold table locks for them. When the locks have been acquired, the statement blocks transactions that attempt to update the tables, while permitting read-only operations to continue.

 b.It checks whether all storage engines for the tables support FOR EXPORT. If any do not, an ER_ILLEGAL_HA error occurs and the statement fails.

 c.The statement notifies the storage engine for each table to make the table ready for export. The storage engine must ensure that any pending changes are written to disk.

 d.The statement puts the session in lock-tables mode so that the metadata locks acquired earlier are not released when the FOR EXPORT statement completes.

 The FLUSH TABLES … FOR EXPORT statement requires that you have the SELECT privilege for each table. Because this statement acquires table locks, you must also have the LOCK TABLES privilege for each table, in addition to the RELOAD privilege that is required to use any FLUSH statement.

 This statement applies only to existing base (non-TEMPORARY) tables. If a name refers to a base table, that table is used. If it refers to a TEMPORARY table, it is ignored. If a name applies to a view, an ER_WRONG_OBJECT error occurs. Otherwise, an ER_NO_SUCH_TABLE error occurs.

 InnoDB supports FOR EXPORT for tables that have their own .ibd file file (that is, tables created with the innodb_file_per_table setting enabled). InnoDB ensures when notified by the FOR EXPORT statement that any changes have been flushed to disk. This permits a binary copy of table contents to be made while the FOR EXPORT statement is in effect because the .ibd file is transaction consistent and can be copied while the server is running. FOR EXPORT does not apply to InnoDB system tablespace files, or to InnoDB tables that have FULLTEXT indexes.

 FLUSH TABLES …FOR EXPORT is supported for partitioned InnoDB tables.

 When notified by FOR EXPORT, InnoDB writes to disk certain kinds of data that is normally held in memory or in separate disk buffers outside the tablespace files. For each table, InnoDB also produces a file named table_name.cfg in the same database directory as the table. The .cfg file contains metadata needed to reimport the tablespace files later, into the same or different server.

 When the FOR EXPORT statement completes, InnoDB will have flushed all dirty pages to the table data files. Any change buffer entries are merged prior to flushing. At this point, the tables are locked and quiescent: The tables are in a transactionally consistent state on disk and you can copy the .ibd tablespace files along with the corresponding .cfg files to get a consistent snapshot of those tables.

 For the procedure to reimport the copied table data into a MySQL instance, see Section 14.6.3.7,“Copying Tablespaces to Another Instance”.

 After you are done with the tables, use UNLOCK TABLES to release the locks, LOCK TABLES to release the locks and acquire other locks, or START TRANSACTION to release the locks and begin a new transaction.

 While any of these statements is in effect within the session, attempts to use FLUSH TABLES … FOR EXPORT produce an error:

FLUSH TABLES … WITH READ LOCK
FLUSH TABLES … FOR EXPORT
LOCK TABLES … READ
LOCK TABLES … WRITE
 While FLUSH TABLES … FOR EXPORT is in effect within the session, attempts to use any of these statements produce an error:

FLUSH TABLES WITH READ LOCK
FLUSH TABLES … WITH READ LOCK
FLUSH TABLES … FOR EXPORT
 FLUSH TABLES tbl_name [, tbl_name] … FOR EXPORT 适用于 InnoDB 表。它确保已将指定表的更改刷新到磁盘，以便在服务器运行时创建二进制表副本。
声明的作用如下：
 a. 它获取指定表的共享元数据锁。只要其他会话具有已修改这些表或为其保存表锁的活动事务，该语句就会阻塞。获取锁定后，该语句将阻止尝试更新表的事务，同时允许只读操作继续。
 b. 它检查表的所有存储引擎是否支持 FOR EXPORT。如果没有，则发生 ER_ILLEGAL_HA 错误，并且语句失败。
 c. 该语句通知存储引擎的每个表以使表准备好导出。存储引擎必须确保将所有挂起的更改写入磁盘。
 d. 该语句将会话置于锁定表模式，以便在 FOR EXPORT 语句完成时不会释放先前获取的元数据锁。
 FLUSH TABLES … FOR EXPORT 语句要求您具有每个表的 SELECT 权限。由于此语句获取表锁，因此除了使用任何 FLUSH 语句所需的 RELOAD 权限之外，还必须为每个表具有 LOCK TABLES 权限。
  此语句仅适用于现有的基本（非 TEMPORARY）表。如果名称引用基本表，则使用该基本表。如果它引用 TEMPORARY 表，则忽略它。如果名称适用于视图，则会发生 ER_WRONG_OBJECT 错误。否则，发生 ER_NO_SUCH_TABLE 错误。
  对于具有自己的.ibd 文件文件的表（即，启用了 innodb_file_per_table 设置创建的表），InnoDB 支持 FOR EXPORT。InnoDB 确保 FOR EXPORT 语句发出时任何更改都已刷新到磁盘。这允许在 FOR EXPORT 语句生效时生成表内容的二进制副本，因为.ibd 文件是事务一致的，并且可以在服务器 running 时进行复制。FOR EXPORT 不适用于 InnoDB 系统表空间文件，也不适用于具有 FULLTEXT 索引的 InnoDB 表。
 FLUSH TABLES … FOR EXPORT 支持分区的 InnoDB 表。
  当 FOR EXPORT 通知时，InnoDB 会将数据写入磁盘，这些数据通常保存在内存中或表空间文件之外的单独磁盘缓冲区中。对于每个表，InnoDB 还在与表相同的数据库目录中生成名为 table_name.cfg 的文件。.cfg 文件包含稍后将表空间文件重新导入相同或不同服务器所需的元数据。
  当 FOR EXPORT 语句完成时，InnoDB 会将所有脏页刷新到表数据文件。在刷新之前合并任何更改缓冲区条目。此时，表已锁定且处于静止状态：表在磁盘上处于事务一致状态，您可以将.ibd 表空间文件与相应的.cfg 文件一起复制，以获得这些表的一致快照。
  有关将复制的表数据重新导入 MySQL 实例的过程，请参见第 14.6.3.7 节“将表空间复制到另一个实例”。
  完成表后，使用 UNLOCK TABLES 释放锁，使用 LOCK TABLES 释放锁并获取其他锁，或使用 START TRANSACTION 释放锁并开始新事务。

  如下语句中的任何一个在会话中都有效，但在这个会话中再执行 FLUSH TABLES … FOR EXPORT 会产生错误：
  (报错信息 ERROR 1192 (HY000): Can t execute the given command because you have active locked tables or an active transaction，当然在其他会话执行不会报错，但是会一直等待，等待这个会话释放)
      FLUSH TABLES … WITH READ LOCK
      FLUSH TABLES … FOR EXPORT
      LOCK TABLES … READ
  LOCK TABLES … WRITE
  虽然 FLUSH TABLES … FOR EXPORT 在会话中生效，但在这个会话中再使用如下语句中的任何一个都会产生错误：
  (报错信息 ERROR 1192 (HY000): Can t execute the given command because you have active locked tables or an active transaction，当然在其他会话执行不会报错，但是会一直等待，等待这个会话释放)
  FLUSH TABLES WITH READ LOCK
        FLUSH TABLES … WITH READ LOCK
        FLUSH TABLES … FOR EXPORT

感谢各位的阅读，以上就是“怎么理解 mysql 中 FLUSH TABLES 和 FLUSH TABLES WITH READ LOCK”的内容了，经过本文的学习后，相信大家对怎么理解 mysql 中 FLUSH TABLES 和 FLUSH TABLES WITH READ LOCK 这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！