Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual FLUSH Syntax

    flush_option [, flush_option] ...

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

Sending a SIGHUP signal to the server causes several flush operations to occur that are similar to various forms of the FLUSH statement. See Section 5.1.11, “Server Response to Signals”.

The FLUSH statement causes an implicit commit. See Section 13.3.3, “Statements That Cause an Implicit Commit”.

The RESET statement is similar to FLUSH. See Section, “RESET Syntax”, for information about using the RESET statement with replication.

flush_option can be any of the following items.


    Reloads the DES keys from the file that was specified with the --des-key-file option 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 'host_name' is blocked occurs. (See Section B.5.2.6, “Host 'host_name' is blocked”.) When more than max_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 cache enables further connection attempts from the host. The default value of max_connect_errors is 10. To avoid this error message, start the server with max_connect_errors set to a large value.

  • [log_type] LOGS

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

    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.4.2, “The Error Log”.

    FLUSH LOGS has 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”).

    With a log_type option, only the specified log type is flushed. These log_type options are permitted:

    • BINARY closes and reopens the binary log files.

    • ENGINE closes and reopens any flushable logs for installed storage engines. This causes InnoDB to flush its logs to disk.

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


    Deletes all binary logs, resets the binary log index file and creates a new binary log. FLUSH MASTER is deprecated in favor of RESET MASTER. FLUSH MASTER is still accepted in MySQL 5.5 for backward compatibility, but is removed in MySQL 5.6. See Section, “RESET MASTER Syntax”.


    Reloads the privileges from the grant tables in the mysql database.

    The server caches information in memory as a result of GRANT, CREATE USER, CREATE SERVER, and INSTALL PLUGIN statements. This memory is not released by the corresponding REVOKE, DROP USER, DROP SERVER, and UNINSTALL 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 with FLUSH PRIVILEGES.


    Defragment the query cache to better utilize its memory. FLUSH QUERY CACHE does not remove any queries from the cache, unlike FLUSH TABLES or RESET QUERY CACHE.


    Resets all replication slave parameters, including relay log files and replication position in the master's binary logs. FLUSH SLAVE is deprecated in favor of RESET SLAVE. FLUSH SLAVE is still accepted in MySQL 5.5 for backward compatibility, but is removed in MySQL 5.6. See Section, “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_connections to 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 TABLES flushes 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_RESOURCES does 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 such as flush-hosts, flush-logs, flush-privileges, flush-status, and flush-tables. See 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”.


FLUSH TABLES 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. FLUSH TABLE is a synonym for FLUSH TABLES, except that 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 TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.

    As of MySQL 5.5.3, 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.

  • 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. No error occurs if a named table does not exist.


    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.

    FLUSH TABLES WITH READ LOCK acquires a global read lock and not 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:

    FLUSH TABLES WITH READ LOCK does 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 LOCK is not compatible with XA transactions.

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

    This variant of FLUSH is available as of MySQL 5.5.3.

User Comments
  Posted by Simon Mudd on May 30, 2011
Please note: while the documentation does not explicitly say this for MYISAM tables FLUSH TABLES does write data out for example if you have delay_key_write set to ON (default) / ALL any dirty buffer pages are written out.

So many people use this command to FLUSH DATA.

However, for InnoDB tables if you expect FLUSH TABLE xx to do something you are in for a shock, this does not happen. It appears that even the .bfd files are not closed (if using file_per_table) so it would be useful if the documentation for this command made some reference to exceptions and that with some engines the behaviour may not be what is expected.
  Posted by Bas Vijfwinkel on November 24, 2011
For those working in PHP with high volume sites or HPC/HFT systems : using persistent connections might sometimes lead to the mysql server getting unreachable.
Biggest oddity is that it will not occur on all servers in your system/cloud at once but only on a
number of servers. Some servers seem to be able to reuse their persistent connections pretty well while other servers will be creating multiple new connections (without discarding the used ones).
Simple solutions like doubling the amount of allowed connections does not work, it will only delay the moment until it will happen again.
You can either refrain from using persistent connections or flush all hosts when you notice that you can't reach the database anymore.

The error that you will notice is the one below :
DB connection error: Host 'your.mysql-server.url' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
Also the number of connections to the mysql server will be big (netstat -a | grep mysqld) while just a few of these connections appear to be active. You might want to flush your hosts in advance in such situations.

  Posted by Matthias Siebler on June 10, 2013
A client hostname has changed from 'oldclient' to 'newclient'. IP remained the same.
New user account user1@newclient with same grants and password is created.
Try to connect from newclient: mysql -u user1 -p
The server might reject connections with "ERROR 1045 (28000): Access denied for user 'user1'@'oldclient' ...".
Connection from other clients, that have not changed hostnames, still works. New Account with IP instead of hostname works too.

The goal is to flush hosts!

Sign Up Login You must be logged in to post a comment.