🐬Building a Linux Web Server with Terraform & Ansible – Part 9: MySQL Database

Now that we’ve got our Flask app running on Nginx with Gunicorn, it’s time to make things a little more “production-like” by adding a real database. In this part, we’ll install and configure MySQL using Ansible.

🧱 Step 1: Create the MySQL Role

First, let’s create a new Ansible role to handle our MySQL installation.

cd ansible
mkdir -p roles/install-mysql/{handlers,tasks}

📜 Step 2: Add MySQL Tasks

In roles/install-mysql/tasks/main.yml, add the following:

---
- name: Update APT package cache
  apt:
    update_cache: yes
- name: Installing Mysql  and dependencies
  package:
    name: "{{item}}"
    state: present
    update_cache: yes
  loop:
  - mysql-server
  - mysql-client 
  - python3-mysqldb
  - libmysqlclient-dev
- name: start and enable mysql service
  service:
    name: mysql
    state: started
    enabled: yes
- name: Creating mysql user 
  mysql_user:
    name: "{{db_user}}"
    password: "{{db_pass}}"
    priv: '*.*:ALL,GRANT'
    host: '%'
    state: present
- name: Creating database
  mysql_db:
    name: "{{db_name}}"
    state: present
- name: Ensure MySQL only listens on localhost
  lineinfile:
    path: /etc/mysql/mysql.conf.d/mysqld.cnf
    regexp: '^bind-address'
    line: 'bind-address = 127.0.0.1'
  notify: Restart mysql
- name: Remove remote root access
  mysql_user:
    name: root
    host: '%'
    state: absent

🧠 Let’s Break It Down

• We install MySQL and necessary dependencies for Python/Ansible to interact with it.
• We ensure the MySQL service is started and enabled.
• We create a dedicated database user with full privileges.
• We create the application database.
• We harden security by:
  • Binding MySQL to localhost
  • Removing remote root access

🔁 Step 3: Add a Handler to Restart MySQL

In roles/install-mysql/handlers/main.yml:

---
- name: Restart mysql
  service:
    name: mysql
    state: restarted

🔗 Step 4: Include the Role in Your Main Playbook

In main.yml, include the new role:

...
  roles:
  ...
  - roles/install-mysql

🔐 Step 5: Use Environment Variables for Secrets

Add the following to your vars.yml (or wherever you’re loading vars):

db_user: "{{ lookup('env', 'TUTORIAL_MYSQL_USER') }}"
db_pass: "{{ lookup('env', 'TUTORIAL_MYSQL_PASS') }}"
db_name: "{{ lookup('env', 'TUTORIAL_DB_NAME') }}"

Instead of storing secrets in plain text, we use environment variables to keep credentials out of Git.

📁 Step 6: Create a .env File Locally

To make working with these variables easier during local development, create a .env file:

TUTORIAL_MYSQL_USER=<fill in>
TUTORIAL_MYSQL_PASS=<fill in>
TUTORIAL_DB_NAME=<fill in>

Load it before running your playbook:

source .env

And don’t forget to ignore it in git:

echo '.env' >> .gitignore

🚀 Step 7: Run the Playbook

Now deploy your changes to the server:

play main.yml

If all goes well, your MySQL server will be installed, your app database will be created, and your app will be ready to talk to it 🎉

✅ Summary

In this article, we:

• Created an Ansible role to install and configure MySQL
• Secured the database by binding it to localhost and removing remote root access
• Used environment variables to safely manage database credentials
• Set up a .env file for local use without leaking secrets into Git

🔜 Next Up…

In the next part of the series, we’ll refactor our Flask app to use the MySQL database, and wire it all together using SQLAlchemy.

See you there! 👋