How to Setup and Initialize Mysql Server on Ubuntu 22.04 Using Ansible

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. 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 install and set up Mysql in Ubuntu 22.04. This guide also works on other Debian derivatives like Debian and other Ubunti versions

Also check:

Creating the Ansible playbook

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

1
2
3
4
5
6
7
---
- name: Install and configure mariadb server on Ubuntu 22.04
  hosts: db-srv
  gather_facts: false
  become: true
  vars:
    mysql_root_password: 'superSecretPassword'

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 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 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 apt module to do this.

Please note that we are also doing an OS update and upgrade before proceeding to ensure that we are using the latest packages in our system.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
- name: Update apt repositories and cache on server
  apt:
    update_cache: yes
    force_apt_get: yes
    cache_valid_time: 3600

- name: Upgrade all packages on server
  apt:
    upgrade: dist
    force_apt_get: yes

- name: Check if a reboot is needed on all servers
  register: reboot_required_file
  stat:
    path: /var/run/reboot-required
    get_checksum: false

- name: Reboot the server if kernel updated
  reboot:
    msg: "Reboot initiated by Ansible for kernel updates"
    connect_timeout: 5
    reboot_timeout: 300
    pre_reboot_delay: 0
    post_reboot_delay: 30
    test_command: uptime
  when: reboot_required_file.stat.exists

- name: Install Nginx, MySQL, PHP, and related packages
  apt:
    name:
      - mysql-server
      - unzip
      - python3-mysqldb
      - vim
      - git
    state: present

Start mysql server

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

1
2
3
4
5
- name: Start and enable MySQL
  systemd:
    name: mysql
    state: started
    enabled: yes

Initialize Mysql set up

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
- 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: Secure root account
  mysql_user:
    login_user: root
    login_password: "{{ mysql_root_password }}"
    name: root
    host: "{{ item }}"
    state: absent
  with_items:
    - "%"
    - "::1"
  when: item != 'localhost' and item != '127.0.0.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 /root/.my.cnf by copying inline content

1
2
3
4
5
6
7
8
- name: Add .my.cnf to user home
  copy:
    content: |
      [client]
      user=root
      password={{ mysql_root_password }}
    dest: /root/.my.cnf
    mode: 0600

Remove anonymous users

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

1
2
3
4
5
6
7
- name: Remove anonymous users
  mysql_user:
    login_user: root
    login_password: "{{ mysql_root_password }}"
    name: ''
    host_all: yes
    state: absent

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:

1
2
3
4
- name: Disallow root login remotely
  mysql_query:
    login_password: "{{ mysql_root_password }}"
    query: "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1')"

Remove test database and access to it

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
- name: Remove test database
  mysql_db:
    login_user: root
    login_password: "{{ mysql_root_password }}"
    name: test
    state: absent

- name: Remove privileges on test database
  mysql_query:
    login_user: root
    login_password: "{{ mysql_root_password }}"
    query: "DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'"

Reload privileges to apply changes

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

1
2
3
4
5
- name: Reload privileges
  mysql_query:
    login_user: root
    login_password: "{{ mysql_root_password }}"
    query: "FLUSH PRIVILEGES"

Delete the `.my.cnf` that we copied

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

1
2
3
4
- name: Delete .my.conf
  file:
    path: /root/.my.cnf
    state: absent

Whole playbook

This is the whole playbook with all the tasks:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
---
- name: Install and configure mariadb server on Ubuntu 22.04
  hosts: db-srv
  gather_facts: false
  become: true
  vars:
    mysql_root_password: 'superSecretPassword'
  tasks:
    - name: Update apt repositories and cache on server
      apt:
        update_cache: yes
        force_apt_get: yes
        cache_valid_time: 3600

    - name: Upgrade all packages on server
      apt:
        upgrade: dist
        force_apt_get: yes

    - name: Check if a reboot is needed on all servers
      register: reboot_required_file
      stat:
        path: /var/run/reboot-required
        get_checksum: false

    - name: Reboot the server if kernel updated
      reboot:
        msg: "Reboot initiated by Ansible for kernel updates"
        connect_timeout: 5
        reboot_timeout: 300
        pre_reboot_delay: 0
        post_reboot_delay: 30
        test_command: uptime
      when: reboot_required_file.stat.exists

    - name: Install Nginx, MySQL, PHP, and related packages
      apt:
        name:
          - mysql-server
          - unzip
          - python3-mysqldb
          - vim
          - git
        state: present

    - name: Start and enable MySQL
      systemd:
        name: mysql
        state: started
        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
      copy:
        content: |
          [client]
          user=root
          password={{ mysql_root_password }}
        dest: /root/.my.cnf
        mode: 0600

    - name: Remove anonymous users
      mysql_user:
        login_user: root
        login_password: "{{ mysql_root_password }}"
        name: ''
        host_all: yes
        state: absent

    - name: Disallow root login remotely
      mysql_query:
        login_password: "{{ mysql_root_password }}"
        query: "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1')"

    - name: Remove test database
      mysql_db:
        login_user: root
        login_password: "{{ mysql_root_password }}"
        name: test
        state: absent

    - name: Remove privileges on test database
      mysql_query:
        login_user: root
        login_password: "{{ mysql_root_password }}"
        query: "DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'"

    - name: Reload privileges
      mysql_query:
        login_user: root
        login_password: "{{ mysql_root_password }}"
        query: "FLUSH PRIVILEGES"

    - name: Secure root account
      mysql_user:
        login_user: root
        login_password: "{{ mysql_root_password }}"
        name: root
        host: "{{ item }}"
        state: absent
      with_items:
        - "%"
        - "::1"
      when: item != 'localhost' and item != '127.0.0.1'

    - 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:

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

Conclusion

In this guide, we were able to use ansible to install Mysql on a Ubuntu Linux 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.