0

I am trying to reset the root password for a mariadb database, followed every variation of tutorial to do so I've found so far, and every time I try to log in with the new password, it does not accept it.

Mainly, this is what I've been doing:

mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root -e 'use mysql; update user SET PASSWORD=PASSWORD("jkjkjkjjk"); flush privileges;'

I also tried adding an additional flush privileges; before the udpate command, removing it from the end as well, different variations for reset password, such as SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

I am using mariadb with galera, running on kubernetes statefulset, if relevant. version of mariadb is mysqld 10.3.21-MariaDB-1:10.3.21+maria~bionic

I am incredibly frustrated about this.

Tom Klino
  • 2,358
  • 5
  • 35
  • 60
  • arch specific [how to](https://wiki.archlinux.org/title/MariaDB#Reset_the_root_password), did you stop the service first before running `mysqld_safe` ?. – Adi Prasetyo Jun 04 '21 at 03:55

1 Answers1

1

what you have to do is the following:

stop the mysql service with something like:

sudo systemctl stop mariadb

then restart it

sudo mysqld_safe --skip-grant-tables &

login without a password

mysql -u root

update the password

use mysql;
 update user SET PASSWORD=PASSWORD("password") WHERE USER='root';
 flush privileges;
 exit

restart the db normally

sudo systemctl start mariadb

This assuming you are using a linux machine and you have access to the shell. Note that in your update query you are also missing the where clause so that you are updating ALL your users with the same password!

UPDATE 2022 - in case you face error 1348 "password is not updatable"

The solutions for MySQL Error1348 So you can not update a user password using Update commands, to update the password, use ALTER commands instead. like the following.

ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';

And then you need to do the flush privileges to commit previous queries (that alter command above) into the system, simply do like this.

flush privileges;

So now you have your root with password 1234.

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
  • As I mentioned, I have done exactly that, and it didn't work. Any ideas on why? The query says it executed successfully – Tom Klino Dec 20 '19 at 14:40
  • did you see my note in bold? you miss the where clause on the query. Did you also restarted the mysql instance after? – Lelio Faieta Dec 20 '19 at 14:42
  • I saw that, it was one of my previous variations but I tried it again just in case. I did restart it, though in a slightly different way since I'm using kubernetes and there is no systemd in the container – Tom Klino Dec 20 '19 at 14:44
  • The thing is, after performing the above, even if I try to log in using the new password while still in safe mode, it does not accept it – Tom Klino Dec 20 '19 at 14:46
  • and I just noticed this: `MariaDB [mysql]> select * from user; Empty set (0.000 sec)` – Tom Klino Dec 20 '19 at 14:49
  • if you have no users in the table then the query will update nothing at all but will obviously be successfull. if you login in safe mode you have to do it without the password because what happens in safe mode is that it doesn't check the user table (simplyfing) – Lelio Faieta Dec 20 '19 at 15:09
  • 1
    @TomKlino If you have no users in the `mysql.user` table you have to add users with the `CREATE USER` command, see https://dev.mysql.com/doc/refman/8.0/en/creating-accounts.html – Progman Dec 20 '19 at 17:12
  • Thank you for your help. The issue was in fact that the `user` table was empty. I don't know why this happened but I recreated it form our template and then reset the password. That fixed things – Tom Klino Dec 22 '19 at 11:40
  • I tried this, but mariadb gives me an error that 'password' is not updateable. – lurker Nov 29 '22 at 11:43