Thank you. This is Komiya.

Continuing from the previous article, I will write about dbserver recipes.
The only thing I have to do is test with munin, zabbx and serverspec. It's a little patience already.

Since it is a fixed version installation like the web, external recipes are basically not used.

• Install and launch MySQL and enable auto-start

[shell]# cd /root/chef-repo/site-cookbooks/mysqld/recipes

mkdir -p /root/chef-repo/site-cookbooks/mysqld/files/default/usr/local/src/rpms

mkdir -p .. /templates/default/etc/init.d

mkdir -p .. /files/default/etc/init.d

mkdir -p .. /files/default/root

mkdir -p /root/chef-repo/site-cookbooks/mysqld/files/default/etc/logrotate.d

mkdir -p /root/chef-repo/site-cookbooks/mysqld/files/default/opt/{backup,bin}

scp -Cp xxx-db02:/opt/bin/mysql-back.sh /root/chef-repo/site-cookbooks/mysqld/files/default/opt/bin/

cp -p /etc/logrotate.d/mysqld /root/chef-repo/site-cookbooks/mysqld/files/default/etc/logrotate.d/

scp -Cp xxx-db01:/etc/init.d/mysqld .. /files/default/etc/init.d/

scp -Cp xxx-db02:/etc/my.cnf .. /templates/default/etc/

cp -p /root/.path_to_file .. /files/default/root/

cd /opt/src/rpms/

tar czf /root/chef-repo/site-cookbooks/mysqld/files/default/usr/local/src/rpms/db-rpm.tar.gz ./db-rpm/

tar czf /root/chef-repo/site-cookbooks/mysqld/files/default/usr/local/src/rpms/db-tools-rpm.tar.gz ./db-tools-rpm/

tar tzf /root/chef-repo/site-cookbooks/mysqld/files/default/usr/local/src/rpms/db-rpm.tar.gz

vi .. /templates/default/etc/my.cnf

diff /etc/my.cnf .. /templates/default/etc/my.cnf

53c53

< server-id = 100

server-id = <%= node['mysqld']['server_id'] %>

vi .. /.. /.. /nodes/10.0.0.241.json

Write the server_id attribute to the ↓node (server_id needs to be changed for each node to be replicated, { "mysqld" : { "server_id" : 103 }, "run_list":[ "role[dbserver]" ] }

cd /root/chef-repo/site-cookbooks/mysqld/recipes

vi mysqld-server.rb

package "perl-DBI" do not_if "rpm -qa|grep perl-DBI" action :install end

package "perl-TermReadKey" do not_if "rpm -qa|grep perl-TermReadKey" action :install end

cookbook_file "/tmp/db-rpm.tar.gz" do source "usr/local/src/rpms/db-rpm.tar.gz" mode 0644 end

cookbook_file "/tmp/db-tools-rpm.tar.gz" do source "usr/local/src/rpms/db-tools-rpm.tar.gz" mode 0644 end

filename = "db-rpm" filename2 = "db-tools-rpm" script "install_mysqld" do not_if 'rpm -qa|grep mysql-server' interpreter "bash" user "root" code <-EOL cd="" mp="" tar="" xzf="" mp/#{filename}.tar.gz="" rpm="" -i="" mp/#{filename}/_rpm="" rpm="" -i="" mp/#{filename}/5.5.27/mysql_rpm="" tar="" xzf="" mp/#{filename2}.tar.gz="" rpm="" -i="" mp/#{filename2}/m*rpm="" eol="" end="" cookbook_file="" "/etc/init.d/mysqld"="" do="" source="" "etc/init.d/mysqld"="" mode="" 0755="" end="" template="" '/etc/my.cnf'="" do="" owner="" 'root'="" group="" 'root'="" source="" 'etc/my.cnf'="" end="" service="" "mysqld"="" do="" supports="" :status=""> true, :restart => true, :reload => :true action [ :enable, :start ] end

cookbook_file "/etc/logrotate.d/mysqld" do source "etc/logrotate.d/mysqld" owner 'root' group 'root' mode 0644 end

directory '/opt/bin/' do owner 'root' group 'root' mode '0755' action :create end

directory '/opt/backup/' do owner 'root' group 'root' mode '0755' action :create end

cookbook_file "/opt/bin/mysql-back.sh" do source "opt/bin/mysql-back.sh" owner 'root' group 'root' mode 0755 end

#cron "mysql-backup" do

minute "31"

hour "4"

day "*"

month "*"

weekday"*"

command "/opt/bin/mysql-back.sh"

action :create

#end[/shell]

・Create a user for mysql
I decided not to use opscode recipes because they are difficult for beginners to read and debug errors.
Reference:
Install MySQL in ChefHandle MySQL passwords in ChefGet started todayManage MySQL users and databases in Chef Chef
[shell]# vi site-cookbooks/mysqld/recipes/mysql-users.rb #include_recipe "openssl" #include_recipe 'database::mysql'

#mysql_connection_info = {:host => "localhost",

:username => 'root',

#:password => node['mysql']['server_root_password']}

:password => ''}

#mysql_database "xxx_db" do

connection mysql_connection_info

action :create

#end

xxxadm_data = Chef::EncryptedDataBagItem.load("mysqlusers","xxx_admin") root_data = Chef::EncryptedDataBagItem.load("mysqlusers","root") repl_data = Chef:: EncryptedDataBagItem.load("mysqlusers","repl") myuser_o = xxxadm_data["user"] mypass_o =xxxadm_data["pass"] myuser_ro = root_data["user"] mypass_ro =root_data[" pass"] myuser_re = repl_data["user"] mypass_re =repl_data["pass"] #mysql_database_user "#{user}" do

connection mysql_connection_info

password "#{password}"

database_name "*"

host "[#{host}, %, localhost]"

privileges [:all]

action [:create, :grant]

#end # mysqlconn = "mysql -u root" script "create_msql_xxxadm" do not_if "ls /root/.path_to_file" #not_if "#{mysqlconn} -p #{mypass_ro} -e 'select count(_) from mysql.user where user=\ 'repl\';'" interpreter "bash" user "root" code

<-EOL #{mysqlconn} < EOF grant all privileges on *._ to #{myuser_o}@'%' identified by "#{mypass_o}"; grant all privileges on . to #{myuser_o}@'10.0.0.%' identified by "#{mypass_o}"; grant all privileges on . to #{myuser_o}@'localhost' identified by "#{mypass_o}"; grant all privileges on . to #{myuser_ro}@'10.0.0.%' identified by "#{mypass_ro}"; grant all privileges on . to #{myuser_ro}@'localhost' identified by "#{mypass_ro}"; grant replication slave, replication client on . to #{myuser_re}@'10.0.0.%' identified by "#{mypass_re}"; grant replication slave, replication client on . to #{myuser_re}@'localhost' identified by "#{mypass_re}"; drop database test; delete from mysql.user where password=''; flush privileges; EOF EOL end

script "create_pfile" do not_if 'ls /root/.path_to_file' interpreter "bash" user "root" code <-EOL echo="" "#{mypass_ro}"=""> /root/.path_to_file chown 400 /root/.path_to_file EOL end[/shell] *An error will occur if EOF is not at the beginning of the line.

・Replication construction recipe (note that it has not been tested)

I haven't been able to test it yet, but I thought about how to turn the actual procedure into a recipe, and it turned out to be as follows.
　I can only think of doing it in bash.
　It seems like it will take time to go through third-party recipes and decipher them completely
　It's very dangerous to use something you don't understand, so I'll go with it for now.
　　The stg server is backed up by stg db, so I don't use it.
　　Bring the latest dump file taken with db02 or something like remote_file and put it in.
　　Since it may be used for migration, it is data taken with master-data=2, so
　　Extract the GHANGE MASTER TO syntax in the dump file with zgrep or something like that and put it in a variable.
　　I passed a variable in mysql -e or something like that, let it see the master, and enable it to be read-only.
　　I think it will feel like replication starts.
　　For the time being, in the direction of making a replication_setup.sh and having it executed.
　　If you put in the data you have taken in all, the entire user will enter the mysql schema.
　　　Maybe I didn't have to encrypt it with databags. (Now)

Writing a Shell to Set Up Replication
[shell]# vi replication_setup.sh ------------------------------- #!/bin/bash

BACKUP_SRV='10.0.0.222'
BACKUP_PATH='/opt/backup'
BACKUP_FL='mysqldump_3306_"`LANG=C;date +%y%m%d`".sql'
MSQL_USER=root
MSQL_PASS=`cat /path_to_file`
MSQL_CONN="mysql -u ${MSQL_USER} -p${MSQL_PASS}"
MSQL_CMD_RO='set global read_only=1;'
#MAIL_TO='hoge@isao.co.jp'
MAIL_TO='hoge@isao.net'
MYHOST=`uname -n`
DATETIME=`date +%Y%m%d_%H:%M:%D`

if [ -s /tmp/${BACKUP_FL}.gz ]; then
 :
else
 echo "replication setup faild"|mail -s `uname -n`_`date +%Y%m%d_%H:%M:%S` ${MAIL_TO}
 exit 0
fi

gunzip /tmp/${BACKUP_FL}.gz
${MSQL_CONN} < /tmp/${BACKUP_FL}
CHANGE_MASTER=`cat /tmp/${BACKUP_FL}|head -30|grep 'CHANGE MASTER TO'|sed -e 's/^-- //g'`

${MSQL_CONN} -e "stop slave; "
${MSQL_CONN} -e "${CHANGE_MASTER}"
${MSQL_CONN} -e "${MSQL_CMD_RO}"
${MSQL_CONN} -e "start slave; "

echo `${MSQL_CONN} -e "show slave status\G"`|mail -s `uname -n`_`date +%Y%m%d_%H:%M:%S` ${MAIL_TO}

exit 0
-------------------------------

chmod +x replication_setup.sh

mkdir /root/chef-repo/site-cookbooks/mysqld/files/default/tmp

mv replication_setup.sh /root/chef-repo/site-cookbooks/mysqld/files/default/tmp/[/shell]

<br>For the time being, I'm going to pick it up from STG every day.<br>```

\[shell\]# mkdir -p /root/chef-repo/site-cookbooks/mysqld/files/default/opt/{backup,bin}

# vi /opt/bin/get\_dbbkup.sh

---

#!/bin/bash

CHEF_BKUP_PATH=/root/chef-repo/site-cookbooks/mysqld/files/default/opt/backup BACKUP_SRV='10.0.0.222' BACKUP_PATH='/opt/backup' BACKUP_FL='mysqldump_3306_"LANG=C;date +%y%m%d".sql' scp -Cp root@${BACKUP_SRV}:${BACKUP_PATH}/${BACKUP_FL}.gz ${CHEF_BKUP_PATH}

exit 0

# chmod +x /opt/bin/get\_dbbkup.sh

# crontab -e

# crontab -l

---

get db backup data for chef slave setup.

0 6 * * * /opt/bin/get_dbbkup.sh > /dev/null 2>&1 --------------[/shell]
```

Place the replication stop detection script in files
[shell]# scp -p xxx-db02:/opt/bin/rep_fail_mail.sh /root/chef-repo/site-cookbooks/mysqld/files/default/opt/bin/[/shell]
Write a recipe for replication setup
[shell]# cd /root/chef-repo/site-cookbooks/mysqld/recipes

vi replication.rb

----------------------------
dumpfile = mysqldump_3306_"`LANG=C;date +%y%m%d`".sql
cookbook_file "/tmp/#{dumpfile}.gz" do
  source "opt/backup/#{dumpfile}.gz"
  mode 0644
end

script "setup_replication" do
  interpreter "bash"
  user        "root"
  code <<-EOL
    /tmp/replication_setup.sh 
  EOL
end

cookbook_file "/opt/bin/rep_fail_mail.sh" do
  source "opt/bin/rep_fail_mail.sh"
  mode 0755
end

cron "rep_fail_mail" do
  minute "0-59/15"
  hour "*"
  day "*"
  month "*"
  weekday"*"
  command "/opt/bin/rep_fail_mail.sh"
  action :create
end
----------------------------[/shell]

・About HA construction settings

There are three main types used in the company: Heartbeat linkage, MHA, and VIP manual switching.
So, this time it will be VIP manual switching (the unfortunate type with the longest downtime and the most likely to cause data loss).
I don't particularly feel the need to attach VIP to the DB after the third one, but for when both are broken.
Just in case, I will prepare a recipe that only transfers the VIP grant script. (Not in rolls)

[shell]# scp -Cp xxx-db02:/opt/bin/eni_vip_up.sh /root/chef-repo/site-cookbooks/mysqld/files/default/opt/bin/

# vi vip-attach.rb

cookbook_file "/opt/bin/eni_vip_up.sh" do source "opt/bin/eni_vip_up.sh" mode 0755 end

#script "eni_vip_up" do

interpreter "bash"

user "root"

code <-EOL

/opt/bin/eni_vip_up.sh

EOL

#end -----------------------------[/shell] ※間違ってロールに入れてしまったときのために転送したスクリプトの実行はコメントインしておく

クックブックのシンタックステスト
[shell]# knife cookbook test mysqld[/shell] FATAL:エラーがでなければOKと思われる

今回はここまでです。長々読んでいただいてありがとうございました。
Next time it will be a recipe for munin and zabbix.</-EOL></-EOL>