We are trying to move a HIPA compliant SQL Server based application to Percona. We were using CDC for query auditing in SQL Server. The audit plugin offered by Percona has a lot of information about configuration and installation but how does one access the JSON logs. One article I found spoke about using named pipes to move data to a custom MySQL table. Is that the suggested way or is there a faster method to query the audit logs and create audit reports. Thanks in advance for any replies
Most solutions are going to require a custom implementation.
I believe for the MySQL ingestion you’re talking about this blog: https://www.percona.com/blog/2020/07/22/percona-audit-log-plugin-and-the-percona-monitoring-and-management-security-threat-tool/
That is a supported method by Percona and will keep everything within MySQL. This is pretty easy to manage and won’t require many approvals for other software or moving data around which might be a concern with HIPAA compliancy.
For a robust, external solution, I would recommend an ELK stack. None of those databases are officially supported by Percona but it does serve the purpose of a robust, open source log ingestion and visualization tool. The downside is that it complicates the environment and would require approval for using additional open source software.
I hope this was helpful!
Thanks for replying Barret. We are trying out clickhouse at the moment as the log storage purely for enhanced performance. We will be using inotify to monitor the percona log and then use a python script to load the data to clickhouse. The idea comes from one of Percona’s blogs which mentions clicktail and clickhouse but unfortunately clicktail doesnt seem to be in development anymore. I am just noting this down here as a possible implementation for anyone else who may be going down the route that we are. Logstash and ELK as a whole also looks good.
For anyone else reading this, I believe this is the blog that espidi is referring to: https://www.percona.com/blog/2018/03/29/analyze-mysql-audit-logs-clickhouse-clicktail/
There are lots of methods for ingesting logs and analyzing them. It’s all about what meets requirements and what your org is willing to implement/support. Thanks for the feedback, espidi.
Barrett, I just realised, albeit a bit late that the audit plugin does not actually capture data changes, only the queries thereby doing audit but not CDC , is that right or am I mistaken ?