怎么理解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這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！