Mysql – How to sync a subset of tables between two databases on the same thesql database server

databaseMySQLmysql-replication

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?

Best Answer

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

Related Topic