What is MySQL and MariaDB
MySQL and MariaDB - very popular management systems for SQL-queries based databases. Today there're using for websites, machine learning projects, development and so on.
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.
How to reset root password of MySQL and MariaDB services
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
Conclusion
Now you knew how to reset root password of MySQL or MariaDB service, why this operation could be needed and how to make each-day authorisation more comfortable.
