Ansible MySQL Tutorial


In this video you will see how to build and configure a MySQL server on Ubuntu using Ansible.

We will start with an empty / base image, and then use Ansible to install all the requirements for MySQL, MySQL itself, and then still using Ansible, configure any databases and user accounts that we want.

The really nice things about using an Ansible MySQL set up are:

  • Documented config - your build script is also a perfect reference for what's installed
  • Easy provisioning - identical builds for dev, pre-prod / staging, and prod
  • Existing roles - no need to re-invent the wheel, make use of existing setups by the pros

If you haven't yet got Ansible setup and running, be sure to check out the previous videos in this series.

Otherwise, let's get cracking!

Ansible MySQL

I'm not going to document each step quite so thoroughly for this video. Instead, I will largely refer to the configs.

If you are unsure what any of the config refers too, please do go back and watch the previous videos as there is nothing new here, rather, it is a combination of the various pieces of Ansible knowledge we have covered so far.

Adding an Ansible MySQL Host

Adding in the server to our hosts file is the first stage:

# /hosts
our-mysql-server ansible_ssh_host=your.ip.address.here ansible_ssh_user=your_ssh_user

[mysql_servers]
our-mysql-server

Change up these details to match your own environment.

We are putting our new server into a group:

[mysql_servers]

The reasoning for this is that should we have multiple MySQL servers being managed by Ansible, we can update them as a whole with much fewer commands. This is slightly more advanced, so be sure to check out the Ansible Inventory video tutorial and write up if unsure.

If you're looking for a default starting template at this point, check out the code repo for this project at GitHub.

An Existing Ansible MySQL Role

Rather than re-invent the wheel - and as covered before - the easiest way to get started quickly with Ansible is by making use of existing Roles.

Use Ansible Galaxy to pick out pre-built Ansible Roles to quickly spin up new services. In this case, I have picked the Ansible MySQL role by ANXS.

I've used this role before with good result. It's really easy to use, and you can have a fresh MySQL server up and running in next to no time.

Your Choice To Make

Depending on how you like to work, you can either use the Ansible Galaxy route, or clone the repository into your local set up.

I've covered the differences between these two options before, but my preference is to have the files locally so I can see them, and edit them more easily. Choose what works for you.

Public Key Issues

If you do clone the repo then you may very well hit upon the issue shown in the video whereby the SSH key is incorrect.

This will only happen if you are using my Common role:

ansible-playbook playbook/common.yml -i hosts -l mysql_servers -k -K -s

To fix this you need to paste the contents of your own id_rsa.pub into /playbook/roles/users/files/ssh-keys/deploy.pub. From your machine:

cat ~/.ssh/id_rsa.pub
// or 
vim ~/.ssh/id_rsa.pub

Copy the big long string into /playbook/roles/users/files/ssh-keys/deploy.pub, then remember to upload it to your Ansible server, and re-run the Common playbook.

Not got an id_rsa.pub file? Follow this guide from GitHub on Generating SSH Keys.

Host and Group Var Files

Perhaps, up until now, you have not truly found a good use for host_vars or group_vars.

A situation where they become very handy is in customising a Role for your groups of servers (e.g. our [mysql_servers] group), and individual servers (e.g. our our-mysql-server server ;)).

What we can say is:

make sure each Ansible MySQL server is set up with a standard user account.

BUT

only create user: Tim on the MySQL server called mysql-server-3.

We do this using host_vars and group_vars.

Simply, any setting we want to apply to a group of servers, we set in that groups group_vars file.

Earlier, in our hosts file (also called our Inventory), we added in a group:

[mysql_servers]

We can add in as many groups as we like. Groups can contain other groups, and so on. [Read more here on Groups], and Inventory in general.

Our group names are completely up to us.

All we need to do to make use of them is to create a file of the same name inside our /group_vars directory, e.g. /groups_vars/mysql_servers - with no file extension.

Now, the lack of file extension means this file is ugly inside our text editor. But aside from that, it functions like any other .yml file.

All Please

We can also create a file:

/group_vars/all

Which will apply the given config to every server that Ansible manages.

Ansible MySQL Group Vars

We are going to configure any server in our [mysql_servers] group to have a certain user. This is pretty handy for development, but not best practice for security / production.

A workaround for this may be to have a child group called [mysql_servers_dev] or similar.

Inside our /group_vars/mysql_servers file we can put:

---
mysql_users:
  - name: Tim
    pass: tim_password_123
    priv: "*.*:ALL"                     # optional, defaults to "*.*:ALL"
    host: "%"                           # optional, defaults to "localhost"

We could customise this a bit more, maybe restricting Tim down to a known database (which we could also add in this same file).

Because priv and hosts are left as default, we could remove them without issue. Being explicit is fine though, so for this demo, I'm leaving them in.

Ansible MySQL Host Vars

It's almost entirely the same process for our host_vars entry.

We create a new file inside the /host_vars directory:

/host_vars/our-mysql-server

Only this time, we have named the file after the exact name of our Ansible-managed MySQL server.

---
mysql_databases:
  - name: our_db_name
    collation: "utf8_general_ci"        # optional, defaults to "utf8_general_ci"
    encoding: "utf8"                    # optional, defaults to "utf8"

Settings in here will only apply to this specific server.

Let's say now we went back to our hosts file and added in another new MySQL server:

# /hosts
our-mysql-server ansible_ssh_host=your.ip.address.here ansible_ssh_user=your_ssh_user
a-second-mysql-server ansible_ssh_host=another.ip.address.here ansible_ssh_user=your_ssh_user

[mysql_servers]
our-mysql-server
a-second-mysql-server

Assuming we destroyed our two servers and rebuilt them from scratch (using Ansible, of course), we would end up with:

Both servers have a user called Tim, with a password of tim_password_123.

Only our-mysql-server has a database: our_db_name.

Everything else would be the same, but one is slightly more customised. Pretty powerful stuff for very little effort.

Where Do These Settings Come From?

If you are wondering where I am getting these settings, I have lifted them straight from the ANXS MySQL Role documentation. You can do use this same practice with any Ansible role.

I put this here because although it may seem obvious if you already know about it, when I first started playing with Ansible, this bit confused me no end!

Code For This Course

Get the code for this course.

Episodes