一発！EC2 で Flask を mod_wsgi で起動し、そのアプリから mysql を触る UserData, および CloudFormation テンプレート

UserData で一発でやろうとすると案外難しくて、数時間くらいかけてしまったので供養します。
このようなアプリケーションが一発で立ち上がります。EC2 のパブリックIPを有効にしていればそのIPアドレスからhttpでアクセスできます。
![Screenshot 2025-02-21 at 18.03.12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884991/7130ee4d-453d-4a67-93da-6e2d7baa3f08.png)
もちろん、mysqlにメッセージが保存されているため、再読み込みしてもデータは残ったままです。
## つまりポイント
覚えているところとしては以下のようなものがあります。
- mysql のインストール（意外と面倒）
- mysqlの初期パスワードは手動でやるならlogから取得できるが、UserData一発でやらなければならないので回りくどい方法でリセットしなければならない。
- venv上のflask のライブラリを wsgi に読み込ませるのが権限の問題でできなくて、`Initializing Python failed: failed to get the Python codec of the filesystem encoding` になる
- session manager から流し込むと一発でいけるのだが、何故かUserDataから流し込むと実行されない(`#!/bin/bash`を`#!/usr/bin/env sh`にする)
## コード(UserData)

```
#!/usr/bin/env sh
sudo mkdir -p /var/www/flask/
cd ~
sudo chmod -R 755 ~
sudo dnf update -y
sudo dnf install -y httpd httpd-tools python3 python3-devel python3-pip httpd-devel gcc virtualenv
sudo dnf group install -y 'Development Tools'
sudo dnf -y localinstall https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm
sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023
sudo dnf -y install mysql mysql-server

virtualenv -p python3 venv
source venv/bin/activate 

python3 -m pip install flask mysql-connector-python mod_wsgi
# Start and enable MySQL
sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
sudo systemctl start mysqld
sudo systemctl enable mysqld

# Set up MySQL root password and create database
sudo mysql --connect-expired-password -e "UPDATE mysql.user SET authentication_string=null WHERE User='root'";
sudo systemctl set-environment MYSQLD_OPTS=""
sudo systemctl restart mysqld
MYSQL_ROOT_PASSWORD=$(openssl rand -base64 64)
sudo mysql --connect-expired-password -e "set password ='$MYSQL_ROOT_PASSWORD'"

# Create database and user
sudo mysql -u root -p"$MYSQL_ROOT_PASSWORD" <<EOF
CREATE DATABASE IF NOT EXISTS dbname;
CREATE USER 'dbusername'@'localhost' IDENTIFIED BY 'とても複雑なパスワード';
GRANT ALL PRIVILEGES ON dbname.* TO 'dbusername'@'localhost';
FLUSH PRIVILEGES;
USE dbname;
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
EOF

# Start and enable Apache
sudo systemctl start httpd
sudo systemctl enable httpd



# Create a Flask app with data insertion and display capabilities
sudo tee /var/www/flask/app.py > /dev/null <<EOF
from flask import Flask, request, render_template_string
import mysql.connector
from datetime import datetime

app = Flask(__name__)

def get_db_connection():
    return mysql.connector.connect(user='dbusername', password='とても複雑なパスワード',
                                   host='localhost',
                                   database='dbname')

@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        content = request.form.get('content')
        if content:
            conn = get_db_connection()
            cursor = conn.cursor()
            cursor.execute("INSERT INTO messages (content) VALUES (%s)", (content,))
            conn.commit()
            cursor.close()
            conn.close()

    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT content, created_at FROM messages ORDER BY created_at DESC")
    messages = cursor.fetchall()
    cursor.close()
    conn.close()

    return render_template_string('''
        <!doctype html>
        <html>
            <body>
                <h1>Message Board</h1>
                <form method="post">
                    <input type="text" name="content" placeholder="Enter a message">
                    <input type="submit" value="Post">
                </form>
                <h2>Messages:</h2>
                <ul>
                    {% for message in messages %}
                        <li>{{ message[0] }} ({{ message[1] }})</li>
                    {% endfor %}
                </ul>
            </body>
        </html>
    ''', messages=messages)

if __name__ == '__main__':
    app.run()
EOF

sudo tee /var/www/flask/adapter.wsgi > /dev/null <<EOF
import sys
sys.path.insert(0, '/var/www/flask')
from app import app as application
EOF

# Find the correct path for mod_wsgi
MOD_WSGI_PATH=$(sudo find ~/venv/* -name 'mod_wsgi*.so' | head -n 1)
MOD_WSGI_FOLDER=$(sudo find ~ -type d -name 'venv' | head -n 1)
MOD_WSGI_PYTHON_PATH=$(sudo find $MOD_WSGI -type d -name 'site-packages' | head -n 1)
sudo tee /etc/httpd/conf.d/flask.conf > /dev/null <<EOF
LoadModule wsgi_module ${MOD_WSGI_PATH}
<VirtualHost *:80>
  DocumentRoot /var/www/flask
  WSGIDaemonProcess flask threads=5 python-home=${MOD_WSGI_FOLDER} python-path=${MOD_WSGI_PYTHON_PATH}
  WSGIProcessGroup flask
  WSGIScriptAlias / /var/www/flask/adapter.wsgi
  <Directory "/var/www/flask/">
    options +Indexes +FollowSymLinks +ExecCGI
  </Directory>
</VirtualHost>
EOF

# Restart Apache to apply changes
sudo systemctl restart httpd
```

## CloudFormation テンプレート
```
AWSTemplateFormatVersion: '2010-09-09'
Description: 'CloudFormation pattern 1 template for Resiliency Workshop for Local Government Vendors'

Parameters:
  VpcCIDR:
    Description: Please enter the IP range (CIDR notation) for this VPC
    Type: String
    Default: 10.0.0.0/16

  PublicSubnet1CIDR:
    Description: Please enter the IP range (CIDR notation) for the public subnet in the first Availability Zone
    Type: String
    Default: 10.0.0.0/24

  PublicSubnet2CIDR:
    Description: Please enter the IP range (CIDR notation) for the public subnet in the second Availability Zone
    Type: String
    Default: 10.0.1.0/24

  PrivateSubnet1CIDR:
    Description: Please enter the IP range (CIDR notation) for the private subnet in the first Availability Zone
    Type: String
    Default: 10.0.2.0/24

  PrivateSubnet2CIDR:
    Description: Please enter the IP range (CIDR notation) for the private subnet in the second Availability Zone
    Type: String
    Default: 10.0.3.0/24

  InstanceType:
    Description: EC2 instance type
    Type: String
    Default: t3.micro
    AllowedValues:
      - t2.micro
      - t3.micro
      - t3.small
      - t3.medium

  DBName:
    Description: The database name
    Type: String
    Default: mydb

  DBUsername:
    Description: The database admin account username
    Type: String
    Default: admin

  DBPassword:
    Description: The database admin account password
    Type: String
    Default: workShOp_P@ss_Word1231
    NoEcho: true

Resources:
  VPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: !Ref VpcCIDR
      EnableDnsHostnames: true
      EnableDnsSupport: true
      InstanceTenancy: default
      Tags:
        - Key: Name
          Value: Workshop VPC

  InternetGateway:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: Workshop IGW

  InternetGatewayAttachment:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      InternetGatewayId: !Ref InternetGateway
      VpcId: !Ref VPC

  PublicSubnet1:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      AvailabilityZone: !Select [ 0, !GetAZs '' ]
      CidrBlock: !Ref PublicSubnet1CIDR
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: Public Subnet (AZ1)

  PublicSubnet2:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      AvailabilityZone: !Select [ 1, !GetAZs  '' ]
      CidrBlock: !Ref PublicSubnet2CIDR
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: Public Subnet (AZ2)

  PrivateSubnet1:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      AvailabilityZone: !Select [ 0, !GetAZs  '' ]
      CidrBlock: !Ref PrivateSubnet1CIDR
      MapPublicIpOnLaunch: false
      Tags:
        - Key: Name
          Value: Private Subnet (AZ1)

  PrivateSubnet2:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      AvailabilityZone: !Select [ 1, !GetAZs  '' ]
      CidrBlock: !Ref PrivateSubnet2CIDR
      MapPublicIpOnLaunch: false
      Tags:
        - Key: Name
          Value: Private Subnet (AZ2)

  PublicRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: Public Routes

  DefaultPublicRoute:
    Type: AWS::EC2::Route
    DependsOn: InternetGatewayAttachment
    Properties:
      RouteTableId: !Ref PublicRouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref InternetGateway

  PublicSubnet1RouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PublicRouteTable
      SubnetId: !Ref PublicSubnet1

  PublicSubnet2RouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PublicRouteTable
      SubnetId: !Ref PublicSubnet2

  PrivateRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: Private Routes

  PrivateSubnet1RouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PrivateRouteTable
      SubnetId: !Ref PrivateSubnet1

  PrivateSubnet2RouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PrivateRouteTable
      SubnetId: !Ref PrivateSubnet2

  WebServerSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Enable HTTP access via port 80
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 80
          ToPort: 80
          CidrIp: 0.0.0.0/0
      VpcId: !Ref VPC


  SingleEC2Instance:
    Type: AWS::EC2::Instance
    Properties:
      InstanceType: !Ref InstanceType
      ImageId: ami-072298436ce5cb0c4  # Amazon Linux 2023 AMI 2023.6.20250218.2 x86_64 HVM kernel-6.1
      #KeyName: mykey 
      NetworkInterfaces:
        - AssociatePublicIpAddress: "true"
          DeviceIndex: "0"
          GroupSet:
            - !Ref WebServerSecurityGroup
          SubnetId: !Ref PublicSubnet1
      UserData:
        Fn::Base64: !Sub 
        - |
          #!/usr/bin/env sh
          sudo mkdir -p /var/www/flask/
          cd ~
          sudo chmod -R 755 ~
          sudo dnf update -y
          sudo dnf install -y httpd httpd-tools python3 python3-devel python3-pip httpd-devel gcc virtualenv
          sudo dnf group install -y 'Development Tools'
          sudo dnf -y localinstall https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm
          sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023
          sudo dnf -y install mysql mysql-server

          virtualenv -p python3 venv
          source venv/bin/activate 

          python3 -m pip install flask mysql-connector-python mod_wsgi
          # Start and enable MySQL
          sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
          sudo systemctl start mysqld
          sudo systemctl enable mysqld

          # Set up MySQL root password and create database
          sudo mysql --connect-expired-password -e "UPDATE mysql.user SET authentication_string=null WHERE User='root'";
          sudo systemctl set-environment MYSQLD_OPTS=""
          sudo systemctl restart mysqld
          MYSQL_ROOT_PASSWORD=$(openssl rand -base64 64)
          sudo mysql --connect-expired-password -e "set password ='$MYSQL_ROOT_PASSWORD'"

          # Create database and user
          sudo mysql -u root -p"$MYSQL_ROOT_PASSWORD" <<EOF
          CREATE DATABASE IF NOT EXISTS ${DBName};
          CREATE USER '${DBUsername}'@'localhost' IDENTIFIED BY '${DBPassword}';
          GRANT ALL PRIVILEGES ON ${DBName}.* TO '${DBUsername}'@'localhost';
          FLUSH PRIVILEGES;
          USE ${DBName};
          CREATE TABLE IF NOT EXISTS messages (
              id INT AUTO_INCREMENT PRIMARY KEY,
              content VARCHAR(255) NOT NULL,
              created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          );
          EOF

          # Start and enable Apache
          sudo systemctl start httpd
          sudo systemctl enable httpd



          # Create a Flask app with data insertion and display capabilities
          sudo tee /var/www/flask/app.py > /dev/null <<EOF
          from flask import Flask, request, render_template_string
          import mysql.connector
          from datetime import datetime

          app = Flask(__name__)

          def get_db_connection():
              return mysql.connector.connect(user='${DBUsername}', password='${DBPassword}',
                                                  host='localhost',
                                                  database='${DBName}')

          @app.route('/', methods=['GET', 'POST'])
          def index():
              if request.method == 'POST':
                  content = request.form.get('content')
                  if content:
                      conn = get_db_connection()
                      cursor = conn.cursor()
                      cursor.execute("INSERT INTO messages (content) VALUES (%s)", (content,))
                      conn.commit()
                      cursor.close()
                      conn.close()

              conn = get_db_connection()
              cursor = conn.cursor()
              cursor.execute("SELECT content, created_at FROM messages ORDER BY created_at DESC")
              messages = cursor.fetchall()
              cursor.close()
              conn.close()

              return render_template_string('''
                  <!doctype html>
                  <html>
                      <body>
                          <h1>Message Board</h1>
                          <form method="post">
                              <input type="text" name="content" placeholder="Enter a message">
                              <input type="submit" value="Post">
                          </form>
                          <h2>Messages:</h2>
                          <ul>
                              {% for message in messages %}
                                  <li>{{ message[0] }} ({{ message[1] }})</li>
                              {% endfor %}
                          </ul>
                      </body>
                  </html>
              ''', messages=messages)

          if __name__ == '__main__':
              app.run()
          EOF

          sudo tee /var/www/flask/adapter.wsgi > /dev/null <<EOF
          import sys
          sys.path.insert(0, '/var/www/flask')
          from app import app as application
          EOF

          # Find the correct path for mod_wsgi
          MOD_WSGI_PATH=$(sudo find ~/venv/* -name 'mod_wsgi*.so' | head -n 1)
          MOD_WSGI_FOLDER=$(sudo find ~ -type d -name 'venv' | head -n 1)
          MOD_WSGI_PYTHON_PATH=$(sudo find $MOD_WSGI -type d -name 'site-packages' | head -n 1)
          sudo tee /etc/httpd/conf.d/flask.conf > /dev/null <<EOF
          LoadModule wsgi_module ${!MOD_WSGI_PATH}
          <VirtualHost *:80>
            DocumentRoot /var/www/flask
            WSGIDaemonProcess flask threads=5 python-home=${!MOD_WSGI_FOLDER} python-path=${!MOD_WSGI_PYTHON_PATH}
            WSGIProcessGroup flask
            WSGIScriptAlias / /var/www/flask/adapter.wsgi
            <Directory "/var/www/flask/">
              options +Indexes +FollowSymLinks +ExecCGI
            </Directory>
          </VirtualHost>
          EOF

          # Restart Apache to apply changes
          sudo systemctl restart httpd
        - DBUsername: !Ref DBUsername
          DBPassword: !Ref DBPassword
          DBName: !Ref DBName

Outputs:
  SingleEC2InstancePublicDNS:
    Description: Public DNS of Single EC2 Instance
    Value: !GetAtt SingleEC2Instance.PublicDnsName
```