Total Pageviews


November 26, 2017

Database Server provisioning using Ansible

by 4hathacker  |  in Python at  2:27 PM

Hello everyone in MyAnsibleQuest!

I went through webserver provisioning in the previous post. In this post, I am going to provision my database server in my dbservers machine [] as defined in my /etc/ansible/hosts file.

I have used some modules like 'yum', 'apt', 'block', etc. during webserver installation. In addition to them, I will be using some modules to install mysql server in dbservers and then securing the server with the help of deleting default test databases, blank password accounts, etc. using ansible playbook. 

[root@server Desktop]# vim hands_on_ansible/mysql.yml

- hosts: dbservers

     - name: To install mysql
       action: yum name=
       action: yum name={{ item }}
           - MySQL-python
           - mysql
           - mysql-server

     - name: Start the MySQL service
       action: service name=mysqld state=started

     - name: Changing root password for all root accounts
       mysql_user: name=root host={{ item }} password={{ mysql_root_password }}
           - $ansible_hostname
           - ::1
           - localhost

     - name: copy config file of mysql (.my.cnf) with root credentials
       template: src=templates/my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600

     - name: delete anonymous MySQL server user for $server_hostname
       action: mysql_user user="" host=$server_hostname  state="absent"

     - name: delete anonymous MySQL server user for localhost
       action: mysql_user user="" state="absent"

In the above playbook, I have 6 tasks to accomplish on host dbservers.

1. Here I have used old legacy format, action: module options. According to ansible docs, it is not recommended but still prevailing in ansible playbook. I found it more readable but it depends on individual choice. My first task is to install mysql for which I need rpm packages of mysql. So it is same as:

rpm -ivh

Post that, it will install three packages for mysql(client), mysql-server, MySQL-Python using the same 'yum' module. 'with_items' is used for repeated tasks over a list defined. My list contains three packages and 'yum' module is installing them one by one.

2. In the second task, it will start the mysql service using 'service' module. 

3. In the upcoming four tasks, it will do some security stuff to enhance the level of security for mysql. We know that, MySQL server installs with default login_user of ‘root’ and no password. To secure this user as part of an idempotent playbook, we must create at least two tasks: the first must change the root user’s password, without providing any login_user/login_password details. The second must drop a ~/.my.cnf file containing the new root credentials. Subsequent runs of the playbook will then succeed by reading the new credentials from the file. So, I have created a variable mysql_root_password in the /etc/ansible/hosts file. This will be set for $ansible_hostname,, ::1 and localhost using 'mysql_user' module. This confirms that  whosoever want to interact with mysql must enter the same password as defined by mysql_root_password.

[root@server Desktop]# vim /etc/ansible/hosts

node218 ansible_ssh_host=
node227 ansible_ssh_host=
node228 ansible_ssh_host=
node229 ansible_ssh_host=






4. I have created a jinja template my.cnf.j2 to set client credentials and copy that config file to nodes.

[root@server Desktop]# vim hands_on_ansible/templates/my.cnf.j2

password={{ mysql_root_password }}
5. In the final two tasks, I have removed the anonymous user accounts for mysql.

Finally, we can just check whether mysql is configured properly or not.


Like Our Facebook Page

Nitin Sharma's DEV Profile
Proudly Designed by 4hathacker.