LOG DESTINATION

Selecting General Query and Slow Query Log Output Destinations

  1. Check log_output value (File/csv)
mysql> show variables like '%log_output%' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)

2. Check General Query Log Path and its Value (ON/OFF)

mysql> show variables like '%general_log%';
+------------------+------------------------------+
| Variable_name | Value |
+------------------+------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)


3. Check Slow Query Log Path and its Value (ON/OFF)

 mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)

---------ENABLE slow_query_log-----------------

mysql> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.03 sec)

mysql> show variables like '%slow_query_log%';

+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)

Now Execute any command which will take much time

mysql> select sleep(12) ;
+-----------+
| sleep(12) |
+-----------+
| 0 |
+-----------+
1 row in set (12.01 sec)

Now check /var/lib/mysql/localhost-slow.log 

Now chang log_output to TABLE

mysql> SET GLOBAL log_output = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

Now Execute again any command which will take much time

mysql> select sleep(12) ;
+-----------+
| sleep(12) |
+-----------+
| 0 |
+-----------+
1 row in set (12.01 sec)

Now check mysql.slow_log table (pre defined) to check logged

mysql> select * from mysql.slow_log\G;
 *************************** 1. row ***************************
 start_time: 2018-04-15 15:29:39
 user_host: root[root] @ localhost []
 query_time: 00:00:12
 lock_time: 00:00:00
 rows_sent: 1
 rows_examined: 0
 db:
 last_insert_id: 0
 insert_id: 0
 server_id: 0
 sql_text: select sleep(12)
 thread_id: 19
 1 row in set (0.00 sec)

ERROR:
 No query specified

Above output showing query logged in table.

 

Leave a Reply

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