Wednesday, June 27, 2012

MySQL with Replication and storage on separate EBS Volume

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


        # master id

        # binary logging for replication)

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

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

    Restart MySQL
Create Replication User on Master Node 
        GRANT REPLICATION SLAVE ON *.* TO 'replicator' IDENTIFIED BY 'replicatorPassword';
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

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


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.