Database Server provisioning using Ansible

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 [10.0.0.228] 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

  tasks:
     - name: To install mysql
       action: yum name=http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
       action: yum name={{ item }}
       with_items:
           - 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 }}
       with_items:
           - $ansible_hostname
           - 127.0.0.1
           - ::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:

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

rpm -ivh  http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

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, 127.0.0.1, ::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=10.0.0.218
node227 ansible_ssh_host=10.0.0.227
node228 ansible_ssh_host=10.0.0.228
node229 ansible_ssh_host=10.0.0.229

[webservers]
node218
node227

[dbservers]
node228

[lbservers]
node229

[datacenter:children]
webservers
dbservers
lbservers

[datacenter:vars]
ansible_ssh_user=root
ansible_ssh_pass=redhat123
mysql_root_password=redhat123

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

[client]
user=root
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.