Playbook to initialize a MySQL database

 

In this example we will see how to initialize a MySQL server with a preloaded database. We will install the MySQL server with an Ansible Galaxy role. We will download the database script with an Ansible task for downloading files. We will load it with an Ansible task of the MySQL module for data loading.

MySQL Installation
One level above our roles folder we will install the MySQL role from testrole.

$ ansible-galaxy install testrole.mysql -p roles

The file testrole.mysq/defaults/main.yml contains variables for customization of the MySQL installation. We will change the values of the two variables that set the user's password root

...
mysql_user_password: changeme
...
mysql_root_password: changeme
...

We will create a playbook ( mysql.yml) for the role installation

---

- name: MySQL Playbook
  hosts: dbserver
  roles:
    - testrole.mysql

We will run the playbook

$ ansible-playbook mysql.yml --become

This will have installed MySQL on the host dbserver. The user's password root will be change me.
Creation of the DB
The creation of the database will be done in two steps. We will first download the script containing the database initialization code to the managed machine. Next, we will import the downloaded script into the database.
Example: Role ( crearbdSG) to download the SQL script and import it to the database

---

- name: Download SG.sql
  get_url: #1
    url: https://raw.githubusercontent.com/gitusername/docker_customer_catalog/master/init.sql
    dest: /home/ubuntu/SG.sql

- name: Import SG database
  mysql_db: #2
    name: SG
    state: import
    target: /home/ubuntu/SG.sql #3

1. Download the indicated file in the specified path indest
2. The module mysql_dballows the creation and deletion of databases, as well as import and export operations
3. Path of the remote machine where the file to import is located

Next, we will modify the previous playbook ( mysql.yml) to add the new role

---

- name: MySQL Playbook
  hosts: dbserver
  roles:
    - testrole.mysql
    - crearbdSG #1

1. New role added for data upload

It is not necessary to run the entire playbook from the beginning. We can indicate that it starts executing from a specific task with the parameterstart-at-task

$ ansible-playbook mysql.yml --become --start-at-task "Download SG.sql"

Add a PHP application to the SG database
Next we could create another playbook to add an Apache server and a PHP interpreter to the previous host. As an example, we could download a PHP script that displays the list of clients from the SG database .
The script is set up for proof of concept only and uses the account rootand password in the same code. The database is named SG, and is accessed through the account rootand with the password changeme.

---

- name: Update package cache
  apt:
    update_cache: yes

- name: Install Apache and PHP
  apt:
    name: ['apache2', 'php', 'libapache2-mod-php', 'php-mysql']

- name: Restart Apache
  service:
    name: apache2
    state: restarted

- name: Download customer_catalog
  get_url:
    url: https://raw.githubusercontent.com/gitusername/CustomerCatalog/master/index.php
    dest: /var/www/html/index.php