I have a databases in a remote machines. Assume db1 at http://example.com . I mean this database is not on my local machine but I can connect that by a URL.
I want whenever some updates happen on db1.table1 a trigger calls a function in a JAVA Application on my local machine to do some processing.
I found a solution here but it needs to have the java function in a same machine that you have the MySQL server because of this line:
SET result = sys_exec('/path/to/javabin -jar your.jar');
How can I give the IP address of my machine instead of path in the lib_mysqludf_sys library or some other library like this?
Db server gets a modification to table. Let's assume you have php wrapper and you know from the call that the insert/update succeeded at the tranaction level, so use php/jms combo to add to the jms queue (activemq etc same thing). If it is not php but rather db server side java middleware that is handling insert/update, same deal.
Now on your machine (not the db server) u have your java app running constantly and it is peeking at the remote jms
You can do it with sockets too of course.
I would use triggers as a last resort to avoid rollbacks after as they said, you are trying to call back the missile (that is, your local machine could be at times processing data related to a rollback). These are generalities (the rollbacks) that may not be relevant in a narrow scope that you are facing
Furthermore not a big fan of trigger/udf/exec. So i lean toward queues