I use my home machine as my personal database for my hobby analytics project. This machine has an SSD for the main drive where MySQL stores its data. It also has a secondary hard drive that is larger and not an SSD. I am about to create a table that I fear might be too large to fit on my SSD; is it possible for me to instead store that one table on the larger (but slower) drive? If it matters, I'm generally using MyISAM tables but could be persuaded to use InnoDB if that would help.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Rolando's answer almost works, except that it requires hard links. As far as I understand it, hard links are not possible between 2 different physical drives (using a different physical drive is the only reason I want a new directory).
However, his answer (and Vladislav's) provided me inspiration to find an answer that does seem to work for me. In short, create a
Migrating s single MyISAM table to another disk is only possible in Linux versions, not Windows, of MySQL with the DATA DIRECTORY and INDEX DIRECTORY clauses of ALTER TABLE on a MyISAM table.
However, in Windows, you can manually move the .MYD and .MYI files to where you want.
UPDATE 2012-01-03 22:03 EDT
Interestinging, MySQL 5.5.15 on my Windows 7 machines says symlink support exists:
I also discovered that symlinking is possible in Windows:
On my Windows 7 machines at home, the command line utility
I AM SHOCKED !!! That means you can experiment with
I just tried this:
I just got this:
You still cannot use the
UPDATE 2012-01-03 22:45 EDT
I ran these commands
I made two directories
I created those folders and created hard links for data_table_sharded
I went back to MySQL and loaded the data from data_table:
So, it can be done. Just use
I learned something about MySQL for Windows. I doubt if Oracle will implement the
Notwithstanding, you create the empty table, move the .MYD and .MYI to different folders from the OS, create hard links, run
Give it a Try, and let me know how it goes...
As already discussed here, there is no automatic support for symlinks in MySQL. However you can create symlinks youself.
Move the files from data directory to somewhere else, as Administrator create symbolic links with
mklink original-path new-path
If you have an ancient Windows(say XP or Windows 2003), this would not work. symlink support in NTFS debuted in Vista.