Dynamically changeable database

Question

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.

• RolandoMySQLDBA: Many overlook this feature of using symlinks with MyISAM tables. So, +1 for putting the spotlight on this one.

Answer 1

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

Answer 2

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.

• Dave Rix: 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!

Answer 3

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

• RolandoMySQLDBA: +1 for bringing up the skip-synbolic-links issue. Of course, symlinks would have to be allowed in the OS as well.