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.

We have a database and some data is stored in a Trucrypt folder, my question is how can I include this trucrypt folder. The data type is MyISAM. I saw there is a symbolic link, but not really clear for me how can I use that.

share|improve this question
    
Many overlook this feature of using symlinks with MyISAM tables. So, +1 for putting the spotlight on this one. –  RolandoMySQLDBA Sep 20 '11 at 21:13
add comment

3 Answers

up vote 2 down vote accepted

You could create a MyISAM and specify on-the-fly what the data directory and index directory are. The symlinks are made by mysqld.

According to MySQL Documentation, you can run CREATE TABLE with a DATA_DIRECTORY and INDEX_DIRECTORY option (not available for Windows).

For example, if you run this command

CREATE TABLE mydb.mytb
(
    id int not null auto_increment,
    stuff TEXT,
    PRIMARY KEY (id)
) ENGINE=MyISAM
DATA DIRECTORY '/backups/dat_folder'
INDEX DIRECTORY '/backups/ndx_folder';

here is what you get

/var/lib/mysql/mydb/mytb.frm
/var/lib/mysql/mydb/mytb.MYD -> /backups/dat_folder/mytb.MYD
/var/lib/mysql/mydb/mytb.MYI -> /backups/ndx_folder/mytb.MYI

The advantage of doing this is to reduce disk I/O by having data and index files operate on different disks if you choose such mount points for the directories.

WARNING : This cannot be done with InnoDB because of its heavy interaction with ibdata1. I addressed this back on August 7, 2011.

If you have data already established, you will have to do the following

  • shutdown mysqld
  • move the .MYD and .MYI to specific locations
  • create the symlinks manually in the OS from the datadir (/var/lib/mysql/mydb from the example)
  • start mysqld
  • do SHOW CREATE TABLE mydb.mytb\G

If that fails, do this:

CREATE TABLE mydb.mytblink like mydb.mytb;
ALTER TABLE mydb.mytblink
    DATA DIRECTORY '/backups/dat_folder'
    INDEX DIRECTORY '/backups/ndx_folder';
INSERT INTO mydb.mytblink SELECT * FROM mydb.mytb;

As a potential shortcut you could do this:

CREATE TABLE mydb.mytblink like mydb.mytb;
ALTER TABLE mydb.mytblink
    DATA DIRECTORY '/backups/dat_folder'
    INDEX DIRECTORY '/backups/ndx_folder';
cp /var/lib/mysql/mydb/mytb.MYD /backups/dat_folder
cp /var/lib/mysql/mydb/mytb.MYI /backups/ndx_folder
FLUSH TABLES;

Give it a Try !!!

share|improve this answer
add comment

You can use symbolic links with MySQL. All you would need to do is make a symbolic link for the .frm, .MYI and .MYD files of your MyISAM tables into the MySQL database schema directory that you want. After that, run flush tables; and they should show up.

It is also important that the MySQL user is able to access those files, so you might have a bit of an issue with user rights.

Also, depending on your MySQL version, there maybe a setting in the my.cnf which enables/disables symbolic links, so you would need to keep an eye on that too.

share|improve this answer
1  
It's best not to symlink the .frm files, as MySQL can sometimes overwrite these with physical files when you do certain types of ALTER TABLE statements. I can't remember which, but I've come unstuck with this before and have been left with two .frm files! –  Dave Rix Sep 20 '11 at 9:21
add comment

It is recommended to symlink the entire database, rather than specific tables - see section 7.11.3.1 of the MySQL 5.5 manual.

Section 7.11.3.1.2 states that it is possible to symlink the MyISAM data and index files to different directories (on different partitions for performance reasons), but NOT the .FRM table definition file.

You also need to ensure that you don't have "skip-symbolic-links" turned on in the server variables.

You can move existing data and index files (or a whole database) and then symlink back, just make sure you shut down the server first! If it doesn't start back up properly, check the "skip-symbolic-links" option has NOT BEEN SET...

share|improve this answer
    
+1 for bringing up the skip-synbolic-links issue. Of course, symlinks would have to be allowed in the OS as well. –  RolandoMySQLDBA Sep 20 '11 at 21:11
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.