Return to Answer

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, (sorry but, I can't remember how, but when I think of it I will update this). This step was not straightforward and required some name and other adjustments, e.g. my fields named "ok?" had to be renamed "ok". 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.

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.

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/user/Shared ntfs rw,permissions,user_id="owner",group_id="group",... 0 2

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

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

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

chown -R <your Linux username>: * (Change ownership of all shared files to 'howard" (my user name) (back from root))

chown -R mysql: mysql5.6 (Change ownership of mysql files to 'mysql') IMPORTANT: in Linux mysql needs to have it's files owned by the user named 'mysql'. In Windows it doesn't seem to have any such concern.

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

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

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

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.