Introduction :

Ansible is an open-source IT automation engine which can remove drudgery from your work life, and will also dramatically improve the scalability, consistency, and reliability of your IT environment.

Nowadays without automation to manage the Databases is very tricky. We are using Ansible as an infra automation tool to install, configure and manage DB infra at Mydbops.

For example, you have 10 Linux server’s which needs MySQL latest version 8.0 to be installed. Anyone can install MySQL using yum or apt-get. But the manual installation is a time-consuming process.

In this blog, I am going to describe the installation of MySQL 8.0 using Ansible.

Ansible Architecture :

Host Inventory :

The file can be in one of many formats depending on your Ansible environment and plugins.

The default location for the inventory file is “/etc/ansible/hosts”. If necessary, you can also create project-specific inventory files in alternate locations.

Example :

 
[all:vars]
ansible_user=vagrant
ansible_become=yes
[mysql]
192.168.33.14 ansible_host=192.168.33.14
192.168.33.15 ansible_host=192.168.33.15
	

Playbook :

An Ansible playbook is an organized unit of scripts that define work for a server configuration managed by the automation tool Ansible.

Writing playbooks we are using YAML language, it is a strict typed so, extra care needs to be taken while writing the YAML files.

Example :

 
- hosts: mysql
  tasks:
   - name: Pre check before installing Oracle MySQL Server8.0.
     shell: ps aux | grep mysql | grep -v grep
     ignore_errors: yes
     register: mysql_proc_status
	

Modules :

Ansible Core Modules :

Our Senior DevOps engineer, Manosh has explained in detail about ansible core modules in the previous post.

Ansible Custom Modules :

We will be explaining in detail about Ansible Custom Modules in our upcoming post.

MySQL 8.0 installation using Ansible :

Requirements :

We require to have internet connectivity to download the required Repo and DB Packages. Our automation is battle tested for Key operating systems like RHEL, CentOS, Ubuntu and Amazon Linux.

We have automated this installation process the Oracle MySQL, Percona Servers and MariaDB .

Playbook :

A playbook consists of the simple set of steps called tasks that run on remote machines defined in inventory file.

Hosts file :

 
[mysql]
192.168.33.14 ansible_host=192.168.33.14
	

Playbook  Example :

 
- hosts: mysql
  tasks:
    - name: Starting Mysql Installation Tasks.
      include_role:
         name: mysql
      vars:
         mysql_version: 8.0
         mysql_version_repo: mysql{{ mysql_version|string |replace(".", "") }}-community
     when: mysql_proc_status.failed == true or mysql_force_installation == true
	

Terminologies Explained:

host – Its the place we can specify host group or individual host that going to change the state of the machine.

tasks – In the play, after task declaration only we can specify all our task’s.

include_role – Include role, which works dynamically.

name – The role needs to executed.

vars – Under var section, we can specify all required variable by task and roles.

Config Templating :

conf.j2 :

The Jinja2 is a templating language. Ansible uses Jinja2 templating to enable dynamic expressions and access to variables. Using Templating, we will create custom my.cnf MySQL configuration file according to system Configuration without any Human intervention.

Example :

 
# {{ ansible_managed }}
[client]
port = {{ mysql_port }}
socket = {{ mysql_socket }}

[mysqld]
port = {{ mysql_port }}
bind-address = {{ mysql_bind_address }}
datadir = {{ mysql_data_dir }}
socket = {{ mysql_socket }}
pid-file = {{ mysql_pid_file }}
{% if mysql_sql_mode %}
sql_mode = {{ mysql_sql_mode }}
{% endif %}

# InnoDB settings.
innodb_checksum_algorithm = {{ mysql_innodb_checksum_algorithm }}
innodb_file_per_table = {{ mysql_innodb_file_per_table }}
innodb_autoinc_lock_mode = {{ mysql_innodb_autoinc_lock_mode }}
innodb_buffer_pool_size = {{ mysql_innodb_buffer_pool_size }}

	

Roles :

Roles are a set of tasks grouped together If we consider MySQL installation as a role. We came up with task subsets and organized as separate YAML files for better manageability.

dbserver-repo.yml :

 
- name: Install Oracle MySQL repo for RedHat Family.
  yum:
    name: "{{ redhat_mysql_repo }}"
    state: present
  when: ansible_os_family == "RedHat" and 'mysql' in group_names
	

So here I am installing Oracle MySQL 8.0, the above role is to check the OS family and check the community MySQL repo is installed or not.

If the repo is not installed the yum module will install the repo package.

 
TASK [mysql : Installing Oracle MySQL Server for RedHat Family.] ******************
changed: [mysql_test] => (item=[u'mysql', u'mysql-server', u'mysql-common'])
	

Setup-mysqlserver.yml :

 
- name: Installing Oracle MySQL Server for RedHat Family.
  yum:
    name: "{{ item }}"
    state: present
    enablerepo: "base,{{ mysql_version_repo }}"
    disablerepo: "*"
  with_items: "{{ mysql_packages }}"
  register: rh_mysql_install_packages
  when: ansible_os_family == "RedHat" and 'mysql' in group_names and ansible_distribution != "Amazon"
	

The above task is checking the OS version and enable MySQL-8 in repo file during runtime.

Once the MySQL-8 is enabled, the yum module will start the MySQL installation.

 
TASK [mysql : Installing Oracle MySQL Server for RedHat Family.] ******************************
changed: [mysql_test] => (item=[u'mysql', u'mysql-server', u'mysql-common'])
	

Secure-installation.yml :

 
- name: Initialize MySQL 8.0 Insecure.
  shell: >
       mysqld --initialize-insecure
  when: ('8.0.' in mysql_cli_version.stdout) and (ansible_os_family == "RedHat")
	

• After complete the installation, the above task will be executed.
• In this task checks the MySQL client version and OS family.
• Once the pre-checks are complete, the shell module will initialize the MySQL.

 
TASK [mysql : Initialize MySQL 8.0 Insecure.] ******************************************************************************************************
changed: [mysql_test]

TASK [mysql : Get MySQL version.] ******************************************************************************************************************
ok: [mysql_test]

TASK [mysql : Ensure MySQL is started and enabled on boot.] ****************************************************************************************
changed: [mysql_test]
	

Hardening :

For security purpose, the below task will remove the anonymous user and ensure default user is present in MySQL.

 
- name: Disable root remote login and remove anonymous User.
  command: 'mysql -NBe "{{ item }}"'
  with_items:
    - DELETE FROM mysql.user WHERE User!='{{ mysql_root_username }}' AND Host NOT IN ('localhost', '127.0.0.1', '::1')
changed_when: false

TASK [mysql : Disable root remote login and remove anonymous User.] ********************************************************************************
ok: [mysql_test] => (item=DELETE FROM mysql.user WHERE User!='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1'))
TASK [mysql : Ensure default user is present.] *****************************************************************************************************
changed: [mysql_test]
	

Conclusion :

Automation through Ansible helped our team to focus on real challenges in the database. This has made the installation process more efficient and less prone to human errors.

I believe this blog may help everyone. If anyone needs more details about any specific ansible feature please comment below.