참고 게시글은
https://sysdocu.tistory.com/1858 

https://docs.ansible.com/ansible/2.9/modules/list_of_all_modules.html  두 링크를 참조 하였습니다. 

# vi mysql.yaml

# ansible-playbook -i servers.ini mysql.yaml

DB 접속해서 확인 

디비 시작 , 스톱 파일 

my.cnf 파일 배포 

저같은 경우에는 미리 my.cnf 파일을 host-node에 저장해두었습니다. 

—
– name: Copy files to remote servers
  hosts: db1:db2:db3
  tasks:
    – name: Copy my.cnf to db1
      copy:
        src: /root/master/my.cnf
        dest: /etc/mysql/mysql.conf.d/mysqld.cnf
      delegate_to: “{{ item }}”
      with_items: “{{ groups[‘db1’] }}”

    – name: Copy my.cnf to db2
      copy:
        src: /root/slave2/my.cnf
        dest: /etc/mysql/mysql.conf.d/mysqld.cnf
      delegate_to: “{{ item }}”
      with_items: “{{ groups[‘db2’] }}”

    – name: Copy my.cnf to db3
      copy:
        src: /root/slave3/my.cnf
        dest: /etc/mysql/mysql.conf.d/mysqld.cnf
      delegate_to: “{{ item }}”
      with_items: “{{ groups[‘db3’] }}”

—
– name: Start Apache Web Server
  hosts: dball
  become: yes
  tasks:
    – name: Ensure Apache service is running
      service:
        name: mysql
        #state: stopped  #  << 정지 
        state: started   #    << 시작 
#  ansible-playbook -i servers.ini mysql.yaml

mysql> SHOW VARIABLES LIKE ‘server_id’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id     | 2     |
+—————+——-+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘server_id’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id     | 3     |
+—————+——-+
1 row in set (0.00 sec)

# vi status.yaml
—
– name: Check MySQL Replication Status
  hosts: db1
  tasks:
    – name: Get master binlog file name and binlog position
      community.mysql.mysql_replication:
        mode: getmaster
      become: yes
      register: replication_status

    – name: Display Replication Status
      debug:
        var: replication_status

# ansible-playbook -i servers.ini status.yaml 
PLAY [Check MySQL Replication Status] ********************************************************************************

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

TASK [Get master binlog file name and binlog position] ***************************************************************
ok: [10.101.0.3]

TASK [Display Replication Status] ************************************************************************************
ok: [10.101.0.3] => {
    “replication_status”: {
        “Binlog_Do_DB”: “”,
        “Binlog_Ignore_DB”: “”,
        “Executed_Gtid_Set”: “”,
        “File”: “binlog.000005“,
        “Is_Master”: true,
        “Position”: 157,
        “changed”: false,
        “failed”: false,
        “queries”: []
    }
}

PLAY RECAP ***********************************************************************************************************
10.101.0.3                 : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

– name: Configure MySQL Replication on Slave Servers
  hosts: db2, db3
  become: yes
  tasks:
    – name: Start MySQL replication on slave servers
      mysql_replication:
        mode: changemaster
        master_host: “10.101.0.3“
        master_user: “replication“
        master_password: “87654321“
        master_log_file: “binlog.000005″
        master_log_pos: 157
      notify: Restart MySQL Service

  handlers:
  – name: Restart MySQL Service
    service:
      name: mysql
      state: restarted

# ansible-playbook -i servers.ini mysql_replication_slave.yaml

1.

# vi check_replication.yaml

—
– name: Check MySQL Replication Status
  hosts: db2, db3  # 마스터와 슬레이브 서버의 그룹을 여기에 지정합니다.
  become: yes
  tasks:
    – name: Get replication status from MySQL
      shell: “mysql -u root -p12345678 -e ‘SHOW SLAVE STATUS\\G’ | grep ‘Running:'”
      register: replication_status
      changed_when: false
      ignore_errors: yes
      tags: replication_status

    – name: Display Replication Status
      debug:
        var: replication_status.stdout_lines
      when: replication_status.rc == 0
      tags: replication_status

# ansible-playbook -i servers.ini check_replication.yaml 

PLAY [Check MySQL Replication Status] ********************************************************************************

TASK [Gathering Facts] ***********************************************************************************************
ok: [10.101.0.23]
ok: [10.101.0.17]

TASK [Get replication status from MySQL] *****************************************************************************
ok: [10.101.0.17]
ok: [10.101.0.23]

TASK [Display Replication Status] ************************************************************************************
ok: [10.101.0.23] => {
    “replication_status.stdout_lines”: [
        ”             Slave_IO_Running: Yes”,
        ”            Slave_SQL_Running: Yes”
    ]
}
ok: [10.101.0.17] => {
    “replication_status.stdout_lines”: [
        ”             Slave_IO_Running: Yes”,
        ”            Slave_SQL_Running: Yes”
    ]
}

PLAY RECAP ***********************************************************************************************************
10.101.0.17                : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   
10.101.0.23                : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0 

    – name: Display Record Count
      debug:
        var: record_count.stdout_lines
      when: record_count.rc == 0
      tags: record_count

PLAY [Check Record Count in MySQL Table] *****************************************************************************

TASK [Gathering Facts] ***********************************************************************************************
ok: [10.101.0.17]
ok: [10.101.0.3]
ok: [10.101.0.23]

TASK [Get record count from MySQL table] *****************************************************************************
ok: [10.101.0.17]
ok: [10.101.0.23]
ok: [10.101.0.3]

TASK [Display Record Count] ******************************************************************************************
ok: [10.101.0.3] => {
    “record_count.stdout_lines”: [
        “COUNT(*)”,
        “9”
    ]
}
ok: [10.101.0.23] => {
    “record_count.stdout_lines”: [
        “COUNT(*)”,
        “9”
    ]
}
ok: [10.101.0.17] => {
    “record_count.stdout_lines”: [
        “COUNT(*)”,
        “9”
    ]
}

PLAY RECAP ***********************************************************************************************************
10.101.0.17                : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   
10.101.0.23                : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   
10.101.0.3                 : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0 

# apt install python3-pip
# pip3 install flask

#vi app.py
app = Flask(__name__)

@app.route(‘/’)
def index():
    replication_status = read_file(‘/var/www/html/replication_status.txt’)
    record_count = read_file(‘/var/www/html/record_count.txt’)
    return f'<h1>Replication Status</h1><pre>{replication_status}</pre><h1>Record Count</h1><pre>{record_count}</pre>’

def read_file(file_path):
    try:
        with open(file_path, ‘r’) as file:
            content = file.read()
        return content
    except Exception as e:
        return str(e)

if __name__ == ‘__main__’:
    app.run(host=’0.0.0.0′, port=5000)

정상작동 ip:50000 으로 확인가능