Check MariaDB replication status inside Ansible


Warning: count(): Parameter must be an array or an object that implements Countable in /home/fbsqlcom/public_html/youdidwhatwithtsql.com/wp-content/plugins/wp-codebox/main.php on line 31

I needed a method to check replication status inside Ansible. The method I came up with uses the shell module

---
- hosts: mariadb
  vars_prompt:
      - name: "mariadb_user"
        prompt: "Enter MariaDB user"
      - name: "mariadb_password"
        prompt: "Enter MariaDB user password"
 
  tasks:
    - name: "Check MariaDB replication state"
      shell: "test 2 -eq $(mysql -u '{{ mariadb_user }}' -p'{{ mariadb_password }}' -e 'SHOW SLAVE STATUS' --auto-vertical-output | grep -E 'Slave_IO_Running|Slave_SQL_Running' | grep Yes | wc -l)"
      register: replication_status
 
    - name: "Print replication status var"
      debug:
        var: replication_status

This is executed like so…

ansible-playbook mariadb_check_repl.yml -i inventories/my_mariadb_hosts

The playbook will prompt for a MariaDB user and password and will output something like below…

PLAY [mariadb] *****************************************************************

TASK [setup] *******************************************************************
ok: [slave2]
ok: [master2]
ok: [slave1]
ok: [master1]

TASK [Check MariaDB replication state] *****************************************
changed: [master1]
changed: [master2]
changed: [slave2]
changed: [slave1]

TASK [Print replication status var] ********************************************
ok: [master1] => {
    "replication_status": {
        "changed": true,
        "cmd": "test 2 -eq $(mysql -u 'mariadb_user' -p'secret' -e 'SHOW SLAVE STATUS' --auto-vertical-output | grep -E 'Slave_IO_Running|Slave_SQL_Running' | grep Yes | wc -l)",
        "delta": "0:00:00.009477",
        "end": "2017-06-02 16:52:51.293609",
        "rc": 0,
        "start": "2017-06-02 16:52:51.284132",
        "stderr": "",
        "stdout": "",
        "stdout_lines": [],
        "warnings": []
    }
}
ok: [slave1] => {
    "replication_status": {
        "changed": true,
        "cmd": "test 2 -eq $(mysql -u 'mariadb_user' -p'secret' -e 'SHOW SLAVE STATUS' --auto-vertical-output | grep -E 'Slave_IO_Running|Slave_SQL_Running' | grep Yes | wc -l)",
        "delta": "0:00:00.017658",
        "end": "2017-06-02 16:52:51.325027",
        "rc": 0,
        "start": "2017-06-02 16:52:51.307369",
        "stderr": "",
        "stdout": "",
        "stdout_lines": [],
        "warnings": []
    }
}
ok: [master2] => {
    "replication_status": {
        "changed": true,
        "cmd": "test 2 -eq $(mysql -u 'mariadb_user' -p'secret' -e 'SHOW SLAVE STATUS' --auto-vertical-output | grep -E 'Slave_IO_Running|Slave_SQL_Running' | grep Yes | wc -l)",
        "delta": "0:00:00.015469",
        "end": "2017-06-02 16:52:51.292966",
        "rc": 0,
        "start": "2017-06-02 16:52:51.277497",
        "stderr": "",
        "stdout": "",
        "stdout_lines": [],
        "warnings": []
    }
}
ok: [slave2] => {
    "replication_status": {
        "changed": true,
        "cmd": "test 2 -eq $(mysql -u 'mariadb_user' -p'secret' -e 'SHOW SLAVE STATUS' --auto-vertical-output | grep -E 'Slave_IO_Running|Slave_SQL_Running' | grep Yes | wc -l)",
        "delta": "0:00:00.014586",
        "end": "2017-06-02 16:52:51.291047",
        "rc": 0,
        "start": "2017-06-02 16:52:51.276461",
        "stderr": "",
        "stdout": "",
        "stdout_lines": [],
        "warnings": []
    }
}

PLAY RECAP *********************************************************************
master1   : ok=3    changed=1    unreachable=0    failed=0
master2   : ok=3    changed=1    unreachable=0    failed=0
slave1   : ok=3    changed=1    unreachable=0    failed=0
slave2   : ok=3    changed=1    unreachable=0    failed=0

N.B. There is the mysql_replication Ansible module that some may prefer to use but it requires the MySQLdb Python package to be present on the remote host.

Leave a Reply