MariaDB change root password

Introduction to MariaDB change root password

MariaDB provides a secure installation shell script designed for UNIX systems. A key aspect of MariaDB administration is changing the root password. You can establish a new root password, modify the existing one, or even remove the root account if it's accessible from locations other than localhost.

Maintaining a secure MariaDB environment is essential for safe data management. To access MariaDB securely, you must possess the root user's password. By default, MariaDB includes an anonymous user, allowing anyone to access the database without a specific account. However, this feature is primarily for testing purposes. From a security perspective, it's advisable to update or change the root password periodically to enhance system security.

Syntax

update user SET PASSWORD=PASSWORD("specified_password") WHERE USER='root';

Explanation

The provided syntax demonstrates how to update the root user's password in MariaDB. It involves using the "UPDATE" clause to modify the password for the root user. Specifically, the "SET PASSWORD" command is utilized to assign a new password to the root user, with the new password specified in the syntax. The "WHERE" clause is employed to identify and target the root user within the MariaDB server for this password change.

How to change the root password in MariaDB using various ways?

In the context of MariaDB, it's important to explore various methods for altering the root password. This becomes necessary when we encounter situations like forgetting or misplacing our passwords. Resetting the root password is the key to regaining access to both the MariaDB database and server.

To address this issue, there are several approaches to reset the password. In the following section, we will examine the steps to recover a forgotten password.

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 given example, we employ the update statement and a where clause to modify the root password. In this process, we utilize the "authentication_string" variable to set a new password. We also utilize the where clause to target the root user on the local environment, matching the hostname as demonstrated in the preceding example. The resulting outcome of this query is visually represented in the following snapshot.

After that, we need to flush all privileges by using the following command.

FLUSH PRIVILEGES;

Explanation

The "flush privileges" command is utilized to ensure the proper loading of the grant table. While loading the grant table, errors may sometimes occur. To prevent such issues and successfully update the root password, it is necessary to execute the "flush privileges" command. The resulting outcome of the query above is depicted in the following snapshot.

Conclusion

We trust that this article has provided you with a clear understanding of how to change the root password in MariaDB. Throughout this article, we have covered the fundamental syntax for changing the root password in MariaDB, and we've presented various examples of this process. By reading this article, you have gained insights into when and how to utilize MariaDB for altering the root password.