"Python Atomation : AWS EC2 Setup with MySQL Installation and Database Configuration"

Installing and import Required Python Packages: boto3 and paramiko

!pip install boto3 paramiko

import boto3
import paramiko
import time
import subprocess

Declare important variable

must be change the variable values

# AWS Credentials
aws_access_key = 'your aws_access_key'
aws_secret_key = 'your aws_secret_key'

# EC2 instance details
#you can change according to your need
region_name = 'ap-south-1'
instance_type = 't2.micro'
ami_id = 'ami-0287a05f0ef0e9d9a'  #default ami Ubuntu 20.04 LTS 64-bit
key_pair_name = 'ETL_KP'
security_group_name = 'ETL_SG'
tag_key = 'Name'
tag_value = 'Etl_Project_server'

AWS EC2 Connection Setup

# Create object for Connect to EC2
ec2 = boto3.resource('ec2', aws_access_key_id=aws_access_key, aws_secret_access_key=aws_secret_key, region_name=region_name)

# Create object for Connect an EC2 client
ec2_client = boto3.client('ec2', aws_access_key_id=aws_access_key, aws_secret_access_key=aws_secret_key, region_name=region_name)
     

Configure AWS EC2 Instance, Key Pair, Security Group, and Instance Launch Setup

# Create key pair
key_pair = ec2.create_key_pair(KeyName=key_pair_name)
with open(f'{key_pair_name}.pem', 'w') as key_file:
    key_file.write(key_pair.key_material)
print(f'Key pair "{key_pair_name}" created and saved to {key_pair_name}.pem')

# Create security group
security_group = ec2.create_security_group(
    GroupName=security_group_name,
    Description='Security group for MySQL, HTTP, SSH access',
    VpcId='vpc-051c6bf2ff976c0e8'  # Replace with your VPC ID
)

# Authorize inbound rules for SSH, HTTP, and MySQL
security_group.authorize_ingress(
    IpProtocol='tcp',
    FromPort=22,
    ToPort=22,
    CidrIp='0.0.0.0/0'
)

security_group.authorize_ingress(
    IpProtocol='tcp',
    FromPort=80,
    ToPort=80,
    CidrIp='0.0.0.0/0'
)

security_group.authorize_ingress(
    IpProtocol='tcp',
    FromPort=3306,
    ToPort=3306,
    CidrIp='0.0.0.0/0'
)

print(f'Security group "{security_group_name}" created with SSH, HTTP, and MySQL access')

# Launch EC2 instance
instances = ec2.create_instances(
    ImageId=ami_id,
    InstanceType=instance_type,
    MinCount=1,
    MaxCount=1,
    KeyName=key_pair_name,
    SecurityGroupIds=[security_group.id],
    TagSpecifications=[
                {
                    'ResourceType': 'instance',
                    'Tags': [
                        {'Key': tag_key, 'Value': tag_value},
                        # Add more tags as needed
                    ]
                }
            ]
)

instance = instances[0]

# Wait for the instance to be running
print(f'Launching EC2 instance with ID: {instance.id}')
instance.wait_until_running()
instance.reload()
print(f'EC2 instance running with public IP: {instance.public_ip_address}')

SSH into AWS EC2 Instance and Execute MySQL Setup Commands

before connecting attach a iam role with s3 permission to the EC2 instance

change the sql user password at 'your_sql_user_password'

# SSH into the instance (you might need to replace the key file path and username)
ssh_client = paramiko.SSHClient()
ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())

key_file_path = f'{key_pair_name}.pem'
ssh_client.connect(instance.public_ip_address, username='ubuntu', key_filename=key_file_path)

# Commands to install MySQL server and client, check version, secure installation, and check status
#change the sql user password at 'your_sql_user_password'
ec2_commands = [
    'sudo apt-get upgrade -y',
    'sudo apt-get install nginx -y',
    'sudo systemctl enable nginx',
    'sudo systemctl start nginx',
    'sudo systemctl status nginx',
    'sudo apt-get update',
    'sudo apt-get install -y mysql-server mysql-client',
    'sudo systemctl daemon-reload',
    'sudo systemctl enable mysql.service',
    'sudo systemctl start mysql.service',
    'sudo systemctl status mysql.service',
    'sudo mysql --version',
    'sudo mysql -u root -pyour_sql_user_password -e "ALTER USER \'root\'@\'localhost\' IDENTIFIED WITH mysql_native_password BY \'your_sql_user_password\';"',
    'sudo mysql -u root -pyour_sql_user_password -e "CREATE DATABASE imdb;"',
    'sudo mysql -u root -pyour_sql_user_password -e "CREATE USER \'etl\' IDENTIFIED BY \'your_sql_user_password\';"',
    'sudo mysql -u root -pyour_sql_user_password -e "GRANT ALL PRIVILEGES ON imdb.* TO \'etl\';"',
    'sudo mysql -u root -pyour_sql_user_password -e "FLUSH PRIVILEGES;"',
    'sudo systemctl restart mysql.service'

]

# Execute each command
for command in ec2_commands:
    stdin, stdout, stderr = ssh_client.exec_command(command)
    output = stdout.read().decode('utf-8')
    error = stderr.read().decode('utf-8')

    # Print the output and error messages
    print(f"Command Output for '{command}':\n{output}")
    print(f"Command Error for '{command}':\n{error}")

# Close the SSH connection
ssh_client.close()