We have an app with some peculiar characteristics that prevent us from doing master-slave MySQL replication. It's something to do with random numbers being generated for each session and stored procedures.
We plan on fixing the app next year but in the meantime we need to make our MySQL machines "highly available" so what options do we have? Would a plain RH cluster work? We run MySQL mysql-server-5.0.45-7.el5.x86_64.
Thanks for any advice.
I cannot think of any scenario in which you could not setup replication to produce the same data set as on the master with proper architecture. In short, it is still probably an option.
You could setup two servers with DRBD replicating the data on the backend. For failover, use Linux-HA. (heartbeat/pacemaker) This will be an active/passive configuration that will not failover as quickly as dual master without shared storage but data integrity is better guaranteed.
MySQL has written a white paper on this type of architecture called MySQL and DRBD High Availability Architectures.
The NDB engine, which is used by MySQL Cluster can also enable a high availability architecture. However, the engine has specific limitations, which need to be considered before using. It is not ideal for a transaction based load, such as that which is commonly used with Web applications.
The general consensus from the professional MySQL consultants I've met is, "If you think you need NDB, you probably don't".
+1 for DRBD and Heartbeat - it does the job fairly well and has saved my tail in production more than once. The question is, will your app support failing over to another database server, or do you need to set up some sort of CARP-like shared IP between the db hosts?
Not to overstep my bounds, but have you considered a NoSQL solution, like CouchDB for your application re-write? Replication on CouchDB works like a champ, and Master-Master (N-Master, even) is often only a matter of minutes to configure. Food for thought.
