Re: Which should I use: replication or cluster?

[James Day]

Cluster is new compared to replication. I've quite often seen people suggest waiting a year for a new storage engine to mature in its integration with MySQL before using it in high value situations. That said, if you need Cluster and its features match your application, you can use it today. It's certainly a technology I'm watching closely.

Cluster does guarantee consistency. You make changes as normal, using the normal data definition language commands like alter table.

MySQL doesn't currently have an automatic failover for replication (it's on the planned feature list).
Replication is currently interesting sometimes when it comes to replication lag - you need to take some care of how you update because transactions are currently committed sequentially by the SQL thread and one slow transaction can cause the slave(s) to get behind the master. They won't lose much data (it's collected very rapidly via the IO thread) but it can be an issue if you are making queries against the slave to spread load. Any loss would probably be no more than the last few transactions, if that, which have been written to the binary log on the master but not yet collected by the slave - but the time window for this is fairly short. Some improvements in this area are planned for replication, with row level replication. There's definitely not a guarantee that the master and slave have consistent data at the time of a master failure. Only that, if you're using a transaction-safe engine like InnoDB, the transaction will either be complete on the slave or not applied at all.

People can and do script automatic failover solutions for master-slave replication today. It's not perfect but it definitely gets the job done.

You might also consider a mixed cluster and replication solution, since it can be very convenient to take a slave offline for backup or bulk operations, without affecting the performance of the main production servers.

If you're developing today, with deployment in production in 6 months, Cluster looks like a very attractive option ot consider. Less attractive, for maturity reasons, the closer your production timeline is.

I'm not a MySQL employee. I am very interested in Cluster for use at my place. Just not quite ready for our needs yet (things like a too small record length limit and the size of our data set).