Ansible: MySQL/Nginx Playbook Breakdown and SQL/Linux CLI Equivalent Commands

Recently I’ve been having a bit of a memory lapse when asked some Ansible questions in interviews, so in this post we will be breaking down an Ansible playbook that installs and configures Nginx and MySQL with some later items that perform the same as the mysql_secure_installation script that you would typically (I sure hope!) run after installation of MySQL with your package manager. Admittedly, up until recently I haven’t been messing with Ansible as much as I’d like to, and I created an earlier “basic issues” post with a ping/pong test, and I have a couple Workstation/Server examples on my GitHub, but then I stopped for a bit because I got distracted by some other things.

I hope this post might help some of you as much as I am hoping it will help me recall more of what I’ve done in the past. Heck, I used to be able to read my way through PHP fairly well (thanks to setting up eCommerce stores and blog customization), I don’t remember a single bit of it now. Use it or lose it. As an example, I used Ansible to give me an reproducible WordPress installation quite a few years ago, but I don’t remember much of what I did at the time. This WordPress/Ansible setup was before Docker was ever a possibility in my environment, in case you’re wondering why I did it with Ansible instead of just pulling a Dockerized WordPress environment. Originally, I am talking back when going from a MAMP stack on an XServe (yes you read that right, an Apple rackmount server) then moving to CentOS 5/6 boxes.

This is currently assuming an Ubuntu (or Debian) host, as that’s what I’ve been running personally lately with my Kubernetes testing environments. In theory you should be able to swap in “yum” for “apt” on a RHEL/CentOS box without an issue.

Let’s move on. Let’s get a bird’s eye view first by looking at the entire playbook:

---
- hosts: nginx
  remote_user: username
  become: yes
  become_method: sudo
  tasks:
    - name: "Install NGINX and MySQL"
      apt: name={{ item }} state=present
      with_items:
        - nginx
        - mysql-server
        - python-mysqldb

    - name: "Start/enable NGINX and MySQL services"
      service: name{{ item }} state=started enabled=yes
      with_items:
        - nginx
        - mysql

    - name: "Create test db"
      mysql_db: name=testdb
                state=present

    - name: "Create new user for cx"
      mysql_user: name=webapp1
                  password=password
                  priv=*.*:ALL state=present

    - name: Sets root password
      mysql_user: user=root password="{{ mysql_root_password }}" host=localhost

    - name: Deletes anonymous MySQL server user for ansible_fqdn
      mysql_user: user="" host="{{ ansible_fqdn }}" state="absent"

    - name: Delete anonymous MySQL user for localhost
      mysql_user: user="" state="absent"

    - name: Secures MySQL root user for IPV6 localhost (::1)
      mysql_user: user="root" password="{{ mysql_root_password }}" host="::1"

    - name: Secures MySQL root user for IPV4 localhost (127.0.0.1)
      mysql_user: user="root" password="{{ mysql_root_password }}" host="127.0.0.1"

    - name: Secures MySQL root user for localhost domain (localhost)
      mysql_user: user="root" password="{{ mysql_root_password }}" host="localhost"

    - name: Secures MySQL root user for server_hostname domain
      mysql_user: user="root" password="{{ mysql_root_password }}" host="{{ ansible_fqdn }}"

Well, that’s the whole thing. Now let’s break it down a bit and see what’s going on. In the first section, we are telling Ansbile what hosts in your inventory to apply this configuration to as well as to become sudo to run this install:

- hosts: nginx
  remote_user: username
  become: yes
  become_method: sudo

Then comes the meat of the playbook, which outlines all of the tasks to run in order to get the end result we want on whatever remote servers we are setting up.

 tasks:
    - name: "Install NGINX and MySQL"
      apt: name={{ item }} state=present
      with_items:
        - nginx
        - mysql-server
        - python-mysqldb

The above is as simple as the equivalent of running the following apt command to install packages:

sudo apt install nginx mysql-server python-mysqldb

In the above block, “{{ item }}” is essentially a placeholder that allows us to list the packages in a list below, shown using “with_items”. This means we don’t have to type out new code blocks for every package we want to install with Ansible, we can do this in one section of the file, reducing redundancy, something you want in automation files but not in your infrastructure!

Alrighty, on to the next section:

- name: "Start/enable NGINX and MySQL services"
  service: name{{ item }} state=started enabled=yes
  with_items:
    - nginx
    - mysql

This section is essentially the equivalent of the following:

# This part is performed with the "state=started" portion of the playbook

$ sudo systemctl start nginx
$ sudo systemctl start mysql

 # This part is performed with the "enabled=yes" portion of the playbook

$ sudo systemctl enable nginx
$ sudo systemctl enable mysql

The following will all relate directly to MySQL operations for the rest of the post. This section of the file handles creating a test DB to make sure you’re able to connect to the MySQL server after installation, as well as all operations that would typically be performed by running the “mysql_secure_installation” post-installation script. So, this replaces the option of running that script. I will attempt to also provide mysql command line example substitutes, as I did above for the other items.

In order to test the connection, ,we will try to setup a test database named “testdb”, this at least ensures all basic operations are working at this point, post-installation, before we lock things down. So, let’s look at the section that creates a test database:

    - name: "Create test db"
      mysql_db: name=testdb
                state=present

Easy stuff here, this is as simple as running the following mysql command/query:

CREATE DATABASE testdb;

This isn’t the topic of this tutorial, but a further example of what commands for MySQL could be run include creating a database, table and inserting some data into it, like so:

CREATE DATABASE dbname;
USE dbname;
CREATE TABLE tablename ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
INSERT INTO tablename ( id, name ) VALUES ( null, 'Sample data' );

The above created the database, set it as your active DB, just like you would by perhaps double-clicking in MySQL Workbench’s sidebar (IIRC, or just running “USE” in the query window first to ensure it goes to the right DB, for the paranoid like mysql), creating a table called “tablename”, and inserting some data into said table, “tablename”.

Anyway, back on topic. We now want to create a new DB server user for our connection to MySQL, which is covered in this code block:

    - name: "Create new user for cx"
      mysql_user: name=webapp1
                  password=password
                  priv=*.*:ALL state=present

The above is the equivalent of running the following CLI commands:

mysql -u root -p
GRANT ALL PRIVILEGES ON *.* TO 'webapp1'@'localhost' IDENTIFIED BY 'password';
\q

Getting there, now we have a user added so we don’t have to use the “root” user anymore, a positive in the security direction of things. What we need to do now is set the root password, and delete anonymous users for the MySQL host. The root password can be set with the following section of our YAML file:

    - name: Sets root password
      mysql_user: user=root password="{{ mysql_root_password }}" host=localhost

What is the CLI/SQL equivalent of this? Something like this should work:

mysql -u root -p
USE mysql;

update user set authentication_string=PASSWORD("mynewpassword") where User='root';

flush privileges;

quit
\q

This process should set the root password properly. Now to clear anonymous users:

    - name: Delete anonymous MySQL user for localhost
      mysql_user: user="" state="absent"

The SQL/CLI equivalent:

mysql -u root -p

DROP USER '';

flush privileges;

quit
\q

Removing root remote access:

    - name: Secures MySQL root user for IPV6 localhost (::1)
      mysql_user: user="root" password="{{ mysql_root_password }}" host="::1"

    - name: Secures MySQL root user for IPV4 localhost (127.0.0.1)
      mysql_user: user="root" password="{{ mysql_root_password }}" host="127.0.0.1"

    - name: Secures MySQL root user for localhost domain (localhost)
      mysql_user: user="root" password="{{ mysql_root_password }}" host="localhost"

    - name: Secures MySQL root user for server_hostname domain
      mysql_user: user="root" password="{{ mysql_root_password }}" host="{{ ansible_fqdn }}"

The SQL equivalent command:

mysql -u root -p

DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

flush privileges;

quit
\q

You can see in the above block why we need multiple entries, because removing remote access for the root user properly means you need to specify all local IPs and hostnames.

I think that pretty much completes this post. Happy Automation!

Blog Theme Editing

Where’s the old school “under construction” GIF when I need it? I am currently working on some different options for the theme of this blog. I did not like the level, or lack thereof, of customization options I had with the past one I was using.

One thing to note right now is that the social links I had on my last blog may be missing for you currently. While I do have the LinkedIn and Twitter icons at the bottom, the constraints of this theme do not seem to let me easily add my Slack, GitHub, and Skype links. Once I get those figured out they will be back.

I have to say theming blogs has always been one of my least favorite activities, but I do have a strong appreciation for those that spend the majority of their time doing it.

Will be updating more soon!

Basic Ansible: Small hurdles…

So I’m installing Ansible again to brush up on some things and test out some others, and I’m just setting it up on VirtualBox with a control VM and a few small nodes added to the hosts/inventory right now. I’ve used it before, primarily in one environment for the simple task of SSH key management, which was nice, and a LAMP stack install. This was before I was running cloud services in any of my environments.

All of the VMs involved, including the control machine which I’ve set up as an Ubuntu Desktop, are running 18.04 LTS.

I’ve decided on using Bridged adapters (so they’re just simply part of the local home network) to be sure that I can connect between hosts easily.

Note: Be sure you select the “virtio-net” Adapter type under “Advanced” in the VM settings if you can’t get your guests to pull an IP.

The basic things that I’ve run into so far come down to the following on Ubuntu 18.04:

Most of you may not run into this, but when setting up the install for the host nodes, Python 2 was not installed (this was Ubuntu 18.04 LTS from minimal), however 3 was, but since “python” is not an available command on these boxes from a fresh install, you’ll get a failure. So, at the time to just ran a simple install of python 2 in order to make this work:

sudo apt-get install python-minimal -y

Another issue was SSH user not being recognized, so what do you do? Well, make sure you use ssh-copy-id to copy your key to the node you want to manage, using the following:

ssh-copy-id user@host-or-ip

Once you perform these actions, you should be able to check for connectivity to the remote host/inventory item by running Ansible’s ping module:

ansible all -m ping -u [user on remote host]

The response you should get will be:

X.X.X.X | SUCCESS => {
“changed”: false,
“ping”: “pong”
}

(where X.X.X.X is the host you’re trying to ping)

That’s pretty much it that I can think of for now, and I’ll probably throw up another post soon. Good luck and happy infrastructure automation!