Unable to reset mysql root password after fresh install mariadb-server

abhinanth_k · June 17, 2024, 12:31pm

Hi
I am unable to reset the root password after installing the most recent version of MariaDB Server.
I have added the credentials in manage node /root/.my.cnf
Kindly help me to resolve this issue.

chris · June 17, 2024, 12:37pm

Could you provide the task that generated the error?

You might also need to specify the socket path in the /root/.my.cnf file? For example:

[client]
user="root"
password="foobar"
socket="/run/mysqld/mysqld.sock"

abhinanth_k · June 17, 2024, 1:08pm

hi @chris

Please find the play which am trying to reset the MySQL

name: updating root password
community.mysql.mysql_user:
name: “{{ db_user }}”
password: “{{ db_pass }}”
login_unix_socket: /var/run/mysqld/mysqld.sock
register: root_user
- debug:
  var: root_user

chris · June 17, 2024, 1:29pm

If you SSH to the server and su - root can you run a command such as mysql -e "show databases" without errors?

Does the /root/.my.cnf file exist? I suspect that the “No such file or directory” error error might be related to /root/.my.cnf not existing?

If /root/.my.cnf does exist are the values it it correct, does mysql --defaults-file=/root/.my.cnf -e "show databases" work without an error?

Are you sure that the root user uses a password? On Debian and Ubuntu the system root user connects using the socket without a password by default these days.

In the past when I needed to set the MySQL / MariaDB root password I used the tasks here and now that all the servers I work on have either been updated or have had these tasks run on them to switch to using a socket I don’t have any password based root user authentication for MariaDB anywhere.

abhinanth_k · June 17, 2024, 2:15pm

Hi @chris

I am able to login and list from that DB server. Files /root/.my.cnf exist, MySQL does not have a password since i have not set any after installation, please have a look

chris · June 17, 2024, 2:23pm

Does something like this work?

- name: Update MySQL root password
  community.mysql.mysql_user:
    name: "{{ db_user }}"
    password: "{{ db_pass }}"
    login_user: root
    config_file: /root/.my.cnf
  register: root_user

- name: Debug root_user
  ansible.builtin.debug:
    var: root_user

Note that once you change the root password you will also need to update the /root/.my.cnf file, you can use the ini_file module for this since MySQL configuration files use the INI format.

abhinanth_k · June 17, 2024, 2:46pm

Hi @chris

While running the play, I got this error

name: updating root password
community.mysql.mysql_user:
name: “{{ db_user }}”
password: “{{ db_pass }}”
login_user: root
conf_file: /root/.my.cnf
login_unix_socket: /var/run/mysqld/mysqld.sock
register: root_user
- debug:
  var: root_user

"Unsupported parameters for (community.mysql.mysql_user) module: conf_file.

chris · June 17, 2024, 2:47pm

Sorry I made a typo, it should have been config_file.

abhinanth_k · June 17, 2024, 3:05pm

@chris am getting same error which i got earlier

"unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (2003, "Can’t connect to MySQL server on ‘localhost’

  - name: updating root password
    community.mysql.mysql_user:
      name: "{{ db_user }}"
      password: "{{ db_pass }}"
      login_user: root
      config_file: /root/.my.cnf
      login_unix_socket: /var/run/mysqld/mysqld.sock
    register: root_user
  - debug:
      var: root_user

chris · June 17, 2024, 3:20pm

If you are sure that the existing login details in /root/.my.cnf are correct then I think the task should work with config_file: /root/.my.cnf.

Perhaps you are not using a password, what does this return:

mysql -e "select plugin from user where User='root'" mysql

You could also test the details in /root/.my.cnf using something like this:

mysql -u root -p root007 -h localhost -e "show databases"

abhinanth_k · June 17, 2024, 4:22pm

@chris
I have tested the above command; it is working but from playbook, no changes same error

chris · June 17, 2024, 4:24pm

Can you check if a socket exists and what the path is, perhaps try something like this to find what, if anything it is set to?

grep -r socket /etc/mysql

abhinanth_k · June 18, 2024, 7:55am

@chris it shows no file or directory

chris · June 18, 2024, 9:06am

MySQL will have some configuration files somewhere even if they are not in /etc/mysql, can you use rpm to list the files the package installed to find where the config is?

Earlier you wrote:

Why do you want to set a root password in the first place? Is it becaiuse you want to enable remote rooot access? Generally you don’t need a root password to be set for MySQL / MariaDB if there is no remote access needed since the system root user uses socket authentication, earlier in this thread I ask you to check the auth plugin, did can you do this and post the results?

mysql -e "select plugin from user where User='root'" mysql

Also can you try moving /root/.my.cnf out of the way and then running this task?

- name: Update MySQL root password
  community.mysql.mysql_user:
    name: "{{ db_user }}"
    password: "{{ db_pass }}"
    login_user: root
    login_unix_socket: /var/run/mysqld/mysqld.sock
  register: root_user

- name: Debug root_user
  ansible.builtin.debug:
    var: root_user

abhinanth_k · June 18, 2024, 10:15am

Q. MySQL will have some configuration files somewhere even if they are not in /etc/mysql , can you use rpm to list the files the package installed to find where the config is?

Ans. The output of the rpm -ql mariadb

/etc/my.cnf.d/mysql-clients.cnf
/usr/bin/mariadb
/usr/bin/mariadb-access
/usr/bin/mariadb-admin
/usr/bin/mariadb-binlog
/usr/bin/mariadb-check
/usr/bin/mariadb-dump
/usr/bin/mariadb-find-rows
/usr/bin/mariadb-import
/usr/bin/mariadb-plugin
/usr/bin/mariadb-show
/usr/bin/mariadb-slap
/usr/bin/mariadb-waitpid
/usr/bin/msql2mysql
/usr/bin/mysql
/usr/bin/mysql_find_rows
/usr/bin/mysql_plugin
/usr/bin/mysql_waitpid
/usr/bin/mysqlaccess
/usr/bin/mysqladmin
/usr/bin/mysqlbinlog
/usr/bin/mysqlcheck
/usr/bin/mysqldump
/usr/bin/mysqlimport
/usr/bin/mysqlshow
/usr/bin/mysqlslap

Q. Why do you want to set a root password in the first place? Is it because you want to enable remote rooot access? Generally, you don’t need a root password to be set for MySQL / MariaDB if there is no remote access needed since the system root user uses socket authentication,

Ans. I want to restrict the root user by setting a password so no other user can use it without granting access.

Q. earlier in this thread I ask you to check the auth plugin, did can you do this and post the results?

Ans.

Q. Also can you try moving /root/.my.cnf out of the way and then running this task?

Ans.I have removed the /root/.my.cnf file from the manage node and am still getting the same error

fatal: [192.168.109.130]: 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: (2003, "Can’t connect to MySQL server on ‘localhost’ ([Errno 2] No such file or directory)")”}

Thanks @chris if not able to set the root password, i will set manually Thanks for great help and support

chris · June 18, 2024, 10:29am

I’ve not used RPM based distros for years so I’m afraid I’m unable to explain why your install appears to install no MariaDB server configuration files in /etc perhaps there are some files in addition to mysql-clients.cnf in /etc/my.cnf.d?

The default install of MariaDB, on Debian and Ubuntu, doesn’t set a root password because only the system root user is able to login as root so there is no need to set a password, however your system is set to use a password for root so perhaps RPM based distros don’t use sockets by default?

It will be possible for you to use Ansible to reset the the MariaDB root password, I’m sorry that I haven’t been able to help you achieve this goal so far, as I mentioned above this old version of my MariaDB role, which you can browse starting at this URL, has been used in the past to update the MariaDB root password on Debian and also it has been used to change the root authentication method to use a socket rather than a password, so I know that it is possible. These days I only use socket authentication for the root user.

abhinanth_k · June 18, 2024, 10:43am

thanks @chris for making me understand what was causing the problem.

chris · June 18, 2024, 11:27am

Incidently perhaps this will list the location of the MariaDB server files, including the configuration files?

rpm -ql mariadb-server

bvitnik · June 18, 2024, 3:17pm

It would be helpful if you specified what OS distro you are using and how you installed MariaDB - from MariaDB repos, from OS distro repo, from third party repo…

There is a huge difference in how MariaDB is installed and configured depending on distro and package maintainer. With proper info, we could reproduce the problem.

Don’t be surprised if you can’t use “community.mysql.mysql_user” module to change root password. root user in initial installation has all kinds of restrictions in place until you set new password but all that depends on distro and/or package maintainer decisions. It’s possible you would need to use “ALTER USER” query from shell instead.

From what I can gather, you probably don’t have socket configured so:

login_unix_socket: /var/run/mysqld/mysqld.sock

should be removed. You can try with:

host: 127.0.0.1

instead. Note that MySQL handles 127.0.0.1 and localhost differently where the first implies network connection while the last implies socket connection.

Without distro and repo info, we are currently playing a guess game unfortunately.