0

For example, if I have MyISAM tables t1 and t2, it's possible to store t1 in /data1 and t2 in /data2?

How can I check the current file location of each MyISAM from MySQL (with sql)? is it always created in datadir/db_name as table_name.MYD and table_name.MYI extensions? no way to change it?

Potter
  • 434
  • 8
  • 21

1 Answers1

1

The solution for locating MyISAM tables on another volume is to use symbolic links.

The symbolic link still resides under the default data directory, with other schemas and tables. But the real file that the symbolic link references may be on another filesystem.

Read https://dev.mysql.com/doc/refman/8.0/en/symbolic-links-to-tables.html for details.

Note that this feature is disabled by default in MySQL 8.0, and it is deprecated and there's a note on that manual page that the feature may be removed in a future version of MySQL.

The preferred solution is to stop using MyISAM (there are many good reasons for this), and use the InnoDB storage engine instead. InnoDB has been the default storage engine since MySQL 5.5 in 2010, so I'm a bit surprised that you are still using MyISAM.

In MySQL 8.0, InnoDB supports a DATA_DIRECTORY clause in the CREATE TABLE statement. If you do consider switching to InnoDB, read about this feature here: https://dev.mysql.com/doc/refman/8.0/en/innodb-create-table-external.html#innodb-create-table-external-data-directory

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks. Do you know if it's possible to check which file holds table data and indexes through SQL commands? or is it always stored in datadir/db_name as table_name.MYD and table_name.MYI extensions? – Potter Jun 13 '23 at 12:36
  • 1
    The file is always in the data directory. There's no way to know from SQL whether that file is a symbolic link versus a hard file. You would have to go to the shell to know that and use a shell command, for instance `ls -l`. – Bill Karwin Jun 13 '23 at 14:09