カテゴリー: MySQL

こんにちは。
MySQL の導入が面倒だったので Ansible と expect で自動化にチャレンジします。

mysql_secure_installation の自動化

expect と awk でシェルスクリプトを作成します。

#!/bin/bash


# 初期パスワードを取得
IntPasswd=$(grep "A temporary password is generated for root@localhost:" /var/log/mysqld.log | awk '{ print $13}')

# パスワード指定
MysqlRootPasswd="{{ db_passwd }}"

expect -c '
    set timeout 10;
    spawn mysql_secure_installation;
    expect "Enter password for user root:";
    send -- "'"${IntPasswd}"'\n";
    expect "New password:";
    send -- "'"${MysqlRootPasswd}"'\n";
    expect "Re-enter new password:";
    send -- "'"${MysqlRootPasswd}"'\n";
    expect "Change the password for root ?";
    send "n\n";
    expect "Remove anonymous users?";
    send "y\n";
    expect "Disallow root login remotely?";
    send "y\n";
    expect "Remove test database and access to it?";
    send "y\n";
    expect "Reload privilege tables now?";
    send "y\n";
    interact;'

awk で初期パスワードを取得し、対話処理は expect で処理します。

Playbook

Ansible の playbook で処理します。

---
# tasks file for mysql-server80
- name: install mysql80 repository
  yum:
    name: https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
    state: present

- name: install mysql
  yum:
    name:
      - mysql-server
      - expect
    state: present

- name: put my.cnf
  template:
    src: ../templates/my.cnf.j2
    dest: /etc/my.cnf
    owner: root
    group: root
    mode: 0644
    backup: yes

- name: mkdir /var/log/mysql/
  file:
    path: /var/log/mysql
    state: directory
    owner: mysql
    group: mysql
    mode: 0755

- name: start mysql
  systemd:
    name: mysqld
    state: started
    enabled: yes

- name: confirm check_file
  stat: path=/usr/local/etc/mysql_stat.txt
  register: result_mysql_exit

- name: put mysql_secure_installation_script
  template:
    src: ../templates/mysql_secure_installation_script
    dest: /tmp/mysql_secure_installation_script
    owner: root
    group: root
    mode: 0755

- name: exec mysql_secure_installation_script
  shell: "/tmp/mysql_secure_installation_script"
  when: not result_mysql_exit.stat.exists

- name: create check_file
  file:
    path: /usr/local/etc/mysql_stat.txt
    state: touch
    mode: "u=rw,g=r,o=r"

- name: change error-log location
  lineinfile:
    dest: /etc/my.cnf
    state: present
    backrefs: yes
    regexp: '^log-error = /var/log/mysqld.log'
    line: 'log-error = /var/log/mysql/mysqld.log'
  notify: mysqld_restart

おまけ

こちらに CentOS7用の playbook を纏めていますので宜しければ。
https://github.com/keisukesanuki/default-CentOS7