PMM - Mysql Auditing - How to

Percona Monitoring and Management (PMM) PMM 2.x
1

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

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…

  - 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

1 Like
2

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 where you can contribute a detailed article.

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

Thanks,
K