PHP track recent updates to MySQL database to push to Rails app?

I have a MySQL 5 database that is updated every 5 minutes from a proprietary database of which I have no control over (in terms of programming or database access). The MySQL database is a mirror of this proprietary database, and the proprietary database is in charge of the update procedure (meaning, I can't add a hook when the update process is called).

I have another, completely separate Rails app that I need to import records from the external MySQL database (the Rails app is powered by PostgreSQL and it hosted remotely).

So... pardoning the extreme inefficiency of this setup (it's a system in transition), what I need is this: a PHP script that can either poll or be called whenever there is an update to the MySQL database (whether that update is updating information on a record, a new record created, or an existing record deleted, etc.), then push that information (in JSON) via POST request to the Rails app.

I already have a PHP script written that can pull the data from the MySQL and parse it for my Rails app, but what I don't know is how to (a) get the script to poll or be called whenever an update is made, or (b) how to tell it to find only the most recent changes (since it last polled, keeping in mind that more than one record may have been开发者_StackOverflow altered/created/deleted).

Can anyone advise on this?

As a corollary question, if anyone knows how to skip PHP and do this same procedure directly from Rails to an external MySQL database, I'd (obviously) prefer that method. In the interest of getting at least one workable solution, though, I'll happily accept either a Rails or PHP solution.

Here's one approach that does it all in Rails:

Set up a cron job that fires every five minutes. It either runs a rake task or curl's a certain URL in your Rails app (ideally including a security token, just for good measure). I prefer the latter--it's simpler and doesn't start a new process.

The action triggered by the cron job can look at the created_at column in every table and determine what might be new. For the potentially new records--as determined by created_at--check if they actually are new, and if they are, write them to your PostgreSQL DB. There are two ways to check if the records are new:

1. If the primary keys always match up between MySQL and PostgreSQL, you can just try to save the record, setting the primary key explicitly. If the record has already been saved, you'll get an exception, which you can then catch.

2. If the keys aren't guaranteed to match, you can check against some other unique value. Or, if there is no unique value you can use, you can have a separate table that tells you which records have been copied over. Old records in that table could be cleared periodically, so it wouldn't waste much space.

Do all this in models, not the controller, of course. If you don't have created_at columns, add them, and make sure they get written when your periodic updates happen, most likely by setting default values in MySQL.

Getting your Rails app to connect to the MySQL DB shouldn't be too hard. Here's some info on that:

http://pullmonkey.com/2008/4/21/ruby-on-rails-multiple-database-connections/