Can you use MySQL Cluster and have persistent storage?
Yes, if you run enough cluster (data) nodes that it can survive the loss of one or more nodes.
So if reliability is required, the minimum number of nodes would be 3 data nodes.
The real solution would be to use MySQL Cluster replication. It requires more nodes but almost guarantees no loss data even if you lose one whole cluster.
Use MySQL 5.1 cluster
As this version will give you the ability to have non-indexed data on disk, otherwise the whole lot has to be in memory. So on EC2 that is 1-1.2Gig or so.
As to persistent storage, you will need either to
- Run enough nodes (n+1) so that any crash of your AMI is handled ok. You can then use /mnt as storage.
- Use a S3 filesystem, there are couple of people providing solutions. You could still use S3 as a backup storage, as you still need backups even if you are running a fault tolerant cluster.
I would use option 1, if you are really paranoid use a Master MySQL 5.1 cluster 3 nodes replicating to a Slave MySQL 5.1 cluster 3 nodes.
The MySQL Cluster replication is documented here
I have made a MySQL 5.0 NDB cluster of 4 nodes (1 master, 2 data and 1 mysql) so the next thing is built a Mysql 5.1 NDB master and mysql node and a MySQL 5.1 NDB data node for EC2 AMI (Amazon Machine Images).
The process continues in Part 2...