Updating multiple databases at the same time

Yanni Mac wrote:

I have an application that is load balanced. I have a master database which I update once a day. Then I push the raw mysql files to all other servers so they are the same as the master. This works fine, but there are a few situations where I need all databases to update in real-time. What would be the best way to achieve this in rails?

Here is an example of what I am trying to do. I want to update the name of a product on my website. Here is the controller:

def update_product_name   product = Product.find(params[:id])   product.name = params[:name]   product.save   #Say I want to save this to 3 other databases   #can I do this with activerecord?   #PSUEDO CODE... I know it cant do this   product.save(mysql_host2)   product.save(mysql_host3)   product.save(mysql_host4) end

Any ideas? Alternative ways to do this?   

You could use MySQL's built-in replication support.

Jack Christensen wrote:

Yanni Mac wrote:

I have an application that is load balanced. I have a master database which I update once a day. Then I push the raw mysql files to all other servers so they are the same as the master. This works fine, but there are a few situations where I need all databases to update in real-time. What would be the best way to achieve this in rails?

Here is an example of what I am trying to do. I want to update the name of a product on my website. Here is the controller:

def update_product_name   product = Product.find(params[:id])   product.name = params[:name]   product.save   #Say I want to save this to 3 other databases   #can I do this with activerecord?   #PSUEDO CODE... I know it cant do this   product.save(mysql_host2)   product.save(mysql_host3)   product.save(mysql_host4) end

Any ideas? Alternative ways to do this?   

You could use MySQL's built-in replication support.

  What if I want it on other SQL server?

davi

A "not so easy" way to solve this is to write your own database adapter, that would send the real SQL code to many different databases (your adapter would contain an array of other adapters that would receive the real SQL). That's how tools like C-JDBC do it.

But if you need this for MySQL only, you should try to use the database replication mechanism.

A "not so easy" way to solve this is to write your own database adapter, that would send the real SQL code to many different databases (your adapter would contain an array of other adapters that would receive the real SQL). That's how tools like C-JDBC do it.

Doesn't that boil down to reinventing replication ?

Fred

The problem is that I don't want to replicate everything. The way I understand it, and correct me if I am wrong b/c I have never set up master/slave replication in MySQL, every time there is a insert/update/delete to the database, it will replicate to the others automatically in real-time. I don't want this to happen, because I am running a script that updates 16 million rows and takes 20 hours to complete... replication would affect performance on the live slaves. In parallel to this script running I am making live changes to the data in my app via a web interface. These changes (small portion compared to the script running) will need to write to all 4 databases at the same time. I am assuming mysql replication is all or none and can't do this... meaning replicate some times and not others??