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?


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
    INSERT INTO db2.table(field1,field2, ...) values (db1.table.field1,db1.table.field2, ...);

similar for UPDATE and DELETE


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.

  • can you replicate within the same server, the documentation i've been reading seems to imply that you can't. I've never done replication before, so this is all new to me – user46857 Jun 25 '10 at 23:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy