Set Up and Initialize MySQL on Ubuntu 22.04 With 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 install and set up MySQL on Ubuntu 22.04. This guide also works on other Debian derivatives like Debian and on newer Ubuntu versions.

A note on versions

Which MySQL you get depends on the release you run, and this matters more than it used to:

Ubuntu release	`mysql-server` ships	Status
22.04 LTS (Jammy)	MySQL 8.0	Standard support until May 2027
24.04 LTS (Noble)	MySQL 8.0	Standard support until May 2029
26.04 LTS	MySQL 8.4 LTS	Current LTS

MySQL 8.0 reached upstream end of life on 21 April 2026 — Oracle no longer ships community security fixes for it, and 8.4 LTS is the version to build on for anything new (supported into 2032). Canonical still backports fixes to the 8.0 packages in 22.04 and 24.04 for the life of those releases, so this playbook remains safe to run there. If you are starting fresh, prefer Ubuntu 26.04, where mysql-server is 8.4 and the playbook below works unchanged.

Also check:

Before you begin

The MySQL modules are not part of ansible-core — they live in a collection that you install on the control node. The collection was renamed from community.mysql to ansible.mysql; the old community.mysql.* names still work as redirects, but they are deprecated and are removed in community.mysql 6.0.0 (Ansible 17), so use the new name:

1
ansible-galaxy collection install ansible.mysql

If you are on an older Ansible and stuck with community.mysql, the playbook below works if you swap the ansible.mysql. module prefixes for community.mysql.. Everything else is identical.

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
8
---
- name: Install and configure MySQL server on Ubuntu 22.04
  hosts: db-srv
  gather_facts: false
  become: true
  vars:
    mysql_root_password: 'superSecretPassword'
    mysql_socket: /var/run/mysqld/mysqld.sock

Explanation:

name gives 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 mysql_socket in our case

mysql_socket matters on Ubuntu. A fresh MySQL 8 install authenticates root@localhost with the auth_socket plugin — the account has no password, and it can only be used over the local Unix socket. If Ansible connects over TCP instead, that first login fails and the playbook cannot bootstrap. Pointing the MySQL modules at the socket is what lets them log in as root the first time.

Never keep a real password in plain text like this. In a real playbook, pull mysql_root_password from Ansible Vault or an external secret store.

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 guide for the complete playbook).

Ensure required software 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
37
- name: Update apt repositories and cache on server
  ansible.builtin.apt:
    update_cache: yes
    force_apt_get: yes
    cache_valid_time: 3600

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

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

- name: Reboot the server if kernel updated
  ansible.builtin.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 MySQL server and supporting packages
  ansible.builtin.apt:
    name:
      - mysql-server
      - python3-pymysql
      - python3-cryptography
      - unzip
      - vim
      - git
    state: present

Two things worth calling out here:

python3-pymysql, not python3-mysqldb. The ansible.mysql modules require PyMySQL as their database connector. Older versions of this guide (and plenty of others still online) install python3-mysqldb; that driver is no longer what the modules expect, so install PyMySQL instead.
python3-cryptography is what PyMySQL uses for caching_sha2_password, the default authentication plugin in MySQL 8. You can get away without it when connecting over the local Unix socket, but installing it avoids a confusing authentication failure the moment anything connects over TCP.

The unzip, vim and git packages are just conveniences — drop them if you keep your database hosts minimal.

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

1
2
3
4
5
- name: Start and enable MySQL
  ansible.builtin.systemd_service:
    name: mysql
    state: started
    enabled: yes

ansible.builtin.systemd was renamed to ansible.builtin.systemd_service. The old name still works as an alias, so existing playbooks are not broken, but systemd_service is the canonical name to write today.

Initialize Mysql set up

With the server running, the remaining tasks harden the default install: set a root password, restrict root to local logins, remove the anonymous users and the test database. This is the same ground mysql_secure_installation covers, expressed as idempotent tasks so it can run repeatedly.

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
- name: Ensure root user can only login from localhost
  ansible.mysql.mysql_user:
    login_unix_socket: "{{ mysql_socket }}"
    login_password: "{{ mysql_root_password }}"
    check_implicit_admin: yes
    name: root
    host: "{{ item }}"
    password: "{{ mysql_root_password }}"
    state: present
  loop:
    - 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 loop section.
The check_implicit_admin tells ansible to try logging in without password first, which works on a fresh install because root@localhost still uses auth_socket. As part of this, the password provided will be set for the root user.
login_unix_socket forces that first login over the local socket, which is the only way auth_socket authentication succeeds.

Use loop rather than with_items. Both still work, but loop is the form Ansible recommends for simple lists, and with_* is effectively legacy.

Once this task has run, root@localhost no longer uses auth_socket — it has a real password and authenticates with caching_sha2_password. That is the trade you are making by managing the root password from Ansible: you gain a scriptable credential, and you lose the “only a local root shell can become MySQL root” property that auth_socket gave you.

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 every time 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
  ansible.builtin.copy:
    content: |
      [client]
      user=root
      password={{ mysql_root_password }}
    dest: /root/.my.cnf
    mode: "0600"

Quote the mode. Unquoted 0600 is parsed by YAML as the number 600, not as octal permissions — quoting it keeps the intent (-rw-------) unambiguous.

Remove anonymous users

It’s a good practice to remove anonymous users. Lets do it using this task:

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

Disallow root from logging in remotely

We want the root user to be usable locally only, so drop any root account defined for a remote host:

1
2
3
4
5
6
7
8
- name: Disallow root login remotely
  ansible.mysql.mysql_user:
    login_unix_socket: "{{ mysql_socket }}"
    login_user: root
    login_password: "{{ mysql_root_password }}"
    name: root
    host: "%"
    state: absent

Earlier versions of this guide did this with a raw DELETE FROM mysql.user ... statement. Don’t. MySQL’s documentation is explicit that the grant tables should not be modified directly — a DELETE removes the row but leaves the account cached in memory until a FLUSH PRIVILEGES, so the account you think you deleted can keep working. mysql_user with state: absent issues a proper DROP USER, which updates the tables and the in-memory cache together. It is also idempotent, which the raw DELETE was not.

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
- name: Remove test database
  ansible.mysql.mysql_db:
    login_unix_socket: "{{ mysql_socket }}"
    login_user: root
    login_password: "{{ mysql_root_password }}"
    name: test
    state: absent

The original guide paired this with a DELETE FROM mysql.db WHERE Db='test' query to strip the matching grants. That is the same direct-grant-table anti-pattern as above, and it is no longer needed: MySQL 8 does not create the test database, its grants, or the anonymous users in the first place. DROP DATABASE removes any database-level privileges along with the database, so the task above is sufficient. Keep it as a safety net for servers that were upgraded from MySQL 5.7 and may still carry those leftovers.

Reload privileges to apply changes

1
2
3
4
5
6
- name: Reload privileges
  ansible.mysql.mysql_query:
    login_unix_socket: "{{ mysql_socket }}"
    login_user: root
    login_password: "{{ mysql_root_password }}"
    query: "FLUSH PRIVILEGES"

FLUSH PRIVILEGES is only strictly required when you have edited the grant tables behind MySQL’s back. Now that every account change above goes through CREATE USER / DROP USER, MySQL keeps its in-memory cache in sync on its own, so this task is a belt-and-braces step rather than a necessity. It is harmless to keep.

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.cnf
  ansible.builtin.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
---
- name: Install and configure MySQL server on Ubuntu 22.04
  hosts: db-srv
  gather_facts: false
  become: true
  vars:
    mysql_root_password: 'superSecretPassword'
    mysql_socket: /var/run/mysqld/mysqld.sock
  tasks:
    - name: Update apt repositories and cache on server
      ansible.builtin.apt:
        update_cache: yes
        force_apt_get: yes
        cache_valid_time: 3600

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

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

    - name: Reboot the server if kernel updated
      ansible.builtin.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 MySQL server and supporting packages
      ansible.builtin.apt:
        name:
          - mysql-server
          - python3-pymysql
          - python3-cryptography
          - unzip
          - vim
          - git
        state: present

    - name: Start and enable MySQL
      ansible.builtin.systemd_service:
        name: mysql
        state: started
        enabled: yes

    - name: Ensure root user can only login from localhost
      ansible.mysql.mysql_user:
        login_unix_socket: "{{ mysql_socket }}"
        login_password: "{{ mysql_root_password }}"
        check_implicit_admin: yes
        name: root
        host: "{{ item }}"
        password: "{{ mysql_root_password }}"
        state: present
      loop:
        - localhost
        - 127.0.0.1
        - ::1

    - name: Add .my.cnf to user home
      ansible.builtin.copy:
        content: |
          [client]
          user=root
          password={{ mysql_root_password }}
        dest: /root/.my.cnf
        mode: "0600"

    - name: Remove anonymous users
      ansible.mysql.mysql_user:
        login_unix_socket: "{{ mysql_socket }}"
        login_user: root
        login_password: "{{ mysql_root_password }}"
        name: ''
        host_all: yes
        state: absent

    - name: Disallow root login remotely
      ansible.mysql.mysql_user:
        login_unix_socket: "{{ mysql_socket }}"
        login_user: root
        login_password: "{{ mysql_root_password }}"
        name: root
        host: "%"
        state: absent

    - name: Remove test database
      ansible.mysql.mysql_db:
        login_unix_socket: "{{ mysql_socket }}"
        login_user: root
        login_password: "{{ mysql_root_password }}"
        name: test
        state: absent

    - name: Reload privileges
      ansible.mysql.mysql_query:
        login_unix_socket: "{{ mysql_socket }}"
        login_user: root
        login_password: "{{ mysql_root_password }}"
        query: "FLUSH PRIVILEGES"

    - name: Delete .my.cnf
      ansible.builtin.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

Run it a second time to confirm it is idempotent: every task should report ok rather than changed, which tells you the playbook describes a settled state instead of re-applying work on every run.

You can then confirm the result on the database host:

1
2
mysql --version
sudo mysql -e "SELECT user, host, plugin FROM mysql.user;"

The SELECT should show root only for localhost, 127.0.0.1 and ::1 — no % row, and no empty-username anonymous accounts.

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.