
If you cannot access MariaDB root user for any of reasons, like “I forgot MariaDB password”, “I have not set the MariaDB root myself and need to reset it”, “I have installed MariaDB but no prompt asked me to set a root password” etc. We got you covered! 😉
Not to worry, here are the steps how to (re)set the root password MariaDB (MySQL alternative) on your Linux environment:
First step, check that you really run MariaDB and not MySQL:
mysql --version
You should see something like:
mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
First stop the service:
sudo systemctl stop mariadb
Start Maria DB service without permission checking
sudo mysqld_safe --skip-grant-tables --skip-networking &
Now, you can connect to the database as the root user, which would not require a password:
mysql -u root
You will receive this prompt:
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement
MariaDB [(none)]>
After mysql > prompt enter the line bellow and press the Enter key:
FLUSH PRIVILEGES;
Now, lets change the root password.
Use the following command for MySQL 5.7.6 or newer and MariaDB 10.1.20 or newer.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
replace new_password with your new password of choice.
If the above does not work try this command:
UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';
In either case, you should see confirmation that the command has been successfully executed:
Output
Query OK, 0 rows affected (0.00 sec)
Now lets restart MariaDB in normal mode. First stop the service:
sudo systemctl stop mariadb.service
or with:
sudo kill /var/run/mariadb/mariadb.pid
Now start the service normaly:
sudo systemctl start mariadb
And login with the command from below and use the new password when prompted:
mysql -u root -p
At this point you will regain the happiness 🥳 as you have regained access to your MariaDB installation.