Search code examples
mysqltriggersreplication

MySQL - Trigger or Replication is better?

I want to replicate certain table from one database into another database in the same server. This tables contain exactly the same fields.

I was considering to use MySQL Replication to replicate that table but some people said that it will increase IO so i find another way to create 3 Trigger (Insert, update and Delete) that will perform exactly the same thing like what i expect.

My Question is, which way is better? Is it using MySQL replication is better even though it's in the same server or using Trigger to replicate the data is better.

Thanks.

Solution

  • Assuming you have two databases on the same server i.e DB1 and DB2 and the table is called tbl1 and it is sitting in DB1 you can query the table like this:

    USE DB1;
SELECT * FROM tbl1;

USE DB2;
SELECT * FROM DB1.tbl1;

    This way you wont need to copy the data and worry about extra space and extra code. You can query a table in another database on the same server. Replication and triggers are not your answer here. You could also create a view to encapsulate the SQL statement.