How to Enable MariaDB Slow Query Log and Change the Long Query Time on CentOS 7.6
How To Enable the Slow Query log, Change the Long Query time for MariaDB database On CentOS 7.6
Procedure
Login to the MariaDB database using the root password as follows
[root@linuxhelp ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SET GLOBAL slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SET GLOBAL long_query_time=6;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
After enabling the Slow query log and setting the long query time,navigate to the file location of the slow query log
[root@linuxhelp ~]# cd /var/lib/mysql
List the directories in the mysql directory to check the slow query log
[root@linuxhelp mysql]# ls -la
total 28708
drwxr-xr-x 4 mysql mysql 191 Aug 3 10:18 .
drwxr-xr-x. 65 root root 4096 Apr 15 11:09 ..
-rw-rw---- 1 mysql mysql 16384 Aug 3 10:08 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Aug 3 10:08 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Aug 3 10:08 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Aug 3 10:09 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Apr 15 10:44 ib_logfile1
-rw-rw---- 1 mysql mysql 176 Aug 3 10:18 linuxhelp-slow.log
drwx------ 2 mysql mysql 4096 Apr 15 10:44 mysql
srwxrwxrwx 1 mysql mysql 0 Aug 3 10:09 mysql.sock
drwx------ 2 mysql mysql 4096 Apr 15 10:44 performance_schema
Login to the MariaDB database to disable the slow query log as follows
[root@linuxhelp mysql]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
With this, Slow query log has been successfully enabled , set the time interval for the execution of the query and disabled it successfully for MariaDB On CentOS 7.6
Q
Does this feature gets enabled by default in MYSQL?
A
No, this feature gets disabled by default as it weakens the server performance.
Q
How to enable the Slow Query Log?
A
To enable the Slow Query log in MYSQL thorugh command is " SET GLOBAL slow_query_log="ON"
Q
How does MYSQL create a name to Slow Query log?
A
MYSQL creates SLow query log with a hostname.
Q
What is the default location of the Slow Query log file?
A
The default Location of Slow Query log is /var/lib/mysql/
Q
What is slow query log?
A
Slow Query log is a feature in MYSQL which records the SQL statements(queries) that exceeds A SET LONG QUERY time.