Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.