Jump to content

connecting to two mysql servers simulanteously


klb

Recommended Posts

we had our db server crash (again) and lost some data in the process, and i was thinking for the new system we're making, why not write simultaneously to two different mysql db's on two different servers?  that way, if one crashed, we'd have a real time backup that we could move over with very little downtime.  is it possible to have two open connections to two databases and then tell mysql which connection to use for which insert statement?  and is there a server it would default to so i wouldn't have to declare which server to get all my select statements from?  thanks for any insight ....

What you're looking for is mostly built into mysql: database replication.  Set your currenct db as the master and a new db as the slave.  With full replication the database will always have an exact duplicate.

 

As for directing queries, it can get quite complicated.  (Here's a simple writeup on programming with replication.)  One option for backups is to set up a load balancer which can automatically switch all connections permanently to the slave if the master stops responding.  That's a simple situation, handled elegantly, with no change to application code.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.