MySQL failover on Windows with SAN available

Question

My backoffice components are doing MySQL queries. I need to ensure High Availability of MySQL (on Windows). I have a SAN available where I will store the DB file.

This is what I was thinking.

I will install 2 Windows machines with MySQL pointing to the shared db file. One will be active, the other one passive (MySQL service stopped). What I basically need, is a hearbeat between the 2 machines and when one is down the other one will actually start the MySQL Service.

Is this the right approach? If so, are there any product/open source out there that can help me set up this environment?

If I am using 2 different machine in such an active/passive mode -- how would my clients connect? Do they have to change their connection strings (basically the IP address/Machine Name) based on the currently active machine.

Thanks

Answer 1

That's not a bad approach to the problem. I don't know what you would use on Windows to make this happen, but on Linux the software you would most likely use is actually called "Heartbeat". I'm fairly certain it doesn't run on Windows, but I'd be surprised if someone hadn't produced something similar (although it's likely to cost you a chunk). Personally, I'd save myself the cost and annoyance and use Linux to run my MySQL servers, but I expect that's out of your hands.

As far as making clients "failover", you would do this by providing a "virtual IP", which is different the ones permanently assigned to each machine, and the heartbeat service would make sure that whichever machine is currently running MySQL would also be configured to use the virtual IP for the service. You then configure clients to connect to that virtual IP, or a DNS name that points to that virtual IP, and hence the failover event is seamless to the clients (except that they have to reconnect).

Answer 2

I wouldn't use the same files for both databases. Actually if I recall NTFS will not let you do that because you can't access an already "open" file. It may work if you are exporting it over the network but in general this is not going to be a good idea.

I would suggest a floating IP address for the server, some sort of middleware that will move this IP to a different system (Or sets the IP/Enables the configured device on the secondary system if the primary goes down). Then I would setup either a Master/Slave or Master Master relationship. Depending on how your setup is a Master/Master setup may be something you don't want to do (Basically lets you write/insert on both primary and secondary servers and see changes on both databases). From what I initially read a straight Master Slave should be fine. Read more about replication here:

http://dev.mysql.com/doc/refman/5.0/en/replication.html

Answer 3

It sounds like you would like to try clustering MySQL using Microsoft Cluster Services (MSCS). From my quick searching I'm not sure how well MySQL works well with MSCS but I did find A Guide to MySQL for Microsoft Windows Server 2008 Failover Clustering.

MySQL has its own clustering but it appears that is only in development for Windows in version 7.