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?
-
4FOR 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, 2011 at 15:48
-
A: I have just provided an extensive answer to this question here, in the Unix & Linux StackExchange.– Elliptical viewSep 18, 2016 at 21:25
3 Answers
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' toX:/mysql_data
(replaceX:
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). Apr 19, 2012 at 21:45 -
"sudo mv /var/lib/mysql /dev/{sdXN}/mysql_data" gives an error. See unix.stackexchange.com/questions/310433/… Sep 16, 2016 at 21:56
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.
I faced same problem. But I solved this by having a sqlite db on shared folder. Whenever we want sqlite to mysql and vice versa. we can use python pip package to convert it.
pip install sqlite3-to-mysql
pip install mysql-to-sqlite3
That's All!