How do I set and save a random password for the MySQL/MariaDB root user using Ansible?

Question

How do I use Ansible to set a random (32 character alphanumeric) root password for a MariaDB / MySQL database and save it to the ~/.my.cnf file (to allow commands to find this password)?

It should only configure it once, and not change the password every time if the playbook is run multiple times.

This uses a password from a variable.

If I use this, it changes the password every time that the playbook is run: (and it fails to save the password - if the playbook is interrupted after this task, the password is lost)

- name: "Change database root user password"
  mysql_user:
    name: root
    password: "{{ lookup('password','/dev/null chars=ascii_letters,digits length=32') }}"
    host: "{{ item }}"
    check_implicit_admin: yes
    priv: "*.*:ALL,GRANT"
    state: present
  when: mysql_root_result.stat.exists == False
  with_items:
  - localhost
  - "::1"
  - 127.0.0.1

(It feels like this belongs on Server fault, but [meta disagrees](https://meta.stackoverflow.com/questions/294923/are-ansible-puppet-chef-salt-questions-on-topic)) — Gert van den Berg, May 28 '18 at 10:05

score 3 · Accepted Answer · answered May 28 '18 at 10:04

Some assumptions:

The root user is sufficiently covered by 'root'@'localhost' and other local root users needs to be removed
The tasks need to be failure tolerant. If interrupted, it should be possible to run again, as far as reasonably possible
The Ansible tasks runs as root - ~/.my.cnf expands to /root/.my.cnf
Everything else that needs the password can get it from ~/.my.cnf (All the normal tools does, as long as they run as the right user, root in this case)

Overview:

This generates the root password using this
It saves it to ~/.my.cnf.new (To ensure it is available on the server before it is set. This allows for (manual) recovery if it is interrupted)
It sets the password in the database
It renames the file (replicating mv's functionality in two tasks - a hardlink and delete) (link and unlink)
It cleans up any other equivalent users, like 'root'@'127.0.0.1', 'root'@'::1', 'root'@'<hostname>' (Using a few facts to ensure that most possibilities are hit)

Template for password config: ('templates/my_passwd.cnf.j2' relative to the role directory)

[client]
user={{ item.user }}
password={{ item.password }}

Tasks:

# MariaDB: Set up secure root password
# Set up (and save) secure root password
# Check for /root/.my.cnf
# All the other things are skipped if this file already exists
- name: "Check if we already have a root password config"
  stat:
    path: /root/.my.cnf
  register: mysql_root_result

# Generate password
# This uses https://docs.ansible.com/ansible/latest/plugins/lookup/password.html
# to generate a 32 character random alphanumeric password
- name: "Generate database root password if needed"
  set_fact:
    mysql_root_passwd: "{{ lookup('password','/dev/null chars=ascii_letters,digits length=32') }}"
  when: mysql_root_result.stat.exists == False

# Generate /root/.my.cnf.new
# A temporary file is used to keep it from breaking further commands
# It also ensures that the password is on the server if the critical
# parts are interrupted
- name: "Save new root password in temporary file"
  template:
    src: my_passwd.cnf.j2
    dest: /root/.my.cnf.new
    owner: root
    group: root
    mode: 0400
  when: mysql_root_result.stat.exists == False
  with_items:
  - user: root
    password: "{{ mysql_root_passwd }}"

# START of area that you don't want to interrupt
# If this is interrupted after the first task
# it can be fixed by manually running this on the server
# mv /root/.my.cnf.new /root/.my.cnf
# If the playbook is reran before that. The password would be lost!
# Add DB user
- name: "Add database root user"
  mysql_user:
    name: root
    password: "{{ mysql_root_passwd }}"
    host: "{{ item }}"
    check_implicit_admin: yes
    priv: "*.*:ALL,GRANT"
    state: present
  when: mysql_root_result.stat.exists == False
  with_items:
  - localhost

# Now move the config in place
- name: "Rename config with root password to correct name - Step 1 - link"
  file:
    state: hard
    src: /root/.my.cnf.new
    dest: /root/.my.cnf
    force: yes
  when: mysql_root_result.stat.exists == False
# END of area that you don't want to interrupt

# Interrupting before this task will leave a temporary file around
# Everything will work as it should though
- name: "Rename config with root password to correct name - Step 2 - unlink"
  file:
    state: absent
    path: /root/.my.cnf.new
  when: mysql_root_result.stat.exists == False

# Remove additional root users - these don't have the password set
# You might want to ensure that none of these variables are `localhost`
# All return somewhat different values on my test system
- name: "Clean up additional root users"
  mysql_user:
    name: root
    host: "{{ item }}"
    check_implicit_admin: yes
    state: absent
  with_items:
  - "::1"
  - 127.0.0.1
  - "{{ ansible_fqdn }}"
  - "{{ inventory_hostname }}"
  - "{{ ansible_hostname }}"

How do I set and save a random password for the MySQL/MariaDB root user using Ansible?

1 Answers1