This is quite alarming situation in infrastructure when we are not able to login as root in Database server. Database servers are very crucial and it should be very carefully managed. But sometimes there are unwanted situation when we can encounter such problems.

Today, while working on my POC setup I also encountered this problem that I can not login as root user in MariaDB server. Although it is a POC environment yet as an IT engineer we should be careful while handling the servers.

In this post, I will share you the solutions and some industrial best practices which will hopefully help you a lot.

Lets talk about first thing first. The Problem and its solution.

Forgot Password: Can not login as root in MySQL/MariaDB

In case you forgot or misplace the root password of your Database server (MySQL/MariaDB). To reset the root password in this situation follow the given below steps.

1. Make sure you have all the Database Server backup in place. If it is hosted in Cloud Virtual Machine, you can also take the VM image or snapshot of the disk (Depending upon the size of VM – smaller size of VM is much easy to decided to go for this plan).
2. Now we will perform some technical activities for resetting root password of the database server.
   • Stop the Service of MySQL or MariaDB Server.
   • Run the Service in Safe mode
   • Reset the root password
   • Kill the process of the service running in safe mode.
   • Start the Service of the Database server.

Stop the Service of MySQL or MariaDB Server.

Select the command which is applicable to your Database server type.

sudo systemctl stop mariadb   # This command is explicitly for MariaDB

sudo systemctl stop mysqld   # This command is explicitly for MySQL

Run the Service in Safe mode

sudo mysqld --skip-grant-tables

Check the mysql process id. It should be in running state.

ps -ef|grep mysql

Reset the root password

mysql -u root        # Login with root without password

use mysql;
update user SET PASSWORD=PASSWORD("YourNewPasswd") WHERE USER='root';
flush privileges;
exit;

Kill MySQL/MariadBD Process

Kill the process id of MySQL/MariaDB which we have run in safe mode.

_processId=$(sudo ps -ef|grep mysql| grep -v pts| awk '{print $2}')
sudo kill -9 $_processId

Start the Service of the Database server

Now start the MySQL/MariaDB service.

sudo systemctl start mariadb   # This command is for MariaDB

sudo systemctl start mysqld   # This command is for MySQL

Check root user login

Now we have reset the root password, check the mysql root user login.

mysql -u root -p

After installation root not able to login in MySQL/MariaDB

This kind of issue rarely happen but in this issue, you should make sure that you are installing the STABLE version of MySQL or MariaDB.

I had similar issue with MariaDB RC version which was not Stable one.

Try with Stable version and check is the problem resolved.

Best Practices For MySQL/MariaDB

It is always a good practices to safeguard your Database server. If this situation is in Production environment then situation become business critical.

1. Save the password in some Password Vault like Azure Keyvault, AWS Secret Manager, Keepass etc.
2.  Create secondary user with the same privileges as the root has.
3. Avoid sharing the human readable password in chat, email, sms or any medium.
4. Always take the regular Database backup.

Frequently Asked Question

How to avoid forgotten root password situation?

To avoid such situations, always keep your password in Keyvault. You should also create secondary user with same level of privileges which root has.

Why have you suggested for regular backup whereas it does not relate to password?

We should also take regular backup of any server. If in case, you are not able to reset the password. Then as a plan B you can setup a new Database server. Always remember, the Database server is very important and very sensitive.

Do we need downtime when we try to reset forgotten root password in MySQL/MariaDB?

The MySQL/MariaDB Server should not be available for any writing during the resetting root password period. Whereas to run MySQL/MariaDB in safe mode, we have to stop the normal service hence it will be unavailable for that moment. The term downtime generally we use in business related servers and it means the Server is not available for certain period of time. If it is your own POC or Test environment then without downtime also you can perform this activity as no business is involved here.

Conclusion

In this post, we have learned how to reset the forgotten root password of MySQL and MariaDB. We have also learned the best practices to safeguard the root password and avoid worst situation.