MySQL FLUSH Commands

MySQL launched in 1995, has become the most popular open source database system.MySQL flush command is used to clean up the internal caches used by MySQL and only the root level user can have permissions for a FLUSH command.It is mainly used to clear the host cache tables.

The FLUSH command syntax:

The most common options for FLUSH command are:

1. PRIVILEGES
2. TABLES
3. HOSTS
4. LOGS

1. Flush PRIVILEGES

FLUSH PRIVILEGES command can be used before and after adding new users.This command simply reloads the grant tables in your MySQL database by enabling the changes to take effect without stopping and restarting MySQL.

FLUSH PRIVILEGES command returns the Query OK response means that the cleaning process occurred without a problem.

2. Flush TABLES

The FLUSH TABLES command closes all tables currently open or in use. This operation will clear the query cache content.

When your caches are empty, MySQL can better utilize available memory.

FLUSH TABLES WITH READ LOCK is global for the database table lock.If you want to lock several tables, you can use following syntax:

This is also used to unlock tables.read-lock allows other concurrent read requests but it block a write request which can be read at the same time but does not allow any write and it also known as shared lock.However, write-lock is exclusive lock.

Again, you will get the response like the Query OK response after using FLUSH TABLES command.

3. Flush HOSTS

The FLUSH HOSTS command used specifically with the host cache tables.

If you are unable to connect to your MySQL server, a most common reason is that the maximum number of connections has been reached for a particular host so it’s throwing errors.

When MySQL gets numerous errors on connection, it assumes something is missing and it’s just blocks any additional connection attempts to that particular host. The FLUSH HOSTS command resets this process and again allows to connect to that particular HOST.

4. Flush LOGS

The FLUSH LOGS MySQL command closes and reopens all log files.Sometimes the size of the logfile is too big and to open or reload that files can take much time so if you want to start a new one so FLUSH LOGS command will create a new empty log file.

Here I have explained some basics and most useful FLUSH commands whereas MySQL have total nine FLUSH commands.You would also like to read how to repair MySQL Table.

Flush operations will be logged into the binary log file except FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, FLUSH TABLES WITH READ LOCK commands.If you find this article useful, do share it on Facebook and Google plus.Do you have any query or question, I would like to answer.Write on Comment Section.