Today we will discuss on monitoring slow MySQL queries logging that is the important point comes under database design.
Here are the following steps
First of all, we need to open the Linux terminal and then log in on the MySQL prompt using the following command:
$ mysql -hlocalhost -uroot -p
After hit Enter, It will ask for the root user password.
After entering the correct password it will open mysql prompt like this
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 622
Server version: 5.5.53-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Now we will enable slow_query_log using the following mysql sql query:
mysql> SET GLOBAL slow_query_log = 'ON';
Now in the next step, we will set the long_query_time.
Note: By default it set for 10 seconds so it means when we enable the slow_query_log then it will log every slow query who is taking more than 10 seconds in execution.
mysql> SET GLOBAL long_query_time = 15;
According to the above global setting, it will log query is taking more than 15 seconds.
Now we will set the log file where all the query logs will store.
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-mysql-query-log.log';
Now all the logs will store in this file those taking more than 15 seconds in execution.
All setting for slow log queries has been done and we need to verify the execution.
We will execute the query whose taking more than 15 seconds in execution.Suppose I have a query
mysql> SELECT sleep(20);
this query will take more than 20 seconds in execution so its logging must be stored in the logging file.
Open the file and check the log file content
# Time: 161222 17:51:05
# User@Host: root[root] @ localhost 
# Query_time: 30.000188 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
If you want to disable the mysql slow query logging then you need to execute the following commands
mysql> SET GLOBAL slow_query_log = 'OFF';
mysql> FLUSH LOGS;
If you want to log those queries not using the indexes then you could also set the following
SET GLOBAL log_queries_not_using_indexes = 'ON';