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

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:

• I updated variables for Ansible roles

• 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?

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.

StackExchange Database Administrators Q#102066, answer score: 16