MySQL under the flush command MySQL log polling

MySQL logs can be set up via MySQL's my.cnf file. However, if the log file is not cleaned or recycled for a long time, it will cause the log file to become bigger and larger, thus affecting the performance of MySQL. And the use of the RPM package installation of the MySQL log from the polling refresh, the same can be done through the source package installed to achieve this function. The implementation of this feature is accomplished through flush commands and MySQL script modifications. (except, of course, binary log files, since the system is set with the Expire_logs_days and max-binlog-size parameters, and the binaries are important files for system data recovery, this method is not recommended for configuration. If there are other backup policies you can also use this method for polling)

The use of the Flush command is as follows:

mysql> help flush;

Name: ' FLUSH '

Description:

Syntax:

FLUSH [No_write_to_binlog | Local]

Flush_option [, Flush_option] ...

............

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

Information about using the RESET statement with replication.

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

From the English help document above, you can view MySQL's flush command and log-related usage.

BINARY closes and reopens the BINARY log files.
ENGINE closes and reopens any flushable logs for installed storage. Currently, this is causes InnoDB to flush it 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.

On the other side of the use of flush, I do not want to directly translate, directly to get someone on the Internet ready! As follows:

MySQL's FLUSH syntax (erase or reload the internal cache) FLUSH Flush_option [, flush_option], if you want to clear some MySQL using the internal cache, you should use the FLUSH command. In order to perform flush, you must have reload privileges.
Flush_option can be any of the following things:

HOSTS This use most, often met. It is mainly used to flush the host cache table. If some of your hosts change IP numbers, or if you get the error message host ... isblocked, you should clear the host table. When the MySQL server is connected to a given host more than max_connect_errors error occurs continuously, MySQL for security needs will block the host further connection requests. Emptying the host table allows the host to try the connection again.

LOGS closes the current binary log file and creates a new file, with the name of the new binary log file added 1 to the current binary file number.

Privileges This is also often used, whenever the right after the weight, in order to just in case, let the new authority take effect immediately, generally do one, the target is from the Database authorization table reload permissions to the cache.

Tables closes all open tables, and the operation clears the contents of the query cache.

FLUSH tables with read lock closes all open tables and adds a read lock to all tables in the database until the unlock tables are displayed, which is often used for data backup.

The status resets most state variables to 0.

MASTER deletes the binary log files in all binary log index files, resets the index file for the binary log file to empty, and creates a new binary log file, but this is deprecated and changed to reset Master. Can imagine, before oneself is many dirt, originally a simple command can be done, but better several commands, the previous practice is to first find out the current binary log file name, and then use purge operation.

Query cache to restructure queries, eliminate fragmentation, improve performance, but does not affect existing data in the query cache, which is not the same as flush table and reset query cache, which will empty query caching.

SLAVE is similar to resetting replication, let the database forget the copy location of the main database, but also delete the downloaded relay log, as with Master, has not recommended use, changed to reset SLAVE. This is also very useful.

Generally, FLUSH operations are recorded in binary log files, but FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, FLUSH TABLES with READ lock are not logged, so if the above actions are recorded in the binary log file, will have an impact on the database. Note: The reset operation actually acts as an enhanced version of the flush operation.

MySQL's own log polling script is located in the Support-files directory of the source package--mysql-log-rotate.sh files.

The action is as follows (query log for example):

First the Query_log.log file mv

[Root@localhost logs]# LL

Total 32

-RW-RW----1 MySQL mysql 1 14:28 mysql.err

-RW-RW----1 MySQL mysql 6 Mar 5 15:44 mysql.pid

-RW-RW----1 MySQL mysql 1015 15:32 query_log.log

-RW-RW----1 mysql mysql 704 Mar 5 15:44 Slow.log

[Root@localhost logs]# MV Query_log.log query_log.log.1

Login to the MySQL command interface, perform the following actions

Mysql> Flush general logs;

Query OK, 0 rows affected (0.06 sec)

Note: This step-by-step key, but also easy to mistake, I had to do flush general operation always error, and then read the official documents, only to find that behind the logs.

Next look at the log file

[Root@localhost logs]# LL

Total 40

-RW-RW----1 MySQL mysql 1 14:28 mysql.err

-RW-RW----1 MySQL mysql 6 Mar 5 15:44 mysql.pid

-RW-RW----1 MySQL mysql 176 15:38 query_log.log

-RW-RW----1 MySQL mysql 1285 15:38 query_log.log.1

-RW-RW----1 mysql mysql 704 Mar 5 15:44 Slow.log

[Root@localhost logs]# Cat Query_log.log

/usr/local/mysql/bin/mysqld, Version:5.5.21-log (Source distribution). Started with:

TCP port:3306 Unix Socket:/tmp/mysqld.sock

Time Id Command Argument

Note points about using polling scripts:

/data/mysql/mysqld.log {

# create MySQL MySQL

Notifempty

Daily

Rotate 3

Missingok

Compress

Postrotate

# just if Mysqld is really running

If Test-x/usr/local/mysql/bin/mysqladmin &&

/usr/local/mysql/bin/mysqladmin Ping &>/dev/null

Then

/usr/local/mysql/bin/mysqladmin Flush-logs

Fi

Endscript

}

The two in the script above will have an error when setting the password LIFO because it uses the direct holder of the password. The specific:

/usr/local/mysql/bin/mysqladmin–uroot-ptest Ping (where user name is root and password is test)

/USR/LOCAL/MYSQL/BIN/MYSQL-UROOT-PTEST-E "Flush General logs"

After the setup is complete, put the script in the/ETC/LOGROTATE.D directory.