Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am using MySQL 5 with MyISAM on my system. What happens is that one large table is populated each day and I want this table to reside on a separate (fast) disk, the day it is created and populated. The next day I would like to move it to the slower disk, without interrupting access to the database of course. The question is: is this something that can be done with MySQL and MyISAM?

Thank you.

share|improve this question

1 Answer 1

AFAIK multiple data directories are not supported in mysql till now. You have several other options, here:

  • if you're running MySQL on Linux, you could symlink the table (the .myd file) into your normal data directory from some other mountpoint, e.g. the fast storage
  • If the table is not very big, you could store it with the "memory" table type and spent your application some code which takes care of syncing.
  • you could set up multiple mysql instances on your machine with different data-directories.
  • Switch over to InnoDB and make use of *innodb_data_file_path*
share|improve this answer
    
although just having the data file path changed will not help much - the binary log would reside on a fast disk as well (ideally on a disk which is not holding the data files as well) –  the-wabbit Sep 26 '11 at 20:21
    
These could be valid solutions except for using memory engine, because the table is pretty big (30 million rows). The question is: how you can move the table from disk to disk without affecting the db's availability? –  Stathis Sep 27 '11 at 16:40
    
Do you have a timestamp field in the table? Then you could do something like a delta transfer of the table (create a new table with select * from oldtable where timestamp < today, when finished, transfer only new values from today, rename oldtable and newtable) –  shakalandy Oct 6 '11 at 8:00

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.