Deployment and Migration

# CloudFormation template that deploys a VPC, subnet, subnet group, security
# group, and an RDS MySQL instance
Resources:
  # Create a VPC for the database
  Vpc:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: "10.0.0.0/16"
      EnableDnsSupport: true
      EnableDnsHostnames: true
      Tags:
        - Key: Name
          Value: MyDatabaseVpc

  # Create a subnet for the database
  Subnet:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref Vpc
      CidrBlock: "10.0.0.0/24"
      AvailabilityZone: "us-west-2a"
      Tags:
        - Key: Name
          Value: MyDatabaseSubnet

  # Create a subnet group for the database
  DatabaseSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: Subnet group for MyDatabase
      SubnetIds:
        - !Ref Subnet

  # Create a security group for the database
  SecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Allow traffic to database
      VpcId: !Ref Vpc
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 3306
          ToPort: 3306
          CidrIp: 0.0.0.0/0
      Tags:
        - Key: Name
          Value: MyDatabaseSecurityGroup

  # Create an RDS instance for the database
  Database:
    Type: AWS::RDS::DBInstance
    Properties:
      Engine: mysql
      EngineVersion: "8.0"
      DBInstanceClass: db.t2.micro
      AllocatedStorage: "20"
      DBInstanceIdentifier: MyDatabase
      MasterUsername: admin
      MasterUserPassword: mypassword
      DBSubnetGroupName: !Ref DatabaseSubnetGroup
      VPCSecurityGroups:
        - !Ref SecurityGroup
      Tags:
        - Key: Name
          Value: MyDatabaseInstance

# Deploy the stack with:
#   aws cloudformation create-stack --stack-name mydatabase --template-body file://database.yaml

# CloudFormation template that deploys a VPC, subnet, subnet group, security
# group, and an RDS MySQL instance
Resources:
  # Create a VPC for the database
  Vpc:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: "10.0.0.0/16"
      EnableDnsSupport: true
      EnableDnsHostnames: true
      Tags:
        - Key: Name
          Value: MyDatabaseVpc

  # Create a subnet for the database
  Subnet:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref Vpc
      CidrBlock: "10.0.0.0/24"
      AvailabilityZone: "us-west-2a"
      Tags:
        - Key: Name
          Value: MyDatabaseSubnet

  # Create a subnet group for the database
  DatabaseSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: Subnet group for MyDatabase
      SubnetIds:
        - !Ref Subnet

  # Create a security group for the database
  SecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Allow traffic to database
      VpcId: !Ref Vpc
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 3306
          ToPort: 3306
          CidrIp: 0.0.0.0/0
      Tags:
        - Key: Name
          Value: MyDatabaseSecurityGroup

  # Create an RDS instance for the database
  Database:
    Type: AWS::RDS::DBInstance
    Properties:
      Engine: mysql
      EngineVersion: "8.0"
      DBInstanceClass: db.t2.micro
      AllocatedStorage: "20"
      DBInstanceIdentifier: MyDatabase
      MasterUsername: admin
      MasterUserPassword: mypassword
      DBSubnetGroupName: !Ref DatabaseSubnetGroup
      VPCSecurityGroups:
        - !Ref SecurityGroup
      Tags:
        - Key: Name
          Value: MyDatabaseInstance

# Deploy the stack with:
#   aws cloudformation create-stack --stack-name mydatabase --template-body file://database.yaml

# Terraform configuration that deploys a VPC, subnet, security group, and an
# RDS MySQL instance

# Create a VPC for the database
resource "aws_vpc" "database_vpc" {
  cidr_block           = "10.0.0.0/16"
  enable_dns_hostnames = true
  enable_dns_support   = true

  tags = {
    Name = "MyDatabaseVpc"
  }
}

# Create a subnet for the database
resource "aws_subnet" "database_subnet" {
  vpc_id            = aws_vpc.database_vpc.id
  cidr_block        = "10.0.0.0/24"
  availability_zone = "us-west-2a"

  tags = {
    Name = "MyDatabaseSubnet"
  }
}

# Create a subnet group for the database
resource "aws_db_subnet_group" "database_subnet_group" {
  name       = "MyDatabaseSubnetGroup"
  subnet_ids = [aws_subnet.database_subnet.id]
}

# Create a security group for the database
resource "aws_security_group" "database_security_group" {
  name   = "MyDatabaseSecurityGroup"
  vpc_id = aws_vpc.database_vpc.id

  ingress {
    from_port   = 3306
    to_port     = 3306
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }

  tags = {
    Name = "MyDatabaseSecurityGroup"
  }
}

# Create an RDS instance for the database
resource "aws_db_instance" "database_instance" {
  engine                  = "mysql"
  engine_version          = "8.0"
  instance_class          = "db.t2.micro"
  allocated_storage       = 20
  identifier              = "MyDatabase"
  username                = "admin"
  password                = "mypassword"
  db_subnet_group_name    = aws_db_subnet_group.database_subnet_group.name
  vpc_security_group_ids  = [aws_security_group.database_security_group.id]

  tags = {
    Name = "MyDatabaseInstance"
  }
}

# Deploy with:
#   terraform init
#   terraform apply

# Terraform configuration that deploys a VPC, subnet, security group, and an
# RDS MySQL instance

# Create a VPC for the database
resource "aws_vpc" "database_vpc" {
  cidr_block           = "10.0.0.0/16"
  enable_dns_hostnames = true
  enable_dns_support   = true

  tags = {
    Name = "MyDatabaseVpc"
  }
}

# Create a subnet for the database
resource "aws_subnet" "database_subnet" {
  vpc_id            = aws_vpc.database_vpc.id
  cidr_block        = "10.0.0.0/24"
  availability_zone = "us-west-2a"

  tags = {
    Name = "MyDatabaseSubnet"
  }
}

# Create a subnet group for the database
resource "aws_db_subnet_group" "database_subnet_group" {
  name       = "MyDatabaseSubnetGroup"
  subnet_ids = [aws_subnet.database_subnet.id]
}

# Create a security group for the database
resource "aws_security_group" "database_security_group" {
  name   = "MyDatabaseSecurityGroup"
  vpc_id = aws_vpc.database_vpc.id

  ingress {
    from_port   = 3306
    to_port     = 3306
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }

  tags = {
    Name = "MyDatabaseSecurityGroup"
  }
}

# Create an RDS instance for the database
resource "aws_db_instance" "database_instance" {
  engine                  = "mysql"
  engine_version          = "8.0"
  instance_class          = "db.t2.micro"
  allocated_storage       = 20
  identifier              = "MyDatabase"
  username                = "admin"
  password                = "mypassword"
  db_subnet_group_name    = aws_db_subnet_group.database_subnet_group.name
  vpc_security_group_ids  = [aws_security_group.database_security_group.id]

  tags = {
    Name = "MyDatabaseInstance"
  }
}

# Deploy with:
#   terraform init
#   terraform apply

# File: dms_migration.yaml
# Describes an AWS DMS full-load replication task from a MySQL source to a
# PostgreSQL target
---
Task:
  Name: MySQL-to-PostgreSQL-Migration
  SourceEndpoint:
    EndpointIdentifier: mysql-source-endpoint
    EngineName: mysql
    ServerName: mysql-server.example.com
    Port: 3306
    DatabaseName: mydb
    ExtraConnectionAttributes: "ssl_mode=required"
    UserName: user
    Password: password
  TargetEndpoint:
    EndpointIdentifier: postgresql-target-endpoint
    EngineName: postgresql
    ServerName: postgresql-server.example.com
    Port: 5432
    DatabaseName: mydb
    ExtraConnectionAttributes: "sslmode=require"
    UserName: user
    Password: password
  ReplicationInstanceArn: arn:aws:dms:us-west-2:123456789012:rep:my-replication-instance
  MigrationType: full-load
  TableMappings:
    - TableName: mytable
      TargetTablePrepMode: doNothing
  MigrationSettings:
    TargetMetadata:
      TargetSchema: public
      SupportLobs: true

# Create the replication task from this file:
#   aws dms create-replication-task --replication-task-settings file://dms_migration.yaml

# File: dms_migration.yaml
# Describes an AWS DMS full-load replication task from a MySQL source to a
# PostgreSQL target
---
Task:
  Name: MySQL-to-PostgreSQL-Migration
  SourceEndpoint:
    EndpointIdentifier: mysql-source-endpoint
    EngineName: mysql
    ServerName: mysql-server.example.com
    Port: 3306
    DatabaseName: mydb
    ExtraConnectionAttributes: "ssl_mode=required"
    UserName: user
    Password: password
  TargetEndpoint:
    EndpointIdentifier: postgresql-target-endpoint
    EngineName: postgresql
    ServerName: postgresql-server.example.com
    Port: 5432
    DatabaseName: mydb
    ExtraConnectionAttributes: "sslmode=require"
    UserName: user
    Password: password
  ReplicationInstanceArn: arn:aws:dms:us-west-2:123456789012:rep:my-replication-instance
  MigrationType: full-load
  TableMappings:
    - TableName: mytable
      TargetTablePrepMode: doNothing
  MigrationSettings:
    TargetMetadata:
      TargetSchema: public
      SupportLobs: true

# Create the replication task from this file:
#   aws dms create-replication-task --replication-task-settings file://dms_migration.yaml

-- Sample AWS Schema Conversion Tool (SCT) script, run in the SCT SQL editor,
-- that stages a table and converts its data types and functions from an
-- Oracle source schema to a MySQL target schema

-- Work against the source database
USE aws_oracle_source;

-- Stage the conversion in a temporary table
CREATE TABLE temp_table (
    id   INT,
    name VARCHAR(50)
);

INSERT INTO temp_table
SELECT id, name FROM source_table;

DROP TABLE source_table;
ALTER TABLE temp_table RENAME TO source_table;

-- Switch to the target database and adjust data types/functions for MySQL
USE aws_mysql_target;

ALTER TABLE source_table
    MODIFY COLUMN id BIGINT,
    MODIFY COLUMN name VARCHAR(255);

UPDATE source_table
    SET name = CONCAT('Hello, ', name);

-- Sample AWS Schema Conversion Tool (SCT) script, run in the SCT SQL editor,
-- that stages a table and converts its data types and functions from an
-- Oracle source schema to a MySQL target schema

-- Work against the source database
USE aws_oracle_source;

-- Stage the conversion in a temporary table
CREATE TABLE temp_table (
    id   INT,
    name VARCHAR(50)
);

INSERT INTO temp_table
SELECT id, name FROM source_table;

DROP TABLE source_table;
ALTER TABLE temp_table RENAME TO source_table;

-- Switch to the target database and adjust data types/functions for MySQL
USE aws_mysql_target;

ALTER TABLE source_table
    MODIFY COLUMN id BIGINT,
    MODIFY COLUMN name VARCHAR(255);

UPDATE source_table
    SET name = CONCAT('Hello, ', name);

import boto3
import mysql.connector
import psycopg2

# Connect to the source MySQL database
mysql_conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="mydb"
)

# Select the data to migrate
mysql_cursor = mysql_conn.cursor()
mysql_cursor.execute(
    "SELECT column1, column2, column3 FROM mytable WHERE condition"
)
mysql_data = mysql_cursor.fetchall()

# Connect to the target PostgreSQL database
pg_conn = psycopg2.connect(
    host="localhost",
    user="postgres",
    password="password",
    database="mydb"
)

# Create the target table
pg_cursor = pg_conn.cursor()
pg_cursor.execute(
    "CREATE TABLE mytable (column1 INT, column2 VARCHAR(50), column3 TEXT)"
)

# Insert the extracted rows into PostgreSQL
for data in mysql_data:
    pg_cursor.execute(
        "INSERT INTO mytable (column1, column2, column3) VALUES (%s, %s, %s)",
        data
    )

pg_conn.commit()
mysql_cursor.close()
mysql_conn.close()
pg_cursor.close()
pg_conn.close()

# Export the migrated PostgreSQL table to S3 as CSV
s3 = boto3.client('s3')
bucket_name = 'mybucket'
key = 'mytable.csv'

pg_conn = psycopg2.connect(
    host="localhost",
    user="postgres",
    password="password",
    database="mydb"
)
pg_cursor = pg_conn.cursor()
with open(key, "w") as f:
    pg_cursor.copy_expert(
        "COPY mytable TO STDOUT WITH DELIMITER ',' CSV HEADER", f
    )
s3.upload_file(key, bucket_name, key)

# Load the S3 object into Amazon Redshift via the Redshift Data API
redshift = boto3.client('redshift-data')
redshift_copy_command = (
    f"COPY mytable FROM 's3://{bucket_name}/{key}' "
    "CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRedshiftRole' "
    "CSV DELIMITER ','"
)
redshift.execute_statement(Sql=redshift_copy_command)

import boto3
import mysql.connector
import psycopg2

# Connect to the source MySQL database
mysql_conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="mydb"
)

# Select the data to migrate
mysql_cursor = mysql_conn.cursor()
mysql_cursor.execute(
    "SELECT column1, column2, column3 FROM mytable WHERE condition"
)
mysql_data = mysql_cursor.fetchall()

# Connect to the target PostgreSQL database
pg_conn = psycopg2.connect(
    host="localhost",
    user="postgres",
    password="password",
    database="mydb"
)

# Create the target table
pg_cursor = pg_conn.cursor()
pg_cursor.execute(
    "CREATE TABLE mytable (column1 INT, column2 VARCHAR(50), column3 TEXT)"
)

# Insert the extracted rows into PostgreSQL
for data in mysql_data:
    pg_cursor.execute(
        "INSERT INTO mytable (column1, column2, column3) VALUES (%s, %s, %s)",
        data
    )

pg_conn.commit()
mysql_cursor.close()
mysql_conn.close()
pg_cursor.close()
pg_conn.close()

# Export the migrated PostgreSQL table to S3 as CSV
s3 = boto3.client('s3')
bucket_name = 'mybucket'
key = 'mytable.csv'

pg_conn = psycopg2.connect(
    host="localhost",
    user="postgres",
    password="password",
    database="mydb"
)
pg_cursor = pg_conn.cursor()
with open(key, "w") as f:
    pg_cursor.copy_expert(
        "COPY mytable TO STDOUT WITH DELIMITER ',' CSV HEADER", f
    )
s3.upload_file(key, bucket_name, key)

# Load the S3 object into Amazon Redshift via the Redshift Data API
redshift = boto3.client('redshift-data')
redshift_copy_command = (
    f"COPY mytable FROM 's3://{bucket_name}/{key}' "
    "CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRedshiftRole' "
    "CSV DELIMITER ','"
)
redshift.execute_statement(Sql=redshift_copy_command)

-- Direct SQL migration script that transfers data from a MySQL source
-- database to a PostgreSQL target database

-- Select the data to migrate from the MySQL source
USE mydb;

SELECT column1, column2, column3
FROM mytable
WHERE condition;

-- Connect to the PostgreSQL target (psql meta-command)
\connect postgresql://user:password@localhost/mydb

-- Create the target table
CREATE TABLE mytable (
    column1 INT,
    column2 VARCHAR(50),
    column3 TEXT
);

-- Insert the migrated rows
INSERT INTO mytable (column1, column2, column3)
VALUES
    (value1, 'value2', 'value3'),
    (value4, 'value5', 'value6');

-- Direct SQL migration script that transfers data from a MySQL source
-- database to a PostgreSQL target database

-- Select the data to migrate from the MySQL source
USE mydb;

SELECT column1, column2, column3
FROM mytable
WHERE condition;

-- Connect to the PostgreSQL target (psql meta-command)
\connect postgresql://user:password@localhost/mydb

-- Create the target table
CREATE TABLE mytable (
    column1 INT,
    column2 VARCHAR(50),
    column3 TEXT
);

-- Insert the migrated rows
INSERT INTO mytable (column1, column2, column3)
VALUES
    (value1, 'value2', 'value3'),
    (value4, 'value5', 'value6');