I have a small internal web app I'm developing with PHP and MySQL. I'm looking to sync client webpages to a server by requesting all changes since a certain date.

I've found a number of solutions for doing this via a 3rd party Change Data Capture library in MySQL (Maxwell, Debezium, etc.), but they all seem to stream to Kafka or something similar. This is a smaller, simple project without any sort of message queue/broker.

All I want is for changes to be recorded in the same MySQL database in a table with columns like the CRUD action, the table that changed, the table row ID, and the datetime. Then I could query for all rows changed since a certain date and time.

Is there a simple way or a library that can record this information right in the same MySQL database?

  • Does it have to be MySQL, or would you consider MariaDB as well? If so, I have an idea which may be able to give you what you want to achieve very easily.
    – dbdemon
    Jul 31 '19 at 22:18
  • @dbdemon Unfortunately I'm working with a long existing MySQL database and just adding onto an existing web app. Changing the entire database to MariaDB would not be something I could get approved.
    – dallin
    Jul 31 '19 at 22:25

The most obvious solution to this would perhaps be to use triggers. You can have triggers that fire before/after inserts, deletes and updates on the table(s) in question, and then log the changes in your special table, including a time stamp and what type of write operation it was.

Another approach could be to use an audit plugin - MariaDB's audit plugin works with older versions of MySQL at least up to 5.5. MySQL has Enterprise Audit, which presumably has a proprietary licence. These plugins will log to a file, but you can set up a cron job or similar to load that into a database table.

(With MariaDB 10.3+ you would be able to use system-versioned tables which would allow you to store all changes to rows.)

  • In the end I used triggers in the exact manner you suggested. Triggers are not usually recommended for this purpose because they can slow down your app if you do batch operations because the database is locked until all the triggers complete as well. In my case, I only needed to track 5 tables and I knew those tables were not going to be changed with batch operations or changed very often at all, so it worked out perfectly!
    – dallin
    Aug 2 '19 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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