Multi Server Query

[AdamField]

Hey Guys,

I'm stuck on this problem and hoping somebody can give me a step into the good direction on it.

First i'll try to give you all a view on the situation

Mysql Server1 (lets call him Webserver)
Mysql Server2 (lets call that one LocalHost)

Now i'm working with Navicat so that's my gui for doing this

In my LocalHost i have a Table called stuff_to_update with 3 colum's (number, stock, BO )
On my webserver i have a Table called article with the same colum's (and some more)

My goal is to make 1 Query that connect's to both the servers (remember navicat) and that update's both the stock and the BO value on the webserver linking the 2 on the number value (unique key).

So far i can't get navicat to connect in the query builder to table's in 2 different server nor am i shure that a simple UPDATE will do the job.

All help would be appriciated and if somebody knows another program that could do this (and that can be sheduled, the query has to run 4 times every hour) it would make my day.

Tnx already for the feedback !!

Adam

 

[r937]

copy stuff_to_update table from localhost into your webserver database

then the query is easy

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[AdamField]

Hey R937,

You mean split up the query in 2 and make

1: a Copy query that copy's the full table to the webserver
2: update the stuff_to_update into the article table
3: truncate the stuff_to_update on the webserver

Tnx for the advice already

Adam

 

[DonQuichote]

What do you want exactly? Is this how an application should connect? If so, just use two connections.

Is this a one-time migration or synchronization? If so, use navicat. Maybe create a dump form one table and read in the dump on the other server.

If you really want the servers to talk to each other, at least one of them should be able to contact the other (your "localhost" can probably contact the web server). Look for the "federated" database engine in the documentation.

Alternatively, you could set up replication, but that is a heavy answer to a probably too simple problem.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[ingresman]

I would think as you need to do it every 4 hours you want a script based solution (I have no idea what navicat can or can't do).
The tool of choice would be PHP really (just because mysql and php sit together well and you can get some support here).
You could use C or any other language that supports the API.
You don't say what the client platform is?. If it's a *NIX you may as well stick with PHP, if it's windows you have a little more choice. You could create a VB.NET (or C#) program to the job or if you have MSAaccess you could link local table to the two back ends and then as far as Access knows the tables are local and you can do joins etc. Might not be amazingly quick but it should work.
Scheduling should be easy if you have a script you would use cron on *nix and at on windows.
Federation/distributed queries are not MYSQL strong suit.