Is it possible to use the same directory for 2 MySQL servers?

Question

I have 2 OS-es on my machine: Windows XP and Linux Slackware. Both of them have mysql installed:

1. Windows - 5.5.25
2. Linux - 5.5.27

Is it possible to use the data directory from Windows on Linux? So the databases will be shared between MySQL instances.

Answer 1

That would not be possible. Why?

MySQL for Windows is very stringent about locking files. It is impossible for two MySQL Instances on Windows to lock on the same ibdata1 files.

From your question, you mentioned launching one MySQL Instance from Windows and another from Linux hitting the same datadir. At the risk of sounding redundant, MySQL for Windows is very stringent about locking files. Trying to lock ibdata1 exclusively, MySQL for Windows will block another instance from anywhere (Windows, MacOS, Linux, etc) from opening a file handle on ibdata1. The reverse is also true: MySQL for Windows will not be able to acquire an exclusive lock on ibdata1 if it is opened from elsewhere (Windows, MacOS, Linux, etc).

I have seen this done using two Linux-based mysql instances, only to see the data go down in flames due to file corruption.

Even if what you asked was possible, there is another issue you have to account for. The following system variables need to be set properly:

• lower_case_file_system
• lower_case_table_names

The way Windows handles filenames is radically different from Linux. I can easily see foreign key references going haywire when mixing and matching. I have seen one person mysqldump from a Linux instance and load a Windows instance and had all foreign key references vanish on him. Turns out, the source of the problem was the letter casing for the table names. Believe me, you don't want to troubleshoot this one unless aging rapidly is no big deal to you.

SUGGESTIONS

You are much better off doing the following:

• Setup MySQL for Windows with its own datadir on a Windows Disk (not using Samba)
• Setup MySQL for Linux with its own datadir on a Linux Disk
• Setup Replication from the one Instance to the Other (It's your choice which is Master)
• Split your reads between the two
• Confine writes to just one server
• Use TCP/IP to connect to either instance