>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)
>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
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
- To check:
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.
>ENV
debian 10/9
10.x , 10.3.27-MariaDB
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;.
