MySQL with Replication and storage on separate EBS Volume

Motivation

1. To elliminate the uncertainity of EC2 instances going down for unknown reasons leading loss of our data.
2. To have almost unconstrained storage space.
3. To have a robust system that can cope with application scaling.

Prerequisite

1. At least two running EC2 machines (one master, other slave)
2. Ability to SSH to the MySQL machines.
3. If you decide to use EC2 API CLI, it must be on your machine where you are running EC2 API commands from, and configured.

   
Install MySQL on the Machines
           

        yum install -y mysql mysql-server
    

Create and Attach EBS Volume to Your MySQL Machines
There are two ways to do this. Use the one that looks less scary to you.

1. Via EC2-API CLI
   
   

   ec2-create-volume -z us-east-1b -s 10

   
   Here -z is Availability Zone and -s is size of the volume in GiB. Please note that it's important to have the volume in same Availability Zone as the MySQL machine you  are trying to attach to.
   
   Note down the volume id this command returns, assume it's vol-XXXXX. Now attach it to a running intance, say, with instance id as i-YYYYYY
   
   

   ec2-attach-volume -d /dev/sdf -i i-YYYYYY vol-XXXXX

   
   here -d is device name. It's not necessary to use /dev/sdf you may use /dev/sdh or anything else.
    
2. Use AWS Console, Go to EC2 > Elastic Block Store > Volumes
   
   Click Create Volume, type in specifications
   
   
   
   Select the Volume, click More > Attach Volume
   
   

Format and Mount the Volume

SSH to the MySQL machine. Run the following commands to make XFS file system, register to fstab, and mount to /vol
   

        sudo mkfs.xfs /dev/sdf
        echo "/dev/sdf /vol xfs noatime 0 0" | sudo tee -a /etc/fstab
        sudo mkdir -m 000 /vol
        sudo mount /vol

   
Move Databases to the New Volume

This step is basically moving MySQL data directories to the new Volume, and tell MySQL about it. One thing that I noted that some of the installations have different location for log files and data directories than mentioned here. Also, I found that /etc/mysql directory was non existant. You may look for their location in /etc/my.cnf. If you find there is no /etc/my.cnf, you should create it. (I will tell you later how.)
  

    
        # stop MySQL
        sudo /etc/init.d/mysql stop
        
        # create appropriate directories in volume
        sudo mkdir /vol/etc /vol/lib /vol/log
        
        # move from currently installed location
        sudo mv /etc/mysql     /vol/etc/
        sudo mv /var/lib/mysql /vol/lib/
        sudo mv /var/log/mysql /vol/log/

        # replace the moved directories with a placeholder
        sudo mkdir /etc/mysql
        sudo mkdir /var/lib/mysql
        sudo mkdir /var/log/mysql

        # mount them to refer to the ones in volume
        echo "/vol/etc/mysql /etc/mysql     none bind" | sudo tee -a /etc/fstab
        sudo mount /etc/mysql

        echo "/vol/lib/mysql /var/lib/mysql none bind" | sudo tee -a /etc/fstab
        sudo mount /var/lib/mysql

        echo "/vol/log/mysql /var/log/mysql none bind" | sudo tee -a /etc/fstab
        sudo mount /var/log/mysql
        
        #start MySQL
        sudo /etc/init.d/mysql start
        
    

You may, now, try creating a database and look into /vol/lib/mysql.

---

Lets move to setting up replication.

Create or Edit my.cfg

If you have a my.cnf file, back it up. We are going to edit it. This file is going to have details about master and slave.
   

        mv /etc/my.cnf /etc/my.cnf.old
        vi /etc/my.cnf
    

If not just create one by touch /etc/my.cnf
  
Edit Master Node's Config file

On master machine, give it a server-id and enable binary logging. Add these two lines   
   

        server-id               = 1
        log-bin                 = mysql-bin
    

The file looks like this:
   

        [mysqld]

        datadir=/var/lib/mysql
        socket=/var/lib/mysql/mysql.sock
        user=mysql

        # master id
        server-id=1

        # binary logging for replication)
        log-bin=mysql-bin


        # Default to using old password format for compatibility with mysql 3.x
        # clients (those using the mysqlclient10 compatibility package).
        old_passwords=1

        # Disabling symbolic-links is recommended to prevent assorted security risks;
        # to do so, uncomment this line:
        # symbolic-links=0

        [mysqld_safe]
        log-error=/var/log/mysql/mysqld.log
        pid-file=/var/run/mysqld/mysqld.pid
    

    Restart MySQL
   
Create Replication User on Master Node 
   

        GRANT REPLICATION SLAVE ON *.* TO 'replicator' IDENTIFIED BY 'replicatorPassword';
        FLUSH PRIVILEGES;

   
Also, lets find starting position of binary log. This information will be required to configure the slave.
   

    
        mysql> SHOW MASTER STATUS;
        +------------------+----------+--------------+------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
        +------------------+----------+--------------+------------------+
        | mysql-bin.000006 |     1013 |              |                  |
        +------------------+----------+--------------+------------------+
        1 row in set (0.00 sec)

    

Configure Slave Node

Edit or create slave machine /etc/my.cnf to have server-id, you may enable binary logging but not required.
   

        server-id = 2
        log-bin = mysql-bin
    

Lets connect the two machines. We will require the private DNS/IP of the master machine and some of the informations that we have got earlier. Let's say private DNS is ip-12-34-567-890.ec2.internal

   

        CHANGE MASTER TO
           MASTER_HOST='ip-12-34-567-890.ec2.internal',
           MASTER_USER='replicator',
           MASTER_PASSWORD='replicatorPassword',
           MASTER_LOG_FILE='mysql-bin.000006',
           MASTER_LOG_POS=1013; 
           
        START SLAVE;

   
Now you may create/delete a database, a table, insert/remove data and see on slave machine happening the same.

Summary

Here are the things that I did:

1. Installed MySQL on  my EC2 instances.
2. Created, attached, formatted, mounted a Volume to these machines. Added the details to fstab.
3. Stopped MySQL. Moved MySQL data and log directories to the volume.
4. Recreated placeholder directories in the same place as previous ones, and binded it to the new location. Added to fstab and mounted.
5. Restarted MySQL and ran a sanity test.
6. Stopped MySQL, and edited/created my.cnf on both machines to assign them a server-id and enable binary logging. Started MySQL.
7. Created a replication user. Noted down master node's status.
8. Connected slave with master using CHANGE MASTER ... command.
9. ran START SLAVE command to start replication. Performed a sanity test.

Refer

• EC2 CLI Documentation  
• Detailed Article on MySQL with EBS Volume 
• Detailed Article on MySQL Replication on EC2