Looking for suggestions on how to do this

[EdLentz]

I have a mysql query that I want to take the result of and upload/insert it into a different Mysql server. This would be once a day and over the internet. I will have many PCs sending the same info to a commn Mysql DB. I can setup ssh access. Just not sure how to get the info across. Is there a way to connect the MySql DB together and then run a query? I just don't know. Any suggestions are much appreciated.

Thanks

 

[Andrzejek]

Here are some suggestions about using a Database Link

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

As normally you connect to a MySQL server and query it, why try to do this inversely? If you have a central server, how about the idea to let that server query all the other servers instead of all servers reporting?

It's not just meant as a rhetorical question. Both ways are surely possible and have their pros and cons.

Chriss

 

[EdLentz]

Thanks Andrzejek I'll check that out.

Thanks Chris, hadn't thought of doing it from the central server to the others, makes more sense. Then I can manage the query activity better.

 

[Chris Miller]

I can think of reasons for both scenarios.

1. Each single server knwoing the central server may be simpler than the other way around, but then each server once connecting the the central makes itself known there.
2. If there is a time when one server is "done for the day" that makes it easier for it to report exactly then, as timely as possible, whereas the central server might not know.
3. Exactly the optimal timing of 2 is only optimal for the single server, not necessary for the overall system.

I can also give better advice if knowing the general situation better. I have worked no POS systems that report most any single sale/receipt to a central server or bundle this as closing data.

The most general idea of a database system, no matter if just one central server or a distributed system that it's there for all clients and kept up to date mutually at all times, so any client can know within rules of what data it's allowed to see or manipulate, so that you don'T have some kind of hording data and data exchange as closing task for a day, but continuous exchange.

What you want I'd usually subsumize into the topic of data replication. Which in the end is just a special configuration between several servers that acts on itself from then onwards and needs no specific programming about what data is queried and goes where. In fact, it does do a mixture of "reporting" and "querying", under the overall simpler task to keep all data available in several places.

Chriss