Usage of the FLUSH command in MySQL backup

The FLUSH command is often used for MySQL database backup. The following is a summary of the syntax.

FLUSH syntax

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

If you want to clear some internal caches used by MySQL, you should use the FLUSH statement. To perform FLUSH, you must have the RELOAD permission.

Flush_option can be any of the following:

· HOSTS

Used to clear the host cache table. If some hosts change the IP number or if you get the error message Host host_name is blocked, you should refresh the Host table. When connecting to the MySQL server, if an error "more than max_connect_errors" occurs for a given host, MySQL assumes that an error occurs and stops subsequent connection requests from the host. Refresh the host table to allow the host to try again. You can use max_connect_errors = 999999999 to start mysqld to avoid this error message.

· DES_KEY_FILE

It is used to re-load the des keyword from the file specified by -- DES-key-file when the server is started.

· LOGS

Used to close and re-open all log files. If you have specified an Update log file or a binary log file without expansion, the log file extension number increases by 1 compared to the previous file. If you use an extension in the file name, MySQL closes and re-opens the log file. In Unix, this is also true when the mysqld server sends a SIGHUP signal (except for some Mac OS X 10.3 versions. In these versions, mysqld ignores SIGHUP and SIGQUIT ).

If the server uses the -- log-error option, flush logs will rename the error log (with the suffix-old), and mysqld will create a new empty log file. If the -- log-error option is not specified, it will not be renamed.

· PRIVILEGES

Used to re-load permissions from the authorization table in the mysql database.

· QUERY CACHE

Sort query cache fragments to make better use of memory. Unlike the reset query cache, this statement does not cancel any QUERY from the CACHE.

· STATUS

It is used to reset most state variables to zero. You must use this item only when debugging a query.

· {TABLE | TABLES} [tbl_name [, tbl_name]...]

When no table is named, close all open tables and force them to be closed. This will also refresh the query cache. This option contains one or more table names. Only the specified table is refreshed. Like the reset query cache statement, flush tables also cancels all QUERY results from the query cache.

· TABLES WITH READ LOCK

For all databases with read locks, close all open TABLES and lock all TABLES until you execute the unlock tables command. If you have a file system that can take snapshots in a timely manner, such as Veritas, this is a very convenient method for backup.

· USER_RESOURCES

It is used to reset all user resources per hour to zero. This allows clients that have reached the hourly connection, query, or update threshold to immediately restore activity. FLUSH USER_RESOURCES does not apply to the maximum value of simultaneous connections. The FLUSH statement is written to the binary log unless the NO_WRITE_TO_BINLOG keyword (or its alias is LOCAL) is used ). Note: In any case, flush logs, flush master, flush slave, and flush tables with read lock will not be logged, because if they are copied to a SLAVE server, problems may occur.

You can also use the flush-hosts, flush-logs, flush-privileges, flush-status, or flush-tables commands to access statements containing the mysqladmin application.

Note: in MySQL 5.1.2-alpha, FLUSH statements cannot be published in stored functions or triggers. However, you can use FLUSH in stored procedures as long as they are not called from stored functions or triggers.

Recommended reading:

MySQL backup and restoration Parameters

Comparison of backup efficiency between MySQL backup tools mysqldump and mydumper

MySQL Backup Recovery "fault" Summary

Summary of three methods for MySQL backup and recovery