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

How to: CentOS Virtualmin MySQL Slow Query Log Setup and View
Tagged on:                                                                 

Leave a Reply

Your email address will not be published. Required fields are marked *