MySQL - Partition DB tables into different EBS volumes in Amazon EC2 instance

0
votes

I have an Amazon EC2 Server instance and it is attached with five EBS volumes say

  1. ebsvol1
  2. ebsvol2
  3. ebsvol3
  4. ebsvol4
  5. ebsvol5

while provisioning this server I need to install MySQL Server and need set up my Database. Here I need to mount few tables to one EBS volume (say ebsvol1) and other tables to another volume (say ebsvol2).

I am beginner to this Amazon env. Please guide me how to proceed.

1
mysqlamazon-ec2amazon-ebs
I don't think mysql supports that. – Omry Yadan
Why is it you want to put each table on a different volume? – Omry Yadan
@Omry : The data on few tables grows largely, when users count raises. The spreading of tables into different volumes is of interest. – Arun G
@Omry : The spreading of tables into different volumes to improve the IO performance and everything else. – Arun G
The correct approach to scale up size and performance is to use a raid configuration with multiple volumes underneath - not to split based on table. – Omry Yadan

1 Answers

4
votes

This answer applies to LINUX-based instances.

Start with this article at Amazon which explains how to set up MySQL to run on an EBS volume. This is not strictly necessary - the following steps should work fine with a standard MySQL installation.

You will need to create a new schema for each EBS volume / group of tables. I don't believe you can keep your tables in one schema, but spread them over multiple EBS volumes. Each empty schema is represented by a folder in /var/lib/mysql/ containing a single file named db.opt.

To move a schema to it's own EBS volume requires the following steps (using schema my_schema):

  • mount the EBS volume if not already mounted (assuming mounted as /ebsvol1); make sure the volume is listed in /etc/fstab so it will get automatically mounted on reboots

  • change permissions of the mounted folder to match the current schema folder

   chmod 700 /ebsvol1
   chown mysql /ebsvol1
   chgrp mysql /ebsvol1
  • copy the db.opt file from the schema folder to the new volume (if you have already created tables under the schema, those too need to be copied)
   cp /var/lib/mysql/my_schema/db.opt /ebsvol1/
   chmod 660 /ebsvol1/db.opt
   chown mysql /ebsvol1/db.opt
   chgrp mysql /ebsvol1/db.opt
  • replace the original schema folder with a symbolic link to the EBS volume
   cd /var/lib/mysql
   rm -fr my_schema
   ln -s /ebsvol1 my_schema

If your operating system uses AppArmor, you may need to include /ebsvol1 in the list of folders MySQL is allowed to write to. See the article here for details.

Now any tables created under my_schema will be stored on /ebsvol1.