nginxアクセスログをfluentdでMySQLに保存する

自前でアクセス解析できるようにしておきます。

MySQLのインストールと初期設定

インストール

sudo apt install mysql-server

ステータスの確認

sudo systemctl status mysql

MySQLにrootでログイン

sudo mysql -u root

ユーザーの作成、権限付与、反映

CREATE USER 'TestUserName'@'%' IDENTIFIED BY 'TestPassword';
GRANT ALL PRIVILEGES ON * . * TO 'TestUserName'@'%';
FLUSH PRIVILEGES;

設定ファイル

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

外部から接続できるように以下2行をコメントアウトする

#bind-address		= 127.0.0.1
#mysqlx-bind-address	= 127.0.0.1

Access deniedのログを出すためlog_error_verbosity=3を追加しておく（fail2banするため）

log_error_verbosity = 3
log_error = /var/log/mysql/error.log

LOAD DATA INFILE の制限を解除するための設定（secure_file_priv=""）を追加
LOAD DATA LOCAL INFILE ができるようにも設定（local_infile=1）

secure_file_priv=""
local_infile=1

設定を変更したら再起動する

sudo systemctl restart mysql

外部から接続できることを確認する
dbeaverから接続できない場合allowPublicKeyRetrievalをtrueにするとよい

fluentd、MySQLプラグインのインストール

公式ページを参考に進める。
ubuntu24.04の場合は以下。

sudo curl -fsSL https://toolbelt.treasuredata.com/sh/install-ubuntu-noble-fluent-package5-lts.sh | sh

MySQLのプラグインを追加するために必要なライブラリをインストールする。

sudo apt install libmysqlclient-dev
sudo apt install gcc
sudo apt install make

MySQLのプラグインを追加する。

sudo fluent-gem install fluent-plugin-mysql

インストールはこれで完了。以下で稼働していることを確認する。

sudo systemctl status fluentd.service

fluentdの実行ユーザーについて

fluentdは"_fluentd"というユーザー／グループで実行される。以下で確認できる。

cat /usr/lib/systemd/system/fluentd.service

<!--
-->user@Ubuntu:~$ cat /usr/lib/systemd/system/fluentd.service
[Unit]
Description=fluentd: All in one package of Fluentd
Documentation=https://docs.fluentd.org/
After=network-online.target
Wants=network-online.target

[Service]
User=_fluentd
Group=_fluentd
...

fluentdでロギングするファイルを"_fluentd"が参照できる必要があるので、必要なグループに追加しておくなど対応しておく。
（例）nginxのログはその他に読み取り権限がない。"adm"というグループには読み取り権限があるので"_fluentd"を"adm"グループに追加する。

sudo gpasswd -a _fluentd adm

fluentdの設定

以下の設定ファイルを編集する

sudo vim /etc/fluent/fluentd.conf

以下はサンプル。読み込むログファイルはltsvを使うのがよい。

<source>
  @type tail
  path /var/log/nginx/access.log
  pos_file /var/log/fluent/nginx.access.log.pos
  tag nginx.access
  format ltsv
</source>

<match nginx.access>
  @type mysql_bulk
  host localhost
  port 3306
  database IkstNet
  username sampleUser
  password samplePassword
  table NginxAccessLog
  column_names LocalTime,Host,Uri,QueryString,RemoteAddress,Protocol,Scheme,Method,UserAgent,Referer,AcceptEncoding,AcceptLanguage,CacheControl,XForwardedFor,RequestCompletion,StatusCode,ContentType,SentBytes,RequestTime,UpstreamCacheStatus,UpstreamResponseTime
  flush_interval 10s
  retry_limit 3
</match>

なお、上記で読み込んでいるnginxのアクセスログの定義は以下

	log_format  fluentdMysql
		'LocalTime:$time_iso8601\t'
		'Host:$host\t'
		'Uri:$uri\t'
		'QueryString:$query_string\t'
		'RemoteAddress:$remote_addr\t'
		'Protocol:$server_protocol\t'
		'Scheme:$scheme\t'
		'Method:$request_method\t'
		'UserAgent:"$http_user_agent"\t'
		'Referer:$http_referer\t'
		'AcceptEncoding:"$http_accept_encoding"\t'
		'AcceptLanguage:"$http_accept_language"\t'
		'CacheControl:$http_cache_control\t'
		'XForwardedFor:$http_x_forwarded_for\t'
		'RequestCompletion:$request_completion\t'
		'StatusCode:$status\t'
		'ContentType:"$sent_http_content_type"\t'
		'SentBytes:$body_bytes_sent\t'
		'RequestTime:$request_time\t'
		'UpstreamCacheStatus:$upstream_cache_status\t'
		'UpstreamResponseTime:$upstream_response_time';

	access_log /var/log/nginx/access.log fluentdMysql;

出力先のMySQLテーブル定義は以下（これは適当）

CREATE TABLE `NginxAccessLog` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `LocalTime` datetime DEFAULT NULL,
  `Host` varchar(100) DEFAULT NULL,
  `Uri` varchar(2048) DEFAULT NULL,
  `QueryString` text,
  `RemoteAddress` varchar(100) DEFAULT NULL,
  `Protocol` varchar(100) DEFAULT NULL,
  `Scheme` varchar(100) DEFAULT NULL,
  `Method` varchar(100) DEFAULT NULL,
  `UserAgent` varchar(2048) DEFAULT NULL,
  `Referer` varchar(2048) DEFAULT NULL,
  `AcceptEncoding` varchar(100) DEFAULT NULL,
  `AcceptLanguage` varchar(100) DEFAULT NULL,
  `CacheControl` varchar(100) DEFAULT NULL,
  `XForwardedFor` varchar(100) DEFAULT NULL,
  `RequestCompletion` varchar(100) DEFAULT NULL,
  `StatusCode` int DEFAULT NULL,
  `ContentType` varchar(100) DEFAULT NULL,
  `SentBytes` varchar(100) DEFAULT NULL,
  `RequestTime` double DEFAULT NULL,
  `UpstreamCacheStatus` varchar(100) DEFAULT NULL,
  `UpstreamResponseTime` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`Id`)
)

設定ファイルを変更したらfluentdを再起動する。

sudo systemctl restart fluentd.service

なお、開始と終了は以下。

sudo systemctl start fluentd.service
sudo systemctl stop fluentd.service

動作確認

fluentdのログファイルをtail -f で流しつつ...

tail -f /var/log/fluent/fluentd.log

ロギング対象のファイルが更新された際に以下のようなログが出ていることを確認し、MySQLにレコードが登録されていることを確認する。
エラーがある場合はここにエラーが出力されるので、内容を参考にログのフォーマットやテーブル定義を確認する。

<!--
-->user@Ubuntu:~$ tail -f /var/log/fluent/fluentd.log
2024-07-30 21:27:52 +0900 [info]: #0 bulk insert values size (table: NginxAccessLog) => 2
2024-07-30 21:41:58 +0900 [info]: #0 bulk insert values size (table: NginxAccessLog) => 1
2024-07-30 21:54:53 +0900 [info]: #0 bulk insert values size (table: NginxAccessLog) => 12
2024-07-30 21:56:32 +0900 [info]: #0 bulk insert values size (table: NginxAccessLog) => 5
...

MySQLへのインサートに失敗する場合があるので以下のようなコマンドでエラーログが出力されていないか確認する。

gunzip -c /var/log/fluent/fluentd.log.*.gz | grep error