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.
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 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
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.