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

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.


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


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/

Tags:

Mysql

Recent Posts