Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
25
Help us understand the problem. What are the problem?

More than 5 years have passed since last update.

chefで作るmysql

最近chefでレシピを書くのが楽しくてしょうがなくなってきました;
今回はchefのレシピ第三弾としてMySQLのレシピを書いてみました。

どんなレシピ?

  • mysqlの5.5か5.6をインストールします。(attributeで変更)
  • my.cnfに一部の設定が出来ます。(attributeで変更)
  • mysql5.6でもパスワードが空で設定されます。(5.6はデフォルト値が設定してあるので、変更してます)
  • rpmからインストールしています。

ソースコードは?

恐縮ながらgithubにmy_cookbooksというリポジトリを作成して、
自分で書いたレシピを管理しております。
https://github.com/k-motoyan/my_cookbooks

レシピを見てみる

レシピはこんなふうになりました。
mysql5.6の時はパスワードを設定する処理が走ります。
(if文で制御してるけど、only_ifで制御したほうがレシピっぽいかも…)

mysql/recepes/default.rb
version = node['mysql']['version']
my_cnf_path = (version.to_s == '5.6')? '/usr/my.cnf' : '/etc/my.cnf'


bash 'remove_installed_mysql' do
  only_if 'yum list installed | grep mysql*'
  user 'root'

  code <<-EOL
    yum remove -y mysql*
  EOL
end

node['mysql']['install_rpms'].each do |rpm|
  cookbook_file "/tmp/#{rpm[:rpm_file]}" do
    source "#{version}/#{rpm[:rpm_file]}"
  end

  package "#{rpm[:package_name]}" do
    action :install
    provider Chef::Provider::Package::Rpm
    source "/tmp/#{rpm[:rpm_file]}"
  end
end

template "#{my_cnf_path}" do
  user 'root'
  group 'root'
  mode 644
  source 'my.cnf.erb'

  variables ({
    :server_charset                  => node['mysql']['server_charset'],
    :max_connections                 => node['mysql']['max_connections'],
    :query_cache_size                => node['mysql']['query_cache_size'],
    :table_cache_size                => node['mysql']['table_cache_size'],
    :thread_cache_size               => node['mysql']['thread_cache_size'],
    :join_buffer_size                => node['mysql']['join_buffer_size'],
    :sort_buffer_size                => node['mysql']['sort_buffer_size'],
    :read_rnd_buffer_size            => node['mysql']['read_rnd_buffer_size'],
    :innodb_file_per_table           => node['mysql']['innodb_file_per_table'],
    :innodb_data_file_path           => node['mysql']['innodb_data_file_path'],
    :innodb_autoextend_increment     => node['mysql']['innodb_autoextend_increment'],
    :innodb_buffer_pool_size         => node['mysql']['innodb_buffer_pool_size'],
    :innodb_additional_mem_pool_size => node['mysql']['innodb_additional_mem_pool_size'],
    :innodb_write_io_threads         => node['mysql']['innodb_write_io_threads'],
    :innodb_read_io_threads          => node['mysql']['innodb_read_io_threads'],
    :innodb_log_buffer_size          => node['mysql']['innodb_log_buffer_size'],
    :innodb_log_file_size            => node['mysql']['innodb_log_file_size'],
    :innodb_flush_log_at_trx_commit  => node['mysql']['innodb_flush_log_at_trx_commit']
  })
end

service 'mysql' do
  action [ :enable, :start ]
end

# version 5.6 over
if version.to_f >= 5.6
  package 'expect' do
    only_if 'ls /root/.mysql_secret'
    :install
  end

  cookbook_file '/tmp/password_set' do
    only_if 'ls /root/.mysql_secret'
    source "#{version}/password_set"
  end

  execute 'password_set' do
    only_if 'ls /root/.mysql_secret'
    user 'root'
    command '/bin/bash /tmp/password_set && rm -f /tmp/password_set'
  end

  package 'expect' do
    :remove
  end
end

mysql5.6のパスワード設定のために別途shellスクリプトを書いてexecuteで実行してます。
password設定のためにexpectをインストールしていますが、設定が終わったら削除しています。
files以下においてるけど、場所はここでいいのかな?

mysql/files/password_set
#!/bin/bash

PASS=`cat /root/.mysql_secret | sed -e "s/# The random password set for the root user at [a-zA-Z]\+ [a-zA-Z]\+  [0-9]\+ [0-9]\{2\}:[0-9]\{2\}:[0-9]\{2\} [0-9]\{4\} (local time): //"`
expect -c "
  spawn mysql -p
    expect \"Enter Password:\"
    send \"${PASS}\n\"
    expect \"mysql>\"
    send \"SET PASSWORD FOR root@localhost=PASSWORD('');\n\"
    expect \"mysql>\"
    send \"exit\n\"
  interact
"
rm -f .mysql_secret

exit 0

attributeにはmysqlのバージョンやインストールするrpmファイルやmy.cnfの設定が散りばめられています。
だいぶ見づらくなりました…

mysql/attributes/default.rb
# my.cnf default values
default['mysql']['server_charset']                  = 'utf8'
default['mysql']['max_connections']                 = 128
default['mysql']['query_cache_size']                = 0
default['mysql']['table_cache_size']                = 1024
default['mysql']['thread_cache_size']               = 128
default['mysql']['join_buffer_size']                = '16M'
default['mysql']['sort_buffer_size']                = '2M'
default['mysql']['read_rnd_buffer_size']            = '2M'
default['mysql']['innodb_file_per_table']           = true
default['mysql']['innodb_data_file_path']           = 'ibdata1:1G:autoextend'
default['mysql']['innodb_autoextend_increment']     = 64
default['mysql']['innodb_buffer_pool_size']         = '256M'
default['mysql']['innodb_additional_mem_pool_size'] = '10M'
default['mysql']['innodb_write_io_threads']         = 4
default['mysql']['innodb_read_io_threads']          = 4
default['mysql']['innodb_log_buffer_size']          = 16
default['mysql']['innodb_log_file_size']            = '64M'
default['mysql']['innodb_flush_log_at_trx_commit']  = 1

# version 5.6
default['mysql']['version'] = '5.6'
default['mysql']['install_rpms'] = [
  {
    :rpm_file => 'MySQL-server-5.6.13-1.el6.x86_64.rpm',
    :package_name => 'MySQL-server'
  },
  {
    :rpm_file => 'MySQL-client-5.6.13-1.el6.x86_64.rpm',
    :package_name => 'MySQL-client'
  },
  {
    :rpm_file => 'MySQL-shared-5.6.13-1.el6.x86_64.rpm',
    :package_name => 'MySQL-shared'
  },
  #{
  #  :rpm_file => 'MySQL-shared-compat-5.6.13-1.el6.x86_64.rpm',
  #  :package_name => 'MySQL-shared-compat'
  #},
  #{
  #  :rpm_file => 'MySQL-devel-5.6.13-1.el6.x86_64.rpm',
  #  :package_name => 'MySQL-devel'
  #},
  #{
  #  :rpm_file => 'MySQL-embedded-5.6.13-1.el6.x86_64.rpm',
  #  :package_name => 'MySQL-embedded'
  #}
]

# version 5.5
=begin
default['mysql']['version'] = '5.5'
default['mysql']['install_rpms'] = [
  {
    :rpm_file => 'MySQL-server-5.5.33-1.el6.x86_64.rpm',
    :package_name => 'MySQL-server'
  },
  {
    :rpm_file => 'MySQL-client-5.5.33-1.el6.x86_64.rpm',
    :package_name => 'MySQL-client'
  },
  {
    :rpm_file => 'MySQL-shared-5.5.33-1.el6.x86_64.rpm',
    :package_name => 'MySQL-shared'
  },
  #{
  #  :rpm_file => 'MySQL-shared-compat-5.5.33-1.el6.x86_64.rpm',
  #  :package_name => 'MySQL-shared-compat'
  #},
  #{
  #  :rpm_file => 'MySQL-devel-5.5.33-1.el6.x86_64.rpm',
  #  :package_name => 'MySQL-devel'
  #},
  #{
  #  :rpm_file => 'MySQL-embedded-5.5.33-1.el6.x86_64.rpm',
  #  :package_name => 'MySQL-embedded'
  #}
]
=end

my.cnfはテンプレート化しました。
もっと多くの設定出来るようになったら嬉しいかも(レプリケーションとか)

mysql/templates/my.cnf.rb
[mysqld_safe]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character_set_server = <%= @server_charset %>
skip-character-set-client-handshake

max_connections = <%= @max_connections %>

query_cache_size  = <%= @query_cache_size %>
table_cache_size  = <%= @table_cache_size %>
thread_cache_size = <%= @thread_cache_size %>

join_buffer_size     = <%= @join_buffer_size %>
sort_buffer_size     = <%= @sort_buffer_size %>
read_rnd_buffer_size = <%= @read_rnd_buffer_size %>

<% if @innodb_file_per_table %>innodb_file_per_table<% end %>
innodb_data_file_path           = <%= @innodb_data_file_path %>
innodb_autoextend_increment     = <%= @innodb_autoextend_increment %>
innodb_buffer_pool_size         = <%= @innodb_buffer_pool_size %>
innodb_additional_mem_pool_size = <%= @innodb_additional_mem_pool_size %>
innodb_write_io_threads         = <%= @innodb_write_io_threads %>
innodb_read_io_threads          = <%= @innodb_read_io_threads %>
innodb_log_buffer_size          = <%= @innodb_log_buffer_size %>
innodb_log_file_size            = <%= @innodb_log_file_size %>
innodb_flush_log_at_trx_commit  = <%= @innodb_flush_log_at_trx_commit %>

細々とした設定を考えだすと、とても奥が深いですね。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
25
Help us understand the problem. What are the problem?