12

I'd like to use in both Ubuntu and Windows (dual boot) my MySql database files residing on a NTFS partition. Will it work? Any gotchas?

  • 4
    FOR THE RECORD OF THE COMMUNITY: The user is asking about the compatibility of MySQL data formats between Ubuntu Linux and Microsoft Windows. The user is wondering about whether the data stored in the Ubuntu Linux format for MySQL is compatible with the way that the Windows version of MySQL stores data so that the database information can be edited and restored across both Windows and MySQL. – Thomas Ward Apr 20 '11 at 15:48
  • A: I have just provided an extensive answer to this question here, in the Unix & Linux StackExchange. – Elliptical view Sep 18 '16 at 21:25
9

Yes, it works but with some quirks. MySQL uses the same fileformats across platforms so all you need is to share the data directory. One problem is that the data directory need to have mysql as owner and group in ubuntu. And Windows is case-insensitive and Linux is case-sensitive so keep all names uniform: either the whole name lowercase or uppercase but do not mix them.

From start to finish; if you already have things set up this might need some tweaking to fit your setup:

  • Install and setup MySQL on both systems.
  • Stop the mysql server if it is running.
  • Make a new NTFS partition. Mark the device name (let's call it sdXN for now).
  • Move the mysql data directory from Ubuntu to the new partition.

    mkdir /{mountpoint}/mysql_data
    sudo mv /var/lib/mysql /{mountpoint/mysql_data
    

    using mv saves permissions.

  • Make a new mysql directory

    sudo mkdir /var/lib/mysql
    
  • Mount the NTFS partition at /var/lib/mysql. Change the devicename to what it got when you created the NTFS partition.

    sudo mount /dev/{sdXN} /var/lib/mysql -t ntfs-3g -o uid=mysql,gid=mysql,umask=0077
    
  • To automount on boot find the partition UUID and locale and edit /etc/fstab.

    ls -l /dev/disk/by-uuid
    locale -a
    sudo gedit /etc/fstab   
    UUID={number_found_with_the_ls-l} /var/lib/mysql ntfs-3g uid=mysql,gid=mysql,umask=0077,locale={your_locale}.utf8  0  0
    
  • Change the 'datadir' path in /etc/mysql/my.cnf to point to /var/lib/mysql/mysql_data

  • Start the mysql server and test it.

  • Edit the Windows config file (my.ini) and set 'datadir' to X:/mysql_data (replace X: for where you mount it under Windows).

Compiled from topic 1442148 on UF.org.

I got the Ubuntu version working; so partly untested since I do not have Windows

  • Very nice answer! Just two comments: There are already many mixed case names and using lower_case_table_names=1 it works fine. Without your mount options I ran into this bug (which is IMHO indeed a bug). – maaartinus Apr 19 '12 at 21:45
  • "sudo mv /var/lib/mysql /dev/{sdXN}/mysql_data" gives an error. See unix.stackexchange.com/questions/310433/… – Elliptical view Sep 16 '16 at 21:56
1

If you have added a linux partition to a previously windows-only environment, you can use the database in place with only some minor tweaks. Long version here, short version:

You'll need mount --bind '/media/windows/ProgramData/MySQL/MySQL Server 5.5/data' /var/lib/mysql to run at startup (assuming you mount your windows drive to /media/windows using fstab).

In order to get around innodb's log size checking, you'll need to move the innodb logs for the ubuntu partition. In my.cnf, add innodb_log_group_home_dir = /var/log/mysql so that the logs go next to the mysql error log.

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.