Let say I have already installed service MySQLa with mysqla.ini (port number=3306) setting and datadir folder in ...\data
Is it possible then, I install another service MySQLb with mysqlb.ini (port number=3307) setting and sharing datadir folder in ...\data
?
Would there be any conflict with the system schema (mysql, information_schema and performance_schema)?
I have tried locating the datadir into different folder (...\dataa and ...\datab) and it is working well.
Please note the InnoDB Architecture (picture courtesy of Percona CTO Vadim Tkachenko)
If your database is read/write, I wrote about why InnoDB cannot allow multiple instances
Apr 24, 2014 : Two MySQL Server accessing same database over NFSOct 03, 2013 : Is there any way to use different InnoDB settings for different databases on the same server?Your only alternative would be to start InnoDB in read only mode (See my only post mentioning this How to start mysqld with read-only data directory? as well as the MySQL 5.6 Documentation for the ReadOnly InnoDB Setting).
If you have database is all MyISAM or a mix of InnoDB and MyISAM, your MyISAM tables would be very prone to being in crash state (Unfortunately, that is true even for a single mysqld instance).
Keep in mind that every time you open a MyISAM table, there is counter in the header of the MyISAM table that monitors how many open file handles a MyISAM table has (See my Mar 15, 2012 answer to Why do MySQL tables crash? How do I prevent it?). If a single mysqld instance crashes, the table is consider crashed. This will pile up warnings in the error log stating the MyISAM table would have to be repaired. If this is the case for a single mysqld instance, it is far more likely to happen with multiple instances. This would be case even if a mysqld instance was started with innodb_read_only and read_only enabled.
Your mysql schema has some MyISAM tables in it. They are small. Even if they crashed, your could run REPAIR TABLE on them and fix them quickly but there is a 1 in a million chance of dealing with authentication issues if you cannot repair them.
Since you mentioned .ini, I assume you are in Windows. When it comes to MySQL, file handling locking in Windows tending to be more strict that that of Linux. So, InnoDB might be more cumbersome or slower in read only mode.
NO! Do not attempt to have the same files accessed by different instances of MySQL. The code has now way of coordinating access between them. (If it did, it would slow MySQL down to a crawl.)
