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
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
Now, you have to stop the database server. To do this, run these commands:
- If you are using MariaDB:
:~$ su :~# systemctl stop mariadb
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 &
Now, login into MySQL or MariaDB.
:~# mysql
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;
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
Now, restart the service.
- For MariaDB:
:~# systemctl start mariadb
- If you are using MySQL:
:~# systemctl start mysql
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.
