Install MySQL Database Server through Ansible Playbook

Automating the installation of the database Servers is quite easy to set up and configure through an ansible playbook. I have installed & configured the MySQL server through roles and an ansible playbook. MySQL is an open-source relational database and is very popular in setting web hosting Linux, Apache / Nginx, MariaDB / MySQL, and PHP ( LEMP / LAMP ).

The ansible-playbook keeps the application properly deployed and managed the entire life cycle. One of the important features is configuration management, which manages files and deployment.

Here we are going to set up a MySQL server configuration management with test content. Here I will start the simple MySQL Server deployment.

Ansible Lab Environment Set Up :

Basically ,to do this you needed to setup ansible environment like control node and managed host. Where control node connected through manged host through key based authentication to go smoothly with password less authentication and user setup , which is having root privilege and no password authentication through suborders configuration. Please find below screenshot of ansible ad-hoc command to verify setup . I use one control node and one managed host machine for setup of mysql server . So below Commands showing managed host is ping and accordingly connecting through user and showing root privilege . ansible ad-hoc command verify it. The playbook will execute no authentication.

Ansible Ad-hoc Command :

srv1.techtransit.org | SUCCESS => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/libexec/platform-python"
    },
    "changed": false,
    "ping": "pong"
}

ansible all -m command -a ‘id’

Output :

srv1.techtransit.org | CHANGED | rc=0 >>
uid=0(root) gid=0(root) groups=0(root) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

Ansible Roles

Here we are going to use ansible roles. Roles make it simple to reuse the ansible code. We can manage
code through a standardized directory structure like variables, files, and templates in a folder inside the top-level folder. Files are arranged into sub directories that are named according to the purpose of the role.

Ansible-galaxy is a public role library of ansible code written by community users and administrators. Galaxy contains lots of ansible roles, you can search for any role on ansible galaxy official website https://galaxy.ansible.com. We will import the MySQL role into our main playbook and pass the MySQL variable in our playbook. Galaxy role can be searched through the ansible-galaxy command.

 ansible-galaxy search 'mysql

Galaxy search gives lots of output related to MySQL server roles, I have chosen geerlingguy. To set up MySQL server which is for RHEL / CentOS and Debian/Ubuntu. Here I am using the AlmaLinux Operating system in the managed host. Below is the command to download that role.

 ansible-galaxy install geerlingguy.mysql  -p roles 

geerllingguy.mysql has been downloaded in roles directory then we will create our main YAML file .which will import this role and pass the environment variable like MySQL root password .Any database, and user and password.

After that we create the vars folder inside the project directory and pass it like root password , the database you want to create, and user and give privilege through the variable.

vars/main.yml

mysql_root_password: "Zoloboro098pw"
mysql_databases:
  - name: techdbserver
mysql_users:
  - name: techdbuser
    host: "%"
    password: r00tuserpw655
    priv: "techdbserver.*:ALL"

After that, I created my main playbook, which will import roles and vars and run into the managed host. 

---
- name: Install db server 
  hosts: all
  vars_files:
    - vars/main.yml
  roles:
    - role: geerlingguy.mysql
      become: yes
...

Accordingly the complete picture of whole screnario is look like below screenshot. 

Now we can run our playbook to our managed host . So checked and run this playbook through the below command.

ansible-playbook Playbook_name.yml 

Below screenshot showing changes made in the managed host.

Accordingly the playbook create user password from variable . Log in to managed host machine like below screenshot and check the MySQL root password is working.

Below in a screenshot some commands to verify the database and user . As a result we configure mysql database server through ansible playbook.