Skip navigation links
12.4.6.4 KILL Syntax »
Section Navigation      [Toggle]

12.4.6.3. FLUSH Syntax

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.

Note

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 number or if you get the error message 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 tables enables further connection attempts from the host. See Section B.5.2.6, “Host 'host_name' is blocked”. You can start mysqld with --max_connect_errors=999999999 to avoid this error message.

  • [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. On Unix, this is the same thing as sending a SIGHUP signal to the mysqld server (except on some Mac OS X 10.3 versions where mysqld ignores SIGHUP and SIGQUIT).

    If the server is writing error output to a named file (for example, if it was started with the --log-error option), FLUSH LOGS causes it to rename the current error log file with a suffix of -old and create a new empty log file. No renaming occurs if the server is not writing to a named file (for example, if it is writing errors to the console).

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

    • BINARY closes and reopens the binary log files.

    • ENGINE closes and reopens any flushable logs for installed storage engines. Currently, this causes InnoDB 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 of RESET 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 a SIGHUP signal.

    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.

  • QUERY CACHE

    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.

  • 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 of RESET 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 the TABLES option is used, 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.

    • 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 the RESET QUERY CACHE statement.

      As of MySQL 5.5.3, FLUSH TABLES is disallowed when there is an active LOCK TABLES ... READ. To flush and lock tables, use FLUSH TABLES tbl_list WITH READ LOCK instead.

    • FLUSH 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 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.2.1, “Selecting General Query and Slow Query Log Output Destinations”).

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

      With 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 the LOCK TABLES privilege in addition to the RELOAD privilege that is required to use any FLUSH 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 that FLUSH TABLES is disallowed when there is an active LOCK 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 a TEMPORARY 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 active LOCK TABLES.

      Use UNLOCK TABLES to release the locks, or LOCK 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, via command such as flush-hosts, flush-logs, flush-privileges, flush-status, and flush-tables.

Note

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 D.1, “Restrictions on Stored Routines, Triggers, and Events”.