2

I'm trying to share my MySQL database files between a Windows and a Linux distro (Mint). I have a dual boot plus a separate data partition (NTFS).

I've tried mounting the partition with ownership on the files set to "mysql:mysql" and a umask at 000 (chmod 777) but MySQL always says:

Fatal error: Can't open and lock privileges tables: Table 'host' is read only

Tried this but it still won't work. Any ideas? I really don't want two copies of my databases on my computer.

2

There are a number of web posts suggesting that this can be done, but but I could not find a single post telling me how to get this to really work. So, here is a solution that finally works for me, after weeks of struggling with this. :-)


Platform: Dual boot, Windows 10 / Debian 8.5, with MySQL 5.6 on each.

Tip: Upgrade to MySQL 5.6 from Oracle, not from the Debian Backport, as it is broken and won't install properly.

Caution!

  • When you do the mount below it will change the ownership of ALL your files on your shared partition. This worried me at first, but so far has not turned out to be a problem.

  • Make a backup of everything before you start.

  • Two independent backups are better than one, believe me, I've seen the midnight light a time or two and want no more of that.

  • Others have noted that because Windows is case-less and Linux case sensitive, you should keep your names all lower case, I'm guessing this means your database names at a minimum, as they get converted to file names when stored in the mysql/data folder.

Background: I am moving my MS Access data from Windows to Linux. The first step was to move my data out of MS Access. I setup MySQL 5.6 on Windows and copied my tables from Access into MySQL using MDBTools to copy the structure and access w/ ODBC to move the data. This was not completely straightforward and required some name and other adjustments, e.g. my fields named "ok?" had to be renamed "ok", and I had to adjust indexes, and pay attention to primary keys. Then, so I can continue to access these tables as if they were still in Access, I linked the tables back into Access via ODBC. Now the tables were ready to be moved to where Linux could also access them.

Start with necessary Windows changes:

1) Setup an appropriately sized ntfs shared partition (mine is called E: and is about 55GB).

(Tip: Make it big enough the first time. I had to make mine bigger at some point which was a real pain. In addition to mysql data bases, I now keep my shared Thunderbird/Icedove and Firefox profiles here, as well as other shared files like music, spread sheets, and documents as I transition from Windows to Linux.)

2) Move your mysql data base folder from C: to E:
--from: C:/ProgramData/MySQL/MySQL Server 5.6/data/
--to: E:/mysql5.6/

3) Update your (windows) my.ini file to point to new mysql server folder:
--edit this file: C:/ProgramData/MySQL/MySQL Server 5.6/my.ini
--change this parameter: datadir="E:/mysql5.6/data\"

(I don't know what the trailing backslash is for, but keep it there.)

4) Test in windows:
Load MySQL's server via:
(Start menu) | MySQL | System Tray Monitor | Start Instance (from system tray)
[red light on icon should turn green if server instance starts ok]

Next make some Linux changes:

1) Update my.ini to point to new mysql server folder
--edit this file: /etc/mysql/my.ini
--change this parameter: datadir="/home/howard/Shared/mysql5.6/"

2) Add mount line to fstab:

LABEL="Shared"  /home/username/Shared/  ntfs  rw,nosuid,nodev,relatime,user_id=username,group_id=groupname,permissions,allow_other,noatime 0 2

Tips:

  • We need the "permissions" option to allow mysql files to later be owned by linux user 'mysql'.

  • This initially changes ownership of all files to 'root' !!

  • You must not include uid, gid, umode, dmode, and fmode options here as they will disable the "permissions" option which allows you to individually change ownership and permissions for files. If your mount reports 'default_permissions', then you've got a problem and can't proceed until you correct it. (user_id, group_id, and other mount options don't seem to have this problem and work ok with the 'permissions' option.)

  • It doesn't seem to matter if your mount type is ntfs or ntfs-3g.

  • The side effect of the 'permissions' priority is that all files on the partition will be re-owned (even in Windows)!

3) Mount your shared partition

mount -a (or reboot)

4) Change ownership of all files to your main user name

chown -R <your Linux username>: /home/user/Shared/*

5) Change ownership of mysql files to 'mysql'

chown -R mysql: /home/user/Shared/mysql5.6

IMPORTANT: Linux mysql needs to have it's files owned by the user named 'mysql' or the server will gag. In Windows it doesn't seem to have any such concern.

Test in Linux:

sudo mysql -u root --password="<your password>"
mysql> show databases;
mysql> quit

Go back to Windows & fix what Linux just broke:

At this point my mysql server wouldn't start. After a bit of investigation, here is what I found to fix it:

First, when I mounted my shared partition as ntfs with the 'permissions' option and then changed file ownership, it changed the Windows ownership of all files and folders to "Account unknown", because Windows doesn't know about Linux user names.

Also I think (but am not sure of), if there was security file and directory 'inheritance', it got turned off. If I copy a tree on my shared partition, it creates inheritance in the security permissions. But when I investigated how it was after Linux mounted it, there appeared to be no security permission inheritance. Instead there were absolute security parameters.

Grayed out check boxes signify inherited values from a parent, EXCEPT for the 'Special permissions' check box, where it will be grayed out if there are custom set special permisisons.

Special permissions seem to be only an alternate view of normal permissions, and not in addition to normal permissions. (If you are unfamiliar with the windows permissions tools, first play with them in a test file before you start.)

The MS Documentation on this is all screwed up, with wrong headings on columns here, which I have reported so they can fix them.

Now all file and permission ownership is one of two 'Account Unknown' SID's that look something like this: (S-1-5-21-...)

This makes it a little harder to change the security of files, as no windows owner is now the owner. So the first step is to take ownership back of any file(s) we want to change the security parameters of.

To get mysql working again (after Linux mysql server touched it) we need to add a little security permission to one file. I tested adding it to each of the four users, but giving full-control to Administrator, System, and the S-1-xxx user didn't allow mysql to start. But when I gave more permission to Everyone, then mysql started to work. (I suspect that there is a minor bug in mysql, as it seems strange to me that a permission of Everyone should make a difference.)

1) Select the file: E:/mysql5.6/dataauto.cnf

2) Take ownership. Right click on properties, then click security | advanced | Owner: ... Change | Enter | press Check | then OK

Now you're the owner of E:/mysql5.6/dataauto.cnf

3) Set the following permissions for E:/mysql5.6/dataauto.cnf:

Again use: properties | security | advanced
Select 'Everyone' then click Edit | Show advanced permissions
Make sure the following are checked:

  • List folder/execute file,
  • Read extended attributes, and
  • Read permissions

Then click OK | and Apply | and OK | OK

Finally do a full test

The following test sequence will to make sure that when the linux mysql server starts up it does not mess up windows mysql server's ability to startup:

1) In Windows start the windows mysql server, then
2) Reboot into linux and start the linux mysql server, then
3) Reboot back into Windows and start the mysql server again.


Please report any problems here so I can update this.

  • Hi, thanks for this... I've got to stage 2) of make Linux changes. After making the change to fstab this stopped Linux rebooting. Fortunately I had done a disk image of the Linux partition. After restoring I tried again, and did "mount -a": I got "mount: can't find LABEL="Shared"". Maybe this "New Volume" as called by Linux needs to be renamed "Shared"? – mike rodent May 19 '18 at 18:10
  • Managing now to mount the partition "manually" with all those options "mount -o rw,nosuid... " ... but I'm still getting "ERROR 2002 (HY000) : Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)" when I go "mysql -u root -p" as root or as a user (with sudo). – mike rodent May 20 '18 at 10:00
  • Ha ... have got Linux installing and using a dbase on the Shared partition. But when I go back to W10 there is absolutely no file called "*.cnf" under the MySQL directory. BTW am using MariaDB (close fork of MySQL). – mike rodent May 20 '18 at 17:08
  • @mike rodent, With MySQL this was in C/Users/All Users/MySQL Server 5.6/. But by now I've also moved to MaridDB 10.2, so checkout C/Program Files/MariaDB 10.2/data/*.ini – Elliptical view May 21 '18 at 4:27
  • Yes, got that, Tx. ... BTW your answer has been very valuable to me... I'm eventually going to put my own answer here on the basis of what I've learnt (I have now managed to get this sharing working). Incidentally, when I include your fstab line my Linux (Mint Cinnamon 18.3) fails to boot, so mounting manually. As a Linux newb I'm still trying to understand this and a couple of other slight snags. – mike rodent May 21 '18 at 15:34

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.