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:
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.
I am happy to provide scripts or code if people want. The following links should get you started.
Links:
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.
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/databases
- in /etc/my.cnf set innodb_file_per_table (http://dev.mysql.com/doc/refman/5.0/en/innodb-multiple-tablespaces.html)
- 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.
- Start the mysqld service
- configure the mysql root password if necessary
- login to mysql as root
- create the blog database (create database blog;) This creates the blog directory in /mysql/databases/blog At this point there is nothing inside it.
- log out of mysql.
- Mount your iscsi storage volume on /mysql/databases/blog
- 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
- Now go ahead and create your tables. All the MYISAM and InnoDB tables will be created inside the /mysql/databases/blog directory.
I am happy to provide scripts or code if people want. The following links should get you started.
Links:
- http://dev.mysql.com/doc/refman/5.0/en/innodb-multiple-tablespaces.html
- http://dev.mysql.com/doc/refman/5.5/en/option-files.html
- http://docs.redhat.com/docs/en-US/Red_Hat_Enterprise_Linux/6/html/Virtualization/sect-Virtualization-Virtualized_block_devices-Configuring_persistent_storage_in_Red_Hat_Enterprise_Linux_5.html#form-Virtualization-Configuring_persistent_storage_in_Red_Hat_Enterprise_Linux_6-Single_path_configuration
Comments