Description:
If you use the option innodb-file-per-table = 1 there does not appear to be a way to scale a single mysql database beyond the size of a file system upon which datadir (innodb-data-home-dir?) resides.
The only semi-suitable work-around is to create a database per file system, then move the entire database (db) to new file system and symlink back to original location (/var/lib/mysql/db).
RAID controllers from LSI (Dell controllers are OEM LSI I think) do not support online capacity expansion for RAID 10 arrays, nor does Linux md which means you have to size your server for the final size up front if going this route. You can expand a RAID 5 array, but it's not optimal from a performance perspective. This means you have to create one large RAID 10 array up-front and when you run out of a space migrate to another RAID 10 array instead of being able to do so incrementally.
Moving individual files and creating symlinks to the files on another file system appears to work till you need to analyze table / alter table, or even create index on said table. These operations will blow away the symlink create the .frm/.ibd files in datadir at original location.
How to repeat:
create database db;
\u test
create table a (b int);
# /etc/init.d/mysql stop
# d=/var/lib/mysql
mkdir $d2/db
cd $d/db
mv a.* ../../mysql2/db/
ln -s ../../mysql2/db/a.* .
# /etc/init.d/mysql start
analyze table a;
ls -l $d/db/a.*
ls -l $d2/db/a.*
Suggested fix:
1. Create files for table in directory to which symlink point, or 2. Provide a set of directories to be searched for data files and create files in that location instead of single datadir.
