Updating Multiple Databases without Remote Access

sarah

More than anything, I am looking for Best Practices. I can think of some Amish ways of accomplishing this, but I'd like some other ideas.

Basically, there is a situation where people need to make changes to several MySQL databases on different servers at the same time without remote access. I can create and modify scripts on the individual servers, but I cannot access a database on one server from a different server. (No remote tcp-ip access). I do not have permission to modify the server configuration.

Your ideas would be appreciated, and as always...thanks.

maxpup979

Now thats an ugly problem. The easy answer is just have a couple of connect strings to the different servers, and run the appropriate queries across them. This however is hardly a best practice (damn ugly if ya ask me) The problem that you are going to run into is that you have no transactional integrity across multiple servers. for example, you run 3 updates, 2 work, one fails. Do you roll back the other 2?
I would think that the best practice would be to establish your own distinct transaction log that allowed to you keep track of success/failures, and handled them appropriately. If ya want more info, go into the DB forum and ask Sxooter.

Sxooter

Thought I'd chime in here and mention that best practices kinda depend on what exactly you're goals are and how you have to accomplish them.

Is this a situation where you have three servers around the world, and it's better to have two get the update and let the third go stale when it can't process the query?

Or is this the kind of setup where either all the database need to change or none should get it?

Should you look into a message queue type system? MQs are programmable to tell them to handle different errors different ways, and report to some admin the ones they can't. So, what you are really doing is writing a message queue app of some kind.

To do a simple all or nothing approach, you could just go ahead and issue the inserts to each database, check to see if one fails, and roll back the others. If you're on something like postgresql, you can leave the transaction uncommitted on all three, then if one fails, roll them all back and have transactional integrity.

Clustering might be a better answer here.

sarah

Sorry for the delay and thanks for your responses. I get the impression from both of you that it is possible to accomplish this from a single interface... How would I do that without remote access? Have a processing page on each server and use one interface to send the query to each processing page?

Sxooter

I'd do this with the CURL library and some kind of simple web-sql interface then.

CURL lets you open URLs like they were a file, and it allows you to do things like set headers to send and such (i.e. post headers and all) and greatly simplifies this stuff compared to doing it with opening sockets to port 80 by hand.

sarah

Could your recommend a good CURL resource?

Sxooter

http://curl.haxx.se/ has the package and some pretty good docs. Can you install packages on the main PHP/Apache server? if so then Curl should be the shizznazznizznuts... To quote Snoop Dog. 🆒

If that's beyond the realm of possibilities due to lack of ability to change the PHP server, then look at whether you can use fopen URL wrappers, which most PHP boxen are setup to use. Then you can do something like:

$p = fopen("http://server.dom.com/sql.php?query=<URL ENCODED QUERY GOES HERE>");