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

**URL:** <https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596>\
**Category:** Get Help\
**Tags:** mysql, mariadb\
**Created:** [June 17, 2024, 12:31pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596 "2024-06-17T12:31:51Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![abhinanth\_k](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/abhinanth_k/32/3777_2.png) [@abhinanth\_k](https://forum.ansible.com/u/abhinanth_k)\
**Post date:** [June 17, 2024, 12:31pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/1 "2024-06-17T12:31:51Z")

</div>

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.

![image](https://us1.discourse-cdn.com/ansible/original/2X/a/aecfc74f47b3ee453bc3314fb4c09a37b3ac5528.png)

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 17, 2024, 12:37pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/2 "2024-06-17T12:37:32Z")

</div>

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:

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

```

---

<div class="post-metadata">

**Author:** ![abhinanth\_k](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/abhinanth_k/32/3777_2.png) [@abhinanth\_k](https://forum.ansible.com/u/abhinanth_k)\
**Post date:** [June 17, 2024, 1:08pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/3 "2024-06-17T13:08:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 17, 2024, 1:29pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/4 "2024-06-17T13:29:22Z")

</div>

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](https://git.coop/webarch/mariadb/-/blob/6fe042d2e9bb31beb828c987f092ae79592c87dc/tasks/mariadb_root_password.yml) 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](https://git.coop/webarch/mariadb/-/blob/6fe042d2e9bb31beb828c987f092ae79592c87dc/tasks/mariadb_root_socket.yml) I don’t have any password based `root` user authentication for MariaDB anywhere.

---

<div class="post-metadata">

**Author:** ![abhinanth\_k](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/abhinanth_k/32/3777_2.png) [@abhinanth\_k](https://forum.ansible.com/u/abhinanth_k)\
**Post date:** [June 17, 2024, 2:15pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/5 "2024-06-17T14:15:28Z")

</div>

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

 ![mysql-root-user](https://us1.discourse-cdn.com/ansible/original/2X/f/f29753ffd554aabee6f9766b2b843182d1e10ce9.png)

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 17, 2024, 2:23pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/6 "2024-06-17T14:23:15Z")

</div>

Does something like this work?

```yaml
- 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](https://docs.ansible.com/ansible/latest/collections/community/general/ini_file_module.html) for this since MySQL configuration files use the INI format.

---

<div class="post-metadata">

**Author:** ![abhinanth\_k](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/abhinanth_k/32/3777_2.png) [@abhinanth\_k](https://forum.ansible.com/u/abhinanth_k)\
**Post date:** [June 17, 2024, 2:46pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/7 "2024-06-17T14:46:42Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/ansible/original/2X/8/8999f1527337faf548b3c1012b68b397e758add5.png)

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 17, 2024, 2:47pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/8 "2024-06-17T14:47:53Z")

</div>

Sorry I made a typo, it should have been [`config_file`](https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_user_module.html#parameter-config_file). 🙄

---

<div class="post-metadata">

**Author:** ![abhinanth\_k](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/abhinanth_k/32/3777_2.png) [@abhinanth\_k](https://forum.ansible.com/u/abhinanth_k)\
**Post date:** [June 17, 2024, 3:05pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/9 "2024-06-17T15:05:30Z")

</div>

@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

```

 ![image](https://us1.discourse-cdn.com/ansible/original/2X/f/f18f6c8b5efc0d5e1f938eebb378bf42c0acde1a.png)

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 17, 2024, 3:20pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/10 "2024-06-17T15:20:38Z")

</div>

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:

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

```

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

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

---

<div class="post-metadata">

**Author:** ![abhinanth\_k](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/abhinanth_k/32/3777_2.png) [@abhinanth\_k](https://forum.ansible.com/u/abhinanth_k)\
**Post date:** [June 17, 2024, 4:22pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/11 "2024-06-17T16:22:20Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/ansible/original/2X/3/3b47ad03a7ab6f1c1579c70f2f41ce7a1d863185.png)

```auto

`
```

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 17, 2024, 4:24pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/12 "2024-06-17T16:24:42Z")

</div>

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?

```yaml
grep -r socket /etc/mysql

```

---

<div class="post-metadata">

**Author:** ![abhinanth\_k](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/abhinanth_k/32/3777_2.png) [@abhinanth\_k](https://forum.ansible.com/u/abhinanth_k)\
**Post date:** [June 18, 2024, 7:55am UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/13 "2024-06-18T07:55:55Z")

</div>

@chris it shows no file or directory

![image](https://us1.discourse-cdn.com/ansible/original/2X/0/0f3488959fbfe46a6191e1ad4b37a0ae9402b6df.png)

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 18, 2024, 9:06am UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/14 "2024-06-18T09:06:07Z")

</div>

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](https://linuxconfig.org/how-to-list-all-files-installed-by-rpm-package) to find where the config is?

Earlier you wrote:

> [@abhinanth\_k](#):
>
> MySQL does not have a password since i have not set any after installation

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?

```bash
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?

```yaml
- 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

```

---

<div class="post-metadata">

**Author:** ![abhinanth\_k](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/abhinanth_k/32/3777_2.png) [@abhinanth\_k](https://forum.ansible.com/u/abhinanth_k)\
**Post date:** [June 18, 2024, 10:15am UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/15 "2024-06-18T10:15:15Z")

</div>

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](https://linuxconfig.org/how-to-list-all-files-installed-by-rpm-package) 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.

 ![image](https://us1.discourse-cdn.com/ansible/original/2X/b/beae2cd49f16677b1fa672c1440e300310f7df70.png)

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

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 18, 2024, 10:29am UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/16 "2024-06-18T10:29:09Z")

</div>

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

> [@abhinanth\_k](#):
>
> I want to restrict the root user by setting a password so no other user can use it without granting access.

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](https://git.coop/webarch/mariadb/-/releases/2.4.2), which you can browse starting at [this URL](https://git.coop/webarch/mariadb/-/tree/6fe042d2e9bb31beb828c987f092ae79592c87dc), 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.

---

<div class="post-metadata">

**Author:** ![abhinanth\_k](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/abhinanth_k/32/3777_2.png) [@abhinanth\_k](https://forum.ansible.com/u/abhinanth_k)\
**Post date:** [June 18, 2024, 10:43am UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/17 "2024-06-18T10:43:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 18, 2024, 11:27am UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/18 "2024-06-18T11:27:44Z")

</div>

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

```bash
rpm -ql mariadb-server

```

---

<div class="post-metadata">

**Author:** ![bvitnik](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/bvitnik/32/4132_2.png) [@bvitnik](https://forum.ansible.com/u/bvitnik)\
**Post date:** [June 18, 2024, 3:17pm UTC](https://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/19 "2024-06-18T15:17:49Z")

</div>

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:

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

```

should be removed. You can try with:

```yaml
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.