FLUSH [NO_WRITE_TO_BINLOG | LOCAL]
FLUSH statement has several
variant forms that clear or reload various internal caches,
flush tables, or acquire locks. To execute
FLUSH, you must have the
RELOAD privilege. Specific flush
options might require additional privileges, as described later.
By default, the server writes
FLUSH statements to the binary
log so that they replicate to replication slaves. To suppress
logging, specify the optional
NO_WRITE_TO_BINLOG keyword or its alias
FLUSH TABLES WITH
READ LOCK (with or without a table list) are not
written to the binary log in any case because they would cause
problems if replicated to a slave.
FLUSH statement causes an
implicit commit. See Section 13.3.3, “Statements That Cause an Implicit Commit”.
flush_option can be any of the
Reloads the DES keys from the file that was specified with the
--des-key-fileoption at server startup time.
Empties the host cache. You should flush the host cache if some of your hosts change IP address or if the error message
Host 'occurs. (See Section B.5.2.6, “Host 'host_name' is blocked”.) When more than
host_name' is blocked
max_connect_errorserrors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host cache enables further connection attempts from the host. The default value of
max_connect_errorsis 10. To avoid this error message, start the server with
max_connect_errorsset to a large value.
FLUSH LOGScloses and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file.
Prior to MySQL 5.5.7, if you flush the logs using
FLUSH LOGSand mysqld is writing the error log to a file (for example, if it was started with the
--log-erroroption), log file renaming may occur, as described in Section 5.4.2, “The Error Log”.
FLUSH LOGShas no effect on tables used for the general query log or for the slow query log (see Section 5.4.1, “Selecting General Query and Slow Query Log Output Destinations”).
log_typeoption, only the specified log type is flushed. These
log_typeoptions are permitted:
BINARYcloses and reopens the binary log files.
ENGINEcloses and reopens any flushable logs for installed storage engines. This causes
InnoDBto flush its logs to disk.
ERRORcloses and reopens the error log file.
GENERALcloses and reopens the general query log file.
RELAYcloses and reopens the relay log files.
SLOWcloses and reopens the slow query log file.
log_typeoptions were added in MySQL 5.5.3.
Deletes all binary logs, resets the binary log index file and creates a new binary log.
FLUSH MASTERis deprecated in favor of
FLUSH MASTERis still accepted in MySQL 5.5 for backward compatibility, but is removed in MySQL 5.6. See Section 220.127.116.11, “RESET MASTER Syntax”.
Reloads the privileges from the grant tables in the
The server caches information in memory as a result of
CREATE SERVER, and
INSTALL PLUGINstatements. This memory is not released by the corresponding
DROP SERVER, and
UNINSTALL PLUGINstatements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with
Resets all replication slave parameters, including relay log files and replication position in the master's binary logs.
FLUSH SLAVEis deprecated in favor of
FLUSH SLAVEis still accepted in MySQL 5.5 for backward compatibility, but is removed in MySQL 5.6. See Section 18.104.22.168, “RESET SLAVE Syntax”.
This option 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 sets
Max_used_connectionsto the current number of open connections. This is something that may be of use when debugging a query. See Section 1.6, “How to Report Bugs or Problems”.
FLUSH TABLESflushes tables, and, depending on the variant used, acquires locks. The permitted syntax is discussed later in this section.
Resets all per-hour user resources to zero. This enables clients that have reached their hourly connection, query, or update limits to resume activity immediately.
FLUSH USER_RESOURCESdoes not apply to the limit on maximum simultaneous connections. See Section 6.3.4, “Setting Account Resource Limits”.
The mysqladmin utility provides a
command-line interface to some flush operations, using commands
Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”.
It is not possible to issue
FLUSH statements within stored
functions or triggers. However, you may use
FLUSH in stored procedures, so
long as these are not called from stored functions or
triggers. See Section C.1, “Restrictions on Stored Programs”.
has several forms, described following. As of MySQL 5.5.3, if
any variant of the
TABLES option is used in a
FLUSH statement, it must be the
only option used.
TABLE is a synonym for
TABLE does not work with the
WITH READ LOCK variants prior to MySQL 5.5.3.
Closes all open tables, forces all tables in use to be closed, and flushes the query cache.
FLUSH TABLESalso removes all query results from the query cache, like the
RESET QUERY CACHEstatement.
With a list of one or more comma-separated table names, this statement is like
FLUSH TABLESwith no names except that the server flushes only the named tables. No error occurs if a named table does not exist.
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 TABLESto release the lock.
FLUSH TABLES WITH READ LOCKacquires a global read lock and not table locks, so it is not subject to the same behavior as
UNLOCK TABLESwith respect to table locking and implicit commits:
UNLOCK TABLESimplicitly commits any active transaction only if any tables currently have been locked with
LOCK TABLES. The commit does not occur for
FLUSH TABLES WITH READ LOCKbecause the latter statement does not acquire table locks.
Beginning a transaction causes table locks acquired with
LOCK TABLESto 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 LOCKdoes not prevent the server from inserting rows into the log tables (see Section 5.4.1, “Selecting General Query and Slow Query Log Output Destinations”).
FLUSH TABLES WITH READ LOCKis not compatible with XA transactions.
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.
This statement applies only to existing base tables. If a name refers to a base table, that table is used. If it refers to a
TEMPORARYtable, it is ignored. If a name applies to a view, an
ER_WRONG_OBJECTerror occurs. Otherwise, an
This variant of
FLUSHenables tables to be flushed and locked in a single operation. It provides a workaround for the restriction as of MySQL 5.5.3 that
FLUSH TABLESis 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 TABLESor 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.
This variant of
FLUSHis available as of MySQL 5.5.3.