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
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