No escaping the fact that you need to know Voodo magic to install MySQL 5.7 on Ubuntu 18.04 from the base repositories using Ansible and expect to be able to connect to said databases. Fed up with:

root@ansible-target2:~# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Fed up with all the searching and gleaning snippets from everywhere, here is the minimal solution I came up with. No attempt to hide passwords or do any kind of configuration beyond what is needed to connect to the server as root and as a regular user.

In truth I was following along to a tutorial written for an older Ubuntu where things just worked, now they don’t.

The solution comprises of adding the MySQL base repository to the servers and installing the server from there. An initial .my.cnf is provided without a password and used to obtain a connection from which roots password is updated to the one we want to run with. Finally, a .my.cnf file is added with the desired root password – this step is of course optional if not wanting to keep root password in plain text on the machine.

First off the inventory file:

target1 ansible_host=192.168.1.198 ansible_user=osboxes ansible_ssh_pass=osboxes.org ansible_become=yes
target2 ansible_host=192.168.1.110 ansible_user=osboxes ansible_ssh_pass=osboxes.org ansible_become=yes

Next the playbook:

-
  name: Create a database
  hosts: target1, target2
  vars:
    mysql_root_password: my-secret-pw
    mysql_user_password: Passw0rd
  tasks:

    - name: Install required dependencies
      apt:
        name: "{{ item }}"
        state: present
      loop:
        - python
        - python-setuptools
        - python-dev
        - build-essential
        - python-pip
        - python-pymysql

    - name: Add MySQL repository key
      apt_key:
        keyserver: pgp.mit.edu
        id: 5072E1F5
        state: present

    - name: Add official MySQL 5.7 ubuntu bionic repository
      apt_repository:
        repo: "deb http://repo.mysql.com/apt/ubuntu bionic mysql-5.7"
        state: present

    - name: Install MySQL dependencies
      apt:
        name: "{{ item }}"
        state: present
      loop:
        - mysql-common
        - mysql-server
        - mysql-client

    - name: Start MySQL Service
      service:
        name: mysql
        state: started
        enabled: yes

    - name: Copy initial MySQL client config file, no password, for root
      copy:
        src: .my.cnf
        dest: /root/.my.cnf

    - name: update mysql root password for all root accounts
      become: true
      mysql_user:
        name: root
        host: "{{ item }}"
        password: "{{ mysql_root_password }}"
        login_user: root
        login_password: "{{ mysql_root_password }}"
        check_implicit_admin: yes
        priv: "*.*:ALL,GRANT"
      with_items:
        - 127.0.0.1
        - ::1
        - localhost
        - "{{ ansible_host }}"

    - name: Create `/root/.my.cnf` with root password credentials
      become: yes
      template:
        src:  my.cnf.j2
        dest: /root/.my.cnf
        owner: root
        mode: 0600

    - name: Create the MySQL database
      become: true
      mysql_db:
        name: "employee_db"
        state: present

    - name: Create database user
      become: true
      mysql_user:
        name: db_user
        password: "{{ mysql_user_password }}"
        priv: '*.*:ALL'
        state: present

.my.cnf:

[client]
user=root
password=
socket=/var/run/mysqld/mysqld.sock

.my.cnf.j2:

[client]
user=root
password={{ mysql_root_password }}
socket=/var/run/mysqld/mysqld.sock

And off we go:

ansible-playbook -i inventory.txt playbook.yaml

Connecting to the database as root on one of the machines:

root@ansible-target1:/home/osboxes# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> 

Connecting to the database as sample user:

root@ansible-target1:/home/osboxes# mysql -udb_user -pPassw0rd -hlocalhost employee_db
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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>

How did we get to this point? Lots of things were tried and many articles read, two that were of most use were this article on installing MySQL and when I tried it, it worked:

https://www.fosstechnix.com/install-mysql-5-7-on-ubuntu-18-04/

Next was this Server Fault solution:

https://serverfault.com/questions/891373/unable-to-create-mysql-database-through-ansible