deploy-mysql-based-on-ebs.yml

ROSTemplateFormatVersion: '2015-09-01'
Description:
  zh-cn: 创建ESSD存储的ECS实例，配置安全组与VPC，自动部署MySQL 8.0，优化磁盘性能，实现高性能MySQL服务。
  en: Create an ECS instance with ESSD storage, configure security groups and VPC,
    automatically deploy MySQL 8.0, optimize disk performance to achieve a high-performance
    MySQL service.
Parameters:
  MySQLPassword:
    Default: ''
    NoEcho: true
    Type: String
    Label:
      zh-cn: 数据库密码
      en: Mysql Password
  EcsType:
    Default: ecs.g6.large
    AssociationProperty: 'ALIYUN::ECS::Instance::InstanceType'
    AssociationPropertyMetadata:
      SystemDiskCategory: cloud_essd
      InstanceChargeType: PostPaid
      ZoneId: '${ZoneId}'
    Type: String
    Label:
      zh-cn: ECS 实例规格
      en: InstanceType
  ZoneId:
    Default: ''
    AssociationProperty: 'ALIYUN::ECS::Instance::ZoneId'
    Type: String
    Label:
      zh-cn: 可用区
      en: Availability Zone
  EcsPassword:
    Type: String
    Description:
      zh-cn: >-
        服务器登录密码,长度8-30，必须包含三项（大写字母、小写字母、数字、 ()`~!@#$%^&*_-+=|{}[]:;'<>,.?/
        中的特殊符号）
      en: >-
        Server login password, Length 8-30, must contain three(Capital letters,
        lowercase letters, numbers, ()`~!@#$%^&*_-+=|{}[]:;'<>,.?/ Special
        symbol in)
    Default: null
    MinLength: 8
    Label:
      zh-cn: 实例密码
      en: Instance Password
    AllowedPattern: '^[a-zA-Z0-9-\(\)\`\~\!\@\#\$\%\^\&\*\_\-\+\=\|\{\}\[\]\:\;\<\>\,\.\?\/]*$'
    MaxLength: 30
    AssociationProperty: 'ALIYUN::ECS::Instance::Password'
    ConstraintDescription:
      zh-cn: '长度8-30，必须包含三项（大写字母、小写字母、数字、 ()`~!@#$%^&*_-+=|{}[]:;''<>,.?/ 中的特殊符号）'
      en: >-
        Length 8-30, must contain three(Capital letters, lowercase letters,
        numbers, ()`~!@#$%^&*_-+=|{}[]:;'<>,.?/ Special symbol in)
Outputs:
  EcsLoginAddress:
    Description:
      zh-cn: Ecs登陆地址。
      en: Ecs login address.
    Value:
      'Fn::Sub':
        - >-
          https://ecs-workbench.aliyun.com/?from=EcsConsole&instanceType=ecs&regionId=${Region}&instanceId=${InstanceId}
        - InstanceId:
            Ref: EcsInstance
          Region:
            Ref: 'ALIYUN::Region'
  EcsInstanceId:
    Description: ECS实例ID
    Value:
      'Fn::GetAtt':
        - EcsInstance
        - InstanceId
  EcsInstanceUser:
    Description: ECS实例初始用户
    Value: root
  EcsInstancePublicIp:
    Description: ECS实例公网IP
    Value:
      'Fn::GetAtt':
        - EcsInstance
        - PublicIp
  MysqlUser:
    Description: MySQL初始用户
    Value: root
  DataDisk:
    Description: 数据盘ID
    Value:
      'Fn::GetAtt':
        - DataDisk
        - DiskId
Resources:
  DataDiskAttachment:
    Type: 'ALIYUN::ECS::DiskAttachment'
    Properties:
      InstanceId:
        Ref: EcsInstance
      DiskId:
        Ref: DataDisk
    DependsOn:
      - EcsInstance
      - DataDisk
  SecurityGroup:
    Type: 'ALIYUN::ECS::SecurityGroup'
    Properties:
      SecurityGroupIngress:
        - Priority: 1
          PortRange: '-1/-1'
          NicType: intranet
          SourceCidrIp: 0.0.0.0/0
          IpProtocol: all
      VpcId:
        Ref: VPC
      SecurityGroupEgress:
        - Priority: 1
          PortRange: '-1/-1'
          DestCidrIp: 0.0.0.0/0
          NicType: intranet
          IpProtocol: all
  VPC:
    Type: 'ALIYUN::ECS::VPC'
    Properties:
      VpcName:
        'Fn::Join':
          - '-'
          - - StackId
            - Ref: 'ALIYUN::StackId'
      CidrBlock: 192.168.0.0/16
  WriteCommand:
    Type: 'ALIYUN::ECS::RunCommand'
    Properties:
      Type: RunShellScript
      CommandContent:
        'Fn::Join':
          - ''
          - - >
              echo '

              # 1. Get data disk label

              DEV_LABEL=vdb


              # 2. Check if data disk exists

              if [ ! -e /dev/${DEV_LABEL} ]; then
                  echo "[ERROR] /dev/${DEV_LABEL} not exist. Please check if data disk label is vdb."
                  exit
              fi


              # 3. Set MySQL home

              MYSQL_HOME="/root/${DEV_LABEL}"

              if [ ! -d ${MYSQL_HOME} ]; then
                  if mkdir -p ${MYSQL_HOME}; then 
                      echo "[INFO] Create directory ${MYSQL_HOME} successfully."
                  else
                      echo "[ERROR] Failed to create directory ${MYSQL_HOME}."
                      exit 
                  fi
              fi

              echo "[INFO] Using /dev/${DEV_LABEL} as the MySQL data home."


              # 4. Prevent virtio io split

              echo 256 >/sys/block/${DEV_LABEL}/queue/max_sectors_kb

              echo "[INFO] Set max sectors kb successfully."


              # 5. mkfs with bigalloc enabled

              umount /dev/${DEV_LABEL}

              echo y | mkfs.ext4 -O bigalloc -C 16k /dev/${DEV_LABEL}

              echo "[INFO] mkfs with bigalloc enabled successfully."


              # 6. Install MySQL

              cd /tmp/

              if wget -N
              http://mirrors.cloud.aliyuncs.com/mysql/MySQL-8.0/mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar
              -O mysql.tar ; then
                  echo "[INFO] Download mysql rpm bundle successfully."
              else
                  echo "[ERROR] Failed to download mysql rpm bundle."
                  exit
              fi

              tar -xf mysql.tar

              echo "[INFO] Extract mysql rpm bundle successfully."


              if yum install -y
              mysql-community-{server,client,common,libs,devel}-*; then
                  echo "[INFO] Install mysql successfully."
              else 
                  echo "[ERROR] Failed to install mysql."
                  exit
              fi


              # 7. Create MySQL directories

              mount /dev/${DEV_LABEL} ${MYSQL_HOME}

              mkdir -p ${MYSQL_HOME}/data/dbs

              mkdir -p ${MYSQL_HOME}/data/mysql

              mkdir -p ${MYSQL_HOME}/log/mysql

              mkdir -p ${MYSQL_HOME}/log/redo

              echo "[INFO] Create MySQL directories successfully."


              # 8. Add mysql group and user

              systemctl stop mysqld

              groupadd mysql

              useradd -g mysql mysql

              chown -R mysql:mysql ${MYSQL_HOME}

              echo "[INFO] Add mysql group and user successfully."


              # 9. Turn off doublewrite

              cat <<EOF | echo "[mysqld]

              port = #{MYSQL_PORT}

              back_log = 3000

              datadir = ${MYSQL_HOME}/data/dbs

              log-bin = ${MYSQL_HOME}/log/mysql/mysql-bin.log

              log-error = ${MYSQL_HOME}/log/mysql/master-error.log

              innodb_data_home_dir = ${MYSQL_HOME}/data/mysql/

              innodb_log_group_home_dir = ${MYSQL_HOME}/log/redo/

              innodb_doublewrite_dir = ${MYSQL_HOME}/doublewrite

              character_set_server = utf8

              sync_binlog = 1000

              innodb_flush_log_at_trx_commit = 2

              innodb_doublewrite = 0

              innodb_flush_method=O_DIRECT

              default_authentication_plugin = mysql_native_password"
              >/etc/my.cnf

              EOF


              # 10. Init MySQL SQL

              cat <<EOF | echo "ALTER USER 'root'@'localhost' IDENTIFIED WITH
              mysql_native_password BY '\'#{MYSQL_PASSWORD}\'';

              create user root@'\'%\'' identified by '\'#{MYSQL_PASSWORD}\'';

              grant all privileges on *.* to root@'\'%\'';

              grant all privileges on *.* to root@'\'%\'' with grant option;

              " >${MYSQL_HOME}/prepare.sql

              EOF


              # 11. Start MySQL

              killall -9 mysqld

              mysqld --defaults-file=/etc/my.cnf --initialize

              mysqld --user=root --daemonize

              mysql -uroot -p"$(cat ${MYSQL_HOME}/log/mysql/master-error.log |
              grep root | grep "temporary password" | awk '\'{print \$13}\'' |
              xargs -n 1 echo)" --connect-expired-password
              <${MYSQL_HOME}/prepare.sql

              echo '[INFO] Prepare MySQL successfully.'

              ' > /tmp/prepare_mysql.sh 


              echo '#!/bin/bash

              # chkconfig: 345 20 80

              # description: EBS MySQL 16K Auto Start Script

              start() {
                  DEV_LABEL=vdb
                  MYSQL_HOME=/root/${DEV_LABEL}
                  echo 256 >/sys/block/vdb/queue/max_sectors_kb
                  umount /dev/${DEV_LABEL}
                  mount /dev/${DEV_LABEL} ${MYSQL_HOME}
                  mysqld --user=root --daemonize
              }

              stop() {
                  pgrep mysqld | xargs kill
                  echo "Stopping mysqld..."
              }

              restart() {
                  stop
                  sleep 2
                  start
              }

              case "$1" in
                  start)
                      start
                      ;;
                  stop)
                      stop
                      ;;
                  restart)
                      restart
                      ;;
                  *)
                      echo "Usage: $0 {start|stop|restart}"
                      exit 1
              esac

              exit 0

              ' > /etc/rc.d/init.d/ebs_mysql_16k_auto_start.sh

              cd /etc/rc.d/init.d

              chmod +x /etc/rc.d/init.d/ebs_mysql_16k_auto_start.sh

              sudo chkconfig --add ebs_mysql_16k_auto_start.sh

              sudo chkconfig ebs_mysql_16k_auto_start.sh on
      Sync: true
      InstanceIds:
        - Ref: EcsInstance
    DependsOn:
      - DataDiskAttachment
  VSwitch:
    Type: 'ALIYUN::ECS::VSwitch'
    Properties:
      VSwitchName:
        Ref: 'ALIYUN::StackName'
      VpcId:
        Ref: VPC
      CidrBlock: 192.168.0.0/24
      ZoneId:
        Ref: ZoneId
  RunCommand:
    Type: 'ALIYUN::ECS::RunCommand'
    Properties:
      Type: RunShellScript
      CommandContent:
        'Fn::Sub':
          - >
            #!/bin/sh

            sed -i "s/#{MYSQL_PASSWORD}/${MySQLPassword}/g;
            s/#{MYSQL_PORT}/3306/g;" /tmp/prepare_mysql.sh

            sh /tmp/prepare_mysql.sh | tee ~/prepare_mysql.log
          - {}
      Sync: true
      InstanceIds:
        - Ref: EcsInstance
      Timeout: 600
    DependsOn:
      - WriteCommand
  EcsInstance:
    Type: 'ALIYUN::ECS::Instance'
    Properties:
      SystemDiskCategory: cloud_essd
      VpcId:
        Ref: VPC
      InternetMaxBandwidthOut: 100
      SecurityGroupId:
        Ref: SecurityGroup
      SystemDiskSize: 40
      ImageId: CentOS7_9
      AllocatePublicIP: true
      VSwitchId:
        Ref: VSwitch
      InternetChargeType: PayByTraffic
      IoOptimized: optimized
      Password:
        Ref: EcsPassword
      InstanceType:
        Ref: EcsType
      ZoneId:
        Ref: ZoneId
  DataDisk:
    Type: 'ALIYUN::ECS::Disk'
    Properties:
      DiskCategory: cloud_essd
      ZoneId:
        Ref: ZoneId
      PerformanceLevel: PL0
      Size: 100
    DependsOn:
      - EcsInstance
Metadata:
  'ALIYUN::ROS::Interface':
    ParameterGroups:
      - Parameters:
          - ZoneId
          - EcsType
          - EcsPassword
        Label:
          zh-cn: 基础配置
          en: Basic Configuration
      - Parameters:
          - MySQLPassword
        Label:
          zh-cn: 数据库配置
          en: DataBase Configuration
    TemplateTags:
      - 'acs:solution:基于EBS部署高性能的MySQL服务'
  ALIYUN::ROS::Composer:
    '59309829':
      Res:
        - DataDisk
        - DataDiskAttachment
      Parent: 1ec672cf
      Rect:
        - 40
        - 40
        - 299
        - 261
        - 5
        - 0
    53f9f214:
      Rect:
        - 648
        - 631
        - 205
        - -59
        - 1
        - 0
      ResT: Composer::ROSParameter::AlibabaCloud
    b2c3b803:
      Parent: 53f9f214
      Rect:
        - 598
        - 545
        - 225
        - -9
        - 2
        - 0
      ResT: Composer::ROSParameter::Region
    4b9cbb31:
      Res:
        - VPC
      Parent: b2c3b803
      Rect:
        - 550
        - 397
        - 245
        - 44
        - 3
        - 0
    770805b4:
      Res:
        - RunCommand
      Parent: b2c3b803
      Rect:
        - 40
        - 40
        - 404
        - 454
        - 3
        - 0
    '698e0078':
      Res:
        - WriteCommand
      Parent: b2c3b803
      Rect:
        - 40
        - 40
        - 560
        - 454
        - 3
        - 0
    1ec672cf:
      Res:
        - ZoneId
      Parent: 4b9cbb31
      Rect:
        - 500
        - 338
        - 279
        - 84
        - 4
        - 0
      ResT: Composer::ROSParameter::Zone
    8f149820:
      Res:
        - VSwitch
      Parent: 1ec672cf
      Rect:
        - 330
        - 267
        - 372
        - 124
        - 5
        - 0
    6515699b:
      Res:
        - SecurityGroup
      Parent: 4b9cbb31
      Rect:
        - 126
        - 136
        - 444
        - 212
        - 10
        - 0
    913a8316:
      Parent: b2c3b803
      Edge:
        - '698e0078'
        - 7ee1a34e
      Line: 0:0:0:gray:0
    8dbc3c9b:
      Parent: b2c3b803
      Edge:
        - 770805b4
        - 7ee1a34e
      Line: 0:0:0:gray:0
    24f8e26e:
      Parent: 1ec672cf
      Edge:
        - '59309829'
        - 7ee1a34e
      Line: 0:0:0:gray:0
    7ee1a34e:
      Res:
        - EcsInstance
      Parent: 8f149820
      Rect:
        - 40
        - 40
        - 487
        - 261
        - 11
        - 0
      Layer:
        - 6515699b