Automate a MySQL Install and Database

Do you want to Automate a MySQL Install and Database using Ansible? Yes you do and in this blog and video we will show you 🙂

Ansible is DevOps automation tool and features heavily in many areas. From managing OSs like Linux and Windows, and through to managing cloud systems and network infrastructure. Based on Python and YAML it is relatively easy to pick up and you certainly do not need to be a Python programmer.

Using an Ansible Playbook, we will be  making the install MySQL 8 onto AlmaLinux 8.6 using Ansible from the EPEL repository. We will also see how to import a database for our testing and development. The one Playbook is used bot tho install MySQL and import the database. Not only this, we can slo set the MySQL root user’s password and ad a new database user with the required Select permissions on the newly imported database.

Starting off with a Vagrant managed virtual machine means that the Playbook can reside in the same host directory as the Vagrantfile used to manage the virtual machine. The Ansible Playbook that we we use is here:

- name: Play to Install and Manage MySQL 8.0 AlmaLinux 8
  hosts: localhost
  become: false 
  vars: 
    - ansible_python_interpreter: /usr/bin/python3
    - password: Password1
  tasks:
    - name: install python mysql connector and mysql client and server
      become: true
      dnf:
        - name:
          - python3-PyMySQL
          - mysql-server
          - mysql state: present
        - name: Manage MySQL Service
          become: true
          service:
            name: mysqld
            state: started enabled:
            true
        - name: Manage MySQL root password
          become: true
          mysql_user:
            login_user: root
            login_password: "{{ password }}"
            name: root
            password: "{{ password }}"
            check_implicit_admin: true
        - name: import database
          mysql_db:
            login_user: root
            login_password: "{{ password }}"
            name: all
            state: import
            target: "{{ item }}"
          loop:
            - /vagrant/sakila-schema.sql
            - /vagrant/sakila-data.sql
        - name: create new mysql user
          mysql_user:
            name: tux
            host: localhost
            password: "{{ password }}"
            login_user: root
            login_password: "{{ password }}"
            priv: "sakila.*:SELECT"
            update_password: on_create

In just a few minutes we have a reliable and repeatable install so you can quickly get on with your database testing and development.

The Playbook contains a single play targeting just the local system.  The Playbook should be executed on the virtual machine after Ansible is installed. In the video we install Ansible from the Vagrantfile provisioner. The Play starts by setting two variables. The password to use for root and we happen to use the same variable for the new user. You of course could use different  passwords. You could also use the pause Ansible module to prompt for a password if you did not want it stored in the play, or use Ansible vault to encrypt it.  As this is likely to be a transient test system, I am not too bothered about the password. Thee other variable set the path to Python, this become more important to ensure the Python module that we add to connect Python to MySQL is added to the main interpreter rather than the cutdown path that is used on Alma and other RedHat based systems by default.

We are then well into our objective to Automate a MySQL Install and Database using Ansible. We see the standard software installs, and service management before using the MySQL specific task to manage the root account, add database and add user. The video explains more: