MySQL - Keep several tables (same structures) in complete sync (Inserts, Updates, Deletes) in multiple databases on the same server
If I have three MySQL databases, residing on the same server, that have the same tables in them (same structure), is it possible to keep several of them in complete sync (any inserts, updates, deletes)? In other words, I would want to be able to load any of the three databases and see the exact same data in the equivalent tables (DatabaseA.Table1 = DatabaseB.Table1 = DatabaseC.Table1). How can this be done?
ASKER
That's the problem, any of those three events could occur on any of the three databases and I would need them all to be run in each of the other two as well, to keep them in sync. Is that a possibility? It would need to be real time as well.
Commonly, one would setup a master master replication (circular repkication)
You could achieve it on a single node through the use of mysql_multi (have three distinct MySQL instances)
Though it would commonly mean each instance has the same named dn.
potentially reviewing the setup, and the goals of your setup.
You could achieve it on a single node through the use of mysql_multi (have three distinct MySQL instances)
Though it would commonly mean each instance has the same named dn.
potentially reviewing the setup, and the goals of your setup.
ASKER
Can you elaborate a little? That all went right over my head.
I can not reason why you have three see-identical databases used by different application where you now have to try to synchronize the data among them.
It's like hiring three writers to write a book on the same theme but each book has a different title.
It is simpler to hire one author to create the story and then create three different book covers with different titles.
It's like hiring three writers to write a book on the same theme but each book has a different title.
It is simpler to hire one author to create the story and then create three different book covers with different titles.
What arnold said.
Having three identical databases with cross triggers or replication will likely slow your system to a crawl.
Far better (faster i/o) to have one database with all tables.
Or maybe use material views... likely overkill... Having all tables in one database is far better.
Having three identical databases with cross triggers or replication will likely slow your system to a crawl.
Far better (faster i/o) to have one database with all tables.
Or maybe use material views... likely overkill... Having all tables in one database is far better.
ASKER
I agree, it would be far better, but not an option in this case. The databases don't see a lot of writes to these tables (just reads which would obviously be from their local copy) so I don't think it'll hurt performance too badly. I'm not synchronizing the entire database, either, only a select few tables.
Can it be done and if so, how?
Can it be done and if so, how?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I like this answer. It's going to take me some time to figure out how to set up a FEDERATED table (or even enable the functionality) so I don't want to hold the question open while I go through that process. But from the way it sounds, it's exactly what I need.
Db1
Db2
Db3
Insert, update, delete on db1 updates db2 and/or db3
Using mysqldump/restore or replaying a binary log..