Introduction to MariaDB change root password
MariaDB has a secure installation shell script that is available on the UNIX system. MariaDB change root password is a most important concept. We can set a new root password. We are also able to update the root password. We can delete the root account from the server if that is accessible from outside the localhost. When we need to login to MariaDB with a secure environment, for that purpose, we need the password for the root user. By default, a MariaDB has an anonymous user that means it is allowing everyone to login into the MariaDB without having any account, but this is only for testing purpose, but when we consider security point then it is not good, after some time or some days we need to update the password, or we can say that change the password for security purpose.
Syntax
update user SET PASSWORD=PASSWORD("specified_password") WHERE USER='root';
Explanation
In the above syntax, we use the update clause to change the root user’s password; here, we use the set password command to set a new password for the root user followed by a password as shown in the above syntax. A specified password means a user-defined password that we need to set the root user and where clause is used to match the root user from the MariaDB server.
How to change the root password in MariaDB using various ways?
Let’s see how we can change the root password by using different ways in MariaDB below:
Sometimes we forget passwords or lose passwords. At that time, we need to reset the root password to gain access to the MariaDB database and server.
There are different ways to reset the password as follows. First, we will see how we can recover the forgotten password, steps as follows:
1. First, we need to identify the database version. Most of the version of MariaDB is compatible with MySQL, but depending on the database version we need the different commands to identify the database version, as below.
mysql --version
Explanation
With the help of the above command, we see the current install version of MariaDB.
2. In the second step, we need to stop the database server. When we need to change the root password at that time, we must need to stop the database server by using the following command as follows.
systemctl stop mariadb
Explanation
After the execution of the above command, the database server will be stopped, and we can reset passwords manually for root users.
3. Restart the Database server without any permission. When we run MariaDB without any permission or privileges, then it will allow us to access the database with root privileges without any password or permission. So that reason, we need to stop the database, which stores the user information that is in the grant table. This can be done by using the command.
4. Change Root Password. After restarting the database server, we can change the root password by using the alter user command; sometimes, this command is not working due to the grant table loading problem, so we can load the grant table by using the FLUSH PRIVILEGES command.
Firstly, we can use the Alter user command in which we can directly change the root password by following the above-mentioned step.
In a second way, we can use an update clause to change the root password.
Examples
Let’s see different examples of changing root passwords as below.
1. By using the ALTER USER command.
In this method, we use the ALTER USER command to change the root password. In this type, we don’t need extra privileges to change the root password means we can directly change the password without any permission.
Syntax
alter user ‘root@localhost’ identified by ‘new specific password’;
Explanation
In the above syntax, we use an alter user command to change the root password. Here we mentioned the root user name with the local environment within a single quote followed by an identified keyword with the new password that we need to change shown in the above syntax.
Example
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123';
Explanation
With the help of alter user command, we successfully change the root password, here we mentioned the root user name with the local environment name, and we also use the identified by keyword with a new specified password here we mentioned a new password as root123, as shown in the above example. The final output of the above query we illustrate by using the following snapshot.
2. We can change the root password by using the Update command as below.
For the update command, we need to follow some steps as below.
First, we need to select the mysql database by using the following command.
use mysql
Explanation
With the help of the above command, we can change the database. The final output of the above query we illustrate by using the following snapshot.
Now use the update command to change the root password as below.
Example
UPDATE user SET authentication_string = PASSWORD('root12345') WHERE User = 'root' AND Host = 'localhost';
Explanation
In the above example, we use the update and where clause to change the root password, here we use the set authentication_string variable to assign a new password followed by where clause to match the root user on the local environment by using the hostname as shown in the above example. The final output of the above query we illustrate by using the following snapshot.
After that, we need to flush all privileges by using the following command.
FLUSH PRIVILEGES;
Explanation
The flush privileges command is used to load the grant table successfully; some errors may occur during the grant table’s loading. So to avoid this problem and change the root password successfully, we need to execute the flush privileges command. The final output of the above query we illustrate by using the following snapshot.
Conclusion
We hope from this article you have understood about the MariaDB change root password. From this article, we have learned the basic syntax of MariaDB change root password, and we also see different examples of MariaDB changing the root password. From this article, we learned how and when we use MariaDB to change root passwords.
Recommended Articles
This is a guide to MariaDB to change the root password. Here we discuss the basic syntax of MariaDB change root password, and we also see different examples of MariaDB changing the root password. You may also have a look at the following articles to learn more –
