Ansible ROLE 사용하여 MySQL 설치

ansible은 코드 재사용성, 클린 코드를 위해 “ROLE” 이라는 표준화 된 구성 기능을 제공합니다.
사용하기 어렵지만 그만큼 간결하게 원하는 작업들을 정의할 수 있는데요
이 ROLE을 사용하여 MySQL을 설치하는 ansible task를 정의해보겠습니다.

ansible role 생성

[root@8429d3d8f01b roles]# ansible-galaxy init mysql
- Role mysql was created successfully

[root@8429d3d8f01b roles]# tree mysql
mysql
|-- README.md
|-- defaults
|   `-- main.yml
|-- files
|-- handlers
|   `-- main.yml
|-- meta
|   `-- main.yml
|-- tasks
|   `-- main.yml
|-- templates
|-- tests
|   |-- inventory
|   `-- test.yml
`-- vars
    `-- main.yml

8 directories, 8 files

defaults – 공통으로 사용할 변수 설정
files – target 으로 배포할 file, 주로 설치파일
handlers – task의 작업이 성공했을 때 호출되는 작업,ex) service start mysql
meta - role에 대한 meta data, role 간 의존성 정의
tasks – 해당 ROLE에 의해 수행 될 task 정의
templates : role 배포 시 수정이 필요한 파일 템플릿, my.cnf 같은것
vars : 변수 설정, defaults 보다 우선순위가 높음

install playbook 생성

$ vi roles/install_db.yaml

---
   - hosts: install
     become: yes
     vars_files:
        - vars/main.yml
     tasks:
        - include: tasks/install.yml
     handlers:
        - include: handlers/main.yml

playbook에서 사용할 변수 설정 (vars)

$ vi roles/mysql/vars/main.yml

---
# vars file for mysql
mysql_collation: utf8mb4_general_ci
mysql_encoding: utf8mb4
mysql_bind_address: 0.0.0.0
mysql_max_connections: 1000
mysql_root_password: qhdks123
mysql_user_db_name: ansible
mysql_user: ansible
mysql_user_password: qhdks123
BufferPoolPct: 0.6

install task 설정

$ vi roles/mysql/tasks/install.yml

---
    - name: install epel-release
      action: "{{ ansible_pkg_mgr }} name=epel-release state=latest"
    - name: install mysql server
      action: "{{ ansible_pkg_mgr }} name=mariadb-server state=present"
    - name: install python mysql support library
      action: "{{ ansible_pkg_mgr }} name=MySQL-python state=latest"
    - name: set innodb_buffer size
      set_fact: 
        buffer_pool_size: "{{ (ansible_memtotal_mb * BufferPoolPct) | int }}"
    - name: copy my.cnf
      template:
        src=my.cnf.j2 
        dest=/etc/my.cnf 
        mode=0600
      notify: 
        - start mysql
      handlers:
        - include: handlers/main.yml  
    - name: update mysql root password
      mysql_user:
         login_user: root
         login_password: "{{ mysql_root_password }}"
         name: root
         host: "{{ item }}"
         password: "{{ mysql_root_password }}"
         check_implicit_admin: yes
         priv: "*.*:ALL,GRANT"
      with_items:
         - "{{ ansible_hostname }}"
         - 127.0.0.1
         - ::1
         - localhost
    - name: remove test db
      mysql_db:
          login_user: root
          login_password: "{{ mysql_root_password }}"
          db: test
          state: absent
    - name: create a new db
      mysql_db:
          login_user: root
          login_password: "{{ mysql_root_password }}"
          name: "{{ mysql_user_db_name }}"
          collation: "{{ mysql_collation }}"
          encoding: "{{ mysql_encoding }}"
          state: present
    - name: delete anonymouse user
      mysql_user:
          login_user: root
          login_password: "{{ mysql_root_password }}"
          name: ""
          host_all: yes
          state: absent
    - name: create a new user
      mysql_user:
          login_user: root
          login_password: "{{ mysql_root_password }}"
          name: "{{ mysql_user }}"
          password: "{{ mysql_user_password }}"
          priv: "{{ mysql_user_db_name }}.*:ALL,GRANT"
          host: "%"
          state: present

=> 설치 서버 마다 메모리가 다르고 서버 메모리의 60%를 innodb_buffer size로 설정하기로 했을때
set_fact 모듈로 buffer_pool_size 변수 설정 후
template 의 my.cnf.j2 에서 해당 변수를 innodb_buffer_size 에 설정하도록함

handler 생성

---
    - name: start mysql
      systemd: 
        name: mariadb
        state: started

=> 위의 tasks 에서 copy my.cnf 완료 후 , notify 를 통해 handler 호출

배포할 file template 설정

vi roles/mysql/templates/my.cnf.j2

[client]
port    = 3306
socket    = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket    = /var/run/mysqld/mysqld.sock
nice    = 0
[mysqld]
user    = mysql
pid-file  = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
port    = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir    = /tmp
lc_messages_dir  = /usr/share/mysql
lc_messages  = en_US
skip-external-locking
bind-address    = {{ mysql_bind_address }}
max_connections    = {{ mysql_max_connections }}
connect_timeout    = 5
wait_timeout    = 600
max_allowed_packet  = 16M
thread_cache_size       = 128
sort_buffer_size  = 4M
bulk_insert_buffer_size  = 16M
tmp_table_size    = 32M
max_heap_table_size  = 32M
# * InnoDB
default_storage_engine  = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size  = 50M
innodb_buffer_pool_size  = {{ buffer_pool_size | int }}M
innodb_log_buffer_size  = 8M
innodb_file_per_table  = 1
innodb_open_files  = 400
innodb_io_capacity  = 400
innodb_flush_method  = O_DIRECT

playbook 수행

[root@8429d3d8f01b mysql]# ansible-playbook mysql_install.yaml

PLAY [install] *************************

TASK [Gathering Facts] *****************
ok: [172.18.0.7]

TASK [install epel-release] ************
ok: [172.18.0.7]

TASK [install mysql server] ************
ok: [172.18.0.7]

TASK [install python mysql support library] *************************
ok: [172.18.0.7]

TASK [set innodb_buffer size] ****************
ok: [172.18.0.7]

TASK [copy my.cnf] ****************************
ok: [172.18.0.7]

TASK [start mysql server] **************
changed: [172.18.0.7]

TASK [update mysql root password] ******
changed: [172.18.0.7] => (item=19ae1de8bfb1)
changed: [172.18.0.7] => (item=127.0.0.1)
changed: [172.18.0.7] => (item=::1)
changed: [172.18.0.7] => (item=localhost)
[WARNING]: Module did not set no_log for update_password

TASK [remove test db] ******************
changed: [172.18.0.7]

TASK [create a new db] *****************
changed: [172.18.0.7]

TASK [delete anonymouse user] **********
changed: [172.18.0.7]

TASK [create a new user] ***************
changed: [172.18.0.7]

PLAY RECAP *****************************
172.18.0.7                 : ok=12   changed=6    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0




[root@19ae1de8bfb1 ~]# mysql -uroot -p -S /var/lib/mysql/mysql.sock
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ansible            |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

MariaDB [(none)]> select user,host from mysql.user;
+---------+--------------+
| user    | host         |
+---------+--------------+
| ansible | %            |
| root    | 127.0.0.1    |
| root    | 19ae1de8bfb1 |
| root    | ::1          |
| root    | localhost    |
+---------+--------------+
5 rows in set (0.00 sec)

ansible role 생성#

install playbook 생성#

playbook에서 사용할 변수 설정 (vars)#

install task 설정#

handler 생성#

배포할 file template 설정#

playbook 수행#