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

这篇文章主要讲解了“怎么理解mysql中FLUSH TABLES和FLUSH TABLES WITH READ LOCK”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么理解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这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！