Ansible Vault Implementation — MySQL offline Installation

This is an article on a common use case, we face during Ansible automation, where in our group_vars and host_vars we used to define the common passwords and also in the hosts file we will have our ssh passwords which needs to be encrypted, as it have the sensitive data. Ansible Vault provides us with a solution for this kind of use case.

In this article I'm going to provide a step by step guide on how we can use the Ansible Vault to achieve our requirement as mentioned above and also for this I’m taking the MySQL offline Installation. Here my main intention is to:

• Encrypt specific variables in group_vars files
• Encrypt specific variables in host_vars files
• Encrypt hosts file — This contains all the ssh credentials
• How to Edit the Encrypted variables or Files
• Along with the above will install the MySQL offline

To start on this first i'll create the recommended best practice directory structure for the Ansible Scripting, for that execute the below script and for more information you can refer:
[1] https://medium.com/swlh/automating-the-server-management-with-ansible-8fa464379fa9

Also note that I’m using the below Ansible distribution.

Use case Flow Diagram

Getting into the Scripting

Once followed the script i have provided the directory will be created as follows:

Now we will move to update the scripts relates to MySQL Database Installation along with Ansible Vault Implementation.

1. Download and copy the below RPMs to the library/mysql folder.

2. As I’m going to install mysql, created a role named “mysql-centos” and created the needed files for that as below.

named as “mysql-centos” as I’m going to execute the installation to the Centos Linux Distribution and also note that this will work in Red Hat Linux Distribution also.

tasks/main.yml — Have the tasks to be executed during the installation.

templates/my.cnf — Have the mysql configuration and this need to be override the file at /etc/my.cnf

After adding all the installation related logic to the script, we will be able to install the MySQL into our remote server. Below are mainly edited files and content.

Content of the main.yml

Content of the my.cnf

Content of the selinux.cnf

3. Now it’s time to do the encryption using the vault.

As mentioned earlier there are two kind of encryption needed in our use case.

• Encrypt the whole file “hosts”
• Encrypt the variable here it will be the “mysql_root_password” inside the file named: host_vars/mysqldb_1.yml

Encrypt the whole file “hosts”

So my intention here is to encrypt the file inventories/production/hosts which currently have the below data:

Content of the inventories/production/hosts

As it already have the data, we can’t encrypt it directly first take a copy of the data and remove the hosts file, then follow the below commands to encrypt the file.

Now to edit the file execute the below command.

Encrypt the variable

To encrypt the specific variable here it is “mysql_root_password” in host_vars/mysqldb_1.yml, execute the below command and get the output and update the specific variable.

So now we are done with both kind of encryption and now it’s time to run our script to install MySQL and check check whether all working fine.

Execute the script as below and after a successful execution you will see a log as below:

Also can be verified by logging into the installed MySQL server.

That’s it.

Note: The code can be found at [2].

[2] https://github.com/ajanthanblog/ansible-vault.git