MySQL Cluster with MySQL innoDB persistent store

Question

We are working on a database solution for a high available (5 9s) application with high performance and data consistency needs. We are planning to use MySQL Cluster as the primary in-memory datastore backed up by a secondary innoDB MySQL datastore for persistent storage. The proposed approach is that the online application will only interact with the in-memory DB (MySQL cluster) and the MySQL Cluster will propagate the data to the innoDB instance through asynchronous replication / messaging for persistent storage. Can MySQL cluster or MySQL on its own support this requirement?

Answer 1

You can replicate tables across different storage engines using MySQL. You could have tables on one server in the memory storage engine replicated to an innodb table somewhere else, or any other combination of storage engines. MySQL's binary log format is compatible with all storage engine types.

(Fun fact: This is just about the only use of the blackhole storage engine. It sends all writes to /dev/null but still logs its changes to the binary log, which means it can be replicated to a real table somewhere else asynchronously.)

See Arjen Lentz' article here: http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html

Answer 2

Yes it is possible to replicate the cluster as you wish.

Be aware that MySQL cluster doesn't currently support foreign keys. So you need to create your own key dependency tracking solution (a common method is triggers)

Answer 3

Thx. Is this doable using just MySQL 5.1 by using the MEMORY engine for the primary datastore and the innoDB engine for the secondary datastore? Can MySQL replicate the data inserted into the primary datastore to the secondary datastore asynchronously using an event based delayed insert approach?