MySQL NFS Access – How Two MySQL Servers Access the Same Database Over NFS

ibdatainnodbMySQL

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

Best Answer

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

First, take a look at the InnoDB Architecture

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.

Related Question