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語句

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 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