What are the options for having multiple mysql servers with the same database and data. I know there is replication but I have been reading that it can have issues especially if there are connection issues. Are there any alternatives to the built in replication that are reliable? Someone suggested getting a server on the same network so the mysql server can be accessed by all the servers over lan, but currently that is not an option for me.
Is mysql just not meant for replication? Is there another sql engine that is better for it?
There are a few options for replicating a MySQL database. Here are a few no-cost options:
Tungsten and Linux HA both provide failover so that the master database will automatically move to another machine if the primary master fails. However, with DRBD, the second machine is typically passive, meaning that it doesn't serve any traffic at all, while with native MySQL or Tungsten you can use the slaves for reading data.
The obvious answer would be MySQL Cluster Manager, it costs though.