0

I regularly back up my MariaDB databases using mariadb-dump (mariadb's mysqldump).

However, I want to ensure data integrity in either of these cases:

  • a vulnerability in an application using a database has been exploited and unauthorized changes to the data have been made (e.g. an attacker forging or deleting data like changing the password of a random user account or deleting a user account)
  • an application error or bug has affected the data (e.g. leading to data corruption or data loss, like a query that affects rows that shouldn't have been affected)

What I mean by that is that I want to make sure that if either of the aforementioned cases happens, I can see the history of changes to the data in an easy-to-follow way (e.g. using a diff tool). So, I probably need something like event logging for DELETE, INSERT, and UPDATE actions that would list each such data manipulation query in a chronologically sorted list (with event timestamps) and I can compare it to the last known revision (to see only changes that I haven't reviewed before).

I am searching for a database data change tool that would let me see if an authorized change to the data has occurred (automatically enforcing any security rules is not needed, only an ability to inspect manually in an easy way after an event has happened).

I know the best thing to do is to take preventive measures (proactively) but I also need an additional measure like the one I described to minimize the consequences.

So far, the best I could do was to compare two .sql sequential mysqldumps using a diff compare tool. However, this approach is not easy to follow as it requires moving forward and backward inside files and also I can't see when a change has happened because there are no columns in the databases storing the time of row updates.

2 Answers 2

2

You can enable the audit plugin in MariaDB or MySQL servers. Configure it to store the logs to a file and backup these files in S3.

This is better than using CDC or using the mysqlbinlog utility since if configured correctly you can track database connections, statements executed, etc.

MariaDB Audit Plugin

1

You could use a change data capture tool like Debezium https://debezium.io/ or Maxwell’s Daemon https://maxwells-daemon.io/ to read the binary log where every row modification is logged as an event and push the events to a streaming platform like Kafka, RabbitMQ Streams, etc. You would be able, then, to inspect every change of a row by consuming the data from the stream and going back and forth in it to analyze and compare the changes.

If the near-realtime processing of it is not needed, you could do it using mysqlbinlog utility with the verbose output which produces pseudo-SQL statements representing the events which could be easily parsed.

4
  • Do you know how they compare to manually creating an audit log table and populating it with triggers in terms of ease of use, flexibility and performance?
    – kataba
    Feb 2 at 20:27
  • @kataba I can't specifically speak to these implementations in MySQL, but typically change tracking features handle some extent of schema changes on the source table. If you were to roll your own implementation with triggers, of course that would still be a problem that you would also have to solve.
    – J.D.
    Feb 3 at 14:06
  • @J.D. You are right, in the best situation, it would be good to track schema changes as well. Fortunately, in my case, I am only concerned about data because the database users accessing the databases only have data manipulation permissions (SELECT, INSERT, UPDATE, DELETE).
    – kataba
    Feb 3 at 15:25
  • 1
    @kataba True. In any case, you'll still have to manually change the trigger whenever you change the schema too, to account for it. With an automated change tracking feature, whenever you change the schema, you don't have to worry about changing anything else.
    – J.D.
    Feb 4 at 3:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.