September 13 2015

mysql flush

mysql> help flush

Name: ‘FLUSH’

Description:

Syntax:

FLUSH [NO_WRITE_TO_BINLOG | LOCAL]

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.

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

Sending a SIGHUP signal to the server causes several flush operations

to occur that are similar to various forms of the FLUSH statement. See

http://dev.mysql.com/doc/refman/5.5/en/server-signal-response.html.

The FLUSH statement causes an implicit commit. See

http://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html.

The RESET statement is similar to FLUSH. See [HELP RESET], for

information about using the RESET statement with replication.

URL: http://dev.mysql.com/doc/refman/5.5/en/flush.html

 

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. 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.2.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.2.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. Currently, 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.

  • 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 MASTERFLUSH MASTER is still accepted in MySQL 5.5 for backward compatibility, but is removed in MySQL 5.6. See Section 13.4.1.2, “RESET MASTER Syntax”.

  • PRIVILEGES

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

    The server caches information in memory as a result of GRANTCREATE USERCREATE SERVER, and INSTALL PLUGIN statements. This memory is not released by the corresponding REVOKEDROP USERDROP 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 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 SLAVEFLUSH SLAVE is still accepted in MySQL 5.5 for backward compatibility, but is removed in MySQL 5.6. See Section 13.4.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. 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 you should use only when debugging a query. See Section 1.6, “How to Report Bugs or Problems”.

  • TABLES

    FLUSH TABLES flushes tables, and, depending on the variant used, acquires locks. The permitted syntax is discussed later in this section.

  • 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 6.3.4, “Setting Account Resource Limits”.

FLUSH HOSTS       这个用的最多,经常碰见。主要是用来清空主机缓存表。如果你的某些主机改变IP数字,或如果你得到错误消息Host … isblocked,你应该清空主机表。当在连接MySQL服务器时,对一台给定的主机有多于 max_connect_errors个错误连续不断地发生,MySQL为了安全的需要将会阻止该主机进一步的连接请求。清空主机表允许主机再尝试连接。

FLUSH LOGS        关闭当前的二进制日志文件并创建一个新文件,新的二进制日志文件的名字在当前的二进制文件的编号上加1。

FLUSH  PRIVILEGES  这个也是经常使用的,每当重新赋权后,为了以防万一,让新权限立即生效,一般都执行一把,目地是从数据库授权表中重新装载权限到缓存中。

FLUSH  TABLES       关闭所有打开的表,同时该操作将会清空查询缓存中的内容。

FLUSH TABLES WITH READ LOCK   关闭所有打开的表,同时对于所有数据库中的表都加一个读锁,直到显示地执行unlock tables,该操作常常用于数据备份的时候。

FLUSH  STATUS       重置大多数状态变量到0。

FLUSH MASTER        删除所有的二进制日志索引文件中的二进制日志文件,重置二进制日志文件的索引文件为空,创建一个新的二进制日志文件,不过这个已经不推荐使用,改成reset master 了。可以想象,以前自己是多土啊,本来一条简单的命令就可以搞定的,却要好几条命令来,以前的做法是先查出来当前的二进制日志文件名,再用purge 操作。

FLUSH QUERY CACHE   重整查询缓存,消除其中的碎片,提高性能,但是并不影响查询缓存中现有的数据,这点和Flush table 和Reset Query  Cache(将会清空查询缓存的内容)不一样的。

FLUSH SLAVE        类似于重置复制吧,让从数据库忘记主数据库的复制位置,同时也会删除已经下载下来的relay log,与Master一样,已经不推荐使用,改成Reset Slave了。这个也很有用的。

一般来讲,Flush操作都会记录在二进制日志文件中,但是FLUSH LOGS、FLUSH MASTER、FLUSH SLAVE、FLUSH TABLES WITH READ LOCK不会记录,因此上述操作如果记录在二进制日志文件中话,会对从数据库造成影响。注意:Reset操作其实扮演的是一个Flush操作的增强版的角色。

 



Copyright 2019. All rights reserved.

Posted 2015年9月13日 by ucjmh in category "mysql

Leave a Reply

Your email address will not be published. Required fields are marked *