EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Flush Log

MySQL Flush Log

By Roja MetlaRoja Metla

MySQL Flush Log

Introduction to MySQL Flush Log

Flush log is used to flush individual logs like Binary, general, 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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 for the slow query log and error log; it just closes and reopens the log file.

Note: We can’t use the Flush statements in the store functions or triggers. We may use it in stored procedures. The functions or triggers should not call this procedure in turn.

Syntax

Below is the syntax for the flush: –

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

Below is the 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 work?

The flush statement works as below:

Before flushing the logs, it is always a good practice to back up 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:

MySQL Flush Log - 1

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:

MySQL Flush Log - 2

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:

MySQL Flush Log - 3

4. Flush Engine logs

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

Code:

FLUSH ENGINE LOGS;

Output:

MySQL Flush Log - 4

5. Flush DES_KEY_FILE

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

6. Flush Hosts

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

Code:

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

Output:

DES_KEY_FILE

Or

Workbench level Syntax: –

FLUSH HOSTS;

Output:

MySQL Flush Log - 6

7. Flush Logs

It is used to flush all information logs.

Code:

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

Output:

Logs

Or

Code – Workbench level

FLUSH LOGS;

Output:

MySQL Flush Log - 8

8. Flush Privilege

It is used to reload the grant tables.

Code:

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

Output:

Privilege

Or

Code – Workbench level

FLUSH PRIVILEGES;

Output:

MySQL Flush Log - 10

9. Flush threads

It is used to flush all the thread cache.

Code:

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

Output:

threads

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:

STATUS

Or

Code – Workbench level

FLUSH STATUS;

Output:

MySQL Flush Log - 13

12. Flush Relay logs

You can use it to close and reopen the relay log files that the server is writing to.

Code:

FLUSH RELAY LOGS;

Output:

MySQL Flush Log - 14

13. Flush Slow logs

You can use it to close and reopen the slow log files that the server is writing to.

Code:

FLUSH SLOW LOGS;

Output:

MySQL Flush Log - 15

14. Flush Tables

You can use it to flush all the tables based on the lock condition.

Code:

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

Output:

MySQL Flush Log - 16

Code:

FLUSH TABLE TBL_NAME;
EG: -
FLUSH TABLE TEST;

Output:

MySQL Flush Log - 17

Example

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

mysqladmin -help;

Output for flush would be as below:

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

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

Output:

MySQL Flush Log - 18

Conclusion

  • The Flush log command flushes individual logs such as Binary, General, Error logs, etc. You can perform the flush operation using “mysqladmin” for 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 for 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 procedures. The functions or triggers should not call this procedure in turn.

Recommended Articles

We hope that this EDUCBA information on “MySQL Flush Log” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL SYSDATE()
  2. Cursor in MySQL
  3. LIKE in MySQL
  4. IF Statement in MySQL
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more