MySQL Replication setting between On-premises and AWS via SSH tunnel

I was struggled to complete migration task of on-premises MySQL database to AWS, as we had tight budget which is unable to afford dedicated VPN connection service between these two environments.

 There were 3 following options were given to achieve the mission.

1. Setup SSL configuration on on-premises MySQL server.
   => This requires me to change current configuration on running server, so I said "No thanks".
   
   
2. Implement internet VPN connection with the use of OpenVPN.
   => This requires me a lots of effort, test and verification process, I immediately turned down.
   
   
3. Establish SSH tunneling between two environments.
   => This is it!

Below image is overview of establishing SSH tunnel between on-premises and AWS.

And the summary is 

1. Setup ssh and mysqlrouter on two sides.
2. Establish SSH tunnel from bastion@AWS side to bastion@on-premises.
3. bastion@AWS listen replication request on 56446, and the request redirects to its 53306.
4. The request received on 53306 on bastion@AWS flows to bastion@on-premises via SSH tunnel.
5. bastion@on-premises passes the request to back-end MySQL server.

Actual configurations would be like these.

mysqlrouter: bastion@AWS

[DEFAULT]

logging_folder = /var/log/mysqlrouter

[logger]

level = debug

filename = mysqlrouter.log

#timestamp_precision = second

[routing:basic_failover]

# To be more transparent, use MySQL Server port 3306

bind_address = 0.0.0.0

bind_port = 56446

routing_strategy = first-available

mode = read-write

destinations = 127.0.0.1:53306

# If no plugin is configured which starts a service, keepalive

# will make sure MySQL Router will not immediately exit. It is

# safe to remove once Router is configured.

[keepalive]

interval = 60

mysqlrouter: bastion@on-premises

[DEFAULT]

logging_folder = /var/log/mysqlrouter

[logger]

level = debug

filename = mysqlrouter.log

#timestamp_precision = second

[routing:basic_failover]

# To be more transparent, use MySQL Server port 3306

bind_address = 0.0.0.0

bind_port = 56446

routing_strategy = first-available

mode = read-write

destinations = ndc:53306

# If no plugin is configured which starts a service, keepalive

# will make sure MySQL Router will not immediately exit. It is

# safe to remove once Router is configured.

[keepalive]

interval = 60

Establishing SSH tunnel (Run on bastion@AWS)

ssh -L 53306:0.0.0.0:56446 onpremises_user@1.1.1.1 -i /actual_ssh_key_path

Avoid ssh connection time-out

watch -n10 ls -l /tmp

Setup replication user on on-premises MySQL server

mysql> create user 'repl'@'%' identified with mysql_native_password by 'replpassword';

mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl';

Get master file position of on-premises MySQL server

mysql> show master status\G

*************************** 1. row ***************************

             File: mysql-bin.009516

         Position: 1367403

     Binlog_Do_DB:

 Binlog_Ignore_DB:

Executed_Gtid_Set: 8bxxxx-xxxx-11e5-8ebd-xxxxx69757cf:1-38xxxx5990,

1 row in set (0.00 sec)

Replication setup on AWS database server (Aurora)

mysql> CALL mysql.rds_set_external_master ('IP addr of bastion@AWS', 53306, 'repl', 'replpassword', 'mysql-bin.009516', 1367403, 0);

mysql> CALL mysql.rds_start_replication;

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.16.0.70

                  Master_User: repl

                  Master_Port: 56446

                Connect_Retry: 60

              Master_Log_File: mysql-bin.009516

          Read_Master_Log_Pos: 1428514

               Relay_Log_File: relaylog.002290

                Relay_Log_Pos: 1098342

        Relay_Master_Log_File: mysql-bin.009516

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

This worked well for me, even running for 2 weeks!

AWS11: Installing AWS CLI on Windows 10

AWS Command line interface allows you to access your AWS resources directly from powershell console.

As of 2020/03/22, you can download msi installer for AWS CLI from here.

After installation of AWS CLI, you have to proceed basic configuration as it is required to access to aws environment.

# Configure your aws credential
> aws configure

AWS Access Key ID [None]: AK*************

AWS Secret Access Key [None]: ViC****************

Default region name [None]: ap-northeast-1

Default output format [None]: json

These information will be stored on .aws folder on your user directory with files "config" and "credentials".

After setting up, you can check connectivity and resources on you aws environment with following commands.

# Run aws commands
> aws ec2 describe-instances

> aws lambda list-functions

AWS10: Cloudformation (Implement loadbalancer for EC2 instances)

Tutorial:

We will set up application loadbalancer for provisioned two EC2 instances.
The stack consists of resource for application loadbalancer which allows HTTP access to public and forward connection to attached EC2 instances.



In below template, "VPC", "SUBNETS", "SecurityGroup" and "WebServers" are defined as parameters which we can specify and refer provisioned stacks.
On outputs, these loadbalancer resource is exported for future usage.



Key resource types are below:

AWS::ElasticLoadBalancingV2::LoadBalancer	Loadbalancer which is required to specified subnets and security groups
AWS::ElasticLoadBalancingV2::Listener	Listener mainly defines listening port of loadbalancer
AWS::ElasticLoadBalancingV2::TargetGroup	It defines specific targets to forward the request which is received by loadbalancer.

{
    "AWSTemplateFormatVersion": "2010-09-09",
    "Description": "Application Loadbalancer.",
    "Parameters": {
        "VPC": {
            "Type": "String"
        },
        "SUBNETS": {
            "Type": "String"
        },
        "SecurityGroup": {
            "Type": "String"
        },
        "WebServers": {
            "Type": "String"
        }
    },
    "Resources": {
        "ApplicationLoadBalancer": {
            "Type": "AWS::ElasticLoadBalancingV2::LoadBalancer",
            "Properties": {
                "Subnets": [
                    {
                        "Fn::ImportValue": {
                            "Fn::Sub": "${SUBNETS}-Subnet01"
                        }
                    },
                    {
                        "Fn::ImportValue": {
                            "Fn::Sub": "${SUBNETS}-Subnet02"
                        }
                    }
                ],
                "Name": "ALB01",
                "SecurityGroups": [{ "Fn::ImportValue": {
                        "Fn::Sub": "${SecurityGroup}-WebServerSecurityGroup"
                    }
                }]
            }
        },
        "ALBListener": {
            "Type": "AWS::ElasticLoadBalancingV2::Listener",
            "Properties": {
                "DefaultActions": [
                    {
                        "Type": "forward",
                        "TargetGroupArn": {
                            "Ref": "ALBTargetGroup"
                        }
                    }
                ],
                "LoadBalancerArn": {
                    "Ref": "ApplicationLoadBalancer"
                },
                "Port": "80",
                "Protocol": "HTTP"
            }
        },
        "ALBTargetGroup": {
            "Type": "AWS::ElasticLoadBalancingV2::TargetGroup",
            "Properties": {
                "HealthCheckIntervalSeconds": 10,
                "HealthCheckTimeoutSeconds": 5,
                "HealthyThresholdCount": 2,
                "Port": 80,
                "Protocol": "HTTP",
                "UnhealthyThresholdCount": 5,
                "VpcId": {
                    "Fn::ImportValue": {
                        "Fn::Sub": "${VPC}-VPCID"
                    }
                },
                "TargetGroupAttributes": [
                    {
                        "Key": "stickiness.enabled",
                        "Value": "true"
                    },
                    {
                        "Key": "stickiness.type",
                        "Value": "lb_cookie"
                    },
                    {
                        "Key": "stickiness.lb_cookie.duration_seconds",
                        "Value": "30"
                    }
                ],
                "Targets": [
                    {
                        "Id": {
                            "Fn::ImportValue": {
                                "Fn::Sub": "${WebServers}-WebServer01"
                            }
                        },
                        "Port": 80
                    },
                    {
                        "Id": {
                            "Fn::ImportValue": {
                                "Fn::Sub": "${WebServers}-WebServer02"
                            }
                        },
                        "Port": 80
                    }
                ]
            }
        }
    },
    "Outputs": {
        "ApplicationLoadBalancer": {
            "Description": "ALB output value",
            "Value": {
                "Ref": "ApplicationLoadBalancer"
            },
            "Export": {
                "Name": {
                    "Fn::Sub": "${AWS::StackName}-ApplicationLoadBalancer"
                }
            }
        }
    }
}

See more detailed information of each element on here.

Here's a video tutorial for this.

AWS09: Cloudformation (Provision EC2 linked to subnets and security groups stack)

Tutorial:

We will set up EC2 which is linked to provisioned subnets and security groups.
The stack contains 2 web server and 1 bastion server which are associated with provisioned security groups, and deployed on provisioned subnets.

In below template, "SUBNETS" and "SecurityGroup" are defined as we can specify and refer provisioned stacks.
On outputs, these EC2 resources are exported for future usage.

{
    "AWSTemplateFormatVersion": "2010-09-09",
    "Description": "EC2 instances.",
    "Parameters": {
        "SUBNETS": {
            "Type": "String"
        },
        "SecurityGroup": {
            "Type": "String"
        }
    },
    "Resources": {
        "WebServer01": {
            "Type": "AWS::EC2::Instance",
            "Properties": {
                "InstanceType" : "t2.micro",
                "ImageId": "ami-0c00780768a0dad61",
                "KeyName": "webserver_key",
                "Tags": [
                    {
                        "Key": "Name",
                        "Value": "WebServer01"
                    }
                ],
                "NetworkInterfaces": [
                    {
                        "DeviceIndex": "0",
                        "GroupSet": [
                            {
                                "Fn::ImportValue": {
                                    "Fn::Sub": "${SecurityGroup}-WebServerSecurityGroup"
                                }
                            }
                        ],
                        "SubnetId": {
                            "Fn::ImportValue": {
                                "Fn::Sub": "${SUBNETS}-Subnet01"
                            }
                        }
                    }
                ]
            },
            "Metadata": {
                "AWS::CloudFormation::Designer": {
                    "id": "fc33911e-cd3b-496b-9975-a77684964fee"
                }
            }
        },
        "WebServer02": {
            "Type": "AWS::EC2::Instance",
            "Properties": {
                "InstanceType" : "t2.micro",
                "ImageId": "ami-0c00780768a0dad61",
                "KeyName": "webserver_key",
                "Tags": [
                    {
                        "Key": "Name",
                        "Value": "WebServer02"
                    }
                ],
                "NetworkInterfaces": [
                    {
                        "DeviceIndex": "0",
                        "GroupSet": [
                            {
                                "Fn::ImportValue": {
                                    "Fn::Sub": "${SecurityGroup}-WebServerSecurityGroup"
                                }
                            }
                        ],
                        "SubnetId": {
                            "Fn::ImportValue": {
                                "Fn::Sub": "${SUBNETS}-Subnet02"
                            }
                        }
                    }
                ]
            },
            "Metadata": {
                "AWS::CloudFormation::Designer": {
                    "id": "e0d44d1a-18b1-47db-92b2-71a9376bce33"
                }
            }
        },
        "Bastion": {
            "Type": "AWS::EC2::Instance",
            "Properties": {
                "InstanceType" : "t2.micro",
                "ImageId": "ami-0c00780768a0dad61",
                "KeyName": "webserver_key",
                "Tags": [
                    {
                        "Key": "Name",
                        "Value": "Bastion"
                    }
                ],
                "NetworkInterfaces": [
                    {
                        "AssociatePublicIpAddress": "true",
                        "DeviceIndex": "0",
                        "GroupSet": [
                            {
                                "Fn::ImportValue": {
                                    "Fn::Sub": "${SecurityGroup}-WebServerSecurityGroup"
                                }
                            }
                        ],
                        "SubnetId": {
                            "Fn::ImportValue": {
                                "Fn::Sub": "${SUBNETS}-Subnet01"
                            }
                        }
                    }
                ]
            },
            "Metadata": {
                "AWS::CloudFormation::Designer": {
                    "id": "f646646c-aa3f-4fec-9252-9eb16f19266a"
                }
            }
        }
    },

    "Outputs" : {
      "Bastion" : {
        "Description" : "EC2 instance exported values",
        "Value" :  { "Ref" : "Bastion" },
        "Export" : { "Name" : {"Fn::Sub": "${AWS::StackName}-Bastion" }}
      },
      "WebServer01" : {
        "Description" : "EC2 instance exported values",
        "Value" :  { "Ref" : "WebServer01" },
        "Export" : { "Name" : {"Fn::Sub": "${AWS::StackName}-WebServer01" }}
      },
      "WebServer02" : {
        "Description" : "EC2 instance exported values",
        "Value" :  { "Ref" : "WebServer02" },
        "Export" : { "Name" : {"Fn::Sub": "${AWS::StackName}-WebServer02" }}
      }
    }    
}  

See more detailed information of each element on here.

Here's a video tutorial for this.

AWS08: Cloudformation (Provision security groups linked to VPC stack)

Tutorial:

We will set up security group which is linked to provisioned VPC exactly same way of previous post.
The security group would be for ssh and web connection with port 22 and 80 respectively.

In below template, "NetworkStackNameParameter" is defined as we can specify provisioned VPC stack "cf-vpc-igw" during subnets stack creation.
On outputs, the security group is exported as well.

{

    "AWSTemplateFormatVersion": "2010-09-09",
    "Description" : "Subnets.",

    "Parameters" : {
        "NetworkStackNameParameter": {
            "Type" : "String"
        }  
    },

    "Resources" : {
      "WebServerSecurityGroup" : {
        "Type" : "AWS::EC2::SecurityGroup",
        "Properties" : {
          "GroupDescription" : "Enable HTTP ingress",
          "VpcId" : { "Fn::ImportValue" : {"Fn::Sub": "${NetworkStackNameParameter}-VPCID" } },
          "SecurityGroupIngress" : [ { 
            "IpProtocol" : "tcp",
            "FromPort" : "80",  
            "ToPort" : "80",
            "CidrIp" : "0.0.0.0/0"
          }, {
            "IpProtocol" : "tcp",
            "FromPort" : "22",  
            "ToPort" : "22",
            "CidrIp" : "0.0.0.0/0"
          } ],
          "Tags" : [ { "Key" : "Name", "Value" : "WebSecurityGroup"} ] 

        }
      }
    },
    
    "Outputs" : {
      "WebServerSecurityGroup" : {
        "Description" : "The subnet ID to use for public web servers",
        "Value" :  { "Ref" : "WebServerSecurityGroup" },
        "Export" : { "Name" : {"Fn::Sub": "${AWS::StackName}-WebServerSecurityGroup" }}
      }
    }
    
}

See more detailed information of each element on here.

Here's a video tutorial for this.

AWS07: Cloudformation (Provision subnets linked to VPC stack)

Tutorial:

We will set up 2 subnets on provisioned VPC exactly same way of previous post.

In below template, "NetworkStackNameParameter" is defined as we can specify provisioned VPC stack "cf-vpc-igw" during subnets stack creation.
Public route is added to "Subnet01" as we need to communicate bastion server via ssh.
On outputs, 2 subnets value are exported as we will refer them another stacks we will create later.

{
    "AWSTemplateFormatVersion": "2010-09-09",
    "Description" : "Subnets.",

    "Parameters" : {
        "NetworkStackNameParameter": {
            "Type" : "String"
        }  
    },

    "Resources" : {
      "Subnet01" : {
        "Type" : "AWS::EC2::Subnet",
        "DeletionPolicy" : "Delete",
        "Properties" : {
          "VpcId" : { "Fn::ImportValue" : {"Fn::Sub": "${NetworkStackNameParameter}-VPCID" } },
          "CidrBlock" : "10.0.0.0/25",
          "AvailabilityZone" : "ap-northeast-1a",
          "Tags" : [{ "Key" : "Name", "Value" : "Subnet-01" }]
        }
      },

      "Subnet02" : {
        "Type" : "AWS::EC2::Subnet",
        "DeletionPolicy" : "Delete",
        "Properties" : {
          "VpcId" : { "Fn::ImportValue" : {"Fn::Sub": "${NetworkStackNameParameter}-VPCID" } },
          "CidrBlock" : "10.0.0.128/25",
          "AvailabilityZone" : "ap-northeast-1c",
          "Tags" : [{ "Key" : "Name", "Value" : "Subnet-02" }]
        }
      },
    
      "PublicRouteTable" : {
        "Type" : "AWS::EC2::RouteTable",
        "Properties" : {
          "VpcId" : { "Fn::ImportValue" : {"Fn::Sub": "${NetworkStackNameParameter}-VPCID" } } 
        }
      },
      "PublicRoute" : {
        "Type" : "AWS::EC2::Route",
        "Properties" : {
          "RouteTableId" : { "Ref" : "PublicRouteTable" },
          "DestinationCidrBlock" : "0.0.0.0/0",
          "GatewayId" : { "Fn::ImportValue" : {"Fn::Sub": "${NetworkStackNameParameter}-InternetGateway" } } 
        }
      },
      "PublicSubnetRouteTableAssociation" : {
        "Type" : "AWS::EC2::SubnetRouteTableAssociation",
        "Properties" : {
          "SubnetId" : { "Ref" : "Subnet01" },
          "RouteTableId" : { "Ref" : "PublicRouteTable" }
        }
      }
    },
    
    "Outputs" : {
      "Subnet01" : {
        "Description" : "The subnet ID to use for public web servers",
        "Value" :  { "Ref" : "Subnet01" },
        "Export" : { "Name" : {"Fn::Sub": "${AWS::StackName}-Subnet01" }}
      },
      "Subnet02" : {
        "Description" : "The subnet ID to use for public web servers",
        "Value" :  { "Ref" : "Subnet02" },
        "Export" : { "Name" : {"Fn::Sub": "${AWS::StackName}-Subnet02" }}
      }
    }
    
}

See more detailed information of each element on here.

Here's a video tutorial for this.

AWS06: CloudFormation (Provision VPC and Internet gateway)

Description: 

CloudFormation is aws feature that helps you create, setup and manage infrastructure resources on aws more efficiently.
You compose template, and CloudFormation handles it for provisioning resources as described in the template.
Construct infrastructure with CloudFormation can be done either by design console on aws or uploading prepared JSON/YAML template.

Tutorial:

Let's check how it works step by step.
We will setup webserver infrastructure which consists of 1 vpc, 2 subnets and webservers within it.
And templates would be following:

1. cf-vpc -------------------- Template defines VPC and Internet gateway.
2. cf-subnets --------------- Template defines subnets and route table.
3. cf-securitygroups ------- Template defines security groups.
4. cf-webserver ------------ Template defines EC2 instances, including bastion host.
5. cf-loadbalancer --------- Template defines application load balancer.

In below template, VPC and Internet gateway are defined with "Resources" section.
At the same time, Internet gateway is associated with VPC.
"Outputs" defines the resources which will be referred by another template.

{
    "AWSTemplateFormatVersion": "2010-09-09",
    "Resources" : {
      "VPC" : {
        "Type" : "AWS::EC2::VPC",
        "Properties" : {
          "EnableDnsSupport" : "true",
          "EnableDnsHostnames" : "true",
          "CidrBlock" : "10.0.0.0/24",
          "Tags" : [ { "Key" : "Name", "Value" : "VPC01"} ]
        }
      },
      "InternetGateway" : {
        "Type" : "AWS::EC2::InternetGateway",
        "Properties" : {
          "Tags" : [ { "Key" : "Name", "Value" : "IntGW01" } ]
        }
      },
      "VPCGatewayAttachment" : {
         "Type" : "AWS::EC2::VPCGatewayAttachment",
         "Properties" : {
           "VpcId" : { "Ref" : "VPC" },
           "InternetGatewayId" : { "Ref" : "InternetGateway" }
         }
      }
    },
    "Outputs" : {
      "VPCId" : {
        "Description" : "VPC ID",
        "Value" :  { "Ref" : "VPC" },
        "Export" : { "Name" : {"Fn::Sub": "${AWS::StackName}-VPCID" }}
      },
      "InternetGateway" : {
        "Description" : "InternetGateway",
        "Value" :  { "Ref" : "InternetGateway" },
        "Export" : { "Name" : {"Fn::Sub": "${AWS::StackName}-InternetGateway" }}
      }
    }
}

See more detailed information of each element on here.

Here's a video tutorial for this.