Get DBs to talk

conorgriff

Hey guys, I want to get several DBs to talk so that updates/inserts to a table on one of the remote DBs will update a specific table on the main DB. The Dbs will be on seperate hosts and I'm not too sure what will be required to accomplish this. Could anyone give me an idea of what is involved and where I could learn more about how to do it? I'll probably be using MySQL.
Thanks in advance, Conor

sh_o

Take a look here
Note that the results or performance can depend on your volume of transactions and also the speed of your connection between the two databases

Tom Dunne

conorgriff wrote:

The Dbs will be on seperate hosts and I'm not too sure what will be required to accomplish this.

Sounds like replication is what you want. But where are the hosts located? Are they on the same network or located at different sites?

conorgriff

tom dunne wrote:

Sounds like replication is what you want. But where are the hosts located? Are they on the same network or located at different sites?

Well in actual fact I want a main database which can have one of it's tables updated by several other dbs located either locally or in remote locations. Looking briefly at the replication link provided by sh_o it seems that using that method only allows one master db to update tables in multiple slave dbs, I want it to function the other way round.

Ginger

use trasnactional replication.. One master will recieve updates and publish updates to each other

Having done this on SQL Server with 25 servers around the country I know it works.. you just need to make sure you set up your schedules correctly and especially if you have a large volume of transactions

amen

well there are numerous ways to do this using replication or writting your own method all fraught with danger and fun.
Before even starting this you need to consider a few items
1: Will the Master always be updated by the slaves?
2: Will the slaves ever be udpated by the master (hope so)
3: what happens if Slave A updates Master with a change for record B then
Salve B udpates Master with change for record B? Which slave is right?
4: What happens if a slave doesn't get a chance to update master (slave can't connect etc) Do all updates get rolled back? do you continue?
5: Do the slaves have to update in order?
6: How do you identify rows to be updated?

You could write a proces on Master to contact all the slaves in returns get the modified data and update the master. That might be the easiest way to do it.

bonkey

Ginger wrote:

use trasnactional replication.. One master will recieve updates and publish updates to each other

Having done this on SQL Server with 25 servers around the country I know it works.. you just need to make sure you set up your schedules correctly and especially if you have a large volume of transactions

Just to clarify on something that Ginger is saying here...

The type of functionality that is described is supported in MSSQL. Whether or not it is supported in MySQL is an entirely seperate question.

Replication is a hard problem. Well, it can be hard. Some replication stuff is easy. Some is nightmarish. The devil, as they say, is in the detail.

Best o' luck with it.

Ginger

Yup as Bonkey said MS SQL has replication support built in and its not too difficult to set up... :P

Not too sure about MySQL (must really get around to looking at that someday)

sh_o

conorgriff wrote:

Well in actual fact I want a main database which can have one of it's tables updated by several other dbs located either locally or in remote locations. Looking briefly at the replication link provided by sh_o it seems that using that method only allows one master db to update tables in multiple slave dbs, I want it to function the other way round.

The replication link I posted also deals with 'multiple master' which sounds to me to be what you are looking for. The details of implementing it for a multiple master set up are not terribly well explained there though.

Take a look at:
http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html

Or google :-)
multiple master replication mysql

cousin_borat

Hows it going. Just wondering if these Multiple DBs are Heterogeneous. If that is the case I would investigate building a data services layer as a abstraction above the various different DB's.

I'm currently working on a similar project. Oracle HR which is the system of record for Employee details. However many other systems (which sit on top of multiple databases including MySQL and DB2) use this information too.

Any updates to the Oracle HR system are automatically propogated to the other systems ensuring everything is in synch. We're using BEA Data Services Platform but there is an open source project to offer this functionality. Ill get back to you on it if you want

conorgriff

Hey guys, thanks for all the great help. You've all had really great suggestions and it's clear I lack quite a bit of knowledge. I think some of the solutions are a little complex for my purposes since it's not going to be a business system or of a critical nature at all. The easiest way to explain the function I'm trying to achieve is using an simple example.

Lets say I have a main database called MAINDB. MAINDB has a table which could be like a list of employees for a company for example. Then there are many other DBs, one for each Department lets say. These maintain an employee table for their own section. I want to achieve the goal of allowing inserts/updates to the department employee tables being reflected in the employee table of MAINDB so effectively the employee table on MAINDB is just an amalgamation of data from all the other DBs. I'm looking for a relatively simple way of implementing this functionality and it could be using postgres or mysql. I will look at the suggestions you guys have made but if this explanation turns on any light-bulbs please share your ideas. Thanks so much, Conor

amen

personally I think you are approaching this the wrong way around.
using your example every employee would belong to the company first and the dept second so I push from the MainDB to the DEPTDB
that said if every DEPDB is unique then you could run a job every night that pushs the data from the DEPDBs to the MainDB (everything that changes)

How would you handle uniqueness for the EmployeeID ? You could have the ID 1 used in DeptA and ID 1 in DeptB but what happens when it gets to MainDB?

Gets more interesting if you have subsequent updates on ID1 in DeptA that have to back to MainDB (someones name spelt incorrectly)

And are these DBs in different physical locations?
Is there a reason why there are multiple DBS?
would now be a good time to do a DB tidy up?

e

conorgriff

I was using the example to demonstrate the functionality really, so it's not employee details I'm storing. The remote DBs are managed by other people, but I want my MAINDB which I will administer to have a table which holds all records from a particular table on the other DBs. Hence the reason I don't want to manage the table from MAINDB, the only commonality between my MAINDB and the remote DBs is the particular records stored in this table. The MAINDB table is just a method of centralising data from the tables in the other DBs, but only the remote DBs can modify that data. Each record on each remote DB will have a primary key and will then be identified on MAINDB with a composite key which will consist of the remote primary key paired with an identifier for which DB it came from. If I'm still going about this ar$eways please tell me

Ultimately this table on MAINDB is to be used on my website while allowing the owners of the remote dbs to affect what is shown on the site by modifying their own data..