Clicktail部署拉取MySQL审计日志

原创冯刚 2022-11-09

1097

前言

该文章介绍在自建（CentOS7）MySQL5.7上部署Clicktail，并拉取MySQL审计日志，写入到ClickHouse库表的实施过程。

ps：原生clicktail，默认可以解析Percona Audit Log Plugin审计日志插件生成的日志；如果使用的别的审计日志插件，需要根据审计日志输出，自行修改clicktail。

1 环境

1.1 拓扑图

1.2 环境信息

主机名	IP	端口	服务	配置文件
mysql001	192.168.6.114	3306	MySQL5.7.22	/etc/mysql57/my_3306.cnf /data/mysql/mysql57_3306/auditlog/server_audit.log
			Clicktail慢查询进程	/etc/clicktail/clicktail_slow_3306.conf /data/app/clicktail/state/slow_3306.leash.state
			Clicktail审计日志进程	/etc/clicktail/clicktail_audit_3306.conf /data/app/clicktail/state/audit_3306.leash.state
clickhouse001	192.168.6.8	8123	clickhouse客户端、服务端	/etc/clickhouse-server/config_9000.xml /etc/clickhouse-server/users01.xml /etc/clickhouse-server/metrika01.xml

2 部署过程

如果使用Percona Audit Log Plugin审计日志插件，按照2.1和2.2进行安装。

并且如果前面已经启动了慢日志clicktail进程，2.1和2.2步骤忽略。

如果使用MariaDB Audit Plugin审计日志插件，修改后重新编译过的可执行二进制文件clicktail如下，仅供参考：

查看审计日志插件：

mysql> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE from INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE='AUDIT';
+--------------+----------------+---------------+-------------+
| PLUGIN_NAME  | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE |
+--------------+----------------+---------------+-------------+
| SERVER_AUDIT | 1.4            | ACTIVE        | AUDIT       |
+--------------+----------------+---------------+-------------+
1 row in set (0.00 sec)

2.1 安装clicktail软件

curl -s https://packagecloud.io/install/repositories/Altinity/clickhouse/script.rpm.sh | bash
yum search clicktail
yum info clicktail
yum -y install clicktail

也可以通过rpm包安装

下载路径：https://packagecloud.io/app/Altinity/clickhouse/search?q=clicktail

yum install clicktail-1.0.20180401-1.x86_64.rpm

2.2 安装相关目录

[ root@mysql001:/data/app ]# mkdir -p /data/app/clicktail/state/

2.3 修改clicktail配置文件

/etc/clicktail/clicktail-example.conf 为默认配置文件，可以看下参数说明。也可以通过clicktail --help看。

新建配置文件clicktail_audit_3306.conf，是为了后面拉取审计日志，和审计日志配置文件clicktail_slow_3306.conf区分开。

clicktail进程可能会占用较大CPU，需要通过NumSenders、BatchFrequencyMs、BatchSize等参数控制拉取的频率和大小，避免引起线上事故。

[ root@mysql001:/data/app ]# vim /etc/clicktail/clicktail_audit_3306.conf
[Application Options]
APIHost = http://192.168.6.8:8123/   --clickhouse地址
NumSenders = 1  --调整为合适值
BatchFrequencyMs = 10000   --调整为合适值
BatchSize = 1000    --调整为合适值

[Required Options]
ParserName = mysqlaudit
LogFiles = /data/mysql/mysql57_3306/auditlog/server_audit.log   --MySQL审计日志文件路径
Dataset = clicktail.mysql_audit_log                     --clickhouse上存储MySQL慢日志的库表

[Tail Options]
StateFile = /data/app/clicktail/state/audit_3306.leash.state

[MySQL Audit Parser Options]
FilterRegex = zabbix   --过滤监控用户的审计日志条目，zabbix为MySQL监控用户
InvertFilter = true    --确认不匹配FilterRegex设置值

2.4 MySQL审计日志

审计日志文件应做好每日切割（logrotate）。

[ root@mysql001:/data/mysql/mysql57_3306/auditlog ]# ll
total 1581356
-rw-r----- 1 mysql mysql 119280400 Nov  9 11:01 server_audit.log
-rw-r----- 1 mysql mysql 500002459 Nov  9 10:53 server_audit.log.1
-rw-r----- 1 mysql mysql 500000016 Nov  9 10:16 server_audit.log.2
-rw-r----- 1 mysql mysql 500001368 Nov  9 09:42 server_audit.log.3

mysql> show variables like '%server_audit_file_path%';
+------------------------+----------------------------------------------------+
| Variable_name          |          Value                                     |
+------------------------+----------------------------------------------------+
| server_audit_file_path | /data/mysql/mysql57_3306/auditlog/server_audit.log |
+------------------------+----------------------------------------------------+
1 row in set (0.01 sec)

2.5 ClickHouse建库表

通过下面sql脚本在clickhouse创建库表，用来存放审计日志数据。（该创建库表脚本及方法只针对clickhouse单实例，集群的话需要改造）

建库脚本来源：https://github.com/Altinity/clicktail/blob/master/schema/db.sql

使用Percona Audit Log Plugin审计日志插件建表脚本来源：https://github.com/Altinity/clicktail/blob/master/schema/mysqlaudit.sql

使用MariaDB Audit Plugin审计日志插件建表语句：
clickhouse001 :) show create table clicktail.mysql_audit_log\G

SHOW CREATE TABLE clicktail.mysql_audit_log

Query id: 8e130745-e89b-4131-abf5-488bcde4e85a

Row 1:
──────
statement: CREATE TABLE clicktail.mysql_audit_log
(
`_time` DateTime,
`_date` Date DEFAULT toDate(_time),
`_ms` UInt32,
`command_class` String,
`connection_id` UInt32,
`db` String,
`hostname` String,
`ip` String,
`name` String,
`query_id` String,
`os_user` String,
`os_login` String,
`os_version` String,
`mysql_version` String,
`priv_user` String,
`proxy_user` String,
`record` String,
`sqltext` String,
`status` UInt32,
`user` String,
`startup_optionsi` String
)
ENGINE = MergeTree(_date, (_time, hostname, user), 8192)
COMMENT '自建MySQL审计日志表';

手动连接clickhouse执行命令，或者执行sql文件

cat ./db.sql | clickhouse-client --port=9000 --multiline    --如果慢日志已经部署，建库步骤忽略
cat ./mysqlaudit.sql | clickhouse-client --port=9000 --multiline  --使用Percona Audit Log Plugin审计日志插件执行

2.6 启动clicktail持续拉取增量慢日志

通过下面clicktail命令测试是否能拉取到审计日志，并观察cpu使用率占用。

[ root@mysql001:/data/app ]# clicktail --api_host=http://192.168.6.8:8123/ --dataset='clicktail.mysql_audit_log' --parser=mysqlaudit --file=/data/mysql/mysql57_3306/auditlog/server_audit.log --send_batch_size=5 --poolsize=5

clicktail进程可能会占用较大CPU，需要通过--send_batch_size --poolsize等参数控制，避免引起线上事故。

[ root@mysql001:/etc/logrotate.d ]# cat /etc/systemd/system/clicktail_audit_3306.service 
[Unit]
Description=for audit log to clickhouse
After=network.target

[Service]
ExecStart=/usr/bin/clicktail -c /etc/clicktail/clicktail_audit_3306.conf
KillMode=process
Restart=on-failure
User=root
Group=root

[Install]
Alias=clicktail_audit_3306 clicktail_audit_3306.service

systemctl start clicktail_audit_3306.service

常见问题有：

clicktail进程占用cpu较大，需要按实际情况调整一些参数；
在限制cpu使用率情况下，当瞬时审计日志较多时，拉取会有延迟；
审计日志量较大，clickhouse侧需定期清理审计日志数据；

3 附录

clicktail安装参考：https://github.com/Altinity/clicktail

clicktail介绍：https://altinity.com/blog/2018/3/12/clicktail-introduction

Analyze MySQL Audit Logs with ClickHouse and ClickTail：https://www.percona.com/blog/2018/03/29/analyze-mysql-audit-logs-clickhouse-clicktail/

3.1 查看审计日志插件命令

Percona Audit Log Plugin审计日志插件

mysql> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE from INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE='AUDIT';
+-------------+----------------+---------------+-------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE |
+-------------+----------------+---------------+-------------+
| audit_log   | 0.2            | ACTIVE        | AUDIT       |
+-------------+----------------+---------------+-------------+
1 row in set (0.00 sec)

MariaDB Audit Plugin审计日志插件

mysql> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE from INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE='AUDIT';
+--------------+----------------+---------------+-------------+
| PLUGIN_NAME  | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE |
+--------------+----------------+---------------+-------------+
| SERVER_AUDIT | 1.4            | ACTIVE        | AUDIT       |
+--------------+----------------+---------------+-------------+
1 row in set (0.00 sec)

3.2 审计日志参考文档

Percona Audit Log Plugin官方文档：https://docs.percona.com/percona-server/8.0/management/audit_log_plugin.html

MariaDB Audit Log Plugin官方文档：https://mariadb.com/kb/en/mariadb-audit-plugin-log-settings/

常用审计插件：https://www.percona.com/blog/2016/02/15/mysql-mariadb-with-mariadb-auditing-plugin/

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者