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?
street9009IT Project ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
You could use triggers, but which way are you chaining?

Db1
Db2
Db3
Insert, update, delete on db1 updates db2 and/or db3


Using mysqldump/restore  or replaying a binary log..
street9009IT Project ManagerAuthor Commented:
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.
arnoldCommented:
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.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

street9009IT Project ManagerAuthor Commented:
Can you elaborate a little? That all went right over my head.
arnoldCommented:
arnoldCommented:
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.
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
street9009IT Project ManagerAuthor Commented:
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?
Tomas Helgi JohannssonCommented:
Hi!

You could have one database hold the actual data and federate the tables to other databases.
That way you have the ability to read/write to the tables in all databases while the data resides in just one database minimizing the footprint of the data. This would be the optimal solution if your databases are all on the same server/instance and are different except those few tables that need to be in sync.

https://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html
https://dev.mysql.com/doc/refman/5.7/en/federated-create-server.html

Regards,
     Tomas Helgi

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
street9009IT Project ManagerAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.