MySQL and MariaDB rank among the most popular relational database management systems (RDBMS) for managing SQL-based databases.They are commonly used in a variety of environments — from powering dynamic websites and web applications to supporting machine learning pipelines, data analytics platforms, and software development projects.
Thanks to their speed, reliability, and open-source nature, both systems remain a top choice for developers and system administrators worldwide.
Why password reset may be needed
The reasons are obvious - current password may be "dropped" after database service upgrade, you can accidentally forget it, your personal passwords storage could be lost etc.
Resetting the Root Password in MySQL and MariaDB
To set new database service root password (it is NOT "built-in" system root!) please follow rurther steps:
Login to your server as privileged user;
Stop the service;
service mysql stop # for MySQL
service mariadb stop # for MariaDB
Allow temporary "passwordless" authentication and start the service:
cat << EOF >> /etc/mysql/my.cnf
[mysqld]
skip-grant-tables
EOF
service mysql start
Authenticate into the database service and set the new password:
mysql
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStr0ngPassword';
exit
Remove skip-grant-tables directive from configuration file and restart the service:
service mysql stop; sed -i -e '$d' /etc/mysql/my.cnf && sed -i -e '$d' /etc/mysql/my.cnf; service mysql start
NOTE: I run sed -i -e '$d' twice cause need to delete two last file rows.
Checking and tuning
To check the result just run:
mysql -uroot -p<your_new_password>
As you see, authorization is successful. To make your further life more comfortable, it is possible to save this password to the special file in your home directory. It will allow you to authenticate without typing password each time:
cat << EOF >> ~/.my.cnf
[client]
user = root
password = <your_new_password>
EOF
All in all
Now you are familiar with the process of resetting the root password for MySQL or MariaDB, the common reasons this may be required, and techniques to simplify daily authentication. Regularly reviewing and updating your database credentials is a key part of maintaining a secure environment, especially after service upgrades or administrative changes.
To make ongoing management easier, consider storing credentials securely in a .my.cnf file with proper permissions or using a trusted password manager designed for database accounts. Additionally, always verify that services like MySQL or MariaDB are running correctly after changes, and keep backups of critical configuration files. Implementing these practices not only prevents accidental lockouts but also strengthens your overall database security and reliability for both development and production environments.
FAQ: Resetting Root Password in MySQL and MariaDB
- Q1: How do MySQL and MariaDB differ?
A: Both are open-source relational database systems using SQL, but MariaDB is a fork of MySQL with extra features, performance enhancements, and a fully open-source license. - Q2: Why might I need to reset the root password?
A: Situations include forgetting your password, losing credentials after a database upgrade, or accidental deletion of password storage. Resetting ensures secure administrative access. - Q3: Is the database root password the same as the server’s system root?
A: No, the root password for MySQL or MariaDB is separate and only grants administrative access to the database service. - Q4: Can I reset the root password without stopping the database?
A: No, you need to stop the service temporarily to allow passwordless authentication for the reset process. - Q5: How can I make future logins easier?
A: By saving your credentials in a .my.cnf file in your home directory, you can log in automatically without entering the password each time. Make sure file permissions are secure. - Q6: Are there risks when using skip-grant-tables?
A: Yes, it temporarily disables password protection, allowing anyone with server access to connect to the database. Always remove this setting immediately after resetting the password and restart the service. - Q7: What if I still can’t log in after resetting the password?
A: Check that the ALTER USER command was applied, skip-grant-tables is removed from the configuration, and the database service is restarted. Also, confirm you are using the correct credentials.
