Execute SQL Queries in different droplets/servers using Ansible.

Ansible Works

Ansible is a tool used to automate and streamline various tasks. Imagine you have a set of instructions or tasks that you need to perform on multiple computers or servers. Instead of manually carrying out each step on each machine, Ansible allows you to automate these tasks. It helps simplify complex IT processes, making it easier to manage and maintain a large number of computers or servers in a consistent and efficient way.

In Ansible there are two main components

1. Playbook
2. Inventory file the ini file.

So Playbook, as the name suggests contains list of tasks that needs to be performed in multiple servers/droplets, and the servers/droplets are defined in the inventory .ini file.

Pre requisites :

a. Ansible to be installed on server/droplet/machine where you run the playbook.

b. Python3 to be installed , libmysqlclient-dev, mysqlclient,mysql-connector-python, pymysql dependencies and package to be installed using pip3.

c. Pem Files for the droplets/servers inorder for ansible to connect.

d. Basics of Yaml

We are going to execute set of SQL queries (transactions) on multiple droplets.

First we will write a simple playbook and inventory file that just pings the multiple droplets.

---
- name: Test Connection to Multiple Droplets
  hosts: droplets
  
  tasks:
    - name: Ping each droplet
      ping:
      register: ping_result

    - name: Display ping results
      debug:
        var: ping_result

Lets understand the above playbook (test_connection.yml), the playbook can contain multiple playlist with each playlist having its own set of tasks.

In this example it has one playlist with the name Test Connection to Multiple Droplets and two tasks with names Ping each droplet and Display ping results.

Every playbook should have at least one playlist with a task.

The hosts defines where this playbook should run , in our case multiple droplets/server. The droplets are defined in the inventory file.

In the task section, there are two task one pings the droplets using the ping service and the other task displays the result (debug key) of ping service which is stored in variable ping_result using register key.

Now the inventory.ini file

[droplets]

beta ansible_ssh_user=root ansible_ssh_host=xx.xxx.xxx.xx ansible_ssh_private_key_file=[path to .pem file where the playbook is run]
demo ansible_ssh_user=root ansible_ssh_host=xx.xxx.xxx.xx ansible_ssh_private_key_file=[path to .pem file where the playbook is run]

Lets understand the inventory.ini file, so playbook needs to run on droplets/servers and their IP and access needs to be defined.

1. [droplets]: This is a group name in square brackets. It's like putting similar things together. In this case, it might represent a group of servers or "droplets" that you want to manage collectively.
2. beta ansible_ssh_user=root ansible_ssh_host=xx.xxx.xxx.xx ansible_ssh_private_key_file=[path to .pem file]: This line describes a specific server (or droplet) named "beta."

Here's what each part means:

beta: The name or identifier of the server.

ansible_ssh_user=root: The username Ansible should use when connecting to this server. In this case, it’s “root.”

ansible_ssh_host=xx.xxx.xxx.xx: The IP address or hostname of the server.

ansible_ssh_private_key_file=[path to .pem file]: The path to the private key file (.pem file) that Ansible should use to authenticate and connect to the server.

You can see the group name droplets is used in the playbook as hosts.

When you run the below command , you can see the ouptupt as shown in below image

ansible-playbook -i inventory.ini test_connection.yml

For this to work make sure your pem keys are in same directory with chmod 600 permission.

So we have successfully run our first playbook and connected to multiple droplets.

Now we will run multiple queries as single transaction on different droplets and handling the failures.

Lets look into our playbook for such task.

---
- name: Run SQL queries on multiple droplets
  hosts: droplets
  become: yes

  vars:
    droplets:
      beta:
        db_user: admin
        db_password: password
        db_host: localhost
        db_name: test
      demo:
        db_user: admin
        db_password: password
        db_host: localhost
        db_name: test

  tasks:
    - name: Debug database name
      debug:
        var: droplets[inventory_hostname].db_name

    - name: Run Query 1 with Commit and Rollback
      community.mysql.mysql_query:
        login_user: "{{ droplets[inventory_hostname].db_user }}"
        login_password: "{{ droplets[inventory_hostname].db_password }}"
        login_host: "{{ droplets[inventory_hostname].db_host }}"
        login_db: "{{ droplets[inventory_hostname].db_name }}"
        query:
          - START TRANSACTION;
          - INSERT INTO dummy2 (`name`) VALUES ('test7');
          - INSERT INTO dummy2 (`name`) VALUES ('test8');
          - COMMIT;
        single_transaction: true
      register: result_query_1

    - name: Check for Query 1 Failure
      fail:
        msg: "Query 1 failed with error: {{ result_query_1.stderr }}"
      when: result_query_1.failed or result_query_1.stderr | regex_search("ERROR")

We have defined variables that holds the database credentials. Used the variables dynamically depending on which host/droplet is run

{{ droplets[inventory_hostname].db_user }} inventory_hostname is ansible resevered variable which returns the host name i.e if under droplets beta is running then beta is returned , so droplets[beta].db_user would return that is defined under vars.

We are using the community.mysql.mysql_query module and the db credentials are passed through vars .

We have 3 tasks, one prints the database name, second runs our multiple queries in single transaction and third in case of query failure we output the error in sql statements.

When we run the command ansible-playbook -i inventory.ini sql_queries.yml

If everything goes well, then

If some query fails the failure output message would be like

That is it, we have successfully run queries on multiple droplets.