mysql如何開啟各種紀錄檔

2022-11-07 14:01:50

general_log

general_log支援熱開啟，熱關閉。開啟general_log會記錄所有操作mysql命令，所以會產生大量檔案，一般不開啟。

相關引數general_log、log_output、general_log_file

mysql> show variables like 'general_log';  --檢視紀錄檔是否開啟
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (1.09 sec)

mysql> show variables like 'general_log_file'; --general_log_file紀錄檔儲存位置
+------------------+--------------------------------------+
| Variable_name    | Value                                |
+------------------+--------------------------------------+
| general_log_file | /opt/sudytech/mysql/data/general.log |
+------------------+--------------------------------------+
1 row in set (2.41 sec)

mysql> show variables like 'log_output'; --紀錄檔輸出型別 table和file兩種型別
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

log_output='FILE' 表示將紀錄檔存入檔案,預設值是FILE　 
log_output='TABLE'表示將紀錄檔存入資料庫,這樣紀錄檔資訊就會被寫入到mysql.slow_log表中.
mysql資料庫支援同時兩種紀錄檔儲存方式,設定的時候以逗號隔開即可,如:log_output='FILE,TABLE'.
紀錄檔記錄到系統專用紀錄檔表中,要比記錄到檔案耗費更多的系統資源,因此對於需要啟用慢查紀錄檔,又需要比夠獲得更高的系統效能,那麼建議優先記錄到檔案。

開啟general_log紀錄檔
mysql> set global general_log=on; --開啟紀錄檔
Query OK, 0 rows affected (2.60 sec)

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

mysql> set global general_log_file='/opt/sudytech/mysql/data/general.log'; --指定紀錄檔產生位置
Query OK, 0 rows affected (0.05 sec)

mysql> show variables like 'general_log_file';
+------------------+--------------------------------------+
| Variable_name    | Value                                |
+------------------+--------------------------------------+
| general_log_file | /opt/sudytech/mysql/data/general.log |
+------------------+--------------------------------------+
1 row in set (0.04 sec)

由於log_output預設值為FILE。所以不需要修改。

檢視/opt/sudytech/mysql/data/目錄下已經產生了general.log紀錄檔

[root@localhost data]# pwd
/opt/sudytech/mysql/data
[root@localhost data]# tail -f general.log 
2021-05-18T06:45:32.140829Z         2 Query     set global general_log=OFF
/opt/sudytech/mysql/bin/mysqld, Version: 5.7.32-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
2021-05-18T10:43:17.049473Z         3 Query     show variables like 'general_log'
2021-05-18T10:44:09.060990Z         3 Query     set global general_log_file='/opt/sudytech/mysql/data/general.log'
/opt/sudytech/mysql/bin/mysqld, Version: 5.7.32-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
2021-05-18T10:44:18.375549Z         3 Query     show variables like 'general_log_file'
......

永久修改需要在my.cnf中[mysqld]新增
general_log = 1
general_log_file=/opt/sudytech/mysql/data/general.log

log_bin

log_bin不支援熱開啟。
mysql> set global log_bin=on;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

需要在my.cnf [mysqld]中新增
log_bin=/opt/sudytech/mysql/data/mysql-bin
expire_logs_days = 180    #紀錄檔過期天數
max_binlog_size = 500M  #單日檔案最大大小

開啟後會在/opt/sudytech/mysql/data目錄下產生mysql-bin.xxxxx和mysql-bin.index兩個檔案。mysql-bin.xxxxxx是記錄binlog紀錄檔的檔案，而index是存放mysql-bin檔名的檔案
[root@localhost data]# ll mysql-bin.*
-rw-r-----. 1 mysql mysql 372 5月  18 18:58 mysql-bin.000001
-rw-r-----. 1 mysql mysql 154 5月  18 18:58 mysql-bin.000002
-rw-r-----. 1 mysql mysql  84 5月  18 18:58 mysql-bin.index
[root@localhost data]# cat mysql-bin.index 
/opt/sudytech/mysql/data/mysql-bin.000001
/opt/sudytech/mysql/data/mysql-bin.000002

遇到以下3種情況時，MySQL會重新生成一個新的紀錄檔檔案，檔案序號遞增

1、MySQL伺服器停止或重啟時（其實重啟時也是呼叫flush logs命令）
2、使用 flush logs 命令；
3、當 binlog 檔案大小超過 max_binlog_size 變數的值時；

max_binlog_size 的最小值是4096位元組，最大值和預設值是 1GB (1073741824位元組)。事務被寫入到binlog的一個塊中，所以它不會在幾個二進位制紀錄檔之間被拆分。因此，如果你有很大的事務，為了保證事務的完整性，不可能做切換紀錄檔的動作，只能將該事務的紀錄檔都記錄到當前紀錄檔檔案中，直到事務結束，你可能會看到binlog檔案大於 max_binlog_size 的情況。

檢視mysql-bin.xxxxx資訊，mysql-bin.xxxxx是以二進位制形式儲存，vim、cat檢視是亂碼，這時可以使用mysqlbinlog命令檢視

[root@localhost data]# /opt/sudytech/mysql/bin/mysqlbinlog  -v --base64-output=decode-rows  --start-datetime='2021-04-11 00:00:00' --stop-datetime='2021-05-19 15:00:00' /opt/sudytech/mysql/data/mysql-bin.000002
 base64-output，可以控制輸出語句輸出base64編碼的BINLOG語句;decode-rows：選項將把基於行的事件解碼成一個SQL語句
..............
create database aaaaa
/*!*/;
# at 316
#210518 19:15:01 server id 1  end_log_pos 381 CRC32 0x6f4cdc6c  Anonymous_GTID  last_committed=1        sequence_number=2       .......
create database bbbb
.....

audit_log（mysql_audit.json）

開啟audit_log需要安裝審計外掛，將audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip檔案上傳到/opt下解壓，登入資料庫檢視外掛存放位置

mysql> show global variables like 'plugin_dir';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| plugin_dir    | /opt/sudytech/mysql//lib/plugin/ |
+---------------+----------------------------------+
1 row in set (0.02 sec)

將外掛複製該路徑下，並授權

[root@localhost mysql]# cp /opt/sudytech/audit-plugin-mysql-5.7-1.1.4-725/lib/libaudit_plugin.so  /opt/sudytech/mysql//lib/plugin/
[root@localhost mysql]# chmod +x /opt/sudytech/mysql//lib/plugin/libaudit_plugin.so
[root@localhost mysql]# chown mysql:mysql /opt/sudytech/mysql//lib/plugin/libaudit_plugin.so

登入資料庫進行安裝

mysql> install plugin audit soname 'libaudit_plugin.so';
ERROR 1123 (HY000): Can't initialize function 'audit'; Plugin initialization function failed.

解決方法：

[root@localhost mysql]# /opt/sudytech/audit-plugin-mysql-5.7-1.1.4-725/utils/offset-extract.sh /opt/sudytech/mysql/bin/mysqld
ERROR: gdb not found. Make sure gdb is installed and on the path.

[root@localhost mysql]# yum -y instal gdb

[root@localhost mysql]# /opt/sudytech/audit-plugin-mysql-5.7-1.1.4-725/utils/offset-extract.sh /opt/sudytech/mysql/bin/mysqld
//offsets for: /opt/sudytech/mysql/bin/mysqld (5.7.32)
{"5.7.32","30165bbd00a2077d2e4b1d3c6768c2f7", 7824, 7872, 3632, 4792, 456, 360, 0, 32, 64, 160, 536, 7988, 4360, 3648, 3656, 3660, 6072, 2072, 8, 7056, 7096, 7080},

編輯my.cnf在[mysql]中新增,重啟mysql

audit_json_file=on #保證mysql重啟後自動啟動外掛
audit_record_cmds='insert,delete,update,create,drop,alter,grant,truncate,show'  #記錄操作     
plugin-load=AUDIT=libaudit_plugin.so   #防止刪除了外掛，重啟後又會載入
audit_json_log_file=/opt/sudytech/mysql/stat/logs/mysql_audit.json  #紀錄檔路徑
audit_offsets=7824, 7872, 3632, 4792, 456, 360, 0, 32, 64, 160, 536, 7988, 4360, 3648, 3656, 3660, 6072, 2072, 8, 7056, 7096, 7080

檢視/opt/sudytech/mysql/stat/logs/目錄下會產生mysql_audit.json紀錄檔

[root@localhost logs]# cat mysql_audit.json  
..........
{"msg-type":"activity","date":"1621349743813","thread-id":"2","query-id":"6","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"8826","_client_version":"5.7.32","_platform":"x86_64","program_name":"mysql"},"pid":"8826","os_user":"root","appname":"/opt/sudytech/mysql/bin/mysql","cmd":"create_db","query":"create database  bbbbb"}
{"msg-type":"activity","date":"1621349802594","thread-id":"2","query-id":"8","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"8826","_client_version":"5.7.32","_platform":"x86_64","program_name":"mysql"},"pid":"8826","os_user":"root","appname":"/opt/sudytech/mysql/bin/mysql","cmd":"drop_db","query":"drop database bbbbb"}

audit_log（server_audit.log）

server_audit.log支援熱開啟，熱關閉。下載mariadb-5.5.68壓縮包，解壓獲取mariadb-5.5.68-linux-x86_64/lib/plugin/server_audit.so(mysql8後不支援該外掛)

MariaDB_5.x.x和MariaDB_10.x.x區別：

MariaDB_5.x.x：相容MySQL5.x.x的，介面幾乎一致，只限於社群版
MariaDB_10.x.x：10.x.x使用新技術，介面會與MySQL逐漸區別開來,向MariaDB新介面過渡

因為測試資料庫版本為5.7.32,所以選擇mariadb-5.5.68

登入資料庫檢視外掛存放位置

mysql> show global variables like 'plugin_dir';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| plugin_dir    | /opt/sudytech/mysql//lib/plugin/ |
+---------------+----------------------------------+
1 row in set (0.02 sec)

將外掛複製該路徑下，並授權

[root@localhost plugin]# cp /opt/sudytech/mariadb-5.5.68-linux-x86_64/lib/plugin/server_audit.so  /opt/sudytech/mysql/lib/plugin/
[root@localhost plugin]# chmod +x /opt/sudytech/mysql/lib/plugin/server_audit.so

登入資料庫進行安裝

mysql> install plugin server_audit soname 'server_audit.so';
Query OK, 0 rows affected (0.00 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+-----------------+---------+
| Name                       | Status   | Type               | Library         | License |
+----------------------------+----------+--------------------+-----------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
.......
| SERVER_AUDIT               | ACTIVE   | AUDIT              | server_audit.so | GPL     |
+----------------------------+----------+--------------------+-----------------+---------+

開啟server_audit.log，紀錄檔預設會在mysql/data目錄下,可通過server_audit_file_path指定檔案存放位置

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

mysql> set  global  server_audit_logging=on;
Query OK, 0 rows affected (0.00 sec)

在my.cnf中[mysqld]新增設定

server_audit_logging = ON #開啟紀錄檔記錄，預設是關閉
server_audit = FORCE_PLUS_PERMANENT #防止外掛被解除安裝
server_audit_file_path = /opt/sudytech/mysql/stat/logs/server_audit.log #定義審計紀錄檔路徑與檔名
server_audit_file_rotations = 2 #定義審計紀錄檔的輪詢個數，0為不輪詢，值為2會產生3個檔案server_audit.log server_audit.log.1 server_audit.log.2
server_audit_file_rotate_size = 1073741824 #定義切割審計紀錄檔的檔案大小1073741824=1GB,當server_audit_file_rotations為0時，設定該值無意義

在/opt/sudytech/mysql/stat/logs目錄下就會產生server_audit.log紀錄檔

[root@localhost logs]# tail -f server_audit.log
20210519 10:05:00,localhost.localdomain,root,localhost,2,27,QUERY,,'show variables like 'server_audit_file_rotations'',0
20210519 10:05:01,localhost.localdomain,root,localhost,2,28,QUERY,,'show variables like 'server_audit_file_rotations'',0
20210519 10:05:01,localhost.localdomain,root,localhost,2,29,QUERY,,'show variables like 'server_audit_file_rotations'',0
20210519 10:05:01,localhost.localdomain,root,localhost,2,30,QUERY,,'show variables like 'server_audit_file_rotations'',0
20210519 10:05:02,localhost.localdomain,root,localhost,2,31,QUERY,,'show variables like 'server_audit_file_rotations'',0
20210519 10:35:02,localhost.localdomain,root,localhost,2,0,DISCONNECT,,,0

server_audit.log引數說明：

server_audit_output_type 指定紀錄檔輸出型別，可為SYSLOG或FILE，為SYSLOG時，記錄在/var/log/message中
server_audit_logging 啟動或關閉審計
server_audit_events 指定記錄事件的型別，可以用逗號分隔的多個值(connect,query,table)，如果開啟了查詢快取(query cache)，查詢直接從查詢快取返回資料，將沒有table記錄
server_audit_file_path 如server_audit_output_type為FILE，使用該變數設定儲存紀錄檔的檔案，可以指定目錄，預設存放在mysql/data目錄的server_audit.log檔案中
server_audit_file_rotations 指定紀錄檔檔案的數量，如果為0紀錄檔將從不輪轉
server_audit_file_rotate_size 限制紀錄檔檔案的大小,當server_audit_file_rotations為0時，該值無意義
server_audit_file_rotate_now 是否立即切割紀錄檔，當server_audit_file_rotations為0時，該值無意義
server_audit_incl_users 指定哪些使用者的活動將記錄，connect將不受此變數影響，該變數比server_audit_excl_users優先順序高
server_audit_syslog_facility 預設為LOG_USER，指定facility
server_audit_syslog_ident 設定ident，作為每個syslog記錄的一部分
server_audit_syslog_info 指定的info字串將新增到syslog記錄
server_audit_syslog_priority 定義記錄紀錄檔的syslogd priority
server_audit_excl_users 該列表的使用者行為將不記錄，connect將不受該設定影響

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

mysql如何開啟各種紀錄檔

目錄

general_log

log_bin

audit_log（mysql_audit.json）

audit_log（server_audit.log）

熱門文章