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