Automation Linux

Using Ansible to Install and Initialize Mysql 8 on Rocky Linux/Centos 8

Ansible is an open-source software provisioning, configuration management, and application-deployment tool enabling infrastructure as code.

MySQL is an open-source relational database management system. It’s one of the popular relational management system.
Mysql is commonly installed as part of the popular LAMP or LEMP (Linux, Apache/Nginx, MySQL/MariaDB, PHP/Python/Perl) stack.

In this guide we are going to use mysql 8 on Centos 8. This guide also works on other RHEL 8 derivatives like Rocky Linux 8 and Alma Linux 8

Also check:

Requirements

  • Internet connection
  • Ansible installed locally

The hosts file

We require the hosts file that will define the servers that we are targetting:

all:
  hosts:
    db-server:
      ansible_ssh_host: 192.168.10.2
      ansible_ssh_user: centos
      ansible_ssh_private_key_file: ~/.ssh/server_key

The above yaml defines a server db-server that has the ip 192.168.10.2 and can be accessed using user centos and ~/.ssh/server_key.

Test that with:

ssh -i ~/.ssh/server_key centos@192.168.10.2

The ansible playbook

Before we define our tasks, we have to tell ansible a couple of things:

- name: Install mysql server 8 and initialize
  hosts: db-server
  gather_facts: false
  become: true
  vars:
    mysql_root_password: 'secure-root-pw'
    ansible_python_interpreter: /usr/bin/python3

Explaination:

  • name defines the playbook a descriptive name of what it does, it is not compulsory to have this.
  • hosts defines the hosts to target as defined in the hosts or hosts.yaml file defined above.
  • gather_facts defines whether we want ansible to gather os facts before processing the tasks. in our case we do not want
  • become defines that we want to execute our tasks as root
  • vars defines the variables that we want to reuse in our tasks. We defined mysql_root_password and ansible_python_interpreter in our case

The ansible tasks

After the section above, we now need to define our tasks. These tasks can either be added in a role or specified as tasks. In our case we will use them as tasks (check the end of this gude for the complete playbook).

Ensure required sofware is installed

Before proceeding, we want to install all the software that we would need. These includes mysql specific software mysql-server and mysql-devel and supporting software like python related software that will be used by ansible to connect to and set up the mysql server instance. We use the ansible dnf module to do this.

- name: Ensure required packages are installed
  dnf:
    name:
      - mysql-server
      - mysql-devel
      - python39
      - python39-devel
      - python39-pip
      - gcc
    state: latest

Install the required pip modules

Ansible uses the PyMySQL module in python3 to connect and set up mysql server. Install it using ansible pip module

- name: Install Required pip modules
  pip:
    name:
      - PyMySQL
    state: present
    executable: pip3

Start and enable mysql service

Because we want to connect and perform operations in the server, lets start it with this task. Since centos 8 uses systemd to manage long running processes, lets start and enable mysqld using the ansible systemd module:

- name: Ensure mysql service is running
  systemd:
    name: mysqld
    state: started
    enabled: yes

Ensure the root user can login from the local server

The mysql root user is the default admin user who has permissions to all resources in the server. A best practice would be to only enable access through this userroot in the local system when we are doing admin tasks otherwise create a dedicated user for each connection, i.e. for each app, have a user that has access to that db alone.

- name: Ensure root user can only login from localhost
  mysql_user:
    login_password: "{{ mysql_root_password }}"
    check_implicit_admin: yes
    name: root
    host: "{{ item }}"
    password: "{{ mysql_root_password }}"
    state: present
  with_items:
    - localhost
    - 127.0.0.1
    - ::1

In the above task definition:

  • the mysql_root_password variable will be picked from the vars defined earlier
  • The item is a loop of the values defined in the with_items section.
  • The check_implicit_admin tells ansible to try logging in without password which should work since this is a new install. As part of this, the password provided will be set for the root user

Add my.cnf to the home dir

Now that we have set the password in the above task, we would want to supply the password when doing more tasks as the root user. We can provide this in the ~/.my.cnf, a file that is checked for credentials everytime we run mysql commands.
Create a file my.cnf.j2 in the current directory:

[client]
user=root
password={{ mysql_root_password }}

Then this is the task to copy to /root/.my.cnf since we are running the tasks as the root user.

- name: Add .my.cnf to user home
  template:
    src: my.cnf.j2
    dest: /root/.my.cnf

Reload privileges

Run the following task to reload privileges for the changes we have made so far to apply:

- name: Reload privilege tables
  command: |
    mysql -p{{ mysql_root_password }} -ne "{{ item }}"
  with_items:
    - FLUSH PRIVILEGES
  changed_when: False

Remove anonymous users

Its a good practice to remove anonymous users. Lets do it using this task:

- name: Remove anonymous users
  command: |
    mysql -p{{ mysql_root_password }} -ne "{{ item }}"
  with_items:
    - DELETE FROM mysql.user WHERE User=''
  changed_when: False

Dissalow root from loging in remotely

Run the following to remove entries in the mysql.user table. This ensure that the root user can only log in locally:

- name: Disallow root login remotely
  command: |
    mysql -p{{ mysql_root_password }} -ne "{{ item }}"
  with_items:
    - DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1')
  changed_when: False

Remove test database and access to it

Since we do not need the test database, we can remove it with this task:

- name: Remove test database and access to it
  command: |
    mysql -p{{ mysql_root_password }} -ne "{{ item }}"
  with_items:
    - DROP DATABASE IF EXISTS test
    - DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'
  changed_when: False

Reload privileges

To apply the changes we have made above, reload privileges with this task:

- name: Reload privilege tables
  command: |
    mysql -p{{ mysql_root_password }} -ne "{{ item }}"
  with_items:
    - FLUSH PRIVILEGES
  changed_when: False

Delete the .my.cnf that we copied.

For security, lets remove the /root/.my.cnf file since it contains root access:

- name: Delete .my.conf
  file:
    path: /root/.my.cnf
    state: absent

Whole playbook

This is the whole playbook with all the tasks:

---
- name: Install mysql server 8 and initialize
  hosts: db-server
  gather_facts: false
  become: true
  vars:
    mysql_root_password: 'secure-root-pw'
    ansible_python_interpreter: /usr/bin/python3
  tasks:
    - name: Ensure required packages are installed
      dnf:
        name:
          - mysql-server
          - mysql-devel
          - python39
          - python39-devel
          - python39-pip
          - gcc
        state: latest

    - name: Install Required pip modules
      pip:
        name:
          - PyMySQL
        state: present
        executable: pip3

    - name: Ensure mysql service is running
      systemd:
        name: mysqld
        state: restarted
        enabled: yes

    - name: Ensure root user can only login from localhost
      mysql_user:
        login_password: "{{ mysql_root_password }}"
        check_implicit_admin: yes
        name: root
        host: "{{ item }}"
        password: "{{ mysql_root_password }}"
        state: present
      with_items:
        - localhost
        - 127.0.0.1
        - ::1

    - name: Add .my.cnf to user home
      template:
        src: my.cnf.j2
        dest: /root/.my.cnf

    - name: Reload privilege tables
      command: |
        mysql -p{{ mysql_root_password }} -ne "{{ item }}"
      with_items:
        - FLUSH PRIVILEGES
      changed_when: False

    - name: Remove anonymous users
      command: |
        mysql -p{{ mysql_root_password }} -ne "{{ item }}"
      with_items:
        - DELETE FROM mysql.user WHERE User=''
      changed_when: False

    - name: Disallow root login remotely
      command: |
        mysql -p{{ mysql_root_password }} -ne "{{ item }}"
      with_items:
        - DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1')
      changed_when: False

    - name: Remove test database and access to it
      command: |
        mysql -p{{ mysql_root_password }} -ne "{{ item }}"
      with_items:
        - DROP DATABASE IF EXISTS test
        - DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'
      changed_when: False

    - name: Reload privilege tables
      command: |
        mysql -p{{ mysql_root_password }} -ne "{{ item }}"
      with_items:
        - FLUSH PRIVILEGES
      changed_when: False

    - name: Delete .my.conf
      file:
        path: /root/.my.cnf
        state: absent

To run the playbook, you need to create the file setup-mysql.yaml with the above content and hosts.yaml with the hosts file content then use the following command to execute:

ansible-playbook -i hosts.yaml setup-mysql.yaml -vv

Conclusion

In this guide, we were able to use ansible to install mysql server 8 on a centos 8 host using ansible.

Ansible gives us a way to automate the process. This can be used to set up multiple instances in a predictable way using a single command.

comments powered by Disqus
© 2020 - 2024 Citizix, Inc.
Citizix Ltd
Built with Hugo
Theme Stack designed by Jimmy