Maxoq
Well-known member
- Joined
- Feb 25, 2015
- Messages
- 520
- Points
- 28
If you are using MySQL version v5.6, you can do changes in your file my.cnf on your VPS to aneb enable Slow Query Log for your MySQL. In order to enable this for MySQL on your VPS or hosting servers, you should do as follows
Open my.cnf
When you opened your my.cnf file, you can add these lines under [mysqld]
Then create the file yourserver.name.slow_queries.log correspond to which you set in my.cnf
Explain:
long_query_time = [value] :
This value will stipulated that if 1 query time-consuming to implement than the time that you specified in this section, the MySQL configuration will log the information related to that query.
slow_query_log = [0/1]
- If the value is "0", the slow query log is off, and "1" this function is activated. The default is to disable this feature.
slow_query_log_file = file_name
- This value specifies the name of the log file will be saved to file, this file will be created in the MySQL data directory unless you give the absolute path to another directory.
log_output = [FILE|TABLES|NONE]
- This configuration is to specify the type of log that we will save the information down, namely 1 file or database table in the MySQL service.
Restart MySQL
Note:
If your hosting system is using MySQL 5.5 or lesser, there is no configuration exists called "slow_query_log" and it has been renamed from "log-slow-queries' to the new. So if you configured on the system, it will use the old MySQL configuration names are:
Sign into MySQL command-line and execute the set of values corresponding GLOBAL configuration as follows:
Check again if the values are set
Open my.cnf
Code:
nano /etc/my.cnf
Code:
[mysqld]
### Slow Query Log Configuration ###
### Only Avaiable on MySQL v5.6 ###
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/yourserver.name.slow_queries.log
long_query_time = 5
log_output = FILEExplain:
long_query_time = [value] :
This value will stipulated that if 1 query time-consuming to implement than the time that you specified in this section, the MySQL configuration will log the information related to that query.
slow_query_log = [0/1]
- If the value is "0", the slow query log is off, and "1" this function is activated. The default is to disable this feature.
slow_query_log_file = file_name
- This value specifies the name of the log file will be saved to file, this file will be created in the MySQL data directory unless you give the absolute path to another directory.
log_output = [FILE|TABLES|NONE]
- This configuration is to specify the type of log that we will save the information down, namely 1 file or database table in the MySQL service.
Restart MySQL
Code:
/etc/init.d/mysql restartIf your hosting system is using MySQL 5.5 or lesser, there is no configuration exists called "slow_query_log" and it has been renamed from "log-slow-queries' to the new. So if you configured on the system, it will use the old MySQL configuration names are:
Code:
log-slow-queries = 1
Code:
# mysql -uroot -p
mysql> SET GLOBAL slow_query_log_file = '/var/lib/mysql/yourserver.name.slow_queries.log';
mysql> SET GLOBAL long_query_time = 1;
mysql> SET GLOBAL slow_query_log = 1;
mysql> FLUSH LOGS;Check again if the values are set
Code:
mysql> SHOW GLOBAL VARIABLES LIKE 'slow\_%';
+---------------------+-----------------------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/yourserver.name.slow_queries.log |
+---------------------+-----------------------------------------------------+
Code:
mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 5.000000 |
+-----------------+-----------+
