FLUSH [NO_WRITE_TO_BINLOG | LOCAL]flush_option
[,flush_option
] ...
The FLUSH
statement clears or
reloads various internal caches used by MySQL. Some variants
acquire locks. To execute FLUSH
,
you must have the RELOAD
privilege. Specific flush options might require additional
privileges, as described later.
By default, FLUSH
statements are
written to the binary log so that they will be replicated to
replication slaves. Logging can be suppressed with the optional
NO_WRITE_TO_BINLOG
keyword or its alias
LOCAL
.
FLUSH LOGS
,
FLUSH MASTER
,
FLUSH SLAVE
,
and 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.
The RESET
statement is similar to
FLUSH
. See
Section 12.4.6.6, “RESET
Syntax”, for information about using the
RESET
statement with replication.
flush_option
can be any of the
following items.
DES_KEY_FILE
Reloads the DES keys from the file that was specified with the
--des-key-file
option at server startup time.HOSTS
Empties the host cache tables. You should flush the host tables if some of your hosts change IP address or if you get the error message
Host '
. When more thanhost_name
' is blockedmax_connect_errors
errors 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 tables enables further connection attempts from the host. See Section C.5.2.6, “Host '
”. You can start mysqld withhost_name
' is blocked--max_connect_errors=999999999
to avoid this error message.[
log_type
] LOGSWith no
log_type
option,FLUSH LOGS
closes 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. On Unix, this is the same thing as sending aSIGHUP
signal to the mysqld server (except on some Mac OS X 10.3 versions where mysqld ignoresSIGHUP
andSIGQUIT
).Prior to MySQL 5.5.7, if you flush the logs using
FLUSH LOGS
and mysqld is writing the error log to a file (for example, if it was started with the--log-error
option), log file renaming may occur, as described in Section 5.2.2, “The Error Log”.With a
log_type
option, only the specified log type is flushed. Theselog_type
options are permitted:BINARY
closes and reopens the binary log files.ENGINE
closes and reopens any flushable logs for installed storage engines. Currently, this causesInnoDB
to flush its logs to disk and perform a checkpoint.ERROR
closes and reopens the error log file.GENERAL
closes and reopens the general query log file.RELAY
closes and reopens the relay log files.SLOW
closes and reopens the slow query log file.
The
log_type
options were added in MySQL 5.5.3.MASTER
Deletes all binary logs, resets the binary log index file and creates a new binary log.
FLUSH MASTER
is deprecated in favor ofRESET MASTER
, and is supported for backward compatibility only. See Section 12.5.1.2, “RESET MASTER
Syntax”.PRIVILEGES
Reloads the privileges from the grant tables in the
mysql
database. On Unix, this also occurs if the server receives aSIGHUP
signal.The server caches information in memory as a result of
GRANT
,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 will be an increase in memory use. This cached memory can be freed withFLUSH PRIVILEGES
.QUERY CACHE
Defragment the query cache to better utilize its memory.
FLUSH QUERY CACHE
does not remove any queries from the cache, unlikeFLUSH TABLES
orRESET QUERY CACHE
.SLAVE
Resets all replication slave parameters, including relay log files and replication position in the master's binary logs.
FLUSH SLAVE
is deprecated in favor ofRESET SLAVE
, and is supported for backward compatibility only. See Section 12.5.2.3, “RESET SLAVE
Syntax”.STATUS
This option adds the current thread's session status variable values to the global values and resets the session values to zero. It also resets the counters for key caches (default and named) to zero and sets
Max_used_connections
to the current number of open connections. This is something you should use only when debugging a query. See Section 1.7, “How to Report Bugs or Problems”.TABLES
FLUSH TABLES
has several variant forms. As of MySQL 5.5.3, if any variant of theTABLES
option is used, it must be the only option used.FLUSH TABLE
is a synonym forFLUSH TABLES
, except thatTABLE
does not work with theWITH READ LOCK
variants prior to MySQL 5.5.3.FLUSH TABLES
Closes all open tables, forces all tables in use to be closed, and flushes the query cache.
FLUSH TABLES
also removes all query results from the query cache, like theRESET QUERY CACHE
statement.As of MySQL 5.5.3,
FLUSH TABLES
is not permitted when there is an activeLOCK TABLES ... READ
. To flush and lock tables, useFLUSH TABLES
instead.tbl_list
WITH READ LOCKFLUSH TABLES
tbl_name
[,tbl_name
] ...With a list of one or more comma-separated table names, this is like
FLUSH TABLES
with 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 until you explicitly release the lock by executing
UNLOCK TABLES
. 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.FLUSH TABLES WITH READ LOCK
acquires a global read lock and not 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 with
LOCK 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 (see Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”).FLUSH TABLES
tbl_name
[,tbl_name
] ... WITH READ LOCKWith a list of one or more comma-separated table names, flushes and acquires read locks for each table. This statement first acquires exclusive metadata locks, 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. Because this statement acquires table locks, you must have theLOCK TABLES
privilege in addition to theRELOAD
privilege that is required to use anyFLUSH
statement.This variant of
FLUSH
enables tables to be flushed and locked in a single operation. It provides a workaround for the restriction as of MySQL 5.5.3 thatFLUSH TABLES
is not permitted when there is an activeLOCK TABLES ... READ
.This statement applies only to existing base tables. If a name refers to a base table, that table is used. If it applies to a view,
ER_WRONG_OBJECT
is returned. If it refers to aTEMPORARY
table, it is ignored. Otherwise,ER_NO_SUCH_TABLE
is returned.This statement begins by acquiring exclusive metadata locks for the named tables, so it waits for transactions that have those tables open to complete. After the statement acquires locks and downgrades the metadata locks, other sessions can read but not modify the tables.
If a flushed table was opened with
HANDLER
, the handler is implicitly flushed and loses its position.This statement does not perform an implicit
UNLOCK TABLES
, so an error results if you use the statement a second time without first releasing the locks acquired or while there is any activeLOCK TABLES
.Use
UNLOCK TABLES
to release the locks, orLOCK TABLES
to release the locks and acquire other locks.This variant of
FLUSH
is available as of MySQL 5.5.3.
USER_RESOURCES
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_RESOURCES
does not apply to the limit on maximum simultaneous connections. See Section 5.5.4, “Setting Account Resource Limits”.
The mysqladmin utility provides a
command-line interface to some flush operations, using commands
such as flush-hosts
,
flush-logs
,
flush-privileges
,
flush-status
, and
flush-tables
.
It is not possible in MySQL 5.5 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 E.1, “Restrictions on Stored Routines, Triggers, and Events”.