I am searching for an answer like the inotify approach to watching files for changes. I am conscious that I possibly could watch the binlog file from the mysql database and run queries to choose the brand new results but that appears very inefficient and inelegant along with simply doing public of queries inside a loop awaiting new results.
Should you give a TRIGGER towards the table(s) you are thinking about, you should use that to alert the watching application. You could do this that in many ways:
- Create an audit table within the database, and also have the trigger write the appropriate info there and also have your watching application poll the audit table for brand new records. You are still polling, however in a controlled way which will not hit the server way too hard.
- Possess the trigger call an external app via a UDF.
So far as MyISAM tables go you can view
information_schema.TABLES.UPDATE_TIME. That will help you save from polling all tables you are thinking about. For InnoDB, watching binlog is the greatest I'm able to think about.
Another approach would be to perform a push/signal rather than a DB poll. Have whatever process updates the database, inform your Perl code that the update ended via any IPC you choose (heck, a log file being appended to with title of table being transformed might have the desired effect good enough).
This is particularly effective when the updates a reasonably rare/low volume the reaction time for you to them should be quick.
Another advantage is portability - works best for any MySQL after sales or every other DB engine.