Oracle MySQL 5.6.14 (3nodes/1Master/2Slave) Replication for Chef + Vagrant

attributes_default.rb

default['mysql']['root_user'] = "root"
default['mysql']['root_password'] = ""
default['mysql']['replica_host'] = "10.10.10.10"
default['mysql']['replica_user'] = "repl"
default['mysql']['replica_password'] = "repl"
default['mysql']['log_expire'] = "14"

default.rb

#
# Cookbook Name:: mysql
# Recipe:: default
#
# Copyright 2013, INSANEWORKS.LLC
#
# All rights reserved - Do Not Redistribute
#
include_recipe "yum"

yum_key "RPM-GPG-KEY-oracle-ol6" do
  url "http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 "
  action :add
end

yum_repository "ol6_MySQL_56" do
  description "MySQL for Oracle Linux 6 ($basearch)"
  url "http://public-yum.oracle.com/repo/OracleLinux/OL6/MySQL56/$basearch/"
  key "RPM-GPG-KEY-oracle-ol6"
  enabled 1
end

%w{mysql-community-client mysql-community-server}.each do |p|
  package p do
    action :install
  end
end

service "mysqld" do
  action [:start, :enable]
end

metadata.rb

name             'mysql'
maintainer       'INSANEWORKS,LLC'
maintainer_email 'webmaster@insaneworks.co.jp'
license          'All rights reserved'
description      'Installs/Configures mysql'
long_description IO.read(File.join(File.dirname(__FILE__), 'README.md'))
version          '0.1.0'

%w{yum}.each do |dp|
  depends dp
end

my.cnf.erb

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

<%- if node['mysql']['master'] %>
server-id = 1
log-bin
expire_logs_days = <%= node['mysql']['log_expire'] %>
<%- end %>
<%- if node['mysql']['slave'] %>
server-id = <%= rand(65535)+1 %>
<%- end %>
<%- if node['mysql']['readonly'] %>
read_only
<%- end %>

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

replication.rb

#
# Cookbook Name:: groonga
# Recipe:: default
#
# Copyright 2013, INSANEWORKS.LLC
#
# All rights reserved - Do Not Redistribute
#
include_recipe "yum"
include_recipe "mysql::default"

template "/etc/my.cnf" do
  source "my.cnf.erb"
  notifies :restart, 'service[mysqld]', :immediately
end

if node[:mysql][:master]
  bash "createrepluser" do
    flags "-e"
    code <<-EOH
      mysql -e "
      GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY '#{node[:mysql][:replica_password]}';
      FLUSH PRIVILEGES;
      "
    EOH
  end
end

if node[:mysql][:slave]
  bash "repldump" do
    flags "-e"
    code <<-EOH
      mysqldump -u #{node[:mysql][:replica_user]} --password=#{node[:mysql][:root_passwordword]} -h #{node[:mysql][:replica_host]} \
        --all-databases \
        --master-data \
        --add-drop-database \
        --opt \
        --hex-blob \
        --single-transaction \
        | mysql -u #{node[:mysql][:root_user]} --password=#{node[:mysql][:root_passwordword]}
    EOH
  end
end

if node[:mysql][:slave]
  bash "changemasterto" do
    flags "-e"
    code <<-EOH
    mysql -e '
      STOP SLAVE;
      CHANGE MASTER TO
      MASTER_HOST="#{node[:mysql][:replica_host]}",
      MASTER_USER="#{node[:mysql][:replica_user]}",
      MASTER_passwordWORD="#{node[:mysql][:replica_password]}\";
      START SLAVE;
      '
    EOH
  end
end

Vagrantfile

# -*- mode: ruby -*-
# vi: set ft=ruby :

# Vagrantfile API/syntax version. Don't touch unless you know what you're doing!
VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|

  vms = [
    { :name => "master", :ip => "10.10.10.10", :master => true, :readonly => false },
    { :name => "slave1", :ip => "10.10.10.20", :slave  => true, :readonly => true  },
    { :name => "slave2", :ip => "10.10.10.30", :slave  => true, :readonly => true  }
  ]

  config.vm.box = "NREL-CentOS-6.4-x86_64-v20131103"
  config.vm.box_url = "http://developer.nrel.gov/downloads/vagrant-boxes/CentOS-6.4-x86_64-v20131103.box"

  config.ssh.forward_agent = true

  vms.each do |param|
    config.vm.define param[:name] do |config|
      config.vm.network :private_network, ip: param[:ip]
      config.vm.host_name = "%s.vagrant" % param[:name]
      config.vm.provision :chef_solo do |chef|
        chef.cookbooks_path = "vagrant/cookbooks","vagrant/site-cookbooks"
        chef.data_bags_path = "vagrant/data_bags"
        chef.add_recipe "default"
        chef.add_recipe "mysql::replication"
        chef.json = {
          "mysql" => {
            "master"    => param[:master],
            "slave"     => param[:slave],
            "readonly"  => param[:readonly]
          }
        }
      end
      config.vm.provision :shell, inline: "grep -q single-request-reopen /etc/resolv.conf || ( echo 'options single-request-reopen' >> /etc/resolv.conf; service network restart )"
    end
  end

end