How to Flush a Slow Query Log in MySQL
When it's started with the --log-slow-queries option, the MySQL slow query log stores a list of all SQL statements that took longer than a preset time to execute. This allows you to see any SQL queries running that may be heavily loading the server, and could benefit from optimization. In the event of a problem on a busy server, log files can quickly grow in size. Flushing the log files stops the logs becoming too large, allowing you to keep the file sizes manageable for further analysis.
Other People Are Reading
Instructions
-
-
1
Connect to a remote MySQL server through SSH, or open a terminal window if you are located with the physical server.
-
2
Type “cd mysql-data-directory” at the command prompt, replacing mysql-data-directory with the path to the location of the mysql data files on your server. By default, the data directory is located in /var/lib/mysql .
-
-
3
Rename the current log files if you want to keep a copy for analysis, by typing the following at the command line.
mv mysql.log mysql.old
mv mysql-slow.log mysql-slow.old -
4
Flush the log files by typing at the command prompt:
mysqladmin flush-logs
MySQL closes any existing log files, and then reopens them as new files with the same names.
-
1
Tips & Warnings
Find the location of the MySQL data directory on your server by typing “show variables like ‘datadir’” at the MySQL command prompt. MySQL will display the location of the data directory on your server.
Change the threshold in seconds for entry into the slow query log by altering the value of the “long_query_time” system variable. The default minimum execution time before a query is logged is 10 seconds.
References
Resources
- Photo Credit Thinkstock Images/Comstock/Getty Images
More Like This
Comments
-
In CentOS and Plesk, mysqld.log and mysql_slow_queries.log files are located in /var/log/ directory so mysqladmin flush-logs command don't work. My mysql_slow_queries.log file is 4GB large. How can I flush it?
