Ajay Sharma wrote:
Marc Perkel wrote:
OK - here's what I want to do. I'd like to have more than one server
that is all feeding into the same database. And I want the databases
set up in a way that if the main server goes dow that the other
servers run locally. I don't need every update to have to make it to
every database in the event of any downtime. I just need it to work
great when everything is up and pretty good when some servers are not
running.
So - what are the MySQL options that people are using with multiple
servers?
What I'm thinking I'd like to do is have all the spam filter servers
run off of a main server and that server replicate to the satalite
servers and that if the main server goes down then the other servers
run on their own. I also would accept once a day copying the mysql
database from the main server to the other servers at night so if the
main server went down the other servers could run off of day old data.
How does everyone else deal with this?
Have you looked into MySQL replication?
http://dev.mysql.com/doc/refman/5.0/en/replication.html
I have a master/slave setup and the slave is there for two reasons:
1) nightly dumps
2) emergency backup.
So every night, the database is locked and we do a full mysqldump on
every database. Once the lock is removed, the slave sync's back up
with the master.
If the master dies then we can switch all of our machines to start
using the slave until the master comes back online. It's not
automatic, but it won't take too long.
I know this kind of things is possible on the DNS (IP address) level.
Do you have a decent firewall? These often have the ability inbuilt to
change DNS records in the event of a failure. If not you can set up a
"heartbeat" server(s) which will run a script to update the firewall if
a ping times out.
set up your servers as:
mysql1.domain.com
mysql2.domain.com
etc
Then you can use DNS to redirect queries (set mysql1.domain.com to point
to 127.0.0.1) until the primary server comes back up again.
Downtime can be set to as little as a minute (30 second heartbeat, must
fail twice to switch). Make sure your TTL is low though or the DNS
response will be cached!
Daniel
