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.
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.
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:
mysql> show variables like 'have_sym%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_symlink | YES | +---------------+-------+ 1 row in set (0.09 sec)
I also discovered that symlinking is possible in Windows:
On my Windows 7 machines at home, the command line utility
C:\Windows\system32>mklink Creates a symbolic link. MKLINK [[/D] | [/H] | [/J]] Link Target /D Creates a directory symbolic link. Default is a file symbolic link. /H Creates a hard link instead of a symbolic link. /J Creates a Directory Junction. Link specifies the new symbolic link name. Target specifies the path (relative or absolute) that the new link refers to. C:\Windows\system32>
I AM SHOCKED !!! That means you can experiment with
CREATE TABLE ... DATA DIRECTORY='...' INDEX DIRECTORY='...' in Windows.
I just tried this:
use test drop table if exists data_table; drop table if exists data_table_sharded; CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM; CREATE TABLE data_table_sharded LIKE data_table; ALTER TABLE data_table_sharded DATA DIRECTORY='C:\DAT' INDEX DIRECTORY='C:\NDX';
I just got this:
mysql> use test Database changed mysql> drop table if exists data_table; Query OK, 0 rows affected (0.02 sec) mysql> drop table if exists data_table_sharded; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE data_table_sharded LIKE data_table; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE data_table_sharded DATA DIRECTORY='C:\DAT' INDEX DIRECTORY='C:\NDX'; Query OK, 0 rows affected, 2 warnings (0.02 sec) Records: 0 Duplicates: 0 Warnings: 2 mysql> show warnings; +---------+------+----------------------------------+ | Level | Code | Message | +---------+------+----------------------------------+ | Warning | 1618 | <DATA DIRECTORY> option ignored | | Warning | 1618 | <INDEX DIRECTORY> option ignored | +---------+------+----------------------------------+ 2 rows in set (0.00 sec) mysql>
You still cannot use the
DATA DIRECTORY AND
INDEX DIRECTORY option in
CREATE TABLE or
UPDATE 2012-01-03 22:45 EDT
I ran these commands
use test drop table if exists data_table; drop table if exists data_table_sharded; CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM; INSERT INTO data_table VALUES (71),(22),(128),(97),(18),(4),(112),(277); CREATE TABLE data_table_sharded LIKE data_table;
I made two directories
I created those folders and created hard links for data_table_sharded
C:\MySQL\data\test>mklink /H data_table_sharded.MYD C:\dat\data_table_sharded.MYD Hardlink created for data_table_sharded.MYD <<===>> C:\dat\data_table_sharded.MYD C:\MySQL\data\test>mklink /H data_table_sharded.MYI C:\ndx\data_table_sharded.MYI Hardlink created for data_table_sharded.MYI <<===>> C:\ndx\data_table_sharded.MYI C:\MySQL\data\test>
I went back to MySQL and loaded the data from data_table:
mysql> flush tables; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO data_table_sharded SELECT * FROM data_table; Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> show create table data_table_sharded\G *************************** 1. row *************************** Table: data_table_sharded Create Table: CREATE TABLE `data_table_sharded` ( `a` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select * from data_table_sharded; +-----+ | a | +-----+ | 4 | | 18 | | 22 | | 71 | | 97 | | 112 | | 128 | | 277 | +-----+ 8 rows in set (0.00 sec) mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql>
So, it can be done. Just use
mklink to create hard links instead of symlinks. WOW !!!
I learned something about MySQL for Windows. I doubt if Oracle will implement the
DATA DIRECTORY and
INDEX DIRECTORY options since the default storage engine is now InnoDB.
Notwithstanding, you create the empty table, move the .MYD and .MYI to different folders from the OS, create hard links, run
FLUSH TABLES; and INSERT your data.
Give it a Try, and let me know how it goes...
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
database_name.sym file and put it in the directory of
database_name's data directory. Put the name of the new directory in that
.sym file, and then move the
database_name data directory to the new location referenced in the
Using hard links for this requires creating a hard link for each file. Using the /J on mklink command creates a Directory Junction, and after that one command mysql would write the files in the directory to the other drive. For instance you could create a junction for C:\dat to your slower drive, and leave C:\ndx on the SSD for faster access when queries are indexed.