How to reset the MySQL / MariaDB root password?

1
366

Making mistakes is human, but correcting is wise, at least that is what a saying says here where I live. It is possible that we have so much information and issues to handle that we forget our root password MySQL / MariaDB. It can not even be this scenario either, let us say the sysadmin had an incident and did not share the key. Imagine that, you need to make a change in the database or assign permission and you can not. Of course, it can also be a less serious scenario, you are learning about MySQL or MariaDB and you did not save it. I do not want to be pessimistic, but it can happen. That is why today I will show you how to reset your MySQL or MariaDB root password using CentOS 7.

1. How to define a root password for MySQL or MariaDB

Sure, you are wondering why I am starting with this step. Believe it or not, many instances of MySQL or MariaDB are not assigned a root password. This happens mainly, in small and almost domestic environments, where some security concessions can be made. Obviously, I do not recommend this for anything in the world, but it happens and that is what I want to avoid.

After installing MySQL or MariaDB, if you run the mysql_secure_installation script with root permission, you’ll be able to define a root password in case you don’t have it, so run it and try it.

:~$ su
:~# mysql_secure_installation
1.- Running the mysql_secure_installation script
1.- Running the mysql_secure_installation script

Again, with this I want you to be sure that it is necessary to reset the password.

2. Resetting the root password

Before I start, I will make this post using CentOS 7 and MariaDB. However, I will leave you the instructions for MySQL.

First of all, it is good to check the version of MySQL or MariaDB version. To know what we are working with.

:~$ mysql --version
2.- Mysql or MariaDB version
2.- Mysql or MariaDB version

Now, you have to stop the database server. To do this, run these commands:

  • If you are using MariaDB:
:~$ su
:~# systemctl stop mariadb
3.- Stop the service
3.- Stop the service

Or, if you are using MySQL:

:~$ su
:~# systemctl stop mysql

Now it is necessary to make MySQL or MariaDB start in a different mode than normal. In other words, we need MySQL or MariaDB to allow us to access without taking into account table permissions and network properties. To do this, run:

:~# mysqld_safe --skip-grant-tables --skip-networking &
4.- Running Mysql on a safe mode
4.- Running Mysql on a safe mode

Now, login into MySQL or MariaDB.

:~# mysql
5.- Log in into Mysql or MariaDB shell
5.- Login into Mysql or MariaDB shell

Now it is your turn to reset the MySQL or MariaDB root password.

UPDATE mysql.user SET password=password('newpassword') WHERE user='root';
FLUSH PRIVILEGES;
6.- Reset the root password
6.- Reset the root password

At the end of the root password reset process, it is necessary to return MySQL or MariaDB to normal. First, stop the mysqld_safe command.

:~# mysqladmin -u root -p shutdown
7.- Stop the safe mode
7.- Stop the safe mode

Now, restart the service.

  • For MariaDB:
:~# systemctl start mariadb
  • If you are using MySQL:
:~# systemctl start mysql
8.- Start the service
8.- Start the service

And that’s it.

A few words to end

These steps, you should not be able to run them on a production server. Someone could take advantage of all this and steal the data.

Therefore, it is necessary that you establish the rules that you consider necessary to avoid this. For example, you can disable the root account or filter connections by IP address.

Conclusion

These tricks are always useful to have available because we don’t know when we could use them. As you can see it is something simple, but in many occasions, it can save us from a hurry.

So, share this post with your friends.

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here