How can I integrate data on regular bases between 2 different MySQL Servers?

Question

I currently have 2 MySQL Serve running on a different machines. Once of them is a staging environment (A) and another is a production environment (B). What I need to do is to take data from (A) and update/insert into B based on the conditions. If MySQL had Linked option then I can simply create a stored procedure that does the work for me and that would solve my whole problem. Unfortunately a great product like MySQL does not have this necessary future.

But since I can't write a procedure to do that what application I can use that will do the integration for me? note this integration will need to be automatic so it can be done daily and sometimes hourly.

My question is there an integration application out there that will integrate data from on MySQL Server to another automatically?

Thanks

Answer 1

I'm not a MySQL guy, and I don't know if it has a Linked/Federated option or not, but I understand it has very good replication. You could replicate tables from A into copy tables in B, then put your SP on B and run it whenever you want.

You could also write an application/batch script/ETL job that transfers the data and applies the conditions and just run it from a scheduler. If you do this frequently (meaning you do many other processes like this), I'd lean toward an ETL tool. I use Pentaho Data Integration. There are several others.