Table of Contents for

MySQL in a Nutshell, 2nd Edition

Options:

DES_KEY_FILE, HOSTS, LOGS, PRIVILEGES, QUERY_CACHE,
STATUS, TABLE, TABLES, TABLES WITH READ LOCK, USER_RESOURCES

Use this statement to clear temporary caches in MySQL. It requires RELOAD privileges. Multiple options may be given in a comma-separated list.

To prevent this statement from writing to the binary log file, include the NO_WRITE_TO_BINLOG keyword or its alias, LOCAL. The DES_KEY_FILE option reloads the DES encryption file. HOSTS clears the hosts cache, which is used to minimize host/IP address lookups. The hosts cache may need to be flushed if a host has been blocked from accessing the server. LOGS is used to close all of the log files and reopen them. The PRIVILEGES option reloads the grant table for users. This is necessary if the user table in the mysql database is modified manually, without a GRANT statement. QUERY CACHE instructs the server to defragment the query cache. The STATUS option resets the status variables that report information about the caches.

The TABLE option, followed by one or more table names, forces the given tables to be closed. This will terminate any active queries on the given tables. The TABLES option, without any table names listed, causes all tables to be closed, all queries to be terminated, and the query cache to be flushed. This option is actually the same as TABLE with no table name.

Use the TABLES WITH READ LOCK option to close all tables and lock them with a global read lock. This should be considered when dealing with transactional tables and implicit commits of changes. This option will allow users to view the data, but not to update it or to insert records. The lock will remain in place until the UNLOCK TABLES statement is executed.

USER_RESOURCES resets all user resources. You can use this when users have been locked out due to exceeding usage limits.

The mysqladmin utility may be used to execute this statement with several of its options. See Chapter 16 for information on this utility.

Two options for this statement have been deprecated: MASTER and SLAVE. RESET MASTER and RESET SLAVE should be used instead.

As of version 5.1 of MySQL, the FLUSH statement cannot be called by a stored function or a trigger, although it can be included in a stored procedure.