Ansible: How to change MySQL server root password by reprovisioning the server

Question

I have provisioned my server with Ansible playbook. I've used the root/bedrock-Ansible playbook.

One of the tasks was to set up mysql server together with mysql root user password.

Now I urgently need to change this password. The steps I took:

1. I updated variables for Ansible roles
2. I executed the command ansible-playbook -i hosts/staging server.yml in order to reprovision the server

All tasks were executed as expected (no changes), but the script failed at [mariadb | Set root user password] with this message:

msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials

My guess is that once MySQL root password has been set, reprovisioning the server cannot change this password.

Is it possible at all to change MySQL root password by reprovisioning the server with Ansible? What are my options?

Answer 1

The problem you have is that Ansible is trying to use the same root password to login as you want to change it to:

- name: Set root user password
  mysql_user: name=root
              host="{{ item }}"
              password="{{ mysql_root_password }}"
              check_implicit_admin=yes
              login_user="{{ mysql_user }}"
              login_password="{{ mysql_root_password }}"
              state=present

Obviously this is never going to work if you want to use this play to change it.

Instead you should change the above play to be something like:

- name: Set root user password
  mysql_user: name=root
              host="{{ item }}"
              password="{{ mysql_root_password }}"
              check_implicit_admin=yes
              login_user="{{ mysql_user }}"
              login_password="{{ mysql_old_root_password }}"
              state=present

And then update the relevant inventory files to add this new variable.

So your group_vars/production should now contain:

mysql_old_root_password: productionpw
mysql_root_password: newproductionpw

It looks like this playbook uses the root password in both the roles/mariadb/tasks/main.yml playbook and also roles/wordpress-setup/tasks/database.yml so you might want to run the whole server.yml playbook to make sure this is set up properly.

Answer 2

You can misuse ~/.my.cnf for being able to change the mysql-root-password.

The trick is to have a task "Set root password"(nr.1), which will set the password. Afterwards, you have a task, which creates a ~/.my.cnf with the correct credentials (nr.2).

On a new system, ~/.my.cnf is not present. Task nr.1 will create mysql-root-user with given credentials. On a present system, credentials from ~/.my.cnf are used to login and set password to mysql_root_password. Task nr.2 will create ~/.my.cnf, oroverwrite existing old credentials ~/.my.cnf with new ones.

The big advantage of this approach is having only one variable "mysql_root_password", which is always the correct one from a playbook's point-of-view. On the present system(s), ~/.my.cnf is kind of storage for current local mysql-credentials.

- name: Set root user password
  # If .my.cnf already exists, this will cause an mysql-root-password update.
  mysql_user:
    name: root
    password: "{{ mysql_root_password}}"
    check_implicit_admin: true

- name: Create .my.cnf
  template:
   src: "client.my.cnf.j2"
   dest: "/root/.my.cnf"
   owner: root
   group: root
   mode: 0600

with client.my.cnf.j2:

[client]
user=root
password={{ mysql_root_password }}

Further reading

Relevant notes from ansible-mysql_user_module-documentation:

• Note1:

  To secure this user as part of an idempotent playbook, you must create at least two tasks: the first must change the root user’s password, without providing any login_user/login_password details. The second must drop a ~/.my.cnf file containing the new root credentials. Subsequent runs of the playbook will then succeed by reading the new credentials from the file. ansible-mysql_user_module, notes

• Note2:

  Both login_password and login_user are required when you are passing credentials. If none are present, the module will attempt to read the credentials from ~/.my.cnf, and finally fall back to using the MySQL default login of ‘root’ with no password. ansible-mysql_user_module, notes

Answer 3

For the next person that comes looking for answers around here. While the accepted answer is true you have to be extra diligent if you're using MySQL 5.7 because there is no anonymous login allowed in mysqld in daemonized mode (service). Instead you MUST scrape the /var/log/mysqld.log for a TEMPORARY password that someone decided to create and use that on the login_password=ydaetskcoR. This was a feature they decided to implement on version 5.7 of the dev repository so if you want to avoid it use an older version (5.6).

Documentation here: https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_initialize-insecure

http://mysqlserverteam.com/initialize-your-mysql-5-7-instances-with-ease/

Answer 4

Take a look at this Ansible Module, it provides an easy and idempotent way for mysql_secure_installation in Ansible

---

Example - with a fresh MySQL Installation

- name: test mysql_secure_installation
  mysql_secure_installation:
    login_password: ''
    new_password: password22
    user: root
    login_host: localhost
    hosts: ['localhost', '127.0.0.1', '::1']
    change_root_password: true
    remove_anonymous_user: true
    disallow_root_login_remotely: true
    remove_test_db: true
  register: mysql_secure

# To see detailed output
- debug:
    var: mysql_secure

Example - Change an existing root password

- name: test mysql_secure_installation
  mysql_secure_installation:
    login_password: password22
    new_password: password23
    user: root
    login_host: localhost
    hosts: ['localhost', '127.0.0.1', '::1']

For usage: All you have to do is create a dir called library in your playbooks or role's dir and copy the mysql_secure_installation.py to it,

you can find a Full example in the following Link

https://github.com/eslam-gomaa/mysql_secure_installation_Ansible

Answer 5

There is an Ansible playbook used for hardening MySQL.

https://github.com/dev-sec/ansible-mysql-hardening

This not only changed the root password, but also performs some additional steps to harden the server.

Have a look at the readme file.