Wednesday, June 27, 2012

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

4 comments:

  1. من المعروف ان نظافة المنازل من بين اهم الخدمات الضرورية في التنظيف والترقية بمنازلنا الى مصاف المنازل العصرية والتي عرفت تنظيفا عصريا من شانه ان يوفر لساكنته ظروفا حياتية راقية ولابد من توفر هيئة تختص في المجال مع تقديم ضمانات متكاملة من شانه ان تجعل الافراد يضمنون نجاح خدمة التنظيف ولا حاجة للمزيد من ضياع الاموال عبر طلبات خدمة نظافة المنازل التي لا تلبي حاجياتهم ولا تتماشى مع رغباتهم لأن العديد من شركات تنظيف المنازل تسعى فقط الى عرض خدماتها عبر طرق ترويجية فحسب من اجل كسب المال فقط دون مراعاة الوازع الاخلاقي والضمير المهني الذي يحتم على مدراء مثل هاته الشركات ان يسعوا خلف ارضاء العملاء ليس اكثر من هذا عبر اتقان تنظيف المنازل . شركة نقل عفش بتبوك
    شركة كشف تسربات المياه بالاحساء
    شركة تنظيف بالاحساء
    شركة مكافحة حشرات بالاحساء
    شركة مكافحة حشرات بتبوك
    شركة تنظيف مجالس بالاحساء

    ReplyDelete