I have performed the following steps successfully:
Then, I would like to execute some queries and examine the logs.
How can I view the logs?
1 Like
https://www.percona.com/doc/percona-server/8.0/management/audit_log_plugin.html#audit_log_file
Since you did not specify a full path, you can find audit.log in your MySQL $DATADIR (usually /var/lib/mysql)
You also have audit_log_format set to “OLD”, which is based on XML. Audit Log Plugin — Percona Server 8.0 Documentation
I would suggest switching to JSON or CSV for easier parsing of the events into another system. I frequently see clients with ELK stacks using filebeat to scrape the audit log into their ElasticSearch for report generation and alarming.
1 Like
Thank you for your reply.
I have checked the file is in /var/lib/mysql:
1 Like
@matthewb
Thank you for your reply.
Since I am using Windows 10 and I finally find my audit.log in \wsl$\docker-desktop-data\version-pack-data\community\docker\overlay2\574356f34b19f3cae8774e8eecded8ef6752804694a089f5758a9098e75f1136\diff\usr\lib64\mysql\plugin\debug
But I find that it did not log properly, there are lots of strange words and no information provided, but show ‘%s’:
Besides, it seems that it does not update when I tried some sql commands.
for example:
mysql> CREATE USER ‘test’@‘localhost’ IDENTIFIED BY ‘12345’;
Query OK, 0 rows affected (0.01 sec)
How can I solve the problem?
1 Like
That is not the correct location of the log. As you said above, the log is located at /var/lib/mysql/audit.log This is the correct log. This log file will be updated and flushed when the internal audit buffer fills. This may or may not be after each SQL you execute.
1 Like
@matthewb Thank you for your reply.
As I mentioned before, I am using Windows 10. I tried to use ‘select @@datadir’ to find the directory of /var/lib/mysql. The output is: C:\ProgramData\MySQL\MySQL Server 8.0\Data. However, there is no audit.log
1 Like
Below is the procedure of installation for your review, I would like to know if I have missed something.
Download images:
1 Like
@Ka_ka_Chan You posted this screenshot earlier in this post: Where can I find the audit logs after installation? - #3 by Ka_ka_Chan
That screenshot clearly shows an audit.log within /var/lib/mysql/ Please check this again.
As I mentioned before, I am using Windows 10.
I see many references to docker. You running Percona Server inside a linux docker container, not Windows. Percona does not run on Windows; it only runs on linux. All of your database data will be contained within the running docker container unless you mounted an external volume inside the container, which I can see in the screenshot, you did not do this.
Please run docker exec /bin/sh mysql4 to access a shell inside the running container. Once there, please run ls /var/lib/mysql/audit.log and confirm existence of the log.
1 Like
@matthewb
Sorry that I am new to docker…
Since my PC is not with me at the moment, I will try your command later.
But I would like to know is there any way to print the logs directly in docker?
Thanks!
1 Like
The audit logs are always and only written to the audit.log file. You can use whatever tools you like to copy that file out of the container and into your Windows host for viewing. Alternatively, once inside the container (using the exec command I mentioned above) you can less the file to view it.
1 Like
Thank you. I can view the logs finally.
But I would like to ask some other questions.
- How to change the log format to JSON?
I found that there is a variable named ‘audit_log_format’, which has the value 'JSON"
However, it is a read only variable. I don’t know how to set the format. - How to filter the events by status?
I created a user and login with wrong password intentionally.
The status showed ‘1045’ instead of ‘0’
STATUS is 0 for successful logins and non - zero for failed logins .
Then, I would like to filter this event so I tried the settings below:
SET GLOBAL audit_log_statement_policy = ALL;
SET GLOBAL audit_log_connection_policy = ERRORS;
However, it gave the error:
Unknown system variable ‘audit_log_statement_policy’
Unknown system variable ‘audit_log_connection_policy’
I don’t know if this settings is correct to filter the event which indicate failed logins.
Could you suggest any advice?
Thank you.
1 Like
However, it is a read only variable. I don’t know how to set the format.
Change in my.cnf and restart mysql
Unknown system variable ‘audit_log_statement_policy’
Please read the documentation. Percona’s Audit plugin is an open-source alternative to the paid-for Oracle MySQL Enterprise plugin. They do not have the same parameters.
https://www.percona.com/doc/percona-server/8.0/management/audit_log_plugin.html
1 Like
For changing to JSON format, I found that I need to add the following command in my.cnf under [mysqld] section
****
audit_log_format="JSON"
audit_log_file="audit.json"
****
However, i don't know how to edit it:
****
bash-4.4$ cat /etc/my.cnf
#
# The Percona Server 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
# Please make any edits and changes to the appropriate sectional files
# included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-server.conf.d/
****
people said using ‘nano /etc/my.cnf’ to edit,but it shows ‘nano: command not found’ in the percona’s container.
1 Like
You can also use vi to edit the file. Be aware that vi is a very different editor and much harder to use than nano. Also, you should be aware that any changes you make to files inside the container will be lost if the container is restarted. This is the nature of docker containers.
1 Like