Citrix NetScaler: A Powerful Defense against Denial of Service Attacks

This WHITE PAPER looks into how the Citrix NetScaler Application Delivery Controller (ADC) provides a robust yet highly affordable foundation for an organization's DoS defenses.

GET YOUR FREE COPY TODAY!!

Tuesday, 24 February 2015

Automating IT infrastructure with Ansible- PART 4

Automating IT infrastructure with Ansible- PART 4 
- MySQL Playbook

In our previous Ansible tutorials, we have looked at how to create simple and re-usable playbooks from scratch. In this tutorial, I'll be demonstrating how to install and configure MySQL Database on either a RedHat or a Debian based system by using just a single playbook.

NOTE: This is a continuation post so if you haven't gone through the previous tutorials, here's your chance to catch up on them:

>> Automating IT infrastructure with Ansible- PART 1 - Installing and configuring Ansible
>> Automating IT infrastructure with Ansible- PART 4 - MySQL Playbook (YOU ARE HERE)


Create the Playbook
First of, lets create the required folders for our playbook. 
The folder structure of this playbook looks something like this:

bootstrap/
   |___ hosts                ~~> Contains the inventory info
   |___ main.yml          ~~> master main.yml file
   |___ ntp/                  ~~> ntp role <Added in PART 3>
   |___ mysql/              ~~> mysql role
             |__ defaults
             |__ files/ 
             |__ handlers/
             |__ meta/
             |__ README.md
             |__ tasks/
             |__ templates/
             |__ vars/

In my earlier tutorial we accomplished this manually by creating each individual folder such as tasks, templates, vars etc. In this case, let's use Ansible's command to do the work for us:

# ansible-galaxy init <playbook_name>


This command initializes and creates all the required sub-folders and their respective main.yml files as shown.




Next, create the vars files that will hold our MySQL-related variables. AS done in PART 3, I have created two variable files namely RedHat.yml and Debian.yml with the following contents:

# RedHat distribution based Variables

mysql_service: mysqld
mysql_conf_path: /etc/my.cnf
mysql_datadir: /var/lib/mysql
mysql_socket: /var/lib/mysql/mysql.sock
mysql_log: /var/log/mysqld.log
mysql_pid: /var/run/mysqld/mysqld.pid



Similarly, for the Debian-based variables:

# Debian based distribution Variables

mysql_service: mysql
mysql_conf_path: /etc/mysql/my.cnf
mysql_datadir: /var/lib/mysql
mysql_socket: /var/lib/mysqld/mysqld.sock
mysql_log: /var/log/mysql/error.log
mysql_pid: /var/run/mysqld/mysqld.pid




Once both the variable files are created, populate the defaults/main.yml with few default values as well.

---
# defaults file for mysql

mysql_port: 3306
dbuser: cloud
dbname: testdb

dbpassword: test

In this case, we are setting the MySQL port value along with a sample database name, its username and password.



Next off, create the templates/my.cnf.j2 file. The reason we are creating this as a template is because the values in my.cnf file varry from RedHat to Debian distributions.  

[mysqld]
datadir={{ mysql_datadir }}
socket={{ mysql_socket }}

user=mysql
symbolic-links=0
port={{ mysql_port }}

[mysqld_safe]
log-error={{ mysql_log }}
pid-file={{ mysql_pid }}






Moving on, create the main tasks file under tasks/main.yml as shown:

---
# tasks file for mysql

- name: Include OS-specific variables
  include_vars: "{{ ansible_os_family }}.yml"

- name: Install MySQL (RedHat)
  yum: name={{ item }} state=installed
  with_items:
   - mysql-server
   - MySQL-python
  when: ansible_os_family == 'RedHat'

- name: Install MySQL (Debian)
  apt: name={{ item }} state=installed
  with_items:
   - mysql-server
   - python-mysqldb
  when: ansible_os_family == 'Debian'

- name: Create Mysql configuration file
  template: src=my.cnf.j2 dest={{ mysql_conf_path }}

- name: Start Mysql Service
  service: name={{ mysql_service }} state=started enabled=yes

- name: Create dummy Database
  mysql_db: name={{ dbname }} state=present

- name: Create dummy Database User
  mysql_user: name="{{ dbuser }}" password="{{ dbpassword }}" priv=*.*:ALL host='%' state=present



Lets have a close look at what we have configured:
1) include_vars: "{{ ansible_os_family }}.yml"
The include_vars will search for a .yml file in the vars directory. In this case, the include_vars will call two files: RedHat.yml and Debian.yml using the {{ ansible_os_family }} variable/ fact.


2/ 3) when: ansible_os_family == 'RedHat'
"when" statement is a condition control statement in Ansible and is used to execute a particular task only when a particular condition is met. If the condition is not met, this task is simply avoided.


4) {{ mysql_conf_path }} 
This value will be picked up from the two variable files and depending on the base OS, the actual value will be populated.
RedHat-based OS: /etc/my.cnf
Debian-based OS: /etc/mysql/my.cnf

5) {{ mysql_service }}
Similar to the previous point, this will start up the My SQL service and enable it to start on boot as well.

6) mysql_db
This is one of Ansible's many core modules. Using this module you can basically create, add and delete databases on remote hosts. You can read more about it HERE

7) mysql_user
Yet another Ansible core module, this helps you to create/ remove as well as assign privileges to your database users. You can read more about this module HERE




Edit the bootstrap main.yml (master yml file) and make sure the mysql role is added to it as shown:





Run the Playbook
Once all the required files and settings are completed, simply execute the playbook as shown:

# ansible-playbook -i hosts main.yml

You should see the playbook install MySQL server on your remote hosts, copy the my.cnf file over to the correct location, start the MySQL service and populate the database as well.




Here's the directory structure of the final playbook.



Lots more still to come on Ansible so stay tuned for a lot more!!