How to setup Slow Query Logging in MySQL in Virtualmin on CentOS.
Steps:
1. Setup Slow Query logging for MySQL
2. Setup MySQL slow Query log viewing in Virtualmin
1. Setup Slow Query logging for MySQL
Log in to your server via SSH and issue:
vim /etc/my.cnf
Move to the bottom of the file and add:
##Slow Query Log long_query_time=1 log-slow-queries=/var/log/mysql/slow-queries.log
You must create the file manually and change ownership on in by issuing these commands:
mkdir /var/log/mysql
touch /var/log/mysql/slow-queries.log
chown mysql:mysql -R /var/log/mysql
Then you need to restart the MySQL server and check the MySQL log to make sure there were no errors.
If you get this error:
[ERROR] Could not use /var/log/mysql/slow-queries.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
Make sure that you have created the file and changed the owner as shown!
If you get this error:
[Warning] ‘–log_slow_queries’ is deprecated and will be removed in a future release. Please use ”–slow_query_log’/’–slow_query_log_file” instead.
change the part in /etc/my.cnf to this:
##Slow Query Log long_query_time=1 slow_query_log=/var/log/mysql/slow-queries.log
Slow Query logging should now be running. We can now go ahead and setup the log in Virualmin to view it.
2. Setup MySQL slow Query log viewing in Virtualmin
– Login to Virtualmin
– browse to Webmin >> System >> System Logs
– Click on >> Add a new system log
– Add the location to the log: Log to – File – /var/log/mysql/slow-queries.log
– Set Facilites to “all”
– Click Save
Now you can enjoy viewing your Slow Query log in Virualmin