16

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?

16

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.

1
  • 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
11

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

6
  • 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
  • 1
    This solution is no more working with Ubuntu 20.04
    – Loenix
    Jun 29 '20 at 12:47
  • 2
    I got it. On Ubuntu 20.04 and MariaDb, you need to add: "login_unix_socket: '/var/run/mysqld/mysqld.sock'" to each call to mysql_* modules.
    – Palantir
    Aug 3 '20 at 12:54
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/

2

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

0
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.

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

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .