MariDB – Logging

測試環境為 CentOS 8 x86_64 (虛擬機) .

關於 MariaDB 的 Logging
參考文章 – https://betterstack.com/community/guides/logging/database/how-to-start-logging-with-mariadb#step-3-%E2%80%94-enabling-and-listing-binary-logs

預設 Error log , General query logs , Binary logs 都是開啟的,後面把 Slow Query 加上去.

[root@localhost ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
...
general_log_file=/var/log/mariadb/general-query.log
general_log=1
log_error=/var/log/mariadb/error.log
log_bin=/var/log/mariadb/binary.log

1. Error log
   這邊儲存關於 MariaDB 這個服務 starting, running, 以及 stopping 的相關訊息

   log_error=/var/log/mariadb/error.log
   

   MariaDB [(none)]> show variables like '%log_error%';
   +---------------+----------------------------+
   | Variable_name | Value                      |
   +---------------+----------------------------+
   | log_error     | /var/log/mariadb/error.log |
   +---------------+----------------------------+
   1 row in set (0.001 sec)
   

2. General query logs
   這邊儲存關於 Client -> MariaDB Server 的所有 SQL 指令紀錄.

   general_log_file=/var/log/mariadb/general-query.log
   general_log=1
   

   MariaDB [(none)]> show variables like '%general%';
   +------------------+------------------------------------+
   | Variable_name    | Value                              |
   +------------------+------------------------------------+
   | general_log      | ON                                 |
   | general_log_file | /var/log/mariadb/general-query.log |
   +------------------+------------------------------------+
   2 rows in set (0.001 sec)
   

3. Binary logs
   這邊儲存關於 實際資料的儲存或是操作資料庫(如 資料庫/表的建立 或是 新增新資料到資料庫 ),相當於整個資料庫的備份.

   log_bin=/var/log/mariadb/binary.log
   

   MariaDB [(none)]> show variables like '%log_bin%';
   +---------------------------------+-------------------------------+
   | Variable_name                   | Value                         |
   +---------------------------------+-------------------------------+
   | log_bin                         | ON                            |
   | log_bin_basename                | /var/log/mariadb/binary       |
   | log_bin_compress                | OFF                           |
   | log_bin_compress_min_len        | 256                           |
   | log_bin_index                   | /var/log/mariadb/binary.index |
   | log_bin_trust_function_creators | OFF                           |
   | sql_log_bin                     | ON                            |
   +---------------------------------+-------------------------------+
   7 rows in set (0.001 sec)
   

   MariaDB [(none)]> show binary logs;
   +---------------+------------+
   | Log_name      | File_size  |
   +---------------+------------+
   | binary.000001 |        348 |
   | binary.000002 |   91017489 |
   | binary.000003 |      30886 |
   | binary.000004 |   91751273 |
   | binary.000005 |      26929 |
   +---------------+------------+
   27 rows in set (0.000 sec)
   

   Binary 檔案無法直接看,可以透過以下指令.

   [root@localhost ~]# mysqlbinlog /var/log/mariadb/binary.000001
   

4. Slow Query Log
   這邊儲存關於那些查詢花費過多的時間(預設為 10 秒).

   MariaDB [(none)]> show variables like '%slow_query_log%';
   +---------------------+--------------------+
   | Variable_name       | Value              |
   +---------------------+--------------------+
   | slow_query_log      | OFF                |
   | slow_query_log_file | localhost-slow.log |
   +---------------------+--------------------+
   2 rows in set (0.001 sec)
   

   MariaDB [(none)]> exit
   Bye
   

   long_query_time 單位為秒.

   [root@localhost ~]# vi /etc/my.cnf.d/mariadb-server.cnf
   [mysqld]
   ...
   slow_query_log         = 1
   slow_query_log_file    = /var/log/mariadb/slow-query.log
   long_query_time        = 5
   

   [root@localhost ~]# systemctl restart mariadb
   [root@localhost ~]# mysql -u root -p
   Enter password:
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 9
   Server version: 10.3.28-MariaDB-log 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)]> show variables like '%slow_query_log%';
   +---------------------+---------------------------------+
   | Variable_name       | Value                           |
   +---------------------+---------------------------------+
   | slow_query_log      | ON                              |
   | slow_query_log_file | /var/log/mariadb/slow-query.log |
   +---------------------+---------------------------------+
   2 rows in set (0.001 sec)