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.
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.
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 !!!
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.
It is recommended to symlink the entire database, rather than specific tables - see section 18.104.22.168 of the MySQL 5.5 manual.
Section 22.214.171.124.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...