monitoring mysql for changes

Question

I have a Java app using a MySQL database through hibernate. The database is really used as persistence layer: The database is read at the initial load of the program, and the records are then maintained in memory.

However, we are adding extra complexity, where another process may change the database as well, and it would be nice for the changes to reflect on the Java app. Yet, I don't particularly like pulling mechanisms to query the database every few seconds, especially that the database is rarely updated.

Is there a way to have a callback to listen to database changes? Would triggers help?

Answer 1

One option would be tail the binary logs (or setup a replication slave) and look for changes relevant to your application. This is likely to be a quite involved solution.

Another would be to add a "last_updated" indexed column to the relevant tables (you can even have mysql update this automatically) and poll for changes since the last time you checked. The queries should be very cheap.

Answer 2

Another way would be to use a self compiled MySQL server with the patches from this project

ProjectPage External Language Stored Procedures

Check this blog post for a more detailed introduction

Calling Java code in MySQL

Answer 3

Is there a way to have a callback to listen to database changes? Would triggers help?

To my knowledge, such a thing doesn't exist and I don't think a trigger would help. You might want to check this similar question here on SO.

So, I'd expose a hook at the Java application level (it could be a simple servlet in the case of a webapp) to notify it after an update of the database and have it invalidate its cache.

Answer 4

Instead of caching the database contents within the memory space of the Java app, you could use an external cache like memcached or Ehcache. When either process updates (or reads) from the database, have it update memcached as well.

This way whenever either process updates the DB, its updates will be in the cache that the other process reads from.

Answer 5

Or change both applications so the Java app is truly the owner of the MySQL database and exposes it as a service. You're coupling the two apps at the database level by doing what you're proposing.

If you have one owner of the data you can hide schema changes and such behind the service interface. You can also make it possible to have a publish/subscribe mechanism to alert interested parties about database changes. If those things are important to you, I'd reconsider letting another application access MySQL directly.