在杭州创建自建数据库网络和服务器环境，部署一台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"
]
}
}
}
}参考阿里云解决方案详情
