---
## Amazon Aurora Labs for MySQL
## Infrastructure template with an Aurora cluster for lab exercises
##
## Changelog:
## 2019-05-13 - Initial release
## 2019-05-29 - Changed how AZ selection works
## 2019-06-12 - Changed template to use Secrets MAnager for DB credentials
## 2019-06-13 - Preconfigured aws cli
## 2019-06-14 - Preconfigured DB credentials as ENV variables
## 2019-06-14 - Fixed issue with Scalable Target and uppercase cluster identifiers
## 2019-09-18 - Updated AMIs, Remove SSH access, bootstrap script changes for SSM
## 2019-09-19 - Fixed resource names to enable EE
## 2019-09-23 - Removed custom Lambda resource, using native role assignment
## 2019-09-23 - Removed !Sub references on static names to pass linter
##
## Dependencies:
## none
##
## License:
## This sample code is made available under the MIT-0 license. See the LICENSE file.

AWSTemplateFormatVersion: 2010-09-09
Description: Amazon Aurora Labs for MySQL

## Parameters
Parameters:
  vpcAZs:
    Type: "List<AWS::EC2::AvailabilityZone::Name>"
    Description: List of Availability Zones to use for the subnets in the VPC. Must pick 3 AZs in regions with 3 or more AZs.

## Conditions
Conditions:
  cond3AZs: !Equals [ !FindInMap [ RegionalSettings, !Ref "AWS::Region", azs ], "3" ]

## Mappings
Mappings:
  RegionalSettings:
    us-east-1:
      bastionAmi: ami-024582e76075564db
      bastionType: m5.large
      nodeType: db.r5.large
      name: Ohio
      azs: "3"
    us-east-2:
      bastionAmi: ami-090a9429be63ca087
      bastionType: m5.large
      nodeType: db.r5.large
      name: N. Virginia
      azs: "3"
    us-west-1:
      bastionAmi: ami-0c6eca62e6c7dacff
      bastionType: m5.large
      nodeType: db.r5.large
      name: N. California
      azs: "2"
    us-west-2:
      bastionAmi: ami-09c08e9cd8d9c1b93
      bastionType: m5.large
      nodeType: db.r5.large
      name: Oregon
      azs: "3"
    ca-central-1:
      bastionAmi: ami-0e76f0489f3cca311
      bastionType: m5.large
      nodeType: db.r5.large
      name: Montreal
      azs: "2"
    eu-central-1:
      bastionAmi: ami-0fd6d676e2e90dc7b
      bastionType: m5.large
      nodeType: db.r5.large
      name: Frankfurt
      azs: "3"
    eu-west-1:
      bastionAmi: ami-08f053fa3d25478f4
      bastionType: m5.large
      nodeType: db.r5.large
      name: Ireland
      azs: "3"
    eu-west-2:
      bastionAmi: ami-012d95ddd53c98e0e
      bastionType: m5.large
      nodeType: db.r5.large
      name: London
      azs: "3"
    eu-west-3:
      bastionAmi: ami-0001c7938b8e89312
      bastionType: m5.large
      nodeType: db.r5.large
      name: Paris
      azs: "3"
    ap-southeast-1:
      bastionAmi: ami-0f8c65cdcc35d0a72
      bastionType: m5.large
      nodeType: db.r5.large
      name: Singapore
      azs: "3"
    ap-southeast-2:
      bastionAmi: ami-056280928f86f80a8
      bastionType: m5.large
      nodeType: db.r5.large
      name: Sydney
      azs: "3"
    ap-south-1:
      bastionAmi: ami-03d48fdf7c142da15
      bastionType: m5.large
      nodeType: db.r5.large
      name: Mumbai
      azs: "3"
    ap-northeast-1:
      bastionAmi: ami-0193f3ddafc2d8921
      bastionType: m5.large
      nodeType: db.r5.large
      name: Tokyo
      azs: "3"
    ap-northeast-2:
      bastionAmi: ami-0b0d9551c4775f4e4
      bastionType: m5.large
      nodeType: db.r5.large
      name: Seoul
      azs: "3"
  NetworkSettings:
    global:
      vpcCidr: 172.31.0.0/16
      subPub1Cidr: 172.31.0.0/24
      subPub2Cidr: 172.31.1.0/24
      subPub3Cidr: 172.31.2.0/24
      subPrv1Cidr: 172.31.10.0/24
      subPrv2Cidr: 172.31.11.0/24
      subPrv3Cidr: 172.31.12.0/24
      sshSourceCidr: 0.0.0.0/0
  ClusterSettings:
    global:
      dbSchema: mylab
      dbDriver: mysql
    scaling:
      maxCapacity: 2
      minCapacity: 1
      cpuLoadTarget: 20
    sysbench:
      dbSchema: sbtpcc
      runTime: '300'
      numThreads: '4'
      numTables: '8'
      numWarehouses: '2'


## Resources
Resources:

## The VPC
  vpc:
    Type: AWS::EC2::VPC
    Properties:
      EnableDnsSupport: true
      EnableDnsHostnames: true
      InstanceTenancy: default
      CidrBlock: !FindInMap [ NetworkSettings, global, vpcCidr ]
      Tags:
        - Key: Name
          Value: labstack-vpc

## Create an IGW & attach it to the VPC
  vpcIgw:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: labstack-igw
  attachIgwVpc:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      VpcId: !Ref vpc
      InternetGatewayId: !Ref vpcIgw

## Create a public subnet in each AZ
  sub1Public:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref vpc
      CidrBlock: !FindInMap [ NetworkSettings, global, subPub1Cidr ]
      AvailabilityZone: !Select [0, !Ref vpcAZs ]
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: labstack-pub-sub-1
  sub2Public:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref vpc
      CidrBlock: !FindInMap [ NetworkSettings, global, subPub2Cidr ]
      AvailabilityZone: !Select [1, !Ref vpcAZs ]
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: labstack-pub-sub-2
  sub3Public:
    Type: AWS::EC2::Subnet
    Condition: cond3AZs
    Properties:
      VpcId: !Ref vpc
      CidrBlock: !FindInMap [ NetworkSettings, global, subPub3Cidr ]
      AvailabilityZone: !Select [2, !Ref vpcAZs ]
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: labstack-pub-sub-3

## Associate the public subnets with a public route table
  rtbPublic:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref vpc
      Tags:
        - Key: Name
          Value: labstack-public-rtb
  rteToIgw:
    Type: AWS::EC2::Route
    DependsOn: attachIgwVpc
    Properties:
      RouteTableId: !Ref rtbPublic
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref vpcIgw
  srta1Public:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref sub1Public
      RouteTableId: !Ref rtbPublic
  srta2Public:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref sub2Public
      RouteTableId: !Ref rtbPublic
  srta3Public:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Condition: cond3AZs
    Properties:
      SubnetId: !Ref sub3Public
      RouteTableId: !Ref rtbPublic

## Create a private subnet in each AZ
  sub1Private:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref vpc
      CidrBlock: !FindInMap [ NetworkSettings, global, subPrv1Cidr ]
      AvailabilityZone: !Select [0, !Ref vpcAZs ]
      MapPublicIpOnLaunch: false
      Tags:
        - Key: Name
          Value: labstack-prv-sub-1
  sub2Private:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref vpc
      CidrBlock: !FindInMap [ NetworkSettings, global, subPrv2Cidr ]
      AvailabilityZone: !Select [1, !Ref vpcAZs ]
      MapPublicIpOnLaunch: false
      Tags:
        - Key: Name
          Value: labstack-prv-sub-2
  sub3Private:
    Type: AWS::EC2::Subnet
    Condition: cond3AZs
    Properties:
      VpcId: !Ref vpc
      CidrBlock: !FindInMap [ NetworkSettings, global, subPrv3Cidr ]
      AvailabilityZone: !Select [2, !Ref vpcAZs ]
      MapPublicIpOnLaunch: false
      Tags:
        - Key: Name
          Value: labstack-prv-sub-3

## Create a NAT Gateway & EIP
  natEip:
    Type: AWS::EC2::EIP
    Properties:
      Domain: vpc
  vpcNgw:
    Type: AWS::EC2::NatGateway
    DependsOn: attachIgwVpc
    Properties:
      AllocationId: !GetAtt natEip.AllocationId
      SubnetId: !Ref sub2Public

## Associate the private subnets with a NATed route table
  rtbNat:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref vpc
      Tags:
        - Key: Name
          Value: labstack-nat-rtb
  rteToNgw:
    Type: AWS::EC2::Route
    Properties:
      RouteTableId: !Ref rtbNat
      DestinationCidrBlock: 0.0.0.0/0
      NatGatewayId: !Ref vpcNgw
  srta1Ngw:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref sub1Private
      RouteTableId: !Ref rtbNat
  srta2Ngw:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref sub2Private
      RouteTableId: !Ref rtbNat
  srta3Ngw:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Condition: cond3AZs
    Properties:
      SubnetId: !Ref sub3Private
      RouteTableId: !Ref rtbNat

## Create VPC S3 endpoint
  s3Enpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      VpcId: !Ref vpc
      ServiceName: !Sub com.amazonaws.${AWS::Region}.s3
      RouteTableIds:
        - !Ref rtbPublic
        - !Ref rtbNat
      PolicyDocument:
        Version: 2012-10-17
        Statement:
          - Principal: '*'
            Effect: 'Allow'
            Action: 's3:*'
            Resource: [ 'arn:aws:s3:::*', 'arn:aws:s3:::*/*' ]

## Create DB subnet group
  dbSubnets:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: labstack-db-subnet-group
      SubnetIds: [ !Ref sub1Private, !Ref sub2Private, !If [ cond3AZs, !Ref sub3Private, !Ref "AWS::NoValue" ] ]
      Tags:
        - Key: Name
          Value: labstack-db-subnet-group

## Create bastion security group
  bastionSecGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      VpcId: !Ref vpc
      GroupName: labstack-bastion-host
      GroupDescription: Aurora Lab SSH Security Group
      Tags:
        - Key: Name
          Value: labstack-bastion-host

## Create DB security group
  dbSecGroupCluster:
    Type: AWS::EC2::SecurityGroup
    Properties:
      VpcId: !Ref vpc
      GroupName: labstack-mysql-internal
      GroupDescription: Aurora Lab Database Firewall
      Tags:
        - Key: Name
          Value: labstack-mysql-internal
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 3306
          ToPort: 3306
          SourceSecurityGroupId: !Ref bastionSecGroup
          Description: Allows MySQL access from bastion host

## Create enhanced monitoring role
  roleEnhancedMonitoring:
    Type: AWS::IAM::Role
    Properties:
      RoleName: !Sub labstack-monitor-${AWS::Region}
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Action:
              - sts:AssumeRole
            Principal:
              Service:
                - monitoring.rds.amazonaws.com
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole

## Create external integration role
  roleServiceIntegration:
    Type: AWS::IAM::Role
    Properties:
      RoleName: !Sub labstack-integrate-${AWS::Region}
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Action:
              - sts:AssumeRole
            Principal:
              Service:
                - rds.amazonaws.com
      Policies:
        - PolicyName: inline-policy
          PolicyDocument:
            Version: 2012-10-17
            Statement:
              - Effect: Allow
                Action:
                  - s3:ListBucket
                  - s3:GetObject
                  - s3:GetObjectVersion
                  - s3:AbortMultipartUpload
                  - s3:DeleteObject
                  - s3:ListMultipartUploadParts
                  - s3:PutObject
                Resource:
                  - arn:aws:s3:::*/*
                  - arn:aws:s3:::*

## Create role for bastion host
  roleBastionHost:
    Type: AWS::IAM::Role
    Properties:
      RoleName: !Sub labstack-bastion-${AWS::Region}
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Action:
              - 'sts:AssumeRole'
            Principal:
              Service:
                - 'ec2.amazonaws.com'
                - 'ssm.amazonaws.com'
      ManagedPolicyArns:
        - 'arn:aws:iam::aws:policy/service-role/AmazonEC2RoleforSSM'
      Policies:
        - PolicyName: inline-policy
          PolicyDocument:
            Version: 2012-10-17
            Statement:
              - Effect: Allow
                Action:
                  - rds:*
                  - s3:*
                  - ssm:*
                  - kms:*
                  - secretsmanager:*
                  - rds-db:connect
                Resource: "*"
  profileBastionHost:
    Type: AWS::IAM::InstanceProfile
    Properties:
      Path: /
      Roles:
        - Ref: roleBastionHost

## Create the bastion host
  bastionHost:
    Type: AWS::EC2::Instance
    Properties:
      SubnetId: !Ref sub1Public
      InstanceType: !FindInMap [ RegionalSettings, !Ref "AWS::Region", bastionType ]
      SecurityGroupIds: [ !Ref bastionSecGroup ]
      Tags:
        - Key: Name
          Value: labstack-bastion-host
      BlockDeviceMappings:
        - DeviceName: /dev/sda1
          Ebs:
            DeleteOnTermination: true
            Iops: 7500
            VolumeSize: 150
            VolumeType: io1
      ImageId: !FindInMap [ RegionalSettings, !Ref "AWS::Region", bastionAmi ]
      IamInstanceProfile: !Ref profileBastionHost
      UserData:
        Fn::Base64: !Sub |
          #!/bin/bash -xe

          # update & upgrade packages
          apt-get update
          DEBIAN_FRONTEND=noninteractive apt-get -y -o Dpkg::Options::="--force-confdef" -o Dpkg::Options::="--force-confold" dist-upgrade
          echo "* updated and upgraded packages" >> /debug.log

          # update SSM agent
          snap remove amazon-ssm-agent
          wget https://s3.us-east-2.amazonaws.com/amazon-ssm-us-east-2/latest/debian_amd64/amazon-ssm-agent.deb
          DEBIAN_FRONTEND=noninteractive dpkg -i amazon-ssm-agent.deb
          echo "* update ssm-agent to latest" >> /debug.log

          # install jq
          apt-get -y install jq
          echo "* installed jq package" >> /debug.log

          # install mysql client tools
          apt-get -y install mysql-client
          mysql --version >> /debug.log
          echo "* installed mysql-client package" >> /debug.log

          # install sysbench
          curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
          apt-get update
          apt-get -y install sysbench
          sysbench --version >> /debug.log
          echo "* installed sysbench package" >> /debug.log

          # install percona tpcc-like test suite
          git clone https://github.com/Percona-Lab/sysbench-tpcc.git /home/ubuntu/sysbench-tpcc
          chown -R ubuntu:ubuntu /home/ubuntu/sysbench-tpcc
          echo "* cloned percona/sysbench-tpcc repo" >> /debug.log

          # download demo databases
          git clone https://github.com/datacharmer/test_db.git /home/ubuntu/samples
          chown -R ubuntu:ubuntu /home/ubuntu/samples
          echo "* cloned test databases repo" >> /debug.log

          # install python pip and aws cli
          apt-get -y install python3-pip
          pip3 install pymysql
          pip3 install awscli
          cd /home/ubuntu
          curl -O https://d313ex006ebt1i.cloudfront.net/scripts/loadtest.py
          chown -R ubuntu:ubuntu /home/ubuntu/loadtest.py
          echo "* pulled load test script" >> /debug.log

          # configure AWS CLI
          mkdir /home/ubuntu/.aws
          touch /home/ubuntu/.aws/config
          echo "[default]" >> /home/ubuntu/.aws/config
          echo "region = ${AWS::Region}" >> /home/ubuntu/.aws/config
          chown -R ubuntu:ubuntu /home/ubuntu/.aws/config
          echo "* configured aws cli" >> /debug.log

          # set DB cluster user and password as env variables
          export SECRETSTRING=`aws secretsmanager get-secret-value --secret-id ${secretClusterMasterUser} --region ${AWS::Region} | jq -r '.SecretString'`
          export DBPASS=`echo $SECRETSTRING | jq -r '.password'`
          export DBUSER=`echo $SECRETSTRING | jq -r '.username'`
          echo "export DBPASS=\"$DBPASS\"" >> /home/ubuntu/.bashrc
          echo "export DBUSER=$DBUSER" >> /home/ubuntu/.bashrc
          echo "* db credentials initialized" >> /debug.log

          # reboot
          echo "* bootstrap complete, rebooting" >> /debug.log
          shutdown -r now

## Create parameter groups for cluster nodes
  pgNodeParams:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Description: labstack-node-params
      Family: aurora5.6
      Parameters:
        innodb_stats_persistent_sample_pages: "256"
        slow_query_log: "1"
        long_query_time: "10"
        log_output: FILE
      Tags:
        - Key: Name
          Value: labstack-node-params

## Create cluster parameter group
  cpgClusterParams:
    Type: AWS::RDS::DBClusterParameterGroup
    Properties:
      Description: labstack-cluster-params
      Family: aurora5.6
      Parameters:
        aws_default_s3_role: !GetAtt roleServiceIntegration.Arn
      Tags:
        - Key: Name
          Value: labstack-cluster-params

## Create a random generated password and store it as a secret
  secretClusterMasterUser:
    Type: AWS::SecretsManager::Secret
    Properties:
      Description: "Master user credentials for labstack-cluster"
      GenerateSecretString:
        SecretStringTemplate: '{"username": "masteruser"}'
        GenerateStringKey: 'password'
        PasswordLength: 10
        ExcludeCharacters: '"@/\$`&'
      Tags:
        - Key: Name
          Value: labstack-secret

## Create Aurora cluster
  dbCluster:
    Type: AWS::RDS::DBCluster
    Properties:
      Engine: aurora
      DBSubnetGroupName: !Ref dbSubnets
      DBClusterParameterGroupName: !Ref cpgClusterParams
      DBClusterIdentifier: labstack-cluster
      BackupRetentionPeriod: 7
      MasterUsername: !Join ['', ['{{resolve:secretsmanager:', !Ref secretClusterMasterUser, ':SecretString:username}}' ]]
      MasterUserPassword: !Join ['', ['{{resolve:secretsmanager:', !Ref secretClusterMasterUser, ':SecretString:password}}' ]]
      DatabaseName: !FindInMap [ ClusterSettings, global, dbSchema ]
      StorageEncrypted: true
      VpcSecurityGroupIds: [ !Ref dbSecGroupCluster ]
      EnableCloudwatchLogsExports: [ error, slowquery ]
      BacktrackWindow: 86400
      EnableIAMDatabaseAuthentication: true
      AssociatedRoles:
        - RoleArn: !GetAtt roleServiceIntegration.Arn
      Tags:
        - Key: Name
          Value: labstack-cluster

## Deploy the first cluster node (always the writer)
  dbNodeWriter:
    Type: AWS::RDS::DBInstance
    DependsOn: [ dbCluster ]
    Properties:
      DBClusterIdentifier: !Ref dbCluster
      DBInstanceIdentifier: labstack-node-01
      CopyTagsToSnapshot: true
      DBInstanceClass: !FindInMap [ RegionalSettings, !Ref "AWS::Region", nodeType ]
      DBParameterGroupName: !Ref pgNodeParams
      Engine: aurora
      MonitoringInterval: 1
      MonitoringRoleArn: !GetAtt roleEnhancedMonitoring.Arn
      PubliclyAccessible: false
      EnablePerformanceInsights: true
      PerformanceInsightsRetentionPeriod: 7
      Tags:
        - Key: Name
          Value: labstack-node-01

## Deploy a reader node
  dbNodeSecondary:
    Type: AWS::RDS::DBInstance
    DependsOn: [ dbNodeWriter ]
    Properties:
      DBClusterIdentifier: !Ref dbCluster
      DBInstanceIdentifier: labstack-node-02
      CopyTagsToSnapshot: true
      DBInstanceClass: !FindInMap [ RegionalSettings, !Ref "AWS::Region", nodeType ]
      DBParameterGroupName: !Ref pgNodeParams
      Engine: aurora
      MonitoringInterval: 1
      MonitoringRoleArn: !GetAtt roleEnhancedMonitoring.Arn
      PubliclyAccessible: false
      EnablePerformanceInsights: true
      PerformanceInsightsRetentionPeriod: 7
      Tags:
        - Key: Name
          Value: labstack-node-02

## Role to overcome current limitations in CFN ScalableTarget implemetation
## This role is *NOT* actively used by any resource and service, but must be present
  roleScalableTarget:
    Type: AWS::IAM::Role
    Properties:
      RoleName: !Sub labstack-aas-target-${AWS::Region}
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Action:
              - sts:AssumeRole
            Principal:
              Service:
                - rds.application-autoscaling.amazonaws.com

## Register the scalable target
## Bug fix: when the stack name contains uppercase letters,
## the DB cluster identifier is actually lowercased, but the resource ID
## still contains uppercase, so you get a mismatch on the scalable target ResourceId
  dbScalableTarget:
    Type: AWS::ApplicationAutoScaling::ScalableTarget
    DependsOn: [ dbNodeSecondary ]
    Properties:
      ServiceNamespace: 'rds'
      ScalableDimension: 'rds:cluster:ReadReplicaCount'
      ResourceId: !Sub 'cluster:${dbCluster}'
      MaxCapacity: !FindInMap [ ClusterSettings, scaling, maxCapacity ]
      MinCapacity: !FindInMap [ ClusterSettings, scaling, minCapacity ]
      RoleARN: !GetAtt roleScalableTarget.Arn

## Add scaling policy
  dbScalingPolicy:
    Type : AWS::ApplicationAutoScaling::ScalingPolicy
    Properties:
      PolicyName: labstack-autoscale-readers
      PolicyType: TargetTrackingScaling
      ScalingTargetId: !Ref dbScalableTarget
      TargetTrackingScalingPolicyConfiguration:
        PredefinedMetricSpecification:
          PredefinedMetricType: RDSReaderAverageCPUUtilization
        ScaleInCooldown: 180
        ScaleOutCooldown: 180
        TargetValue: !FindInMap [ ClusterSettings, scaling, cpuLoadTarget ]

## Create sysbench prep SSM document
  ssmDocSysbenchTest:
    Type: AWS::SSM::Document
    Properties:
      DocumentType: Command
      Tags:
        - Key: Name
          Value: labstack-sysbench-test
      Content:
        schemaVersion: '2.2'
        description: SysBench Percona TPCC-LIKE Preparation
        parameters:
          clusterEndpoint:
            type: String
            description: Aurora Cluster Endpoint
            default: !GetAtt dbCluster.Endpoint.Address
          dbUser:
            type: String
            description: DB User
            default: !Join ['', ['{{resolve:secretsmanager:', !Ref secretClusterMasterUser, ':SecretString:username}}' ]]
          dbPassword:
            type: String
            description: DB Password
            default: !Join ['', ['{{resolve:secretsmanager:', !Ref secretClusterMasterUser, ':SecretString:password}}' ]]
          dbSchema:
            type: String
            description: DB Schema
            default: !FindInMap [ ClusterSettings, sysbench, dbSchema ]
          dbDriver:
            type: String
            description: DB Driver
            default: !FindInMap [ ClusterSettings, global, dbDriver ]
            allowedValues: [ mysql, pgsql ]
          runTime:
            type: String
            description: Test Runtime
            default: !FindInMap [ ClusterSettings, sysbench, runTime ]
          numThreads:
            type: String
            description: Threads
            default: !FindInMap [ ClusterSettings, sysbench, numThreads ]
          numTables:
            type: String
            description: Tables
            default: !FindInMap [ ClusterSettings, sysbench, numTables ]
          numScale:
            type: String
            description: Scale
            default: !FindInMap [ ClusterSettings, sysbench, numWarehouses ]
        mainSteps:
        - action: aws:runShellScript
          name: SysBenchTpccPrepare
          inputs:
            runCommand:
            - 'echo "DROP SCHEMA IF EXISTS {{ dbSchema }}; CREATE SCHEMA {{ dbSchema }};" | mysql -h{{ clusterEndpoint }} -u{{ dbUser }} -p"{{ dbPassword }}" && cd /home/ubuntu/sysbench-tpcc && ./tpcc.lua --mysql-host={{ clusterEndpoint }} --mysql-user={{ dbUser }} --mysql-password="{{ dbPassword }}" --mysql-db={{ dbSchema }} --threads={{ numThreads }} --tables={{ numTables }} --scale={{ numScale }} --time={{ runTime }} --db-driver={{ dbDriver }} prepare'
        - action: aws:runShellScript
          name: SysBenchTpccRun
          inputs:
            runCommand:
            - 'cd /home/ubuntu/sysbench-tpcc && ./tpcc.lua --mysql-host={{ clusterEndpoint }} --mysql-user={{ dbUser }} --mysql-password="{{ dbPassword }}" --mysql-db={{ dbSchema }} --threads={{ numThreads }} --tables={{ numTables }} --scale={{ numScale }} --time={{ runTime }} --db-driver={{ dbDriver }} run'


## Outputs
Outputs:
  vpcId:
    Description: Aurora Lab VPC
    Value: !Ref vpc
  bastionInstance:
    Description: Bastion Instance ID
    Value: !Ref bastionHost
  clusterName:
    Description: Cluster Name
    Value: !Ref dbCluster
  clusterEndpoint:
    Description: Aurora Cluster Endpoint
    Value: !GetAtt dbCluster.Endpoint.Address
  readerEndpoint:
    Description: Aurora Reader Endpoint
    Value: !GetAtt dbCluster.ReadEndpoint.Address
  loadTestRunDoc:
    Description: Load Test Execution Command Document
    Value: !Ref ssmDocSysbenchTest
  dbSubnetGroup:
    Description: Database Subnet Group
    Value: !Ref dbSubnets
  dbSecurityGroup:
    Description: Database Security Group
    Value: !Ref dbSecGroupCluster
  secretArn:
    Description: Database Credentials Secret ARN
    Value: !Ref secretClusterMasterUser