Wednesday, August 31, 2011

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.

  1. in /etc/my.cnf set datastore=/mysql/databases
  2. in /etc/my.cnf set innodb_file_per_table (http://dev.mysql.com/doc/refman/5.0/en/innodb-multiple-tablespaces.html)
  3. Configure SELINUX and user/group rights appropriately for MySQL. There is heaps on this on the internet but please comment if you would like me to add some detail here.
  4. Start the mysqld service
  5. configure the mysql root password if necessary
  6. login to mysql as root
  7. create the blog database (create database blog;) This creates the blog directory in /mysql/databases/blog At this point there is nothing inside it.
  8. log out of mysql.
  9. Mount your iscsi storage volume on /mysql/databases/blog
  10. Configure your system so that the iscsi storage volume has a persistence and is always mounted on this location. Redhat have a great tutorial on how to do this. Basically it involves checking the WID of your iscsi target and then applying that to a udev rule that creates a consistently named block device under /dev
  11. Now go ahead and create your tables. All the MYISAM and InnoDB tables will be created inside the /mysql/databases/blog directory.
Code / Scripts:

I am happy to provide scripts or code if people want. The following links should get you started.

Links:
Post a Comment