Definition:

MySQL is a widely used open-source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and managing content in a database. It’s known for its quick processing, proven reliability, ease and flexibility of use.

Ansible, on the other hand, is an open-source software provisioning, configuration management, and application-deployment tool. It provides large productivity gains to a wide variety of automation challenges, including setting up databases.

When it comes to automating MySQL database setup and management, Ansible shines in several ways:

1. Simplicity: Ansible uses a simple syntax written in YAML called playbooks. These playbooks are easy to write, read, and understand, even for those not familiar with the Ansible tool.

2. Efficiency: Ansible allows you to automate the process of setting up and configuring MySQL databases, which can be a time-consuming and error-prone task if done manually.

3. Consistency: By using Ansible, you ensure that your MySQL databases are set up and configured consistently across all your environments.

4. Scalability: Ansible can easily scale to manage many databases, making it a great choice for large-scale deployments.

The combination of MySQL and Ansible provides a powerful toolset for managing databases efficiently and consistently. Whether you’re a database administrator looking to automate routine tasks or a developer wanting to ensure consistent database setup across multiple environments, Ansible and MySQL can make your life much easier. So lets create ansible playbook for configuring and automating MySQL database on your host machine.

Create an ansible playbook for configure MySQL database on localhost.

[ansible@master ~]$ vim mysql.yml

---
- name: Configure MySQL database using Ansible automation
  hosts: localhost
  vars:
    db_username: ram
    db_name: wordpress
    mysql_root_password: password
  tasks:
    - name: Install pip3
      ansible.builtin.yum:
        name: python3-pip
        state: present
        update_cache: yes

    - name: Install Python MySQL module
      ansible.builtin.pip:
        name: PyMySQL
        state: present

    - name: Install MySQL
      ansible.builtin.yum:
        name: mysql
        state: latest
      notify: restart mysqld

    - name: Create database user with password and all database privileges and 'WITH GRANT OPTION'
      community.mysql.mysql_user:
        name: "{{ db_username }}"
        password: "{{ mysql_root_password }}"
        priv: '*.*:ALL,GRANT'
        state: present

    - name: Create a new database "{{ db_name }}"
      community.mysql.mysql_db:
        login_user: "{{ db_username }}"
        login_password: "{{ mysql_root_password }}"
        name: "{{ db_name }}"
        state: present

    - name: Add sample database to the database
      copy:
        src: ./dump.sql
        dest: /tmp/dump.sql

    - name: insert sample database into database
      community.mysql.mysql_db:
        name: "{{ db_name }}"
        state: import
        target: /tmp/dump.sql
        login_user: "{{ db_username }}"
        login_password: "{{ mysql_root_password }}"

  handlers:
    - name: restart mysqld
      ansible.builtin.service:
        name: mysqld
        state: restarted
        enabled: yes

Create a new table for the newly created database and insert some records.

[ansible@master ~]$ vim dump.sql

CREATE TABLE IF NOT EXISTS test (
  message varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test(message) VALUES('Ansible To Do List');
INSERT INTO test(message) VALUES('Get ready');
INSERT INTO test(message) VALUES('Ansible is fun');
INSERT INTO test(message) VALUES('Learn Ansible');
INSERT INTO test(message) VALUES('Setup Ansible');
INSERT INTO test(message) VALUES('Test Ansible setup');

Let's check whether our MySQL Database is configured correctly or not. This is the output result.

[ansible@master ~]$ mysql -u ram -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.32 Source distribution

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+
5 rows in set (0.00 sec)

mysql> use wordpress;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_wordpress |
+---------------------+
| test                |
+---------------------+
1 row in set (0.00 sec)

mysql> describe test
    -> ;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| message | varchar(255) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from test;
+--------------------+
| message            |
+--------------------+
| Ansible To Do List |
| Get ready          |
| Ansible is fun     |
| Learn Ansible      |
| Setup Ansible      |
| Test Ansible setup |
+--------------------+
6 rows in set (0.00 sec)

mysql>

In-Short Description about playbook

Let's understand this playbook in the description step by step. This Ansible playbook is designed to configure a MySQL database on localhost. Here’s a breakdown of what each part does:

1. Variables: The playbook defines three variables: db_username, db_name, and mysql_root_password. These are used later in the playbook to set up the MySQL database.

2. Tasks: The playbook then performs a series of tasks:

   • Install pip3: This task installs pip3, a package manager for Python, using the ansible.builtin.yum module.

   • Install Python MySQL module: This task uses pip3 to install the PyMySQL module, which is a Python interface for MySQL.

   • Install MySQL: This task installs the latest version of MySQL using the ansible.builtin.yum module. If the installation is successful, it triggers a handler to restart the MySQL service.

   • Create database user: This task creates a new MySQL user with the username and password specified by the db_username and mysql_root_password variables. The user is granted all privileges on all databases.

   • Create a new database: This task creates a new MySQL database with the name specified by the db_name variable.

   • Add sample database to the database: This task copies a sample database file (dump.sql) to the /tmp directory on the target host.

   • Insert sample database into database: This task imports the sample database into the newly created database.

3. Handlers: The playbook includes a handler to restart the MySQL service. This handler is triggered if the MySQL installation task is successful.

This playbook provides a comprehensive example of how to automate the process of setting up a MySQL database using Ansible. It demonstrates the use of variables, tasks, and handlers in an Ansible playbook. It also shows how to use different Ansible modules to perform tasks such as installing packages, creating users and databases, and importing data.

If any issues are found in this code, please comment, so we can fix them as soon as possible. Thank you.

#Ansible #MySQL #DatabaseConfiguration #DevOps #Automation #InfrastructureAsCode #IAC #Playbook #RelationalDatabase #RDBMS #ConfigurationManagement #ITAutomation #CloudComputing #OpenSource #Tech