Mismo Systems

1. Launch an EC2 Instance (Ubuntu)

1.  Configure security groups:

        Allow SSH (port 22) for your IP.

        Allow MySQL/MariaDB (port 3306) from trusted sources (or your IP for now).

1. Now, connect to the instance and Install MariaDB on the EC2 Instance

    # Update package list

sudo apt update

# Install MariaDB server

sudo apt install mariadb-server -y

# Secure MariaDB installation

sudo mysql_secure_installation

1. Create a Sample Database and Table

 sudo mysql -u root -p

CREATE DATABASE testdb;

USE testdb;

CREATE TABLE employees (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100),

    role VARCHAR(100)

);

INSERT INTO employees (name, role) VALUES ('Alice', 'Manager'), ('Bob', 'Developer');

1. Create Users and Grant Permissions

CREATE USER 'test_user'@'%' IDENTIFIED BY 'strong_password';

GRANT ALL PRIVILEGES ON my_database.* TO 'db_user'@'%';

FLUSH PRIVILEGES;

1. Test the new user:

exit

mysql -u db_user -p -h localhost

1. Edit the MariaDB configuration file:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

1. Find the bind-address directive and set it to 0.0.0.0:

Bind-address = 0.0.0.0 

**In this file we must put the following information under the [mysqld]

[mysqld]

log-bin=mysql-bin  

server-id=1

log-bin: This enables binary logging and specifies the prefix for binary log files.

server-id: This sets a unique server ID for the MySQL instance (important for replication).

(For finding the server-ID which is unique for every database we can run the command SHOW VARIABLES LIKE 'server_id'; after connecting to MYSQL by root user.) 

1. sudo systemctl restart mariadb

1. update the EC2 Security Group:

Allow incoming connections to port 3306 from your IP or a range of IPs.

Now,

Set Up RDS Database :

• Step 1: Log in to your AWS Management Console.

Navigate to RDS from the list of services.

• Step 2: Create a New Database

Click on "Create database".

• Step 3: Choose Database Engine

Select MariaDB as the database engine.

• Step 4: Choose a Template

Under the "Template" section, choose Dev/Test for non-production workloads.

• Step 5: Configure Database Credentials

In the Credential settings section:

Select "Self-managed".

Enter a master username and password.

Note down the credentials for later use.

• Step 6: Configure Instance Settings

Under the Instance configuration section:

Select the Burstable classes (e.g., db.t3.micro) for cost-effective usage.

Adjust storage capacity as per your requirement (e.g., 20 GB).

• Step 7: Set Up Connectivity

In the Connectivity section:

Select Connect to an EC2 compute resource.

Choose "No" for public access to ensure security.

Select an appropriate security group that allows access from your EC2 instance.

• Step 8: Create the Database
• Review all configurations and click on "Create database".

Create Secrets for EC2 and RDS Databases

Step 1: Navigate to AWS Secrets Manager

Step 2: Create a Secret for the EC2 Database

1. Click on "Store a new secret".
2. In the Secret type section, select "Credentials for other database".
3. Under Credentials, provide the following details:
   • User name: Enter the username you created for the database on the EC2 instance.
   • Password: Enter the corresponding password.
4. In the Database section:
   • Choose the appropriate database type (e.g., MariaDB).
   • Enter the Server address (e.g., private IP of the EC2 instance).
   • Provide the Database name and Port used by the EC2 database.
5. Assign an Encryption key (or use the default AWS Key Management Service key).
6. Click Next to set permissions, tags, and store the secret.

Step 3: Create a Secret for the RDS Database

1. Repeat the steps above to create a new secret for the RDS database.
2. Under Credentials, use the admin username and password you specified when creating the RDS instance.
3. In the Database section:
   • Choose MariaDB as the database type.
   • Enter the Endpoint of the RDS database (available in the RDS console).
   • Provide the Database name and Port used by the RDS database.
4. Click Next and complete the process to store the secret.

Set Up EC2 Data Base  Migration AWS RDS 

1. Select your database and then I n the Actions dropdown menu, select "Migrate data from EC2 database"

Configure Source EC2 Database and Target RDS Database for Migration

Step 1: Configure the Source EC2 Database

1. Select Source EC2 Instance:

In the migration setup screen, choose the EC2 instance where the source database is hosted.

2. Provide Source Database Details:

Enter the Server Name (private IP or hostname of the EC2 instance).

Specify the database Port (e.g., 3306 for MariaDB).

3. Select or Create a Secret:

Under the Secret section:

Select "Use an existing secret" and choose the secret you created earlier for the EC2 database.

If needed, create a new secret using the credentials of the EC2 database user.

4. Create or Assign an IAM Role:

Under the IAM role for secret access, select "Create and use a new IAM role". AWS will generate a new IAM role to access the secrets securely.

Step 2: Configure the Target RDS Database

1. Select Target Database:

Verify that the correct RDS instance (MariaDB) is selected as the target.

2. Provide RDS Database Details:

Ensure compatibility between the source and target databases.

Optionally, enable SSL if required for secure communication.

3. Select or Create a Secret:

Under the Secret section:

Select "Use an existing secret" and choose the secret you created earlier for the RDS database.

If needed, create a new secret with the admin credentials of the RDS database.

4. Create or Assign an IAM Role:

Under the IAM role for secret access, select "Create and use a new IAM role". AWS will generate a new IAM role to access the RDS database secrets.

Step 3: Proceed with Migration

• After configuring both the source EC2 database and the target RDS database:

Review the settings and ensure all connections are secure and properly configured.

And, Configure data migration,

Select, Full load and change data capture (CDC){ Perform a one-time migration from the source to the target, and then continue replicating data changes from the source to the target. Choose this option for minimal downtime.}

ERRORS OCUURS WHILE PERFORMING DB MIGRATION

1. Failed to connect to source database host 172.31.10.158 and port 3306. Please check network configuration.

Solution – Edit the security group which was created by RDS and was attached to EC2 instance by RDS itself .

Add the port 3306 from 0.0.0.0

1. No tables were found at task initialization. Either the table(s) no longer exist or no match was found. Please verify that table(s) exist on the source database and the database user has proper permissions.

Solution- The user was not able to list the Database and hence we were facing this issue , then we gave full privileges to the user and then this issue got resolved

1. Binary logs are not enabled on source database. Please refer to public documentation for details.

Solution- For this we must enable the binary logs in the Instance

Step 1- Open this file “sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf”

Step 2- In this file we must put the following information under the [mysqld]

[mysqld]

log-bin=mysql-bin

server-id=1

log-bin: This enables binary logging and specifies the prefix for binary log files.

server-id: This sets a unique server ID for the MySQL instance (important for replication).

(For finding the server-ID which is unique for every database we can run the command SHOW VARIABLES LIKE 'server_id'; after connecting to MYSQL by root user.) 

4. Failed to connect to source database host 172.31.0.212 and port 3306. Username or password is incorrect.

Solution- Verify the Database Credentials

1. Test the credentials manually:
   • SSH into the source EC2 instance.
   • Try connecting to the MariaDB database locally using the credentials:
   • If the username or password is incorrect, reset the password:
2. Re-enter credentials in the AWS Database Migration Service (DMS):
   • Go to the DMS Console.
   • Edit the source endpoint for the EC2-hosted database and ensure the username and password are correct

ALTER USER 'test_user'@'%' IDENTIFIED BY 'new_password';
 FLUSH PRIVILEGES;

If using UFW on the EC2 instance, ensure port 3306 is open

sudo ufw allow 3306/tcp
 sudo ufw reload