MariaDB: Reset Root Password

Posted on By Andre Dias
database

PROBLEM

This procedure resets the MariaDB root password when it is not possible to access the database returning:

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

 

ENV

debian 10/9
10.x , 10.3.27-MariaDB


SOLUTION

– Stop database:
sudo systemctl stop mariadb

– Test:
sudo systemctl status mariadb

You shall get;
Status: “MariaDB server is down”
To return to console, use Ctrl+C

– This command shall return empty:
sudo pgrep mysqld

– Starting the database using safe mode:
sudo mysqld_safe –skip-grant-tables –skip-networking &
or
sudo mysqld_safe –skip-grant-tables &

“Enter” to return to prompt.

 

USING A TEMPORARY PASSWORD

– Entering the database prompt without user or password:
mysql -u root

use mysql;
flush privileges;
update user SET PASSWORD=PASSWORD(‘secret’) WHERE USER=’root’;
GRANT ALL PRIVILEGES ON root.* TO ‘root’@’localhost’ WITH GRANT OPTION;
flush privileges;
quit

– kill all mysqld processes
sudo pgrep mysqld

– Kill by the PID numbers returned, for example:
sudo kill -9 1234
sudo kill -9 4567

– Start the database:
sudo systemctl start mariadb

WHEN THE DATABASE FAILS TO START

 During the start, if it hangs, it is necessary to kill all processes.
It happens when some process is left behind, not being killed.
Unfortunately, sometimes when killing the processes doesn’t work because another new process starts just after, I just got solution rebooting the machine even though using killall, etc.
– To check:
sudo systemctl status mariadb

TESTING AND SWITCHING TO DEFINITIVE PASSWORD

– Access the database again to test the “secret” temporary password and also to change to the definitive password:

mysql -u root -p
pass: secret

use mysql;
flush privileges;
UPDATE mysql.user SET authentication_string = PASSWORD(‘MY_DEFINITIVE_PASSWORD’) WHERE User = ‘root’ AND Host = ‘localhost’;
GRANT ALL PRIVILEGES ON root.* TO ‘root’@’localhost’ WITH GRANT OPTION;
GRANT RELOAD ON *.* TO ‘root’@’localhost’;
flush privileges;
quit

 

– Access the database again to test the definitive password:
mysql -u root -p
pass: MY_DEFINITIVE_PASSWORD


***NOTE:

If after switching to the definitive password, the problem comes back denying access, it is because the characters used in the password caused the issue.
Avoid password like this:
Abc1234#-_ (the -_ didn’t work during my attempts)
For sure, use a password with just numbers and letters.
To get confidence, repeat the procedure.
If you desire to use special chars, you may try after making sure that you got success at least once, that way you may discover which special char to avoid in your password.

 

DIFFERENT BEHAVIORS DUE TO ENVIRONMENT DIFFERENCES

This procedure was executed several times successfully, but you may get some issue with some command shown above.
Below, there are some alternatives, not all of them of course, that you may try if some command fails.

set password for ‘root’@’localhost’ = password(‘secret’);
– or
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘secret’;

 

ADDITIONAL SOURCES

www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password

robbinespu.github.io/eng/2018/03/29/Reset_mariadb_root_password.html

 

ALTERNATIVE SITE

alsdias.blogspot.com/2020/12/mariadb-error-1045-28000-access-denied.html

 

@SYSREF:

y;reset root password<memo<mariadb;.