Migration of Database using DMS

Lab Details

1. This lab walks you through the step by step procedure on how to migrate an existing MySQL database on an EC2 instance to an AWS RDS instance with AWS Database Migration Service.
   
2. Duration: 1 Hour 15 minutes
   
3. AWS Region: US East (N. Virginia) 
   

Architetcure Diagram

Tasks

1. Log into the AWS Management Console.
   
2. Create a Source Database on EC2 (Ubuntu) instance and install MySQL server.
   
3. Create an AWS RDS instance which will act as the Destination Database.
   
4. In DMS, create endpoints for your Source & destination databases.
   
5. Create a Replication Instance and Database Migration Task.
   
6. Start the Database migration task to replicate the MySQL database on the EC2 instance (source database) to AWS RDS (Destination Database).
   

Steps

Launch New Ubuntu EC2 Instance

• Navigate to Services → EC2
  
• Make sure you are in N.Virginia region.
  
• Click on 
  
• Choose an Amazon Machine Image (AMI): Search for Ubuntu Server and select  
  
• Choose an Instance Type: select , click on the .
  
• In Configure Instance Details Page:
  
  • Network        : default
    
  • Subnet         : default
    
  • Auto-assign Public IP     : Enable – It should be enabled as public IP is needed for connecting to EC2 via SSH.
    
  • Leave everything else as default and click on 
    
• Add Storage Page : No changes needed. Click on .
  
• Add Tags Page
  
  • Click on 
    
  • Key    : Name
    
  • Value    : SourceEC2Instance
    
  • Click on 
    
• On the Configure Security Group page:
  
  • Select Create a new security group
    
  • Security group name    : Migration-SG
    
  • Description        : Migration-SG
    
  • To add SSH,
    
    • Choose Type    : 
      
    • Source        : 
      
  • For MYSQL/Aurora,
    
    • Click on 
      
    • Choose Type    : MYSQL/Aurora
      
    • Source        : 
      
  • Click on 
    
• Review and Launch: Review all your select settings and click on .
  
• Key Pair – This step is most important, Create a new key Pair and click on  and save it in your local with Key pair name as DMSMigrationKey.
  
• Once the download is complete. Click on .
  
• Click on View Instances. After 1-2 Mins Instance State will become running and is ready.
  

1. Note down the IPv4 Public IP address – 54.221.120.52
   

SSH into the EC2 Instance

• Please follow the steps in SSH into EC2 Instance.
  

Install and Configure MySQL Server

• Once connected to the server switch to root user:
  
  • sudo su
    
• To update all the package:
  
  • apt-get update -y
    
• Install MySQL:
  
  • apt-get install mysql-server -y
    
• Login to the MySQ:
  
  • mysql -u root -p
    

  
  

  • Enter Password: by default, the password is empty. Press enter to proceed further.
    
• Now, we have to set the password for the root user. Please keep a note of this password as we will use this in upcoming steps. To set the default password,  please run the below-provided command:
  
  • SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘source123‘); 
    
• To apply the modified changes, we have to stop and restart the MySQL server.
  
  • Exit from Mysql by using the command : exit
    
  • Restart the mysql
    
    • /etc/init.d/mysql restart;
      
• Login with new root credentials.
  
  • mysql -u root -p
    
    • Password: source123
      
• After successful login, you will be able to see the welcome message and mysql> prompt waiting for your command:
  

• Enter exit to exit out of MySQL.
  

Update the mysql configuration file

We will open mysqld.cnf file and check the below details to configure them. If any rule is missing, then we have to manually add those in the mysql config file. To edit that file, follow the below steps in Source Instance.

• To edit the mysql config file:
  
  • nano /etc/mysql/mysql.conf.d/mysqld.cnf
    
• Check the below details already in the mysql configuration file.
  
  • bind-address = 0.0.0.0
    
  • server-id = 1
    
  • log_bin=mysql-bin
    
  • binlog_format=row
    
• If any rule is not available, then enter the missed ones manually and save the file.
  

• To save the file: Press ctrl+x and confirm by pressing ‘y’ and hit enter.
  
• Restart the Mysql server:
  
  • /etc/init.d/mysql restart
    
• Source Database is configured.
  

Create an Amazon RDS Database

1. Click on the  and select the RDS under the  section.
   
2. In the left navigation pane, click on Databases.
   
3. Make sure you are in N.Virginia Region.
   
4. Click .
   
5. Note that Console UI is changed now, use old UI by clicking on Switch to your original interface.
   
6. Make sure Only enable options eligible for RDS Free Usage Tier is checked which is at the bottom of the page for this lab to work. If not some configurations which are not part of the free tier will not work and you will face issues.

• Select MySQL. Click 
  
• Specify DB details:
  
  • In Instance specifications section,
    
    • License model            : general-public-licence
      
    • DB engine version         : default
      
    • DB instance class        : db.t2.micro – 1 vCPU, 1 GiB RAM.
      
    • Allocated Storage        : 20 GIB
      
    • Enable storage autoscaling    : uncheck
      
  • In the Settings section,
    
    • DB instance identifier        : mydbinstance
      
    • Master username        : awsrdsuser
      
    • Master password        : whizlabs123
      
    • Confirm password        : whizlabs123
      
  • Click .
    
• Under Configure advanced settings, 
  
  • In Network Security section,
    
    • Virtual Private Cloud (VPC)    : Default VPC
      
    • Subnet Group             : Default Subnet Group.
      
    • Public accessibility        : No
      
    • Availability Zone        : default
      
    • VPC security groups        : Select Choose existing VPC security groups as Migration-SG
      
    • Leave other parameters as default. 
      
  • In Database Options section,
    
    • Database name         : myrdsdatabase
      
    • Leave other parameters as default.
      
  • In Backup section,
    
    • Backup retention period     : 0 days
      
    • Copy tags to snapshots    : Uncheck
      
    • Leave other parameters as default.
      
  • In Deletion Protection,
    
    • Enable deletion protection     : uncheck
      
• Leave other parameters as default. Scroll to the bottom of the page, Click .
  
• Click  to see the RDS Instance created.
  
• It will take a few minutes for your MySQL database to become available. In the left navigation pane, click Databases.
  
• It will take around 5 minutes for database to be become available. Once the status changes from creating to available, database is ready.
  
• Note down the DNS end point of RDS
  
  • mydbinstance.c81x4bxxayay.us-east-1.rds.amazonaws.com
    

Create a Replication instance in DMS

• Click on  and then choose Database Migration Service under the .
  
• Make sure you are in N.Virginia region.
  
• Click on 
  
• In Replication instance configuration section,
  
  • Name                             : myreplicationinstance
    
  • Description                     : To replicate EC2-Mysql to AWS RDS
    
  • Instance class                : dms.t2.micro
    
  • Engine version               : Default
    
  • Allocated storage (GB)   : 10 GB
    
  • VPC                                : default VPC
    
  • Multi AZ                           : Uncheck
    
  • Publicly accessible          : Check
    
• In Advanced security and network configuration section,
  
  • Replication subnet group  : Default
    
  • Availability zone                  : default
    
  • VPC security group(s)        : Migration-SG
    
  • KMS master key                  : Default
    
• Leave other settings as default.
  
• Click on the  button to create the replication instance.
  
• It will take 5 minutes for replication instance to be created. Once status changes to available, click on the instance and scroll down. You will find the details section of the replication instance.
  

1. Click on the myreplicationinstance. From Details section, copy the private & public IP address and note it down on the notepad.
   

• Public IP address → 3.224.227.68
  
• Private IP address → 172.31.29.148
  

Configure Replication Instance details in Source EC2 Instance

• SSH back into the Source EC2 instance. For more details go through SSH into Source Database EC2 instance from Mac or Windows systems.
  
• Switch to root user: sudo su
  
• Login to the MySQL:
  
  • mysql -u root -p → (Press enter)
    
  • Enter password: source123
    
• We need to grant root access to the replication instance to connect with the MySQL server on Source EC2. To give access, follow below steps:
  
  • Command Syntax:
    
    • GRANT ALL ON *.* to root@’<<Private IP of Replication Instance>>‘ IDENTIFIED BY ‘your-root-password‘;
      
    • GRANT ALL ON *.* to root@’172.31.29.148′ IDENTIFIED BY ‘source123‘;
      
• And repeat the same step now with Public Ip address of replication instance.
  
  • GRANT ALL ON *.* to root@’3.224.227.68′ IDENTIFIED BY ‘source123‘;
    
• Save the changes
  
  • FLUSH PRIVILEGES;
    
• Exit MySQL and restart the MySQl server
  
  • /etc/init.d/mysql restart
    
• Replication instance has access for Source Instance MySQL Database.
  

Create Endpoints in DMS

We have to create the Source and Target endpoints for EC2 and RDS Instances. These endpoints will help to connect replication instance with both source and target machines.

Source Endpoint

• Make sure you are in N.Virginia region.
  
• To create an Endpoint, Click on the Endpoints(Left panel) in the DMS service console page and click on the button.
  
• Follow the below steps to complete Endpoint type:
  
  • Select endpoint as 
    
  • Select RDS DB instance: Uncheck (This is for Source i.e Mysql on EC2)
    
• Endpoint configuration:
  
  • Endpoint identifier     : sourcemysqlendpoint
    
  • Source engine           : mysql
    
  • Server name               : 54.221.120.52 – Public IP address of Source Instance
    
  • Port                             : 3306
    
  • Secure Socket Layer (SSL) mode: None
    
  • User name                 : root
    
  • Password                   : source123
    
• Leave other configurations as default.
  
• Test endpoint connection:
  
  • VPC                                   : Default
    
  • Replication instance          : myreplicationinstance (Which created earlier)
    
  • Click on to test the connection. If all are working fine, you will able to see the status as “successful” as shown in the below screenshot.
    

Target Endpoint:

• To create an Endpoint, Click on the Endpoints(Left panel) in the DMS service console page and click on the button.
  
• Follow the below steps to complete Endpoint type:
  
  • Select endpoint as 
    
  • Select RDS DB instance: check (This is for Target i.eRDS Instance)
    
• Endpoint configuration:
  
  • Endpoint identifier    : awsrdsendpoint
    
  • Target engine            : mysql
    
  • Server name                 : mydbinstance.c81x4bxxayay.us-east-1.rds.amazonaws.com → DNS endpoint of your RDS Instance.
    
  • Port                            : 3306
    
  • Secure Socket Layer (SSL) mode: None
    
  • User name             : awsrdsuser 
    
  • Password              : whizlabs123
    
• Leave other settings as default.
  
• Test endpoint connection:
  
  • VPC                                   : Default
    
  • Replication instance          : myreplicationinstance (Which we created earlier)
    
  • Click on to test the connection.
    
• Click on the  button.
  

Create a simple custom Database on Source EC2:

Create a simple database and create a table inside which will be migrated using DMS.

1. SSH back to Source EC2 Instance.
   
2. Connect to Source MySQL Client
   

• mysql -u root -p
  
• Enter Password : source123
  

1. Create a Database
   

• CREATE DATABASE SchoolDB;
  

• View the database created
  
  • show databases;
    

    
    

  • Switch the database SchoolDB.
    
    • use SchoolDB;
      

      
      

  • Create a sample Table of students.
    
    • CREATE TABLE students ( 
      

      subject_id INT AUTO_INCREMENT,
      

              subject_name VARCHAR(255) NOT NULL,
    

              teacher VARCHAR(255),
    

              start_date DATE,
    

              lesson TEXT,
    

              PRIMARY KEY (subject_id));
    

    
    

  • See the students table.
    
    • show tables;
      

      .
      

  • Insert data into the table
    
    • INSERT INTO students(subject_name, teacher) VALUES (‘English’, ‘John Taylor’);
      
    • INSERT INTO students(subject_name, teacher) VALUES (‘Science’, ‘Mary Smith’);
      
    • INSERT INTO students(subject_name, teacher) VALUES (‘Maths’, ‘Ted Miller’);
      
    • INSERT INTO students(subject_name, teacher) VALUES (‘Arts’, ‘Suzan Carpenter’);
      
  • Check the items added in the Table
    
    • select * from students;
      
      
      
  • After database migration, this new custom table can be used as proof of database migration.
    

Checking AWS RDS Database before Migration

First, check the databases and tables that exist on the AWS RDS Instance. So that after migration, you will be able to find the new changes. We can use the existing Source EC2 Instance to connect to AWS RDS.

• SSH into the Source EC2 instance. For more details go through SSH into EC2 instance from Mac or Windows systems.
  
• Switch to root user: sudo su
  
• Now, connect to the Amazon RDS Instance by running below command:
  
  • Syntax : mysql -h <RDS Instnace Endpoint> -u <User Name> -p 
    
  • mysql -h mydbinstance.c81x4bxxayay.us-east-1.rds.amazonaws.com -u awsrdsuser -p 
    
  • Enter Password: whizlabs123
    
• After successful login, run below command.
  
  • Show databases;
    

    
    

1. A database by name SchoolDB does not exists now. After migration SchoolDB database will be available here.
   

Create a Database Migration Task

An AWS Database Migration Service task is where all the migration process happens. We will specify the tables and schemas to use for the migration and any special processing, such as logging requirements, control table data, and error handling.

• Navigate to AWS DMS console and click on the .
  
• Make sure you are in N.Virginia Region.
  
• Click on the .
  
• Create a database migration task:
  
  • Task configuration:
    
    • Task identifier                         : Database-Migration-Task
      
    • Replication instance               : Select myreplicationinstance
      
    • Source database endpoint     : sourcemysqlendpoint
      
    • Target database endpoint       : awsrdsendpoint
      
    • Migration type                             : Migrate existing data and replicate ongoing changes
      
    • Start task on create        : Check
      
  • Task settings:
    
    • Target table preparation mode           : Do nothing
      
    • Leave other configurations as default
      
  • Table mappings:
    
    • Editing mode         : 
      
    • Selection rules        : 
      
    • Schema        : Select Enter Schema
      
    • Schema name        : SchoolDB (Database name)
      
    • Table name             : students (Table name)
      
    • Action                      : Include
      
  • Leave other settings as default.
    
• Click on .
  
• Now the migration of Database will occur. Usually it will take around 1 minute in this lab.
  

Status of AWS Database Migration Tasks:

• Navigate toin left panel of DMS page.
  
• If you followed all the previous steps correctly, it will show the migration task status as
  
• Now the Migration of database to Amazon RDS is completed.
  
• To check the migration status and details, we need to connect to destination MySQL database which is in AWS RDS Instance.
  
• SSH back into Source EC2 Instance.
  
• Connect to AWS RDS instance
  
  • mysql -h mydbinstance.c81x4bxxayay.us-east-1.rds.amazonaws.com -u awsrdsuser -p
    
• Once inside MySQL Client, check the databases available.
  
  • show databases;
    

  
  

1. Run command: use SchoolDB;
   
   
2. Run command: show tables;
   
   
3. Run command: select * from students;
   
   
4. Now we can see the SchoolDB which was available in Source EC2 Ubuntu Server migrated to Amazon RDS Instance Database.
   

Completion and Conclusion:

1. You have launched an EC2 instance and installed MySQL server on it.
   
2. You have created AWS RDS(MySQL) instance and accessed via an EC2 instance.
   
3. You have learned how to create Replication Instance, Source Endpoint, Destination Endpoint.
   
4. You have learned how to create Database Migration Task and migrate Database from source database to destination database.
   
5. You have learned how to Migrate your MySQL database exists on the EC2 instance to AWS RDS Instance.