Differences

This shows you the differences between two versions of the page.

--- mysql_slow_query [2016/02/01 17:00] – luke7858
+++ mysql_slow_query [2024/05/23 07:26] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 === Slow Query Logs ===
+Checking the slow query logs via mysql prompt.
+\\
+\\Note: You CAN enable the slow query logging on runtime. A mysql restart will flush the changes made via runtime. If you wish for persistence then add it to the /etc/my.cnf file:
+<sxh bash>
+mysql> SHOW GLOBAL VARIABLES LIKE 'slow\_%';
++---------------------+-------------------------------+
+| Variable_name       | Value                         |
++---------------------+-------------------------------+
+| slow_query_log      | ON                            |
+| slow_query_log_file | /path/to/slow_query.log       |
++---------------------+-------------------------------+
+</sxh>
+Set the logs to another path:
+<sxh bash>
+mysql> SET GLOBAL slow_query_log_file = '/path/to/slow_query.log';
+</sxh>
+<sxh bash>
+mysql> SET GLOBAL slow_query_log = 'ON';
+</sxh>
+\\
+Via the command line:
+\\
+\\
 You can check to see if slow query logging is currently enabled. The following command assumes you have configured .my.cnf file:
 <sxh bash>
-show full variables like "%slow%";
+show variables like "%slow%";
 </sxh>
 Or you can run the following from the command line:
@@ Line 43: / Line 66: @@
 === Configuring Slow Query logs ===
 \\
-There are now 2 options for configuring slow query logging. You can add the settings in the .my.cnf file or you can set the variables through the mysql command line.
+There are 2 options for configuring slow query logs:
-===Option 1===
+\\
+\\
+-** Run time** - this will configure the slow query logs in the current runtime environment, when mysql is restarted the value will be lost. You will need to configure the value in the /etc/my.cnf for variables to remain
+\\
+\\
+- **Permanent** - editing the /etc/my.cnf will allow mysql to retain the values once the service has been restarted. Changing this file will NOT change the current runtime environment.
+\\
+\\
+**__NOTE__**: To enable to variables without restarting the service, use a combination of both techniques above.
+\\
+\\
+=== Run-time Configuration ===
 \\ If run the following command it will set slow query logging without needing to restart mysql
 <sxh bash>
@@ Line 53: / Line 87: @@
 mysql -e 'show variables;' | grep slow
 </sxh>
+\\
-===Option 2 - .my.cnf ===
+=== /etc/my.cnf - Permanent Configuration ===
 <sxh bash>
 slow_query_log = 1
@@ Line 60: / Line 95: @@
 long_query_time = 10
 log_queries_not_using_indexes = 1
-Change permissions so that mysqld can write to the specified log file. Giving write permissions to the 'other' group should suffice.
 </sxh>
+**Note**: Change permissions so that mysqld can write to the specified log file. Giving write permissions to the 'other' group should suffice.