I want to run two separate MySQL instances on two separate machines, but using same data directory over nfs, is it possible with InnoDB Storage Engine? Application operation will assure the following:

• one MySQL instance will be a producer
• one MySQL will be read only consumer

On the MySQL site, it is mentioned this possible with MyISAM and merge storage engine only, but they never recommend it.

http://dev.mysql.com/doc/refman/5.6/en/multiple-data-directories.html

What you are asking for is impossible at this time. Why ???

First, take a look at the InnoDB Architecture

The InnoDB Buffer Pool and the InnoDB Log Buffer are designed to operate on a single system tablespace (a.k.a. ibdata1). I wrote about this back on Oct 03, 2013 : Is there any way to use different InnoDB settings for different databases on the same server?

What you are looking for currently exists in Oracle RAC.

Oracle RAC

Forms a Cluster with these characteristics

• Each Oracle RAC server runs a local instance of Oracle’s RDBMS
• Each Oracle RAC server has its own Log Buffer
• Each Oracle RAC server shares access with other Oracle RAC servers
• All Oracle RAC server connect to the same set of database files

InnoDB

InnoDB Storage Engine for MySQL

• InnoDB only has one Log Buffer, and only one thread that connects to the Log Buffer
• InnoDB does not allow multiple Log Buffer threads
• When you start mysqld (an instance of MySQL) and its InnoDB Storage Engine attempts to connect to ibdata1, the InnoDB Storage Engine has to fail to start up if another Log Buffer thread is already open from another mysqld process.

Epilogue

Please read my earlier post for 2 alternatives. A third alternative would be to switch to MySQL Cluster (using the NDB Storage Engine). That way, you can have one or more Data Nodes shared amongst multiple SQL Nodes.

UPDATE 2014-04-24 16:55 EDT

@ypercube made the following comment

Your linked question (and answer) is about 1 server. This question is about 2 servers that share the same data directory. The two do not seem relevant.

In theory, you could set up multiple instances of mysqld on a single server. However, each mysqld instance must point to a unique system tablespace (ibdata1). in light of this fact, when it comes to multiple mysqld instances, whether they come from one DB server or multiple DB Servers:

• Only one mysqld instance can attain an exclusive log buffer thread on a system tablespace
• Only one mysqld instance can attain an exclusive insert buffer thread on a system tablespace

This is why I referred to my Oct 03, 2013 post. I also mentioned the comparison to Oracle RAC.

Your Actual Question

I want to run two separate MySQL instances on two separate machines, but using same data directory over NFS, is it possible with InnoDB Storage Engine?

InnoDB is not designed to have shared log buffers or shared insert buffers. The answer is no. I don’t think Oracle will ever change the InnoDB Storage Engine to do this. If they did, many people will drop Oracle RAC and MySQL Cluster in favor of InnoDB for budgetary and/or foolhardy reasons.

I just want to check if it is technically possible or not,
recommendation is other story. specially my application assured
write/read will not conflict

As @RolandoMySQLDBA said, this is not possible with InnoDB or MyISAM.

Stated more simply: some of the pages of data you modify are not written to disk immediately. This is done in the background, and not all at once. So if your second read-only instance tries to read data through NFS from the same data directory, it is certain to read changes that have been partially written to disk. Basically, garbage.

MyISAM doesn’t have a buffer pool, but it does make use of the filesystem as a write-back cache, so you never know whether all your changes have reached the disk or not. In this case, you’ll also get an unpredictable mish-mash of data if you try to read the files through NFS from another host.

But why do you want to do this in the first place? Is it to gain greater capacity from running a large number of queries per second? You can run more than one instance of MySQL with data that is kept more-or-less in sync, by using replication. That’s a more common practice, and it works well.

Is your reason to have a failover capability? In that case, you might use DRBD for block-level replication. And there are quite a few other failover solutions besides that.