インフラブログ

とあるWEBサイトのインフラを構築運用するメモ

AWS RDSでMySQLサーバを立ち上げる

RDSを使ってmysqlをmaster,slaveのレプリケーション構成で立ち上げます。 redisと同じようにスクリプトでセットアップできるようにします。パラメータは初期パラメータとしてとりあえずの設定をしています。 MultiAZでマスタを立ち上げるとactiveがap-northeast-1cになる場合があるので、その時はfailover付きでリブートし直します。

AWS RDSMySQLサーバを立ち上げる


RDSを使ってmysqlをmaster,slaveのレプリケーション構成で立ち上げます。
redisと同じようにスクリプトでセットアップできるようにします。パラメータは初期パラメータとしてとりあえずの設定をしています。
MultiAZでマスタを立ち上げるとactiveがap-northeast-1cになる場合があるので、その時はfailover付きでリブートし直します。

stage = "staging" master = "#{stage}-master-1" slave = "#{stage}-slave-1" subnet_group = "#{stage}-rds-subnet" param_group = "#{stage}-mysql56" param_group_family = "mysql5.6" option_group = "#{stage}-mysql56" subnets = ["subnet-xxx", "subnet-xxx"] availability_zones = ["ap-northeast-1a", "ap-northeast-1c"] instance_class = "db.m1.small" storage = 10 db_name = "dbname_#{stage}" username = "xxx" password = "xxx" engine_version = "5.6.13" stage == "production" ? sg = "sg-xxx" : sg="sg-xxx" stage == "production" ? arn = "xxx" : arn = "xxx" event_categories = ["availability", "configuration change", "creation", "deletion", "failover", "failure", "low storage", "maintenance", "notification", "recovery", "restoration"]

client = AWS::RDS::Client.new

オプショングループ作成

client.create_db_parameter_group( :option_group_name => option_group_name, :engine_name => :mysql, :major_engine_version => "5.6", :option_group_description => "#{stage} param group" )

パラメータグループ作成

client.create_db_parameter_group( :db_parameter_group_name => param_group, :db_parameter_group_family => param_group_family, :description => "#{stage} param group" )

パラメータ変更

client.modify_db_parameter_group( :db_parameter_group_name => param_group, :parameters => [ {:parameter_name => "character_set_client", :parameter_value => "utf8mb4", :apply_method => "immediate"}, {:parameter_name => "character_set_connection", :parameter_value => "utf8mb4", :apply_method => "immediate"}, {:parameter_name => "character_set_database", :parameter_value => "utf8mb4", :apply_method => "immediate"}, {:parameter_name => "character_set_server", :parameter_value => "utf8mb4", :apply_method => "immediate"}, {:parameter_name => "character_set_results", :parameter_value => "utf8mb4", :apply_method => "immediate"}, {:parameter_name => "collation_connection", :parameter_value => "utf8mb4_general_ci", :apply_method => "immediate"}, {:parameter_name => "init_connect", :parameter_value => "SET NAMES utf8mb4;", :apply_method => "immediate"}, {:parameter_name => "max_connect_errors", :parameter_value => "999999999", :apply_method => "immediate"}, {:parameter_name => "max_allowed_packet", :parameter_value => "67108864", :apply_method => "immediate"}, {:parameter_name => "slow_query_log", :parameter_value => "ON", :apply_method => "immediate"}, {:parameter_name => "long_query_time", :parameter_value => "0.1", :apply_method => "immediate"}, {:parameter_name => "min_examined_row_limit", :parameter_value => "1000", :apply_method => "immediate"}, {:parameter_name => "query_cache_type", :parameter_value => "1", :apply_method => "pending-reboot"}, {:parameter_name => "general_log", :parameter_value => "OFF", :apply_method => "immediate"}, {:parameter_name => "wait_timeout", :parameter_value => "60", :apply_method => "immediate"}, {:parameter_name => "lock_wait_timeout", :parameter_value => "3", :apply_method => "immediate"}, {:parameter_name => "skip_name_resolve", :parameter_value => "ON", :apply_method => "pending-reboot"}, {:parameter_name => "log_output", :parameter_value => "FILE", :apply_method => "immediate"},

] )

サブネットグループ作成

client.create_db_subnet_group( :db_subnet_group_name => subnet_group, :db_subnet_group_description => "#{stage} subnet group" , :subnet_ids => subnets )

マスタDB作成

client.create_db_instance( :db_name => db_name, :db_instance_identifier => master, :allocated_storage => storage, :db_instance_class => instance_class, :engine => "mysql", :master_username => username, :master_user_password => password, :vpc_security_group_ids => [sg], :db_subnet_group_name => subnet_group, #:availability_zone => availability_zones[0], :db_parameter_group_name => param_group, :option_group_name => option_group, :preferred_maintenance_window => "mon:05:30-mon:06:30", :backup_retention_period => 30, :preferred_backup_window => "19:00-20:00", :port => 3306, :multi_az => true, :engine_version => engine_version, :auto_minor_version_upgrade => false, )

完成を待つ

while "available" != client.describe_db_instances(:db_instance_identifier => master)[:db_instances][0][:db_instance_status] puts "waiting for creating master" sleep 30 end

スレーブDB作成

client.create_db_instance_read_replica( :db_instance_identifier => slave, :source_db_instance_identifier => master, :db_instance_class => instance_class, :availability_zone => availability_zones[0], :port => 3306, :auto_minor_version_upgrade => false, )

完成を待つ

while "available" != client.describe_db_instances(:db_instance_identifier => slave)[:db_instances][0][:db_instance_status] puts "waiting for creating slave" sleep 30 end

イベント通知の設定を行う

client.create_event_subscription( :subscription_name => "#{stage}-event", :sns_topic_arn => arn, :source_type => "db-instance", :event_categories => event_categories, :source_ids => [master, slave], :enabled => true )