MySQL Clustering

[bwwilliams]

I've been reading that MySQL clustering using shared disk is a bad thing. Some of what I have read referrs specifically to NFS mounts being bad. I was considering using a SCSI attached array shared between two servers for storing the databases. Is this considered to be a no-no?

 

[ericbrunson]

I can forsee that being very, very bad.

I'd recommend replication as the best solution for distributing reads. All writes need to be done on the master.

 

[bwwilliams]

So how does one typically handle the situation if the master goes down? Doesn't that take away the ability to write to the databse?

 

[ericbrunson]

Yes, it does. I have a process that monitors the master and if it stops responding moves the IP of the master to the backup and stops replication. Then, syncing back up becomes a big old headache, but at least the DB stays up.

 

[bwwilliams]

The headache is what I am trying to avoid. I thought about two way mirroring, but that's another headache waiting to happen.

 

[ericbrunson]

I'll see if anyone else has a better recommendation, I'd love to see it.

 

[bwwilliams]

Thanks for the info. Sounds like I have my work cut out for me regardless.

 

[Bastien]

Pick up the High Performance MySQL book. Talks specifically about this.

Our solution is to use 4 machines (due to load)

2 masters (1 online and 1 backup)

2 slaves for replication and report queries.

The two masters are connected via heartbeat and the second will take over in the event of the main server failure. The other two are slaved to both masters and will accept data from either. On recovery, one of the slaves will be promoted to standby master and the downed master becomes the slave (happens at slow time). Then the master has time to get caught up with any missing data.

No data loss for replication

Bastien

Cat, the other other white meat

 

[bwwilliams]

I'm assuming you use a virtual IP for your masters. Is there a way to prevent the backup master from accepting inserts unless the primary is offline?

 

[Bastien]

We use the app server to point to the IP of the main master server. When a failure occurs the IP of the backup is rewritten to be the IP of the main server.

Here is a link to a good article about it.

Bastien

Cat, the other other white meat

 

[bwwilliams]

This is one of the ideas I pitched as a solution, and management didn't like that either server could be used to write to the database, even if we give the developers a virtual IP that floats between the servers. I see this as being the ideal solution, so I'll try to get buyoff again. Thanks for the help.

 

[lgarner]

I'm looking at this exact scenario also. We currently have master-slave replication, and I'm thinking of using mon or something like it to move a virtual IP. The only issue is that I have to ensure that the IP doesn't move back to the "master" after an outage, so that all writes are done to the slave until data can be properly replicated again.

Or, I can use master-master replication (I have the HP MySql book- very good). My only concern is that I have been told that master-master isn't terribly reliable. Any thoughts? If it's stable, then I think that's the way to go for the most seamless failover.

Thanks.

 

[bwwilliams]

I set this up as a master-master system with IP failover. Both servers are more than capable of doing inserts and selects, but I'm giving the virtual IP to our developers so they can only talk to the acting master. I'm using the heartbeat utility to do the IP failover, and you can set whether it fails back automatically when the primary server comes back online.