Ubuntu 18.04, MySQL 5.7 and Ansible

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

VirtualBox on a MacBook running Ubuntu 18.04

Most annoying feature here is that Ubuntu 18.04 Desktop does not come with sshd running as standard. Yeah, I know I should have gone for the server install and I will get to that soonish, but I am using wireless connection on MacBook and Ubuntu server does not come with wireless network components installed. I know right, already turning into a nightmare! Add to this I opted for an osboxes.org vmi image compounded my issues as the VM appeared to hang on startup.

Issue 1 then, I try to do an apt upgrade and get the following error:

Reading package lists... Done
E: Could not get lock /var/lib/apt/lists/lock - open (11: Resource temporarily unavailable)
E: Unable to lock directory /var/lib/apt/lists/

The Ubuntu VM was doing automated (daily) unaccompanied updates. CPU was maxing out at 100%. Web searches indicated if this daily process was found then let it finish. 34 minutes later and I had enough of that. Toggle auto updates off, and then shutdown -r now to restart the box.

osboxes@osboxes:~$ sudo vi /etc/apt/apt.conf.d/20auto-upgrades
[sudo] password for osboxes:
osboxes@osboxes:~$ sudo cat /etc/apt/apt.conf.d/20auto-upgrades
APT::Periodic::Update-Package-Lists "0";
APT::Periodic::Unattended-Upgrade "0";
osboxes@osboxes:~$ shutdown -r now

Okay, update the VM manually and discover there is a compatibility issue with VirtualBox Guest Additions.

osboxes@osboxes:~$ sudo apt upgrade -y
Reading package lists... Done
Building dependency tree
Reading state information... Done
Calculating upgrade... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 virtualbox-guest-dkms-hwe : Conflicts: virtualbox-guest-dkms
E: Broken packages

This is probably why the automated updates was getting all in a tizzy. No obvious solution found so I just uninstalled the offending package. 5 mins later I have an updated VM and I can repeatedly stop/start and run apt upgrade on it.

Next task, need ssh. Back to apt then and install ssh server.

sudo apt install openssh-server

Finally I have a VM and I can run that 4 line playbook on it.