Configuring MySQL on Redhat Linux while utilising network (iSCSI) storage.
Challenges:
MySQL only allows a single location to be specified as the default data store for all databases.
Consider the following typical scenario:
A database server running one instance of MySQL has its datastore located at /mysql/databases. This server will contain multiple databases for multiple applications. The database tables are a mixture of InnoDB and MYISAM storage engines. The storage is provided by a SAN and separate storage volumes (iSCSI targets) are provisioned for each database. In this scenario, you would want all your MYISAM files (*.MYI, *.MYD, *.frm) and all your InnoDB table spaces to be contained on the same iSCSI volume on a per-database basis.
Solution:
This is easily achieved by mounting the iSCSI target on a sub-folder of /mysql/databases. For example:
Mount iqn.openfiler.com:2006-06:blog-db-storage on /mysql/databases/blog
Here is a sequence of steps that need to be carried out to make this work.
in /etc/my.cnf set datastore=/mysql/da...