Wednesday, September 30, 2015

Automation for DBA - Ansible part 1


In this post I would like to move forward with software provisioning and configuration. In my last post I covered a ways to build a "core VM" and now it's a time to add some software and configuration to it.

There are couple of automation tools, which can be used for that task like Puppet, Chef or Ansible to name a few. The latter one - Ansible - is my favorite, cause in my opinion it has shortest learning curve and also doesn't require any agents on the remote servers.
Although there are some requirements which has to be met on the remote servers:
- ssh connectivity to the remote host
- Python 2.4 ( with python-simplejson ) or 2.5 installed on the remote host.

If Python is a "big no-no" in your organization, you can still you Ansible to help you run a script over set of servers but you will loose almost all functionality.

How Ansible works:

Ansible has a concept of the control machine and a remote managed servers. Ansible scripts are started on the control machine and other servers are managed over a SSH connection and Ansible modules send to the remote servers on demand. Ansible can run in the fully automated mode only if control machine and remote servers has a password-less SSH configuration. In the other case Ansible can ask for a SSH password.

The control machine can be a dedicated sever or it can be an OS admin or DBA laptop, where Ansible is installed. Please check Ansible documentation to find a information how to install it. 

Remote servers can be managed from control machine, only if they are added into a Ansible inventory. The inventory is a text file with the following format:

envtest ansible_ssh_host= ansible_ssh_user=oracle


There are two groups in inventory file - testdbgroup with one members envtest, and proddb-group with two members rac1 and rac2. Server - testdb - is listed in the inventory file but it not a part of any group. 

Ansible has a concept of host and group variables. 
Group variables are used for all host defined in the a group. There is a directory called group_vars, containing a files named with a group name. Each file is a set of variables for a particular group.
Host variables are used for a particular host only and they overwrite a group variables. There is a directory called host_vars, containing a files named with a host name. Each file is a set of variables for a particular host.

$ ls -l host_vars/
total 24
-rw-r--r--  1 mprzepiorowski  staff  29 Sep 29 12:44 envtest

$ cat host_vars/envtest
password: "host env password"

$ ls -l group_vars/
total 8
-rw-r--r--  1 mprzepiorowski  staff  23 Sep 29 13:10 testgroup

$ cat group_vars/testgroup
groupname: "Test group"
Server envtest has a file with host variables and it belongs to the group testgroup with a defined variables as well. Both will be used in the following example. 

Running mode
There are three ways of running a remote command:
- single command mode, 
- Ansible playbooks
- Ansible playbooks with roles
and commands or playbooks can be run on the specific host from inventory file, specific group or all hosts.


Single command:

In the first run Ansible can be used to add SSH public key from control machine into remote server. By default Ansible connect to the remote servers using a user running a command on the control machine, but this can be changed in the inventory file.
$ cat inventory/lab
envtest ansible_ssh_host= ansible_ssh_user=oracle
Remote server has a user oracle and it will be used by all scripts. If remote server name "envtest" is not resolved by a DNS, IP address can be added to inventory file as well.

Test a SSH connection to remote host
$ ssh oracle@
oracle@'s password:
Password is required and this is an example command to fix this problem

$ ansible -k -i inventory/lab all -m authorized_key -a "user=oracle key=\"{{ lookup('file', '/Users/mprzepiorowski/.ssh/') }}\" "
SSH password:

envtest | success >> {
    "changed": true,
    "key": "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQD43SwP3zXR/LrKqADJDC8jUOv0vgMEQVqWOXoUgwlzQ1vpS1I5m3GjXggG7fjU5I2jGbG+V9xSqPZdw4MGed4nsgOo1m68w9oBpfYLu3hKACSw1Tgu8Ghxd5TB9ohyZ7NOgepXB+zmV8NoqvAUg4yDkk4qPn1fQmoIz7yLkPRErnmSkI6e/gHsGAmZ+5WdvH0FByPCMEfHqDe8vI4ZPVbPWZzl5x9m72HtxjCbB2htHg1JEcch6927oiRE9rllbOL0M/tw5LgjCtYpM3iZTsBwFsGZ6NQ1DM+OiJSrDEhUqUMOZ08X/G7aNYb28QUt9G/FZTTwZjEfnOO75n3i29U/ mprzepiorowski@Marcins-MacBook-Pro.local",
    "key_options": null,
    "keyfile": "/home/oracle/.ssh/authorized_keys",
    "manage_dir": true,
    "path": null,
    "state": "present",
    "unique": false,
    "user": "oracle"
Command options:
- k - ask for SSH password
- i inventory/lab - use a inventory file from folder inventory and file name called lab
all - run this command for all hosts from inventory file
-m authorized_key - use Ansible module authorized_key
- a "user=oracle, key=xxxxxxxx" - Ansible module parameters 

Command output:
- changed : true - remote system has been changed due to Ansible call
- envtest | success - action returned OK status
- other lines are depend on the module

Ansible returned OK status so now SSH connection should work without password:
$ ssh oracle@
Last login: Mon Sep 28 15:58:24 2015 from
[oracle@envtest ~]$
Ansible did a work and SSH public key has been added to user configuration on the remote server. 

Single command is a nice feature if you want to run single command across fleet of servers, but for most of cases Ansible playbook will be a way to deal with.

Ansible playbook:

Next example will show how to create a simple playbook with an one task and install a vim package using yum

Playbook file is formatted in YAML and it looks like this:
- hosts: all
     - name: install vim
       sudo: yes
       yum: name=vim state=present
Be aware that YAML file syntax include a white space, so format of this file matter as well.

Line description:
Hosts: all - mean run a playbook for all hosts from inventory file
tasks: - is a start of task lists (one task in this example)
name: - start of task definition
sudo: - run this module using sudo
yum: - module name with parameters (name is a package to install, state=present mean install it)

Ansible playbook can be stated using this command:
$ ansible-playbook yum.yml -i inventory/lab

PLAY [all] ********************************************************************

GATHERING FACTS ***************************************************************
ok: [envtest]

TASK: [install vim] ***********************************************************
changed: [envtest]

PLAY RECAP ********************************************************************
envtest                    : ok=2    changed=1    unreachable=0    failed=0

Where -i is pointing to the inventory file and yum.yml is a name of file with playbook definition. Output contains a list of tasks executed and summary with number of successes, changes and failures. 

Ansible playbook and variables:

In the all previous examples, only has host has been used and there was no variables set. Let's add some more complexity to playbook and show how flexible is Ansible. Both variables defined for particular host and group will be used to create a text file based on template.

Template file ( Ansible supports Jinja2 template language)
$ cat template.j2
Host name is {{ ansible_hostname }}
Host var password is set to {{ password }}
Group var groupname is set to {{ groupname }}

Ansible playbook:
$ cat template.yml
- hosts: all
     - name: generate scripts
       template: src=template.j2 dest=/tmp/output mode=0644

Running playbook:
$ ansible-playbook -i inventory/lab template.yml

PLAY [all] ********************************************************************

GATHERING FACTS ***************************************************************
ok: [envtest]

TASK: [generate scripts] ******************************************************
changed: [envtest]

PLAY RECAP ********************************************************************
envtest                    : ok=2    changed=1    unreachable=0    failed=0
Output file on envtest server
$ ssh oracle@
Last login: Tue Sep 29 12:29:59 2015 from
[oracle@envtest ~]$ cat /tmp/output
Host name is envtest
Host var password is set to host env password
Group var groupname is set to Test group
[oracle@envtest ~]$

If you think that Ansible is difficult at that stage, and there is no use for DBA's wait until next blog post when I will use a ansible-Oracle playbooks to install Oracle on server changing only one files with a variables.