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.