14

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?

migrated from stackoverflow.com May 20 '15 at 17:27

This question came from our site for professional and enthusiast programmers.

14

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.

  • Thanks so much for a great answer. This is definitely the way to go. I ended up resetting the root password in the console with mysqladmin - but this was before I saw your answer – luqo33 May 21 '15 at 9:34
7

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

  • I quite like this approach and it's much better than the version in my answer. It should probably be the accepted answer. – ydaetskcoR Apr 27 '16 at 18:47
  • 2
    This is handy, but on many systems there are actually 4 'root' users created, with Hosts 127.0.0.1, localhost, ::1, and whatever the local hostname is. The above only modifies root@localhost, leaving three other root accounts with blank passwords. – robo Jun 9 '17 at 21:57
  • List all root users: mysql --database mysql --execute "select host from user where user = 'root';". This post does the same as this answer but has code for setting all passwords. – hlovdal Sep 26 '18 at 21:23
2

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/

1

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.

  • Doesn't look like it handles changing the root password when it is blank. – flickerfly Aug 30 '17 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.