You will have to resort to removing remote access from the MySQL users themselves.
To find out who has remote access, run this
SELECT user,host from mysql.user
WHERE host NOT IN ('localhost','127.0.0.1');
This will show you everyone that can login to MySQL from specific access points as well as broader network specifications.
You could run the utility mysql_secure_installation which do the following:
- You can set a password for root accounts.
- You can remove root accounts that are accessible from outside the local host.
- You can remove anonymous-user accounts.
- You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_. (I posted a question and answer about this weird aspect).
UPDATE 2013-07-02 13:11 EDT
I see you have skip-networking
in the my.cnf
and you removed bind-address=127.0.0.1
. I don't think the two options belong together. Why?
The option skip-networking disables TCP/IP. Using 127.0.0.1
as a bind-address implies TCP/IP. The two can never really mix. They are meant to be mutually exclusive. Use one or the other, not both at the same time. This makes sense because when you read the MySQL Documentation for skip-networking and bind-address, one does not mention the other.
What you are asking for is impossible at this time. Why ???
First, take a look at the InnoDB Architecture
![InnoDB Architecture](https://i.stack.imgur.com/9EcRi.jpg)
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.
Best Answer
If you were only talking about accessing the database via PHP and ASP.net, then a stored procedure could serve as a common middle layer. Once you add the android and iphone applications into the equation, you're looking at having to design another middle layer. Reason for that it is most mobile devices don't have database drivers available to them.
In your case, you should build your middle layer in asp.net or PHP as a web service. Most are designing these web services as a REST API. You'll need to think about the information that your mobile apps need and build API endpoints for those things (users, posts, etc). I also recommend making your ASP.net and PHP site/application use the same API your mobile apps do.
By setting up an API like this, you can reduce the work required for your apps. You're mobile app doesn't know that to get the latest 10 posts, it has to query
SELECT * FROM posts order by create_date desc limit 10
. It just has to make a request tohttp://myapi/posts/latest
.There are several posts on Stack Overflow and Programmers.SE about setting up Web Service APIs, I recommend reviewing them.