Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

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).

share|improve this answer
    
Thanks for the answer -- and you're correct saying that running Linux is "out of my hands". –  Herisson Nov 22 '09 at 13:04
add comment

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

share|improve this answer
    
You're correct, unless he runs a clustered file system he'll either corrupt his volume or find it doesn't work as the file/s will be locked. –  Chopper3 Nov 22 '09 at 10:48
1  
Regarding "already open file" - The way I think is that out of the 2 computers, only 1 will have MySQL running and only that one will access the db file. If that computer fails, the other one will detect it and start the MySQL service. So at any point of time, only a single MySQL service will be running and will access the db fle. Would there be an issue in that case? –  Herisson Nov 22 '09 at 12:55
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.