End-to-End Database Server Configuration Task

This Task automates the installation and configuration of MySQL or PostgreSQL using Terraform and Ansible. It sets up the database server on an AWS EC2 instance, provisions necessary resources, and configures the database with users and permissions.

📌 Task Overview

1️⃣ Goals

• ✅ Automate database server setup (MySQL/PostgreSQL)
• ✅ Provision infrastructure using Terraform
• ✅ Configure the database using Ansible
• ✅ Create databases, users, and assign permissions
• ✅ Ensure high availability and security

Task Workflow

1. Infrastructure Provisioning with Terraform

• Define an AWS EC2 instance (Amazon Linux 2 / Ubuntu).
• Attach an Elastic IP for stability.
• Set up Security Groups to allow only necessary access.
• Create an RDS instance (optional) if managing DB externally.
• Use IAM Roles and Policies for secure access.

2. Database Installation and Configuration using Ansible

• Install required system dependencies.
• Install MySQL or PostgreSQL based on configuration.
• Configure database parameters (e.g., memory limits, connection settings).
• Secure the installation (disable remote root login, set strong passwords).
• Enable and start the database service.

3. Database & User Creation

• Create a new database.
• Create a database user and assign permissions.
• Grant privileges for secure access.

4. Security & Access Control

• Configure firewall rules to allow access only from authorized IPs.
• Set up TLS encryption for secure connections.
• Enable automatic backups and logging.

5. Testing and Validation

• Verify that MySQL/PostgreSQL is installed and running.
• Test database connectivity from an application or client.
• Ensure users and permissions are correctly configured.
• Validate security configurations.

6. Monitoring and Logging Setup

• Enable AWS CloudWatch Logs for database auditing.
• Set up Prometheus and Grafana for monitoring.
• Configure alerts for CPU, memory, storage, and connection metrics.

🔧 Tools & Technologies

• Terraform → Infrastructure provisioning
• Ansible → Configuration management
• AWS → EC2, Security Groups, IAM, RDS (optional)
• GitHub/GitLab → Version control
• Jenkins (optional) → CI/CD for automation

📂 Task Structure

├── terraform/
│   ├── main.tf
│   ├── variables.tf
│   ├── outputs.tf
│   ├── terraform.tfvars
│   └── provider.tf
├── ansible/
│   ├── inventory.ini
│   ├── playbook.yml
│   ├── roles/
│   │   ├── db_install/
│   │   │   ├── tasks/main.yml
│   │   │   ├── handlers/main.yml
│   │   │   ├── templates/
│   │   │   ├── files/
│   │   │   └── vars/main.yml
│   │   ├── db_config/
│   │   │   ├── tasks/main.yml
│   │   │   ├── handlers/main.yml
│   │   │   ├── templates/
│   │   │   ├── files/
│   │   │   └── vars/main.yml
└── README.md
    

🔹 Implementation Steps

Step 1️⃣: Terraform - Provision Infrastructure

Use Terraform to:

• Create an EC2 instance for the database server.
• Attach a security group for DB access.
• Optionally, use RDS for managed DB hosting.

📌 Terraform Configuration (`main.tf`)

provider "aws" {
  region = "us-east-1"
}

resource "aws_instance" "db_server" {
  ami           = "ami-12345678" # Ubuntu 20.04 LTS
  instance_type = "t2.medium"
  security_groups = [aws_security_group.db_sg.name]
  key_name        = "db-key-pair"
  tags = {
    Name = "DatabaseServer"
  }
}

resource "aws_security_group" "db_sg" {
  name        = "db_security_group"
  description = "Allow MySQL/PostgreSQL access"
  ingress {
    from_port   = 3306  # Use 5432 for PostgreSQL
    to_port     = 3306
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]  # Restrict to known IPs
  }
}
    

Run Terraform:

terraform init
terraform apply -auto-approve

Step 2️⃣: Ansible - Install and Configure Database

Once Terraform provisions the EC2 instance, Ansible is used for:

• Installing MySQL or PostgreSQL
• Creating databases and users
• Configuring permissions and settings

📌 Ansible Inventory (inventory.ini)

[database]
db_server ansible_host= ansible_user=ubuntu ansible_ssh_private_key_file=~/.ssh/id_rsa

📌 Ansible Playbook (`playbook.yml`)

- name: Configure Database Server
  hosts: database
  become: true
  roles:
    - db_install
    - db_config
    

📌 Role 1: Install Database

(roles/db_install/tasks/main.yml)

- name: Install MySQL Server
  apt:
    name: mysql-server
    state: present
  when: "'mysql' in ansible_facts['distribution']"

- name: Install PostgreSQL Server
  apt:
    name: postgresql
    state: present
  when: "'postgresql' in ansible_facts['distribution']"

- name: Ensure MySQL is running
  service:
    name: mysql
    state: started
    enabled: yes
  when: "'mysql' in ansible_facts['distribution']"

- name: Ensure PostgreSQL is running
  service:
    name: postgresql
    state: started
    enabled: yes
  when: "'postgresql' in ansible_facts['distribution']"

📌 Role 2: Configure Database

- name: Create Database
  mysql_db:
    name: my_database
    state: present
  when: "'mysql' in ansible_facts['distribution']"

- name: Create PostgreSQL Database
  postgresql_db:
    name: my_database
    state: present
  when: "'postgresql' in ansible_facts['distribution']"

- name: Create Database User
  mysql_user:
    name: db_user
    password: db_pass
    priv: "my_database.*:ALL"
    host: "%"
    state: present
  when: "'mysql' in ansible_facts['distribution']"

- name: Create PostgreSQL User
  postgresql_user:
    db: my_database
    name: db_user
    password: db_pass
    state: present
  when: "'postgresql' in ansible_facts['distribution']"

👉 Run Ansible:

ansible-playbook -i inventory.ini playbook.yml

Run Ansible:

ansible-playbook -i inventory.ini playbook.yml

🔐 Security Best Practices

• 🔹 Restrict DB access to specific IPs
• 🔹 Use IAM roles for EC2 instead of hardcoded credentials
• 🔹 Enable backups (for RDS)
• 🔹 Use parameterized queries to prevent SQL injection
• 🔹 Set up monitoring using CloudWatch or Prometheus

🚀 Deployment Flows

• 🔹 Terraform provisions AWS resources
• 🔹 Ansible installs and configures the database
• 🔹 Users & permissionssare assigned
• 🔹 Security policiess are appliedon
• 🔹 Database is ready for use!

📌 Expected Outputs

• 🔹 MySQL/PostgreSQL installed on EC2s
• 🔹 Database and user configured
• 🔹 Remote access enabled securely
• 🔹 Ready-to-use Terraform & Ansible scripts

📜 Next Steps

• 🔹 Integrate CI/CD with Jenkins
• 🔹 Enable SSL/TLS encryption
• 🔹 Automate backups using AWS Backup
• 🔹 Set up Database Replication for HA

📚 Summary

This project provides an end-to-end automated database setup using Terraform (for infrastructure) and Ansible (for configuration). It installs either MySQL or PostgreSQL, creates databases, users, and permissions, and secures access.