MySQL in a Nutshell, 2nd Edition

Name

FLUSH

Synopsis

FLUSH [LOCAL|NO_WRITE_TO_BINLOG] option[, ...]

Options:

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

Use this statement to clear and reload temporary caches in MySQL. It requires RELOAD privileges. To prevent this statement from writing to the binary log file, the NO_WRITE_TO_BINLOG flag or its LOCAL alias may be given. A particular cache to flush may be given as an option. Multiple options (see Table 4-1) may be given in a comma-separated list.

As of version 5.1 of MySQL, FLUSH cannot be used in stored functions and triggers, but can be used in stored procedures. As an alternative to the FLUSH statement, you can use the mysqladmin command (see Chapter 16).

Table 4-1. Options for FLUSH statement

Option	Explanation
`DES_KEY_FILE`	Reloads the DES encryption file, which is given with the `--des-key-file` option at startup or in the options 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`	Used to close all of the log files and reopen them. If the server has binary logging enabled, it will change the binary log file to the next in numeric sequence. If the error log was enabled, it will rename the error log to the same name, but with the ending `-old`, and start a new error log. This option is not logged.
`MASTER`	This option is not logged and has been deprecated. Use the `RESET MASTER` statement instead.
`PRIVILEGES`	Reloads the grant tables for user privileges. This is necessary if the `user` table in the `mysql` database has been modified manually, without the `GRANT` statement.
`QUERY CACHE`	Instructs the server to defragment the query cache to improve performance. It doesn’t remove queries from cache, though. Use the `RESET QUERY CACHE` statement to remove the queries.
`SLAVE`	This option is not logged and has been deprecated. Use the `RESET SLAVE` statement instead.
`STATUS`	Resets the session values and counters for key caches to 0. The current thread’s session status variables are set to those of the global variables. The `max_used_conections` variable is set to the number of sessions open at the time.
`TABLE[ table, ...]`	Followed by one or more table names, this option forces the given tables to be closed. This will terminate any active queries on the given tables. Specified without any tables, the option has the same effect as `TABLES`.
`TABLES`	Causes all tables to be closed, all queries to be terminated, and the query cache to be flushed. This is the same as `TABLE` with no table name.
`TABLES WITH READ LOCK`	Closes all tables and locks them with a global read lock. This will allow users to view the data, but not to update it or insert records. The lock will remain in place until the `UNLOCK TABLES` statement is executed. This option is not logged.
`USER_RESOURCES`	Resets all user resource values that are calculated on an hourly basis. These are the values for the columns `max_questions`, `max_updates`, and `max_connections` in the `user` table of the `mysql` database. Use this `FLUSH` option when users have been blocked because they exceed hourly limits. If these columns are missing, see Chapter 16 for the explanation of `mysql_fix_privilege_tables`.

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

Table of Contents for
MySQL in a Nutshell, 2nd Edition