Suggest me solution to track the change in test DB and replicate in Another DB

Question

Suggest me solution to track the change in test DB and replicate in Another DB...

My Client need a script or any solution, if he has two Database, One Test DB in which he tests his data on test portal and if he find it appropriate he can use those changes to be done in main DB to display on Live site.. Fior this he needs the solution to record or track all updation/deletion/insertion, so that he can do the same in main DB if found appropriate,

NOTE:

we have only one server, no separate server, hence binary log replication doesnt seems to be working for my case...

Answer 1

Assuming that both test and production databases can evolve independently during the test period, I'd say a good starting point for thinking about a solution would be to activate query logging on the test database then, at the end of the day, extract ALTER, CREATE, DROP, INSERT, UPDATE, DELETE etc. statements from the test database query log file and simply try to play those statements back on the production database, possibly separating DDL and DML (with the DML in one transaction.)

If any of your tables have auto-increment columns used as foreign keys in other tables, use an AFTER trigger to capture actual auto-increment values for INSERT and UPDATE statements and write a corresponding SQL statement to a separate file for production playback; you may still use query logging to capture DDL statements (and DELETEs, although they can be captured via trigger too.)

Unlike binary logs, query logs (or equivalent logs produced from a trigger) are clear-text and allow you to further manually tweak queries to resolve any potential conflicts or divergences between the production and test systems.

Answer 2

Does http://adamspiers.org/computing/mysqldiff/ help?