在杭州创建自建数据库网络和服务器环境，部署一台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"
        ]
      }
    }
  }
}

参考阿里云解决方案详情