Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

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 mklink exists:

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 ALTER TABLE.

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

  • mkdir C:\dat
  • mkdir C:\ndx

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...

share|improve this answer
    
I set innodb_file_per_table, but even after reading Using Per-Table Tablespaces, I'm still not sure how to store a single InnoDB table in a separate file from the other tables in that database. –  Michael McGowan Jan 4 '12 at 2:18
    
I thought the Windows issue was only with respect to MyISAM tables. In the 2nd paragraph I thought you were giving a Windows solution. –  Michael McGowan Jan 4 '12 at 2:47
    
I retract my last comment because of some discoveries I made about Windows and MySQL !!! –  RolandoMySQLDBA Jan 4 '12 at 3:03
    
The mklink didn't seem to work with the /h option...I'm guessing this is because I'm using 2 different physical disks. I tried removing the /h option and thought for a moment that it worked. However, now when I try to query the new table it tells me, "Got error 140 from storage engine." –  Michael McGowan Jan 4 '12 at 4:14
add comment

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.

share|improve this answer
    
+1 for helping to inspire Michael to try something new –  RolandoMySQLDBA Jan 10 '12 at 19:07
add comment
up vote 1 down vote accepted

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 .sym file.

share|improve this answer
    
Please let us all know if your new idea fully worked. For Windows users of MySQL, this could be crucial. However, keep in mind that InnoDB tablespace files (.ibds) written on another disk may not produce an increase in disk performance because of the constant interaction with ibdata1. I wrote an earlier post about this interaction : dba.stackexchange.com/a/4425/877 –  RolandoMySQLDBA Jan 10 '12 at 19:05
    
+1 for trying something new out of inspiration –  RolandoMySQLDBA Jan 10 '12 at 19:07
    
This worked my MyISAM tables. It didn't seem to work with InnoDB, but I didn't really expect or need it to. –  Michael McGowan Jan 11 '12 at 5:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.