MySQL & SAN

[rotis 23]

Hi All,

We have a Java web app on a collection of load balanced web servers with reasonably heavy writes that will talk to dedicated database servers using MySQL 5.1 and InnoDB engine. Each customer will have it's own logical database (I know this is not the best design decision but clients were unmovable on this for data protection/privacy reasons).

Our idea is to use the 2 database servers as failover to each other and to share the logical databases for load purposes. This introduces some interesting questions about how to configure the servers as master for some and slave for other logical databases.

We have a good quality fibre connected SAN (HP EVA series) already available. So, a couple questions:

1) What is the best method of using replication for failover in this instance? I'm assuming the SAN doesn't give us much here due to the risk of data corruptionpost failure. Does anyone have any idea/experience of the reliability of SANs and InnoDB engines after a failure? Can we just store the binary log on a different SAN partition/LUN without explicitly using a slave?

2) Does anyone have any experience of the snapshot functionality that this type of SAN provides? Can this be used for periodic (every hour?) backups? I understand it can lock the filesystem - is that safe/feasible for InnoDB? I think that we will replicate to an off-site backup server using master-slave replication anyway.

3) Should we just forget about the SAN and use MySQL Cluster or DRBD?

Thanks for any pointers on this - we going to do some testing on this but just wondered if anyone has any recommendations with this setup.

rotis23