Ansible and the problem to reset the MySQL root password (unable to connect to database, check login_user and login_password are correct)

Written by Claudio Kuenzler - 2 comments

Published on August 18th 2022 - Listed in Ansible Linux MySQL MariaDB Databases

---

Ansible has a neat feature to manage databases and users (including privileges) on MySQL, MariaDB and Percona servers. With the mysql_user module, the default password of the "root" user (which is empty on a MariaDB install on Debian) can be changed with a random password:

[...]
become: yes
become_user: root
tasks:
[...]
  - name: MYSQL - Create root password
    shell: /usr/bin/pwgen 15 1
    register: mysql_root_password
    when: rootcnf.stat.exists == false

  - name: MYSQL - Change root user password
    mysql_user:
      name: root
      password: "{{ mysql_root_password.stdout }}"
    when: mysql_root_password is defined and rootcnf.stat.exists == false
[...]

However there's a problem with this task when actually running the playbook (Ansible 2.10 on Debian Bullseye):

TASK [MYSQL - Change root user password] *****************************************************************************
fatal: [mariadb1]: FAILED! => {"changed": false, "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"}

The reason is that Debian and Ubuntu are by default using a MySQL socket authentication (without password) for the root user. The mysql_user module is however assuming it needs to connect to "localhost" (using tcp) as this is the default value of the login_host parameter.

This can be verified using a non-root user:

ck@mariadb1:~$ mysql -u root -h localhost
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Even though the Ansible playbook is told to use sudo (become), the error in the playbook output is the same.

To overcome this, the mysql_user module needs to be told to use login_unix_socket instead:

   # Run mysql_secure_installation
  - name: MYSQL - Change root user password
    mysql_user:
      name: root
      password: "{{ mysql_root_password.stdout }}"
      login_user: root
      login_unix_socket: /var/run/mysqld/mysqld.sock
    when: mysql_root_password is defined and mycnf.stat.exists == false

With this change, the task is now correctly executed:

[...]
TASK [MYSQL - Change root user password] *****************************************************************************
changed: [mariadb1]
[...]

Of course subsequent tasks related to MySQL need to be adjusted for the (now) defined root password.

Add a comment

Show form to leave a comment

Comments (newest first)

Sir Jane from wrote on Oct 24th, 2024:

This is the kind of error that can drive you crazy. It appeared on one system for me but not on another. Thank you for this.

---

Jacob from wrote on Apr 23rd, 2024:

You don't know how grateful I am for this.

---