How To Reset MySQL or MariaDB Root Password

 

In this tutorial we will discuss how to reset the root password of MySQL and MariaDB on Linux Cloud server.

 

cloud hosting services

 

1st Step: Identifying the Version of Database

Most of the latest distributions of Linux forward with either MySQL or MariaDB a well known drop-in replacement which is completely compatible with MySQL. Based on the database utilized and its version, you'll have to utilize diverse commands to recover the root/main password.

You can identify the version of database with the following command:

$ mysql --version

Now you will see result like this with MySQL:

MySQL result

mysql  Ver 12.12 Distrib 5.8.16, for Linux (x86_64) using  EditLine wrapper

Or result like this for MariaDB:

MariaDB result

mysql  Ver 14.1 Distrib 5.5.16-MariaDB, for Linux (x86_64) using readline 5.1

2nd Step: Stopping the Database Server

 

To modify the root password, you need to close down the database server beforehand.

You can easily perform this for MySQL by using:

$ sudo systemctl stop mysql

And for MariaDB by using:

$ sudo systemctl stop mariadb

Once the database server is halted, you'll get to it manually to reset the root password.

 

3rd Step: Restarting the Database Server Without Permission Verifying

On the off chance that you run MySQL & MariaDB without loading data about user privileges, it will enable you to get to the database command line with root privileges without giving a password. This will enable you to access the database without knowing it.

 

windows cloud server create

 

To perform this, you have to prevent the database from loading the grant tables, which store user privilege data. Since this is somewhat of a security hazard, you ought to skip networking also to keep different customers from interfacing.

Begin the database without loading the grant tables or empowering networking:

$ sudo mysqld_safe --skip-grant-tables --skip-networking &

The ampersand toward the finish of this command will influence this procedure to keep running out of sight so you can keep on using your terminal window.

Presently you can connect with the database as the root user, which ought not request a password.

$ mysql -u root

You will quickly observe a database shell prompt.

MySQL prompt

Enter 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

MariaDB prompt

Enter 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

 

Since you have root get to, you can modify the root password .

4th Step: Modifying the Root Password

One basic approach to change the root password for latest forms of MySQL is utilizing the ALTER USERcommand. Be that as it may, this command will not work at the present time in light of the fact that the give tables are not loaded.

We should advise the database Linux Cloud server to reload the grant tables by issuing the FLUSH PRIVILEGES command.

mysql> FLUSH PRIVILEGES;

 

linux cloud server create

 

Presently we can really modify the root password.

For the most recent version of MySQl including 5.7.6 as well as MariaDB’s most recent version including 10.2.20, utilize the given command.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_root_ password';

For MySQL version 5.7.5 and previous as well as MariaDB 10.1.20 and previous version utilize:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_root_password');

Don’t forget to replace new_root_password with your new password of selection.

In either case, you must see affirmation that the command has been effectively executed.

Result

Query OK, 0 rows affected (0.00 sec)

Since the password has been modified, so you would now be able to stop the manual instance of the database server and restart it as it was previously.

5th Step 5: Restart the Database Server Normally

To begin with, stop the instance of the database server that you began manually in 3rd Step. This command scans for the PID, or process ID, of MySQL or MariaDB process and sends SIGTERM to instruct it to exit easily in the wake of performing tidy up tasks

For MySQL, utilize:

$ sudo kill `cat /var/run/mysqld/mysqld.pid`
 

For MariaDB, utilize:

$ sudo kill `/var/run/mariadb/mariadb.pid`

At that point, restart the service utilizing systemctl.

For MySQL, utilize:

$ sudo systemctl start mysql
 

For MariaDB, utilize:

$ sudo systemctl start mariadb

At present you can affirm that the new password has been connected accurately by running:

$ mysql -u root -p

The command must now prompt for the recently alotted password. Put it, and you should access the database instant of course.