Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two databases, each on a separate server. Each database has a number of similar but not identical tables. I need to combine the records from these similar tables into a new database, which is on one of the two servers.

SERVER A - Database 1

SERVER B - Database 2, Database 3 (new)

My thinking (as a relative novice re MySQL) is to first Insert existing records into the tables of the combined database, then use Insert|Update|Delete Triggers to maintain those records in the new db.

This would allow me to start with one roughly matching table from each of the two original databases, and gradually go through all the rest of the tables.

Is it possible to create Triggers on Database 1, which updates a table in Database 3 (new), which resides on a different server?

Any suggestions would be most welcome on how to accomplish this.

share|improve this question

migrated from stackoverflow.com Aug 6 '12 at 21:04

This question came from our site for professional and enthusiast programmers.

    
Are you familiar with INSERT INTO your_table (...) SELECT ... syntax? –  OMG Ponies Aug 5 '12 at 22:13

3 Answers 3

Thanks Shlomi, KayKay... looks like I'd be better off searching for a different solution. I may be able to move Database 1 from Server A, to Server B. This would make it easier to create the triggers - if I go down that path at all, considering the performance hit you pointed out. Cheers!

share|improve this answer

There's the FEDERATED storage engine, which makes for a way to create a "proxy" table in your database server, which points to a real table on another server.

FEDERATED is disabled by default. To enable it, add this to your my.cnf configuration file, under the [mysqld] section:

federated = 1

So your trigger on some table T would then INSERT into your FEDERATED table FT, which would connect to the remote server, pass credentials, and perform the operation.

Do note the overhead: triggers are by themselves performance killers. Add to that the connection to the remote machine, and you now also have latency to worry about.

share|improve this answer

I don't think it is possible, especially on different servers, SGDB are not meant to connect to each other. But even if it was, that would not probably be the right way to go. If tables are not in the same database then they should not be dependent over one another.

You would be better off using either :

  • a batch that will continuously read the first 2 tables to update the 3rd,
  • or directly update it from the code that populates the first 2.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.