This article will come in handy if you have forgotten your MariaDB root password. If you are setting up a MySQL or MariaDB database server for the first time, chances are you will be running mysql_secure_installation soon afterward to implement basic security settings.
One of these settings is the password for the database root account – which you must keep private and use only when strictly required. If you forget the password or need to reset it (for example, when a database administrator changes roles).
Let’s follow this step by step tutorial to reset MariaDB root password on your Linux systems.
First of all, you need to stop MariaDB service on your system. You can do this using the following command.
sudo systemctl stop mysql.service
Now start your MariaDB service in safe mode using skip grant and run this command in the background.
mysqld_safe --skip-grant-tables &
After starting MariaDB in safe more connect to MariaDB with root user and without any password. After that run following command to change the root user password.
mysql -u root
> use mysql; > UPDATE user SET authentication_string = password("SecreT") where User='root'; > FLUSH PRIVILEGES;
After successfully changing the password, stop the MariaDB service and start it again in normal mode using the following commands.
sudo systemctl stop mysql.service sudo systemctl start mysql.service
You have now created a new root user password for your MariaDB/My instance. Login to MariaDB instance to verify the new password.
mysql -u root -p
Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1 Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>