Options for Scaling MySQL Environments

Active/Passive Master Server with SAN

Provides H/A and generally acceptable performance
Requirements

• two servers of the same hardware.
• SAN for data storage

Downfalls

• SAN is typically not as fast as localdisk I/O performance

Active/Passive Master Server with DAS

Provides H/A with the best I/O performance
Requirements

• two servers of the same hardware.
• DAS for data storage

Downfalls

• DAS is more expensive than SAN or localdisk

Master/Slave Replication

Provides data redundancy and traffic distribution READS->SLAVE, WRITES->MASTER
Requirements

• two servers of the same hardware.
• Localdisk for data storage - can use SAN or DAS if localdisk is too small

Downfalls

• Does not provide H/A
• Requires manual promotion of Slave if Master fails

Active/Passive Master with Slave Replication

Provides data redundancy and traffic distribution READS->SLAVE, WRITES->MASTER and H/A capabilities
Requirements

• three servers of the same hardware.
• Localdisk for data storage on Slave - or can use SAN or DAS if localdisk is too small
• SAN or DAS required for Master - no localdisk
• Best option for data redundancy, traffic distribution, and H/A

Downfalls

• Most expensive option

NDB Engine Clustering

Provides data redundancy, automatic traffic distribution, and H/A capabilities
Requirements

• three servers of the same hardware.
• Localdisk for data storage - or can use SAN or DAS if localdisk is too small
• Can run a Slave(s) server off of the cluster for even more scaling or for off-loading backups.

Downfalls

• No Foreign Key support
• Other limitations listed in MySQL Docs

Scoping Requirement

fast, cheap, reliable = pick two