[Ansible] 05. DB 이중화

 

♡ 사용모듈

mysql_secure_installation : root 비밀번호 초기화 및 보안 강화를 위한 설정

mysql_replication : 이중화 작업

 

---

 

1. mysql_secure_installation 모듈 설치

https://github.com/eslam-gomaa/mysql_secure_installation_Ansible 참조하여 py 설치

mkdir ~/.ansible/plugins/modules
wget https://raw.githubusercontent.com/eslam-gomaa/mysql_secure_installation_Ansible/master/library/mysql_secure_installation.py

 

 

2. mysql_secure_installation Setting

# dbsetting.yaml
- name: use mysql_secure_installation
  mysql_secure_installation:
    login_password: ''
    new_password: 'dkagh1.'
    user: root
    login_host: localhost
    hosts: ['localhost', '127.0.0.1', '::1']
    change_root_password: true

 - 플레이북 실행

ansible-playbook main.yaml --start-at-task 'use mysql_secure_installation'
# --start-at-task : 지정된 task에서 앤서블 실행

- 접속 확인

 

3. mysql 공통 Setting

# dbsetting.yaml
- name: use mysql_secure_installation
  mysql_secure_installation:
    login_password: ''
    new_password: 'dkagh1.'
    user: root
    login_host: localhost
    hosts: ['localhost', '127.0.0.1', '::1']
    change_root_password: true

- name: mysql setting First
  lineinfile:
    path: /etc/mysql/mariadb.conf.d/50-server.cnf
    regexp: "^bind-address"
    line: bind-address = 0.0.0.0

 

- 플레이북 실행

ansible-playbook main.yaml --start-at-task 'mysql setting First'

- 접속 확인

db01,02 접속하여 /etc/mysql/mariadb.conf.d/50-server.cnf 경로 확인시

bind-address = 0.0.0.0 으로 정상 변경됨 (기존값은 127.0.0.1)

 

4. Master / Slave Setting

# dbsetting.yaml
- name: use mysql_secure_installation
  mysql_secure_installation:
    login_password: ''
    new_password: 'dkagh1.'
    user: root
    login_host: localhost
    hosts: ['localhost', '127.0.0.1', '::1']
    change_root_password: true

- name: mysql setting First
  lineinfile:
    path: /etc/mysql/mariadb.conf.d/50-server.cnf
    regexp: "^bind-address"
    line: bind-address = 0.0.0.0

- name: mysql setting Master
  blockinfile:
    path: /etc/mysql/mariadb.conf.d/50-server.cnf
    block: |
      server-id = 1
      log_bin = /var/log/mysql/mysql-bin.log
  when: ansible_facts.hostname == "db-m01" # when 조건문으로 hostname 부여

- name: mysql setting Slave
  blockinfile:
    path: /etc/mysql/mariadb.conf.d/50-server.cnf
    block: |
      server-id = 2
      relay_log = mysql-relay-bin
      log_slave_updates = 1
      read_only = 1
      innodb-read-only = 1
  when: ansible_facts.hostname == "db-s01" # when 조건문으로 hostname 부여

- 플레이북 실행

조건문에 부합하는 호스트만 실행된 것을 확인할 수 있다.

 

- 접속 확인

[db-m01]

[db-s01]

변경 완료!

 

5. mysql replication Setting

# db-m01
mysql -u root -p
show master status \G

Master 정보 확인

# dbsetting.yaml
- name: use mysql_secure_installation
  mysql_secure_installation:
    login_password: ''
    new_password: 'dkagh1.'
    user: root
    login_host: localhost
    hosts: ['localhost', '127.0.0.1', '::1']
    change_root_password: true

- name: mysql setting First
  lineinfile:
    path: /etc/mysql/mariadb.conf.d/50-server.cnf
    regexp: "^bind-address"
    line: bind-address = 0.0.0.0

- name: mysql setting Master
  blockinfile:
    path: /etc/mysql/mariadb.conf.d/50-server.cnf
    block: |
      server-id = 1
      log_bin = /var/log/mysql/mysql-bin.log
  when: ansible_facts.hostname == "db-m01"

- name: mysql setting Slave
  blockinfile:
    path: /etc/mysql/mariadb.conf.d/50-server.cnf
    block: |
      server-id = 2
      relay_log = mysql-relay-bin
      log_slave_updates = 1
  when: ansible_facts.hostname == "db-s01"

- name: mysql restart Second
  service:
    name: mariadb
    state: restarted

- name: stop Slave
  mysql_replication:
    mode: stopslave
- name: configure mysql Slave
  mysql_replication:
    master_host: 192.168.56.104 # master db server ip
    master_user: rep # M - S 연결 관리자 계정
    master_password: 'dkagh1.'
    master_log_file: mysql-bin.000006 # Master 정보 넣어준다
    master_log_pos: 475
    mode: changemaster
- name: start Slave
  mysql_replication:
    mode: startslave

 

6. 동기화 확인

[db-m01]

DB 생성하여 테스트해보자

create database testdb;

[db-s01]

정상적으로 동기화 됨!!