JIYIK CN >

Today, we will enable MySQL in MySQL using MySQL shell on Windows and Ubuntu 20.04 slow_query_log. For this tutorial, we are using MySQL version 8.0 and Ubuntu 20.04.

MySQLslow_query_log

MySQL slow_query_logcontains SQL statements that took longer than seconds to run long_query_timeand required at least min_examined_row_limitrows (records) to be examined.

The SQL queries that appear in MySQL slow_query_logare the ones that take a lot of running time. Hence, these are the candidates that need to be optimized.

By default, the slow query log is turned off. Let’s see how to enable it on Windows and Ubuntu 20.04.

Enabling MySQL in Windows/Ubuntuslow_query_log

The query given below can be executed on both Windows and Ubuntu operating systems. We need to first enter the MySQL shell and then execute the following command to enable MySQL slow_query_log.

Sample code:

mysql> SET GLOBAL slow_query_log = 'ON';

Now, run the following query to ensure it is enabled slow_query_log.

Sample code:

mysql> SHOW VARIABLES LIKE '%slow%';

Output:

+-----------------------------+--------------------------+
| Variable_name               | Value                    |
+-----------------------------+--------------------------+
| log_slow_admin_statements   | OFF                      |
| log_slow_extra              | OFF                      |
| log_slow_replica_statements | OFF                      |
| log_slow_slave_statements   | OFF                      |
| slow_launch_time            | 2                        |
| slow_query_log              | ON                       |
| slow_query_log_file         | DESKTOP-QF52OT4-slow.log |
+-----------------------------+--------------------------+
7 rows in set (0.01 sec)

Alternatively, we can also execute the following command to check slow_query_logwhether it is enabled.

Sample code:

mysql> SHOW VARIABLES LIKE '%quer%';

Output:

+----------------------------------------+--------------------------+
| Variable_name                          | Value                    |
+----------------------------------------+--------------------------+
| binlog_rows_query_log_events           | OFF                      |
| ft_query_expansion_limit               | 20                       |
| have_query_cache                       | NO                       |
| log_queries_not_using_indexes          | OFF                      |
| log_throttle_queries_not_using_indexes | 0                        |
| long_query_time                        | 10.000000                |
| query_alloc_block_size                 | 8192                     |
| query_prealloc_size                    | 8192                     |
| slow_query_log                         | ON                       |
| slow_query_log_file                    | DESKTOP-QF52OT4-slow.log |
+----------------------------------------+--------------------------+
10 rows in set (0.00 sec)

See the second to last line in both outputs (shown above). slow_query_logNow it is ON.

Once we enable that slow_query_log, we can also enable other options. For example, we can also update how long a query takes to execute before it is logged.

Sample code:

mysql> SET GLOBAL long_query_time = 20;