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!