2

I need a single InnoDB database to be used by a Linux and a Windows MySQL installations (as I boot my PC to Linux or to Windows). Would be great if I could move the same database to a different PC. Importing/exporting data is hardly an option as the database is huge (over 4 GiB - easy to copy, but pretty long to export/import). How can this be achieved?

Ivan
  • 63,011
  • 101
  • 250
  • 382
  • 1
    The very easiest thing is to host the DB on a separate server and connect to it over the network using your Linux or Windows computer when you need it. Bear in mind that a "server" does not need to be another computer, it could be just a virtual machine. – James Andres Mar 13 '12 at 19:45
  • 1
    If changing DBMS is not too complex, could SQLite be an option? There are a some issues on "big" SQLite DBs however: http://stackoverflow.com/questions/784173/what-are-the-performance-characteristics-of-sqlite-with-very-large-database-file Check as well http://www.sqlite.org/whentouse.html – Maxime Pacary Mar 13 '12 at 19:48
  • Expanding on Jame's answer, if you don't have an extra server hanging around, you could put the virtual machine on a separate partition, so that you can run the VM from Linux or Windows. You'll just need to figure out how to mount the separate partition under both operating systems. – GreyBeardedGeek Mar 13 '12 at 20:27
  • I have no problems mounting the same partition under Windows and Linux (my main data partition is NTFS despite my main OS is Linux) but having a separate virtual machine just for the database seems overkill for me. Can't different MySQL installations just be set up to use the same data directories and files? I am indeed considering SQLite, but such a migration would be problematic as I make good and intense use of some MySQL-only SQL features like INSERT-IGNORE and others. And I am not sure if SQLite can demonstrate tolerable performance on many-GiB-large tables. – Ivan Mar 14 '12 at 02:57
  • You can install Xampp Portable. – Marcelo Rodovalho Apr 26 '13 at 11:48
  • OR, see this: http://www.kidstp.com/angolmois/20120113-mysql-portable.php – Marcelo Rodovalho Apr 26 '13 at 11:54

1 Answers1

1

You should then install the MySQL database on the windows partition, and get the path to the database folder

In Linux, MySQL saves its data in "/var/lib/mysql/" so go there and create a symlink to the folder in the windows partition using the following command

ln -s /path/to/database/on/windows/ /var/lib/mysql/databasename

you need to be root to run this command, or use sudo.

This will make the MySQL server on both Operating systems see the same database folder.

NOTE: you should do some testing to check if this is a reliable solution, and if you are doing a mission critical project you should follow a more reliable setup.

Mosab Ibrahim
  • 133
  • 1
  • 6
  • 1
    +1. This should work. Note that NTFS is not case sensitive, but ext is. This means that `SELECT * FROM tABeL` will work on Windows, but might not on Linux. – kba Mar 25 '12 at 03:37
  • I forgot to mention the case insensitivity issue, thanks for pointing that out – Mosab Ibrahim Mar 25 '12 at 10:07