Get MySQL/MariaDB to use dbases in a Windows partition? [SOLVED IN PRINCIPLE]

[mrodent]

I have a dual boot machine and in my W10 OS I use MariaDB.

In Linux I've also installed MariaDB. By editing the contents of `my.cnf` I've been able to change the `datadir` to another one inside the Linux partition, using these instructions.

Now I'm trying to get my Linux (Mint Cinnamon 18.1) OS to use the files in the W10 partition, so that I can use the same databases in both OSs.

I made a few changes as a result of problems followed by googling...
- I have checked that the file permissions for the file in the W10 drive/partition have completely "open" permissions.
- I have upgraded my Linux MariaDB to the latest version (to match the W10 one, i.e. 10.2).
- I have added the following lines to the end of my my.cnf file in /etc/mysql/

[mysqld]:
datadir=/media/mike/W10\ D\ drive/My\ Documents/dbases/w10_data_dir
socket=/media/mike/W10\ D\ drive/My\ Documents/dbases/w10_data_dir/mysql.sock
[client]:
port=3306
socket=/media/mike/W10\ D\ drive/My\ Documents/dbases/w10_data_dir/mysql.sock

This is the output I get when I try to start the service (as root):

Code: Select all

    M17A ~ #  service mysql start
    Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
    M17A ~ #  journalctl -xe
    ...
    -- Subject: Unit mariadb.service has begun start-up
    -- Defined-By: systemd
    -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
    -- 
    -- Unit mariadb.service has begun starting up.
    May 19 15:00:59 M17A mysqld[3239]: 2018-05-19 15:00:59 140579940546752 [Note] /usr/sbin/mysqld (mysqld 10.2.14-MariaDB-10.2.14+maria~xenial) starting as process 3239 ...
    May 19 15:00:59 M17A mysqld[3239]: 2018-05-19 15:00:59 140579940546752 [Warning] Can't create test file /media/mike/W10\ D\ drive/My\ Documents/dbases/w10_data_dir/M17A.lower-test
    May 19 15:00:59 M17A mysqld[3239]: [136B blob data]
    May 19 15:00:59 M17A mysqld[3239]: 2018-05-19 15:00:59 140579940546752 [ERROR] Aborting
    May 19 15:00:59 M17A systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
    May 19 15:00:59 M17A systemd[1]: Failed to start MariaDB 10.2.14 database server.
    -- Subject: Unit mariadb.service has failed

The problem with the above being that there is little indication as to what's actually gone wrong: the "can't create test file" is at WARNING level...

Any idea what I should do next? More generally, what are the prospects for getting this to work at all?

It was suggested that I should provide the output of `mount`... so these are what seem to be the relevant lines.

Code: Select all

   M17A ~ #  mount
    sysfs on /sys type sysfs (rw,nosuid,nodev,noexec,relatime)
    ...
    /dev/sdc2 on /media/mike/W10 D drive type fuseblk (rw,nosuid,nodev,relatime,user_id=0,group_id=0,default_permissions,allow_other,blksize=4096,uhelper=udisks2)
    /dev/sda4 on /media/mike/W10 C drive type fuseblk (rw,nosuid,nodev,relatime,user_id=0,group_id=0,default_permissions,allow_other,blksize=4096,uhelper=udisks2)

As a low-level Linux user currently I have no idea what the `mount` data might tell people about `mysqld`'s inability to create this `M17A.lower-test` dir. NB `w10_datadir/` was created by "mike", owner mike, and now has `777` permissions. I am able to create a file/directory inside it "manually" using the "mike" account in Linux...

[catweazel]

datadir=/media/mike/W10\ D\ drive/My\ Documents/dbases/w10_data_dir

Try putting quotes around that parameter and removing the escapes. Like this:

datadir="/media/mike/W10 D drive/My Documents/dbases/w10_data_dir"

If that doesn't work, you might try changing the label of the drive and creating a path to your database that doesn't include spaces.

I don't know if this will help but it's the obvious place to start troubleshooting.

[mrodent]

Thanks... I've now found a question on Stack Exchange where the poster wants to do exactly what I do... successfully:

https://unix.stackexchange.com/question ... 712#310712

NB those comments there are by me, yesterday. I have now changed the volume label of the new W10 NTFS partition from "New Volume" to "Shared".

With the help of answerers to another question in this forum I have worked out that the way to mount this partition manually with the same settings is:

Code: Select all

~ # mount -o rw,nosuid,nodev,relatime,user_id=mike,group_id=mike,permissions,allow_other,noatime LABEL="Shared"  /home/mike/Shared/

Unfortunately then going

Code: Select all

~ # mysql -u root -p

or

Code: Select all

mike... ~ $ sudo mysql -u root -p 

both fail with:

Code: Select all

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

[mrodent]

Got a step further.

I've been doing innumerable variations and permutations over the past 2 days... but I have now got to the stage where I am able to use the Windows-formatted NTFS partition to make a database from Linux Mint!

For a newb there are many confusing aspects here:
1) mysqld and mysql are different programs, and only mysql is a service;
2) chown and chmod are different things;
3) mysqld apparently has to be run as a user (not root) ... but using sudo (?).
4) some of the files and directories in/under your mysql directory have to have certain ownerships and not others;
5) the following line, adapted from the above Stack Exchange link, has a whole load of options which currently mean nothing to me:

Code: Select all

mount -o rw,nosuid,nodev,relatime,user_id=mike,group_id=mike,permissions,allow_other,noatime LABEL="Shared"  /var/lib/mysql/

Fortunately I take copious notes and copy lots of terminal interactions to my "diary" Writer file. Am currently trying to work out what works and what doesn't... but the latest thing I've done is to mount the NTFS "Shared" partition under /var/lib/mysql/ (rather than under a home/[user]/ directory)... and then also to use this line (with elevated privileges, as a user):

Code: Select all

chown -R mysql:root /var/lib/mysql

Specifically this suggestion comes from here.

Will update for the benefit of future newbs. Also have not checked yet that I can use these new dbase files from within W10...

[mrodent]

Further findings...

By dogged determination I seem to be getting somewhere!

Naturally enough various problems were thrown up when rebooting to W10 and attempting to use this datadir in the W10 X: drive (= Shared partition).

At the risk of this becoming off-topic (since it's describing things you have to do in a horrible proprietary OS which I for one have as much attachment to as war, famine and pestilence) this is what I had to tweak:
I found I had to give the directory concerned, and all its contents, a certain type of ownership, specifically, myself: using an "elevated" prompt I did this:

Code: Select all

X:\mysql-data> takeown /f . /r  

[NB ... note the dot: we are starting in this directory and recursing, making everything the property of the user (i.e. mike in my case). Despite this being an elevated prompt this does not make this owned by "root" or equivalent, but instead the user of the current W10 session]

This change of ownership is a one-off operation you have to do, seemingly, although I'm not clear what happens if you create files (MySQL tables, etc.) when in Linux.

But, in order to avoid the dreaded MySQL 1067 error you also have to change PERMISSIONS (different to OWNERSHIP, as in Linux).

I went (using an Explorer with elevated permission) to X:\mysql-data ...
- right-click --> Properties --> Security tab --> Advanced
- chose the row with "Everyone"
- clicked "Edit"
- in the next dialog, "Permission Entry for mysql-data" in Type I chose "Allow"
- in Applies to I chose "This folder, subfolders and files"
- I checked "Full control" ... which then checked all the other boxes (apart from Special permissions and "Only apply these permissions.."
- Clicked OK
- checked "Replace all child object permission entries with inheritable permission entries from this object"
- Clicked Apply, OK, OK

NB to get an Explorer with elevated permission is not trivial: you have to tweak a registry key, here worked for me. Presumably there's a command at the Command Prompt (easy to get an elevated one) which opens up this same dialog...

At the time of writing I seem to have to change these permissions each time I boot to W10...
But just as I hope to include an appropriate line in fstab to mount this "Shared" partition in Linux, hopefully there will be room for improvement on the W10 front.
It also occurs to me that giving completely open permission to "Everyone" for this directory is not necessarily a good idea. But just giving it to the user doesn't seem to be enough. Maybe MySQL uses some other account.

Anyway this does now mean I can use and modify my MySQL dbases from both OSs.