MySQL Flush Log

Introduction to MySQL Flush Log

Flush log is used to flush the individual logs like Binary logs, general logs and error logs etc. There is a command-line interface provided by the mysql admin utility for flush operations, using commands such as flush-logs, flush-status, flush-hosts, flush-privileges and flush-tables.

To execute the log flushing statements or commands we need to connect to the server with the account which has “RELOAD” Privilege.

All in One Data Science Bundle(360+ Courses, 50+ projects)

Price
View Courses

360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,768 ratings)

Flushing the binary log creates a new binary log file. Whereas flushing the general query log closes and reopens the log file. The same goes with the slow query log and error log, it just closes and reopens the log file.

Syntax

Below is the syntax for the flush: –

FLUSH [NO_WRITE_TO_BINLOG | LOCAL] {
[flush_option] [, flush_option] ...
| [tables_option] }

Below syntax for flush tables: –

FLUSH TABLEStbl_name;
FLUSH TABLES tbl_name [, tbl_name], TABLES WITH READ LOCK;

Here the flush_option and tables_options are as below:

• flush_option: BINARY LOGS, DES_KEY_FILE, ENGINE LOGS, ERROR LOGS, GENERAL LOGS, HOSTS, SLOW LOGS, STATUS, LOGS, PRIVILEGES, QUERY CACHE etc.,
• tables_options: tables_option: TABLES, Multiple tables: – TABLES tbl_name [, tbl_name], TABLES WITH READ LOCK, Multiple tables: – TABLES tbl_name [, tbl_name] … WITH READ LOCK, TABLES tbl_name [, tbl_name] … FOR EXPORT

How does MySQL Flush log file works?

The flush statement works as below:

Before flushing the logs, it is always a good practise to take backup of the log files in some directory.

1. Flush Binary logs

It is used to close and reopen the binary log files where the server is writing.

Code:

FLUSH BINARY LOGS;

Output:

2. Flush General logs

It is used to close and reopen the general log files where the server is writing.

Code:

FLUSH GENERAL LOGS;

Output:

3. Flush Error logs

It is used to close and reopen the error log files where the server is writing.

Code:

FLUSH ERROR LOGS;

Output:

4. Flush Engine logs

It is used to close and reopen the storage engine log files.

Code:

FLUSH ENGINE LOGS;

Output:

5. Flush DES_KEY_FILE

At the server startup time this option is used to load the DES keys from the “DESC_KEY_FILE”.

6. Flush Hosts

It is used to flush all the information from host cache.

Code:

mysqladmin -u root -p flush-hosts /* -- to flush all host information --*/

Output:

Or

Workbench level Syntax: –

FLUSH HOSTS;

Output:

7. Flush Logs

It is used to flush all information logs.

Code:

mysqladmin -u root -p flush-logs /* -- to flush all logs --*/

Output:

Or

Code – Workbench level

FLUSH LOGS;

Output:

8. Flush Privilege

It is used to reload the grant tables.

Code:

mysqladmin -u root -p flush-privileges /* -- to reload all grant tables --*/

Output:

Or

Code – Workbench level

FLUSH PRIVILEGES;

Output:

9. Flush threads

It is used to flush all the thread cache.

Code:

mysqladmin -u root -p flush-threads /* -- to flush all threads --*/

Output:

10. Flush Query cache

It is used to defragment the query cache.

11. Flush STATUS

It is used to clear the status variables.

Code:

mysqladmin -u root -p flush-status /* -- to clear all status variables --*/

Output:

Or

Code – Workbench level

FLUSH STATUS;

Output:

12. Flush Relay logs

It is used to close and reopen the relay log files,where the server is writing.

Code:

FLUSH RELAY LOGS;

Output:

13. Flush Slow logs

It is used to close and reopen the slow log files,where the server is writing

Code:

FLUSH SLOW LOGS;

Output:

14. Flush Tables

It is used to flush all the tables based on the lock condition.

Code:

mysqladmin -u root -p flush-tables /* -- to flush all tables --*/

Output:

Code:

FLUSH TABLE TBL_NAME;
EG: -
FLUSH TABLE TEST;

Output:

Example

Now let us check the flush option by using the below statement: –

mysqladmin -help;

Output for flush would be as below:

• flush-hosts     It is used to Flush all cached hosts
• flush-logs       It is used to Flush all logs
• flush-status   It is used to Clear status variables
• flush-tables   It is used to Flush all tables
• flush-threads    It is used to Flush the thread cache
• flush-privileges    It is used to Reload grant tables

Let us see the screenshot of the output of the command “mysqladmin -help”. Below is the screenshot that represents the same. There are many help option other than the flush that can be seen in the below screenshot.

Output:

Conclusion

• Flush log is used to flush the individual logs like Binary logs, general logs and error logs etc. The flush can be done by using the “mysqladmin” for the hosts, logs, status, tables, threads and privileges.
• To execute the log flushing statements or commands we need to connect to the server with the account which has “RELOAD” Privilege.
• Flushing the binary log creates a new binary log file. Whereas flushing the general query log closes and reopens the log file. The same goes with the slow query log and error log, it just closes and reopens the log file.
• We can’t use the Flush statements in the store functions or triggers. We may use it in stored procedure. This procedure in turn should not be called by the functions or triggers.

Recommended Articles

This is a guide to MySQL Flush Log. Here we discuss an introduction, syntax, how does it work with different examples. You can also go through our other related articles to learn more –

1. MySQL SYSDATE()
2. Cursor in MySQL
3. LIKE in MySQL
4. IF Statement in MySQL