您现在的位置是:首页 > 文章详情

使用ROS创建ECS,安装MySQL并生成用户

日期:2019-10-15点击:402

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

参考阿里云解决方案详情

原文链接:https://yq.aliyun.com/articles/721044
关注公众号

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。

持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。

文章评论

共有0条评论来说两句吧...

文章二维码

扫描即可查看该文章

点击排行

推荐阅读

最新文章