How does everyone else handle this?

My Setup:
Server A:
CentOS 5.x
10.0.0.1
Apache
MySQL Master

Server B:
CentOS 5.x
10.0.0.2
Apache
MySQL Slave

My Domains:
dom1.com A record: 10.0.0.1
A Record: 10.0.0.2
dom2.com A record: 10.0.0.1
A Record: 10.0.0.2
.
.
.

Since you CANNOT write to the slave, and have master updated, I have heard that 2 way replication will cause problems. I have setup all writes to goto master (write.dom1.com) and reads goto slave (read.dom1.com) via hosts file on each server.

If Server A goes down Server B detects this and rewrited hosts file to (writes.dom1.com) to its self so everything works great.

But if the script on Server B acts to quickly and takes over writes and Server A starts responding now the databases are not consistant anymore.

I guess I could have the script that changes the hosts file to reverse the MASTER and SLAVE computers and reestablish the replication but thats a PITA.

Is there a better way to handle this?

Keep in mind I only have 2 Dual P4 servers. These are just my personal websites and if they go down its not really a big deal but I still would like to know how everyone else would handle this if they were 'somewhat' missioan critical with limited resources.

Thanks...

Solutions like Linux Heartbeat can do the job, too.

Our setup involves 150+ RH boxes. This is not just LAMP webservers but all of the MySQL apps servers - we are on a SAN so failing over for several unrelated apps can be relegated to one server, since if all the servers servers failed at once we would be in disaster mode, failing to a remote site.

You must have an I/O problem with your setup - relegating reads ands writes to separate boxes.

Heartbeast is one optionj I did find but im limited to these 2 servers.

The system I have is working OK, im just trying to find if there is a better solution for the MySQL redundancy than having scripts rewriting hosts files and switching MASTER and SLAVE MySQL servers. It just seem there has to be a better solution.

For what you want you need a disk server/linux cluster box of some kind, maybe with high availability raid.

Then, allow both servers to process read & write. When one fails the other is there by default. The disks are already there, mounted, served out by another box, so they do not have a failover problem.

This also solves the 'out of sync' problem.

This is almost exactly what everybody with a SAN is doing right now. Except for orders of magnitude lower cost. Our SAN costs us $US40 per MB; a PC with SATA can have a MB of raid for 2 orders of magnitude less. Circa $US1.00 - $US2.00 per MB.