# PMM - Mysql Auditing - How to

**URL:** <https://forums.percona.com/t/pmm-mysql-auditing-how-to/23463>\
**Category:** PMM 2.x\
**Created:** [July 8, 2023, 7:59pm UTC](https://forums.percona.com/t/pmm-mysql-auditing-how-to/23463 "2023-07-08T19:59:31Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![meyerder](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/meyerder/32/16994_2.png) [@meyerder](https://forums.percona.com/u/meyerder)\
**Post date:** [July 8, 2023, 7:59pm UTC](https://forums.percona.com/t/pmm-mysql-auditing-how-to/23463/1 "2023-07-08T19:59:31Z")

</div>

Ever manage multiple servers running mysql?  
Ever get asked who updated this record?  
Ever get asked where did this data come from?  
Ever wanted to go to one place vs the server or the app logs to figure it out?

The below is what I did. and I LOVE it…

Leveraging the following software

PMM  
Percona Mysql  
Loki  
Promtail

Install the Percona Audit Plugin  
Add the following to the mysql config file

```auto
audit-log-format=json
audit-log-policy="queries"
audit-log-rotate-on-size = 104857600
audit-log-rotations = 2
audit-log-file = /xxxx/audit.log
log_error_verbosity=3
log_error_services="log_filter_internal; log_sink_json"
audit-log-exclude-accounts="xxx@%,pmm@localhost"
audit-log-include-commands="call_procedure,select,insert,update,delete,kill,alter_db,alter_event,alter_function,alter_procedure,alter_table,alter_user,create_db,create_event,create_function,create_index,create_procedure,create_table,create_trigger,create_user,create_view,drop_db,drop_event,drop_function,drop_index,drop_procedure,drop_table,drop_trigger,drop_user,drop_view,grant,grant_roles,install_plugin,rename_table,rename_user,restart,revoke,revoke_all,revoke_roles,show_grants,shutdown,lock_tables"
userstat = 1
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-statements-digest=ON
innodb_monitor_enable=all
performance-schema=1
slow-query-log=ON
log-output=FILE
long-query-time=0
log-slow-admin-statements=ON
log-slow-slave-statements=ON
log-slow-rate-limit=100
log-slow-rate-type='query'
slow-query-log-always-write-time=1
log-slow-verbosity='full'
slow-query-log-use-global-control='all'

```

Use the following in the Promtail config file…

```auto
  - targets:
      - localhost
    labels:
      job: xxx
      hostname: xx
      environment: xx
      __path__ : /xxx/audit.log
  pipeline_stages:
  - json:
      expressions:
        audit_record:
  - json:
      expressions:
        name:
        db:
        ip:
        user:
        status:
        command_class:
      source: audit_record
  - labels:
      name:
      db:
      ip:
      user:
      status:
      command_class:

```

You then can build a pretty simple dashboard in grafana that will allow you to search on items like the status (success/fail/deadlock/rollback/ect) times who modified a table/created users and the likes.

While the Query Analytics is great overall for quite a few things this allows the ability to keep a means of seeing who created tables/modified/updated/ or anything with pretty good detail. For instance I have a few other users who have the ability to make changes (highly trusted people) but I still want to keep a eye on items

---

<div class="post-metadata">

**Author:** ![kedarpercona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kedarpercona/32/809_2.png) [@kedarpercona](https://forums.percona.com/u/kedarpercona)\
**Post date:** [July 10, 2023, 3:52am UTC](https://forums.percona.com/t/pmm-mysql-auditing-how-to/23463/2 "2023-07-10T03:52:59Z")

</div>

Amazing work @meyerder,  
No doubt Pecona’s audit plugin is the best community plugin providing enterprise class features. Also it was smart usage of open source technologies to device a solution. Thanks for sharing with the community. Let me also point you to the [community blog](https://percona.community/blog/) where you can contribute a detailed article.

I’d leave here a few links for future readers:

> **[Percona Server for MySQL - Audit log plugin](https://docs.percona.com/percona-server/8.0/management/audit_log_plugin.html)**
>
> Percona Audit Log Plugin provides monitoring and logging of connection and query
> activity that were performed on specific server. Information about the activity
> is stored in a log file. This
> implementation is alternative to the MySQL Enterprise Audit...

> **[Percona’s MySQL Audit Log Plugin - An Enterprise Feature at a Community Price](https://www.percona.com/blog/perconas-mysql-audit-log-plugin-an-enterprise-feature-at-a-community-price/)**
>
> The free, open source Audit Plugin is included in Percona Server for MySQL for auditing who is using a database and their activities.

> **[Percona Monitoring and Management - Database Monitoring](https://www.percona.com/software/database-tools/percona-monitoring-and-management)**
>
> Percona’s open source database software and tools for managing and monitoring MySQL, MariaDB, MongoDB, PostgreSQL, AWS, and other open source database performance. Over 150,000 downloads.

Thanks,  
K