Go to Qiita Advent Calendar 2024 Top
LoginSignup
25
25

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@k-motoyan

chefで作るmysql

Last updated at Posted at 2013-09-09

最近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 %>

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

25
25
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
25
25

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?