Search code examples
mysqlamazon-ec2amazon-ebs

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

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.

Solution

  • 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.