Movatterモバイル変換

It is not possible to issueFLUSH statements within stored functions or triggers. However, you may useFLUSH in stored procedures, so long as these are not called from stored functions or triggers. SeeSection 27.10, “Restrictions on Stored Programs”.

FLUSH LOGS,FLUSH BINARY LOGS,FLUSH TABLES WITH READ LOCK (with or without a table list), andFLUSH TABLEStbl_name ... FOR EXPORT are not written to the binary log in any case because they would cause problems if replicated to a replica.

• FLUSH BINARY LOGS

  Closes and reopens any binary log file to which the server is writing. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file.

  This operation requires theRELOAD privilege.

• FLUSH ENGINE LOGS

  Closes and reopens any flushable logs for installed storage engines. This causesInnoDB to flush its logs to disk.

  This operation requires theRELOAD privilege.

• FLUSH ERROR LOGS

  Closes and reopens any error log file to which the server is writing.

  This operation requires theRELOAD privilege.

• FLUSH GENERAL LOGS

  Closes and reopens any general query log file to which the server is writing.

  This operation requires theRELOAD privilege.

  This operation has no effect on tables used for the general query log (seeSection 7.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”).

• FLUSH LOGS

  Closes and reopens any log file to which the server is writing.

  This operation requires theRELOAD privilege.

  The effect of this operation is equivalent to the combined effects of these operations:

  FLUSH BINARY LOGSFLUSH ENGINE LOGSFLUSH ERROR LOGSFLUSH GENERAL LOGSFLUSH RELAY LOGSFLUSH SLOW LOGS

• FLUSH OPTIMIZER_COSTS

  Re-reads the cost model tables so that the optimizer starts using the current cost estimates stored in them.

  This operation requires theFLUSH_OPTIMIZER_COSTS orRELOAD privilege.

  The server writes a warning to the error log for any unrecognized cost model table entries. For information about these tables, seeSection 10.9.5, “The Optimizer Cost Model”. This operation affects only sessions that begin subsequent to the flush. Existing sessions continue to use the cost estimates that were current when they began.

• FLUSH PRIVILEGES

  Note

  This statement is deprecated, and triggers a deprecation warning when used; you should expectFLUSH PRIVILEGES to be removed in a future version of MySQL.

  Re-reads the privileges from the grant tables in themysql system schema. As part of this operation, the server reads theglobal_grants table containing dynamic privilege assignments and registers any unregistered privileges found there.

  Reloading the grant tables is necessary to enable updates to MySQL privileges and users only if you make such changes directly to the grant tables; it is not needed for account management statements such asGRANT orREVOKE, which take effect immediately. SeeSection 8.2.13, “When Privilege Changes Take Effect”, for more information.

  Manipulating the grant tables directly is not recommended, and should be considered deprecated functionality. Use access control statements such asCREATE USER,GRANT,REVOKE, as described inSection 8.2.8, “Adding Accounts, Assigning Privileges, and Dropping Accounts”, instead.

  This operation requires theFLUSH_PRIVILEGES privilege (deprecated) or theRELOAD privilege.

  If the--skip-grant-tables option was specified at server startup to disable the MySQL privilege system,FLUSH PRIVILEGES provides a way to enable the privilege system at runtime.

  Resets failed-login tracking (or enables it if the server was started with--skip-grant-tables) and unlocks any temporarily locked accounts. SeeSection 8.2.15, “Password Management”.

  Frees memory cached by the server as a result ofGRANT,CREATE USER,CREATE SERVER, andINSTALL PLUGIN statements. This memory is not released by the correspondingREVOKE,DROP USER,DROP SERVER, andUNINSTALL PLUGIN statements, so for a server that executes many instances of the statements that cause caching, there is an increase in cached memory use unless it is freed withFLUSH PRIVILEGES.

  Clears the in-memory cache used by thecaching_sha2_password authentication plugin. SeeCache Operation for SHA-2 Pluggable Authentication.

• FLUSH RELAY LOGS [FOR CHANNELchannel]

  Closes and reopens any relay log file to which the server is writing. If relay logging is enabled, the sequence number of the relay log file is incremented by one relative to the previous file.

  This operation requires theRELOAD privilege.

  TheFOR CHANNELchannel clause enables you to name which replication channel the operation applies to. ExecuteFLUSH RELAY LOGS FOR CHANNELchannel to flush the relay log for a specific replication channel. If no channel is named and no extra replication channels exist, the operation applies to the default channel. If no channel is named and multiple replication channels exist, the operation applies to all replication channels. For more information, seeSection 19.2.2, “Replication Channels”.

• FLUSH SLOW LOGS

  Closes and reopens any slow query log file to which the server is writing.

  This operation requires theRELOAD privilege.

  This operation has no effect on tables used for the slow query log (seeSection 7.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”).

• FLUSH STATUS

  Flushes status indicators.

  This operation adds the current thread's session status variable values to the global values and resets the session values to zero. Some global variables may be reset to zero as well. It also resets the counters for key caches (default and named) to zero and setsMax_used_connections to the current number of open connections. This information may be of use when debugging a query. SeeSection 1.6, “How to Report Bugs or Problems”.

  FLUSH STATUS is unaffected byread_only orsuper_read_only, and is always written to the binary log.

  This operation requires theFLUSH_STATUS orRELOAD privilege.

• FLUSH USER_RESOURCES

  Resets all per-hour user resource indicators to zero.

  This operation requires theFLUSH_USER_RESOURCES orRELOAD privilege.

  Resetting resource indicators enables clients that have reached their hourly connection, query, or update limits to resume activity immediately.FLUSH USER_RESOURCES does not apply to the limit on maximum simultaneous connections that is controlled by themax_user_connections system variable. SeeSection 8.2.21, “Setting Account Resource Limits”.

The descriptions here that indicate tables are flushed by closing them apply differently forInnoDB, which flushes table contents to disk but leaves them open. This still permits table files to be copied while the tables are open, as long as other activity does not modify them.

• FLUSH TABLES

  Closes all open tables, forces all tables in use to be closed, and flushes the prepared statement cache.

  This operation requires theFLUSH_TABLES orRELOAD privilege.

  For information about prepared statement caching, seeSection 10.10.3, “Caching of Prepared Statements and Stored Programs”.

  FLUSH TABLES is not permitted when there is an activeLOCK TABLES ... READ. To flush and lock tables, useFLUSH TABLEStbl_name ... WITH READ LOCK instead.

• FLUSH TABLEStbl_name [,tbl_name] ...

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

  This operation requires theFLUSH_TABLES orRELOAD privilege.

• FLUSH TABLES WITH READ LOCK

  Closes all open tables and locks all tables for all databases with a global read lock.

  This operation requires theFLUSH_TABLES orRELOAD privilege.

  This operation 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. UseUNLOCK TABLES to release the lock.

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

  • UNLOCK TABLES implicitly commits any active transaction only if any tables currently have been locked withLOCK TABLES. The commit does not occur forUNLOCK TABLES followingFLUSH TABLES WITH READ LOCK because the latter statement does not acquire table locks.

  • Beginning a transaction causes table locks acquired withLOCK TABLES to be released, as though you had executedUNLOCK TABLES. Beginning a transaction does not release a global read lock acquired withFLUSH TABLES WITH READ LOCK.

  FLUSH TABLES WITH READ LOCK does not prevent the server from inserting rows into the log tables (seeSection 7.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”).

• FLUSH TABLEStbl_name [,tbl_name] ... WITH READ LOCK

  Flushes and acquires read locks for the named tables.

  This operation requires theFLUSH_TABLES orRELOAD privilege. Because it acquires table locks, it also requires theLOCK TABLES privilege for each table.

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

  This operation applies only to existing base (non-TEMPORARY)tables. If a name refers to a base table, that table is used. If it refers to aTEMPORARY table, it is ignored. If a name applies to a view, anER_WRONG_OBJECT error occurs. Otherwise, anER_NO_SUCH_TABLE error occurs.

  UseUNLOCK TABLES to release the locks,LOCK TABLES to release the locks and acquire other locks, orSTART TRANSACTION to release the locks and begin a new transaction.

  ThisFLUSH TABLES variant enables tables to be flushed and locked in a single operation. It provides a workaround for the restriction thatFLUSH TABLES is not permitted when there is an activeLOCK TABLES ... READ.

  This operation does not perform an implicitUNLOCK TABLES, so an error results if you perform the operation while there is any activeLOCK TABLES or use it a second time without first releasing the locks acquired.

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

• FLUSH TABLEStbl_name [,tbl_name] ... FOR EXPORT

  ThisFLUSH TABLES variant applies toInnoDB 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.

  This operation requires theFLUSH_TABLES orRELOAD privilege. Because it acquires locks on tables in preparation for exporting them, it also requires theLOCK TABLES andSELECT privileges for each table.

  The operation works like this:

  1. It acquires shared metadata locks for the named tables. The operation 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 operation blocks transactions that attempt to update the tables, while permitting read-only operations to continue.

  2. It checks whether all storage engines for the tables supportFOR EXPORT. If any do not, anER_ILLEGAL_HA error occurs and the operation fails.

  3. The operation 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.

  4. The operation puts the session in lock-tables mode so that the metadata locks acquired earlier are not released when theFOR EXPORT operation completes.

  This operation applies only to existing base (non-TEMPORARY) tables. If a name refers to a base table, that table is used. If it refers to aTEMPORARY table, it is ignored. If a name applies to a view, anER_WRONG_OBJECT error occurs. Otherwise, anER_NO_SUCH_TABLE error occurs.

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

  FLUSH TABLES ...FOR EXPORT is supported for partitionedInnoDB tables.

  When notified byFOR 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 namedtable_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 theFOR EXPORT operation completes,InnoDB has flushed alldirty pages to the table data files. Anychange 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, seeSection 17.6.1.3, “Importing InnoDB Tables”.

  After you are done with the tables, useUNLOCK TABLES to release the locks,LOCK TABLES to release the locks and acquire other locks, orSTART TRANSACTION to release the locks and begin a new transaction.

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

  FLUSH TABLES ... WITH READ LOCKFLUSH TABLES ... FOR EXPORTLOCK TABLES ... READLOCK TABLES ... WRITE

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

  FLUSH TABLES WITH READ LOCKFLUSH TABLES ... WITH READ LOCKFLUSH TABLES ... FOR EXPORT