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)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料