1

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.

0

InnoDB

Please note the InnoDB Architecture (picture courtesy of Percona CTO Vadim Tkachenko)

InnoDB Architecture

If your database is read/write, I wrote about why InnoDB cannot allow multiple instances

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

MyISAM

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 QUESTION

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.

CONCLUSION

  • If your database is read/write, it's out of the question
  • If your database is read only
    • If your database has any MyISAM tables, there can become more subject to file handle count corruption or even data corruption
    • If your database is all InnoDB, then it is possible.
1

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

  • (OK, Rolando's answer says 'yes' in limited cases.) – Rick James Jul 24 '16 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.