使用ROS建立ECS，安裝MySQL并生成使用者

在杭州建立自建資料庫網絡和伺服器環境，部署一台ECS，安裝MySQL并建立root使用者，三個普通使用者并配置設定權限，建立資料庫插入測試資料。

ros模闆

{
  "ROSTemplateFormatVersion": "2015-09-01",
  "Description": "部署自建資料庫網絡和伺服器環境",
  "Metadata": {
    "ALIYUN::ROS::Interface": {
      "ParameterGroups": [
        {
          "Parameters": [
            "CidrBlock",
            "VpcName",
            "Vsw_ZoneId",
            "VSWName"
          ],
          "Label": {
            "default": "網絡"
          }
        },
        {
          "Parameters": [
            "ECSZoneId",
            "InstanceType",
            "LoginPassword",
            "ImageId",
            "SystemDiskCategory",
            "SystemDiskSize",
            "PublicIP",
            "InternetChargeType",
            "InternetMaxBandwidthIn",
            "HostName",
            "InstanceName"
          ],
          "Label": {
            "default": "ECS"
          }
        },
        {
          "Parameters": [
            "MysqlPassword",
            "DataBase1",
            "User1",
            "Password1",
            "User2",
            "Password2",
            "User3",
            "Password3",
            "DBTable"
          ],
          "Label": {
            "default": "MySQL"
          }
        }
      ],
      "TemplateTags": [
        "VPC_ECS"
      ]
    }
  },
  "Parameters": {
    "User2": {
      "Type": "String",
      "Description": "建立MySQL普通使用者",
      "Label": "使用者2",
      "Default": "doman"
    },
    "DBTable": {
      "Type": "String",
      "Description": "資料庫内建立表",
      "Label": "表名",
      "Default": "demo_dplus_buyer"
    },
    "SystemDiskSize": {
      "Type": "Number",
      "Description": "系統盤大小,40 - 500, 機關：GB",
      "Label": "系統盤",
      "Default": 40
    },
    "PublicIP": {
      "Type": "Boolean",
      "Description": "是否配置設定公網IP",
      "AllowedValues": [
        true,
        false
      ],
      "Label": "配置設定公網IP",
      "Default": true
    },
    "Vsw_ZoneId": {
      "Type": "String",
      "Description": "可用區是指在同一地域内，電力和網絡互相獨立的實體區域。在同一專有網絡内可用區與可用區之間内網互通，可用區之間能做到故障隔離。。",
      "AllowedValues": [
        "cn-hangzhou-b",
        "cn-hangzhou-d",
        "cn-hangzhou-e",
        "cn-hangzhou-f",
        "cn-hangzhou-g",
        "cn-hangzhou-h",
        "cn-hangzhou-i"
      ],
      "Label": "可用區",
      "Default": "cn-hangzhou-b"
    },
    "User1": {
      "Type": "String",
      "Description": "建立MySQL普通使用者",
      "Label": "使用者1",
      "Default": "selman"
    },
    "Password3": {
      "Type": "String",
      "Description": "普通使用者密碼",
      "Label": "使用者3密碼",
      "MinLength": 6,
      "MaxLength": 30,
      "Default": "yourpassword"
    },
    "Password2": {
      "Type": "String",
      "Description": "普通使用者密碼",
      "Label": "使用者2密碼",
      "MinLength": 6,
      "MaxLength": 30,
      "Default": "yourpassword"
    },
    "Password1": {
      "Type": "String",
      "Description": "普通使用者密碼",
      "Label": "使用者1密碼",
      "MinLength": 6,
      "MaxLength": 30,
      "Default": "yourpassword"
    },
    "CidrBlock": {
      "Type": "String",
      "Description": "建議您使用RFC私網位址10.0.0.0/8，172.16.0.0/12，192.168.0.0/16 作為專有網絡的網段。如果特殊情況需要使用公網網段作為專有網絡網段，請送出工單申請。",
      "Label": "IPv4網段",
      "Default": "192.168.0.0/16"
    },
    "User3": {
      "Type": "String",
      "Description": "建立MySQL普通使用者",
      "Label": "使用者3",
      "Default": "alterman"
    },
    "VSWName": {
      "Type": "String",
      "Description": "長度為2-128個字元，以大小字母或中文開頭，可包含數字，_或-。",
      "Label": "VSwitch名稱",
      "Default": "vsw_local"
    },
    "InternetChargeType": {
      "Type": "String",
      "Description": "PayByBandwidth（按固定帶寬計費）、PayByTraffic（按流量計費）。",
      "AllowedValues": [
        "PayByBandwidth",
        "PayByTraffic"
      ],
      "Label": "公網帶寬計費",
      "Default": "PayByTraffic"
    },
    "SystemDiskCategory": {
      "Type": "String",
      "Description": "指定系統盤類型。",
      "AllowedValues": [
        "cloud",
        "cloud_efficiency",
        "cloud_ssd",
        "ephemeral_ssd"
      ],
      "Label": "系統盤類型",
      "Default": "cloud_efficiency"
    },
    "InstanceName": {
      "Type": "String",
      "Description": "最長128個字元，可包含英文、中文、數字、下劃線（_）、點（.）、連字元（-）。",
      "Label": "執行個體名稱",
      "Default": "dbserver"
    },
    "InternetMaxBandwidthIn": {
      "Type": "Number",
      "Description": "取值範圍：[1, 100]，機關：Mbps。",
      "Label": "公網最大入網帶寬",
      "Default": 5
    },
    "ECSZoneId": {
      "Type": "String",
      "Description": "可用區是指在同一地域内，電力和網絡互相獨立的實體區域。在同一專有網絡内可用區與可用區之間内網互通，可用區之間能做到故障隔離。",
      "AllowedValues": [
        "cn-hangzhou-b",
        "cn-hangzhou-d",
        "cn-hangzhou-e",
        "cn-hangzhou-f",
        "cn-hangzhou-g",
        "cn-hangzhou-h",
        "cn-hangzhou-i"
      ],
      "Label": "可用區",
      "Default": "cn-hangzhou-b"
    },
    "MysqlPassword": {
      "Type": "String",
      "Description": "MySQLroot使用者密碼",
      "Label": "root使用者密碼",
      "MinLength": 6,
      "MaxLength": 30,
      "Default": "Test123"
    },
    "ImageId": {
      "Type": "String",
      "Description": "鏡像ID, 表示要啟動一個ECS執行個體的映像資源, <a href='#/product/cn-hangzhou/list/imageList' target='_blank'>檢視鏡像資源</a>",
      "Label": "ECS鏡像ID",
      "Default": "centos_7"
    },
    "VpcName": {
      "Type": "String",
      "Description": "長度為2-128個字元，以大小字母或中文開頭，可包含數字，_或-。",
      "Label": "Vpc名稱",
      "Default": "VPC_LOCAL"
    },
    "InstanceType": {
      "Type": "String",
      "Description": "ECS執行個體類型, <a href='#/product/cn-hangzhou/list/typeList' target='_blank'>檢視執行個體類型</a>",
      "AllowedValues": [
        "ecs.e3.medium",
        "ecs.g5.xlarge",
        "ecs.g6.xlarge",
        "ecs.t5-c1m4.xlarge",
        "ecs.sn2ne.xlarge"
      ],
      "Label": "ECS執行個體類型",
      "Default": "ecs.e3.medium"
    },
    "HostName": {
      "Type": "String",
      "Description": "最少 2 個字元，點（.）和連字元（-）不能作為 hostname 的首尾字元，且不能連續使用。",
      "Label": "主機名",
      "Default": "dbserver"
    },
    "DataBase1": {
      "Type": "String",
      "Description": "建立一個指定資料庫",
      "Label": "資料庫",
      "Default": "demodb"
    },
    "LoginPassword": {
      "NoEcho": true,
      "Type": "String",
      "Description": "ECS登入密碼",
      "Label": "ECS登入密碼",
      "Confirm": true,
      "MinLength": 6,
      "MaxLength": 30
    }
  },
  "Resources": {
    "WaitCondition": {
      "Type": "ALIYUN::ROS::WaitCondition",
      "Properties": {
        "Timeout": 600,
        "Count": 1,
        "Handle": {
          "Ref": "WaitConditionHandle"
        }
      }
    },
    "ECS": {
      "Type": "ALIYUN::ECS::Instance",
      "Properties": {
        "IoOptimized": "optimized",
        "PrivateIpAddress": "192.168.0.1",
        "ZoneId": {
          "Ref": "ECSZoneId"
        },
        "UserData": {
          "Fn::Replace": [
            {
              "ros-notify": {
                "Fn::GetAtt": [
                  "WaitConditionHandle",
                  "CurlCli"
                ]
              }
            },
            {
              "Fn::Join": [
                "",
                [
                  "#!/bin/sh \n",
                  "rootpwd=",
                  {
                    "Ref": "MysqlPassword"
                  },
                  " \n",
                  "data_base1=",
                  {
                    "Ref": "DataBase1"
                  },
                  " \n",
                  "user1=",
                  {
                    "Ref": "User1"
                  },
                  " \n",
                  "pwd1=",
                  {
                    "Ref": "Password1"
                  },
                  " \n",
                  "user2=",
                  {
                    "Ref": "User2"
                  },
                  " \n",
                  "pwd2=",
                  {
                    "Ref": "Password2"
                  },
                  " \n",
                  "user3=",
                  {
                    "Ref": "User3"
                  },
                  " \n",
                  "pwd3=",
                  {
                    "Ref": "Password3"
                  },
                  " \n",
                  "db_table=",
                  {
                    "Ref": "DBTable"
                  },
                  " \n",
                  "cd ~ \n",
                  "wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm \n",
                  "rpm -ivh mysql-community-release-el6-5.noarch.rpm \n",
                  "yum repolist all | grep mysql \n",
                  "yum install mysql-community-server -y \n",
                  "# 生成MySQL root使用者，設定密碼 \n",
                  "echo \\#\\!/bin/bash > dbroot.sh \n",
                  "echo MYSQL_PASSWD=\\\"$rootpwd\\\" >> dbroot.sh \n",
                  "echo service mysqld start >> dbroot.sh \n",
                  "echo yum install expect -y >> dbroot.sh \n",
                  "echo \"echo '#!/usr/bin/expect\" >> dbroot.sh \n",
                  "echo set timeout 60 >> dbroot.sh \n",
                  "echo 'set password [lindex $argv 0]' >> dbroot.sh \n",
                  "echo spawn mysql_secure_installation >> dbroot.sh \n",
                  "echo expect { >> dbroot.sh \n",
                  "echo \\\"enter for none\\\" { send \\\"\\\\r\\\"\\; exp_continue} >> dbroot.sh \n",
                  "echo \\\"Y/n\\\" { send \\\"Y\\\\r\\\" \\; exp_continue} >> dbroot.sh \n",
                  "echo '\"password\" { send \"$password\\r\"; exp_continue}' >> dbroot.sh \n",
                  "echo \\\"Cleaning up\\\" { send \\\"\\\\r\\\"} >> dbroot.sh \n",
                  "echo } >> dbroot.sh \n",
                  "echo \"interact ' > mysql_secure_installation.exp\" >> dbroot.sh \n",
                  "echo chmod +x mysql_secure_installation.exp >> dbroot.sh \n",
                  "echo './mysql_secure_installation.exp $MYSQL_PASSWD' >> dbroot.sh \n",
                  "chmod +x dbroot.sh \n",
                  "./dbroot.sh \n",
                  "# 資料庫配置檔案初始化指令 \n",
                  "cp /etc/my.cnf /etc/my.cnf.bak \n",
                  "echo '[mysqld]' > /etc/my.cnf \n",
                  "echo 'server_id=10001' >> /etc/my.cnf \n",
                  "echo 'innodb_buffer_pool_size         = 8G' >> /etc/my.cnf \n",
                  "echo 'port  = 3306' >> /etc/my.cnf \n",
                  "echo 'max_connections         = 1000 ' >> /etc/my.cnf \n",
                  "echo 'max_user_connections    = 1000 ' >> /etc/my.cnf \n",
                  "echo 'table_open_cache            = 10240 ' >> /etc/my.cnf \n",
                  "echo 'table_definition_cache      = 2048  ' >> /etc/my.cnf \n",
                  "echo 'open_files_limit = 65535 ' >> /etc/my.cnf \n",
                  "echo 'innodb_open_files               = 65535' >> /etc/my.cnf \n",
                  "echo 'innodb_log_file_size            = 100M ' >> /etc/my.cnf \n",
                  "echo 'innodb_log_files_in_group       = 2 ' >> /etc/my.cnf \n",
                  "echo 'lower_case_table_names  = 1' >> /etc/my.cnf \n",
                  "echo 'collation_server        = utf8_bin' >> /etc/my.cnf \n",
                  "echo 'character_set_server = utf8' >> /etc/my.cnf \n",
                  "echo 'skip_name_resolve' >> /etc/my.cnf \n",
                  "echo 'skip-external-locking' >> /etc/my.cnf \n",
                  "echo 'explicit_defaults_for_timestamp ' >> /etc/my.cnf \n",
                  "echo 'back_log        = 1024' >> /etc/my.cnf \n",
                  "echo 'thread_cache_size           = 512 ' >> /etc/my.cnf \n",
                  "echo 'max_connect_errors      = 2000 ' >> /etc/my.cnf \n",
                  "echo 'table_open_cache_instances  = 16' >> /etc/my.cnf \n",
                  "echo 'binlog_cache_size           = 512K' >> /etc/my.cnf \n",
                  "echo 'bulk_insert_buffer_size     = 64M' >> /etc/my.cnf \n",
                  "echo 'ft_min_word_len             = 4' >> /etc/my.cnf \n",
                  "echo 'key_buffer_size             = 32M' >> /etc/my.cnf \n",
                  "echo 'max_allowed_packet          = 1024M' >> /etc/my.cnf \n",
                  "echo 'max_heap_table_size         = 32M' >> /etc/my.cnf \n",
                  "echo 'tmp_table_size  = 32M' >> /etc/my.cnf \n",
                  "echo 'myisam_max_sort_file_size   = 10G' >> /etc/my.cnf \n",
                  "echo 'myisam_repair_threads       = 1' >> /etc/my.cnf \n",
                  "echo 'myisam_sort_buffer_size     = 24M' >> /etc/my.cnf \n",
                  "echo 'query_cache_limit           = 1M' >> /etc/my.cnf \n",
                  "echo 'query_cache_size            = 0' >> /etc/my.cnf \n",
                  "echo 'sort_buffer_size            = 4M' >> /etc/my.cnf \n",
                  "echo 'join_buffer_size            = 6M' >> /etc/my.cnf \n",
                  "echo 'read_buffer_size            = 2M' >> /etc/my.cnf \n",
                  "echo 'read_rnd_buffer_size        = 3M' >> /etc/my.cnf \n",
                  "echo 'thread_stack                = 192K' >> /etc/my.cnf \n",
                  "echo '# log' >> /etc/my.cnf \n",
                  "echo 'general_log                 = off ' >> /etc/my.cnf \n",
                  "echo 'log_bin=on' >> /etc/my.cnf \n",
                  "echo 'binlog_format=ROW' >> /etc/my.cnf \n",
                  "echo '#' >> /etc/my.cnf \n",
                  "echo 'datadir=/var/lib/mysql' >> /etc/my.cnf \n",
                  "echo 'socket=/var/lib/mysql/mysql.sock' >> /etc/my.cnf \n",
                  "echo '' >> /etc/my.cnf \n",
                  "echo 'symbolic-links=0' >> /etc/my.cnf \n",
                  "echo '' >> /etc/my.cnf \n",
                  "echo 'sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES' >> /etc/my.cnf \n",
                  "echo '' >> /etc/my.cnf \n",
                  "echo '[mysql_safe]' >> /etc/my.cnf \n",
                  "echo 'log-error=/var/log/mysqld.log' >> /etc/my.cnf \n",
                  "echo 'pid-file=/var/run/mysqld/mysqld.pid' >> /etc/my.cnf \n",
                  "service mysqld restart \n",
                  "# 作業系統層面資料庫優化 \n",
                  "echo 'ulimit -u 65535' >> /etc/profile \n",
                  "echo 'ulimit -n 65535' >> /etc/profile \n",
                  "echo 'ulimit -d unlimited' >> /etc/profile \n",
                  "echo 'ulimit -m unlimited' >> /etc/profile \n",
                  "echo 'ulimit -s unlimited' >> /etc/profile \n",
                  "echo 'ulimit -t unlimited' >> /etc/profile \n",
                  "echo 'ulimit -v unlimited' >> /etc/profile \n",
                  "source /etc/profile \n",
                  "# 建立資料庫、表，插入資料 \n",
                  "echo \\#\\!/bin/bash > db.sh \n",
                  "echo mysql -uroot -p\"$rootpwd\" \\<\\<EOF >> db.sh \n",
                  "echo CREATE DATABASE IF NOT EXISTS \"$data_base1\" DEFAULT CHARSET utf8 COLLATE utf8_general_ci\\; >> db.sh \n",
                  "echo create user \"$user1\" identified by \\\"$pwd1\\\"\\; >> db.sh \n",
                  "echo grant select on $data_base1.* to \\\"$user1\\\"@\\\"%\\\" identified by \\\"$pwd1\\\"\\; >> db.sh \n",
                  "echo create user \"$user2\" identified by \\\"$pwd2\\\"\\; >> db.sh \n",
                  "echo grant select,update,insert,delete on \"$data_base1\".* to \"$user2\"@\\\"%\\\" identified by \\\"$pwd2\\\"\\; >> db.sh \n",
                  "echo create user \"$user3\" identified by \\\"$pwd3\\\"\\; >> db.sh \n",
                  "echo grant create,drop,index,alter on \"$data_base1\".* to \\\"$user3\\\"@\\\"%\\\" identified by \\\"$pwd3\\\"\\; >> db.sh \n",
                  "echo flush privileges\\; >> db.sh \n",
                  "echo use \"$data_base1\"\\; >> db.sh \n",
                  "echo DROP TABLE IF EXISTS demo_data\\; >> db.sh \n",
                  "echo CREATE TABLE \"$db_table\" \\( >> db.sh \n",
                  "echo   buyer_id varchar\\(32\\) DEFAULT NULL , >> db.sh \n",
                  "echo   buyer_prov varchar\\(32\\) DEFAULT NULL , >> db.sh \n",
                  "echo   gender varchar\\(32\\) DEFAULT NULL , >> db.sh \n",
                  "echo   age_range varchar\\(32\\) DEFAULT NULL , >> db.sh \n",
                  "echo   zodiac varchar\\(32\\) DEFAULT NULL  >> db.sh \n",
                  "echo \\) ENGINE=InnoDB DEFAULT CHARSET=utf8\\; >> db.sh \n",
                  "echo insert into \"$db_table\"\\(\"buyer_id\",\"buyer_prov\",\"gender\",\"age_range\",\"zodiac\"\\) values >> db.sh \n",
                  "echo \\(\\\"111111111\\\",\\\"asdasd\\\",\\\"M\\\",\\\"20-30\\\",\\\"by\\\"\\), >> db.sh \n",
                  "echo \\(\\\"22222222\\\",\\\"zxczxc\\\",\\\"M\\\",\\\"20-30\\\",\\\"by\\\"\\)\\; >> db.sh \n",
                  "echo EOF >> db.sh \n",
                  "chmod +x db.sh \n",
                  "./db.sh \n",
                  "ros-notify -d \"{\\\"Data\\\" : \\\"SUCCESS\\\", \\\"Status\\\" : \\\"SUCCESS\\\"}\" \n"
                ]
              ]
            }
          ]
        },
        "SystemDiskSize": {
          "Ref": "SystemDiskSize"
        },
        "VSwitchId": {
          "Ref": "VSW"
        },
        "SecurityGroupId": {
          "Ref": "SG"
        },
        "SystemDiskCategory": {
          "Ref": "SystemDiskCategory"
        },
        "InternetChargeType": {
          "Ref": "InternetChargeType"
        },
        "InstanceName": {
          "Ref": "InstanceName"
        },
        "VpcId": {
          "Ref": "VPC"
        },
        "InternetMaxBandwidthIn": {
          "Ref": "InternetMaxBandwidthIn"
        },
        "ImageId": {
          "Ref": "ImageId"
        },
        "InstanceType": {
          "Ref": "InstanceType"
        },
        "AllocatePublicIP": {
          "Ref": "PublicIP"
        },
        "HostName": {
          "Ref": "HostName"
        },
        "Password": {
          "Ref": "LoginPassword"
        }
      }
    },
    "SG": {
      "Type": "ALIYUN::ECS::SecurityGroup",
      "Properties": {
        "VpcId": {
          "Ref": "VPC"
        },
        "SecurityGroupName": "mysg",
        "SecurityGroupIngress": [
          {
            "PortRange": "-1/-1",
            "Priority": 1,
            "SourceCidrIp": "0.0.0.0/0",
            "IpProtocol": "all",
            "NicType": "internet"
          }
        ],
        "SecurityGroupEgress": [
          {
            "PortRange": "-1/-1",
            "Priority": 1,
            "IpProtocol": "all",
            "DestCidrIp": "0.0.0.0/0",
            "NicType": "intranet"
          }
        ]
      }
    },
    "WaitConditionHandle": {
      "Type": "ALIYUN::ROS::WaitConditionHandle"
    },
    "VPC": {
      "Type": "ALIYUN::ECS::VPC",
      "Properties": {
        "CidrBlock": {
          "Ref": "CidrBlock"
        },
        "VpcName": {
          "Ref": "VpcName"
        }
      }
    },
    "VSW": {
      "Type": "ALIYUN::ECS::VSwitch",
      "Properties": {
        "VpcId": {
          "Ref": "VPC"
        },
        "ZoneId": {
          "Ref": "Vsw_ZoneId"
        },
        "CidrBlock": {
          "Ref": "CidrBlock"
        },
        "VSwitchName": {
          "Ref": "VSWName"
        }
      }
    }
  },
  "Outputs": {
    "ECS執行個體ID": {
      "Value": {
        "Fn::GetAtt": [
          "ECS",
          "InstanceId"
        ]
      }
    },
    "公網IP": {
      "Value": {
        "Fn::GetAtt": [
          "ECS",
          "PublicIp"
        ]
      }
    }
  }
}           

參考阿裡雲解決方案

詳情