How to Migrate MySQL from Amazon EC2 to your On-Prem Data Center Without Downtime

    0
    2

    Since the concept of cloud was born, there has been strong growth in the number of migrations to this environment. However, not everything that shines is gold.

    As the demand grows, so does the costs. We can find ourselves in a situation where our monthly cloud expenses are very high and, in this case, it may make sense to migrate back to an on-prem environment.

    The costs may not be the only reason. There might be security or compliance requirements, or we may need to have more control of our systems. Knowing what happens at a lower level can help us better optimize things.

    AWS not only give us the environment, it also provides us with monitoring and management tools to run our system in the cloud. So, it can be really hard to migrate to an on-prem environment and recreate all these tools to manage our systems in the same way.

    In this blog, we will see how we can migrate our systems from AWS to an on-prem datacenter, and how ClusterControl can help us in the process.

    Concepts

    First of all, let’s see some basic concepts about Amazon Cloud.

    AWS

    Amazon Web Services (AWS) is an Infrastructure as a Service platform, comprising a large number of independent and semi-independent services. The purpose of Infrastructure as a Service platform is to offer, on a commodity basis, services that previously required the purchase of capital-intensive infrastructure components such as high-end servers, network routers and switches, and for larger enterprises, even their own datacenters.

    RDS

    Amazon Relational Database Service (RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups.

    Amazon RDS is available on several database instance types and provides you with six familiar database engines to choose from, including Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and SQL Server.

    EC2

    Amazon Elastic Compute Cloud (EC2) is a service that provides secure, resizable compute capacity in the cloud. It is designed to make web-scale cloud computing easier for developers.

    Amazon EC2’s simple web interface allows you to obtain and configure capacity with minimal friction. It provides you with complete control of your computing resources and lets you run on Amazon’s proven computing environment.

    ClusterControl

    ClusterControl is a comprehensive management system for open source databases that automates deployment and management functions, as well as health and performance monitoring. There are two versions: Community Edition or Enterprise Edition. ClusterControl supports deployment, management, monitoring and scaling for different database technologies on any environment.

    Why Migrate?

    As we mentioned at the beginning, the most common reasons to migration from AWS to an on-premise environment would be costs, security, compliance, or even working with running local applications. In AWS, we don’t know what is happening under the hood of the infrastructure. We only know that all is working. In cases where you experience poor performance or other anomalies, the only solution is to get in contact with Amazon support.

    Example Migration Scenario

    In AWS we have two different products related to this blog: EC2 and RDS.

    The main difference between them is that in EC2 you have SSH access to the server and have to manage the database yourself. RDS is a hosted database service, and you only have access to the database instance.

    In RDS, as you don’t have SSH access, you need to create a dump and import it into the new server, or you can configure replication and promote the slave to the new master. For both options, the process is manual. Also, you can add some load balancer to improve this process. We covered this task in these blogs: Part 1 and Part 2.

    So, let focus on the migration from EC2.

    In our example, let’s see how to migrate MySQL from AWS EC2 to an on-prem datacenter. We will use a MySQL Replication environment, but these steps should work for other technologies like PostgreSQL.

    We will assume that you have your main MySQL database running on EC2 instance. In the on-prem datacenter, we assume you have ClusterControl installed, as well as a fresh database server to migrate to.

    In the AWS console, you should have something like this in the EC2 instances section:

    AWS EC2 SectionAWS EC2 Section

    First, we’ll import our current master running on EC2 to ClusterControl. For this import process, you must open the port 3306 by editing the Security Group associated with the EC2 instance.

    AWS Security GroupAWS Security Group

    After this, within ClusterControl, go to the Import section.

    ClusterControl Import Section 1ClusterControl Import Section 1

    There, you can choose the technology, in our example MySQL Replication, and we must specify User, Key or Password and port to connect by SSH to our server. We also need the name for our new replication ‘cluster’.

    ClusterControl Import Section 2ClusterControl Import Section 2

    After setting up the SSH access information, we must define some database information like the database user, version and basedir. Also, we can enable the ClusterControl Node AutoRecovery and Cluster AutoRecovery features for the new cluster.

    Then, we need to add our server by using the IP address or hostname and press Import.

    ClusterControl Import Section 3ClusterControl Import Section 3

    ClusterControl

    Single Console for Your Entire Database Infrastructure

    Find out what else is new in ClusterControl

    Install ClusterControl for FREE

    We can monitor the status of the import of our setup from the ClusterControl activity monitor.

    Once the task is finished, we can see our master in the main ClusterControl screen.

    Make sure that you have enabled the binlog generation in your current master database. If not, you can enable it from the Node Action section in ClusterControl.

    Now, we can add our future new master as a new replica from our current master database. For this, go to ClusterControl -> Select Cluster -> Cluster Actions -> Add Replication Slave.

    ClusterControl Add Replication SlaveClusterControl Add Replication Slave

    Here, we need to add the hostname or IP address of the new slave server, and if we want ClusterControl to install the software for us.

    Make sure that you have connectivity from AWS to the port 3306 and 9999 in the on-prem server.

    The way ClusterControl stages the slave with data is to take an hotbackup of the master, stream it to the slave and restore it there. Once restored, the slave is connected to the master so it can catch up on events and get in sync. Note that, for large databases running with some load, you might want to avoid the extra load of this operation on the master. In that case, it is possible to build the slave first from an existing backup, and then connect the slave so it catches up with the master.

    After this task, we should have something like this:

    You can also verify the topology on the ClusterControl Topology section.

    ClusterControl Topology View 1ClusterControl Topology View 1

    Then, we need to promote the slave to master (ClusterControl -> Select Cluster -> Node Actions -> Promote slave) and change the endpoint in your application.

    To improve this topology, you can add a load balancer to manage the traffic from the application server to the database. Using a load balancer, during the migration, you don’t need to change the endpoint from your application. The load balancer will change the master in a transparent way for your application.

    ClusterControl Topology View 2ClusterControl Topology View 2

    There are many ways to perform this task and, probably you should be able to adapt this strategy or similar, to your environment, depending on your infrastructure, security, etc.

    For security reasons, you should consider using a VPN between the AWS and the on-premise environment.

    In the case of a multi-master topology like Galera Cluster, you only need to add the nodes that you want on-premise, but be careful with the latency. You can use for example different Galera segments to decrease network usage.

    Considerations

    Related resources

     Comparing RDS vs EC2 for Managing MySQL or MariaDB on AWS

     Cloud Database Features Comparison – Amazon RDS vs Google Cloud SQL

     How to Make Your MySQL or MariaDB Database Highly Available on AWS and Google Cloud

    Some considerations to take into account when we want to leave AWS and start to use our own environment could be:

    Monitoring: Don’t forget to use some monitoring system. You need to know what is happening in your system.
    Disaster Recovery Strategy: You should consider some disaster recovery strategy. In general, you should have the information in three different places, for example, Master, Slave, and backup, each in different physical places.
    High Availability: Nowadays, HA is a must in most production environments, so we need to think about the best HA solution depending on our infrastructure.
    Scaling: We should be able to scale if it’s needed in the future or for some specific event.
    Rollback: If you want to migrate from AWS to an on-premise environment, keep in mind that something could go wrong (as in any type of migration), so you should have some rollback plan.
    If you are after some kind of hybrid environment, with instances running on AWS and on-prem, then ClusterControl can be a good fit for monitoring, managing availability, backups and scaling.

    ClusterControl OverviewClusterControl Overview

    Tags: 

    MySQL
    AWS
    ec2
    amazon

    Read more: severalnines.com

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here