How do I sync a subset of tables between two databases on the same mysql database server

Question

would like to be able to sync a subset of tables between two mysql databases that are running on the same server. One of the databases acts as the master where inserts, updates and deletes can be made. The second database uses those same tables for read-only operations. I do not want to use federated tables to achieve this. The long term goal will be to separate the 2 databases to multiple servers, The second database that has the subset of tables as read-only may also be replicated a few times over to distribute geographically for load and performance purposes each with unqiue data.... Once that is achieved, I plan to use binlog to replicate those specific tables on the secondary databases. In the meantime, I'd like to keep these tables in sync. Is there a more elegant way to do this than other than using a cronjob and mysqldump?

Answer 1

If your end goal is to separate the servers then I would suggest running a separate instance on a different port and using standard replicaton between the two

quick google turns out this

alternatively you can use triggers to replicate, something along the lines of

CREATE TRIGGER replicatetable AFTER INSERT ON db1.table FOR EACH ROW BEGIN INSERT INTO db2.table(field1,field2, ...) values (db1.table.field1,db1.table.field2, ...); END; 

similar for UPDATE and DELETE

Answer 2

Depending on if your version MySQL 5.1+ I would suggestion Replication. Especially considering the fact that you plan to have a cluster down the road.