innodb_file_per_table- move specific tables to separate physical disks

Use of innodb_file_per_table can be useful if you want to move specific database or table(s) to a separate disk.
To enable multiple table spaces, add a line to the [mysqld] section of my.cnf (or my.ini) file and restart mysql server.
[mysqld]
innodb_file_per_table

The new innodb tables will have their own .frm and .ibd files, this is similar to what Myisam storage engine does but it divides the tables into .myi and .myd.

In this post, I'll show you how to move specific database onto separate disk.

Assumptions:

- datadir= /var/lib/mysql
- you have basic linux skills

Steps:

log on to mysql

shell# mysql -uroot -p
# create database
mysql> create database my_db;
mysql> quit;

# stop mysql
shell# /etc/init.d/mysql stop

# create new directory onto separate disk
shell# mkdir /disk2/innodb

# move database onto separate disk
shell# cd /var/lib/mysql
shell# mv my_db /disk2/innodb

# create symlink
shell# ln -s /disk2/innodb/my_db my_db

# correct permissions
shell# chown -R mysql:mysql /disk2/innodb

# start mysql server
shell# /etc/init.d/mysql start

you have now successfully moved my_db database onto separate disk.