Is your application a web based app?
The best way is to use a loadbalancer or DNS round robin to distribute the requests around several servers for your application. Then have each application connect to a "local" SQL node.
If you use a loadbalancer and a clever health-check (a simple PHP script that checks that it can connect to the MySQL server) then you will get total fault tollerance as well because as soon as one node (either apache, the script or the MySQL API node) goes down, it will be removed from the pool serving requests.
There are MySQL loadbalancers available but I have never used one. You could use DNS round robin with failover (try zoneedit.com) and then just point your application at the DNS name. This will have a slower failover time than using a loadbalancer but will still work.
Alex
Alex Davies
http://www.davz.net |
alex@davz.net