Knowing the MySQL/MariaDB root password of your server can come in handy in several cases such as accessing the database server console to manually create databases, create users and associate them with the databases, grant privileges to database users on specific databases, etc.
If you are unsure of the MySQL/MariaDB root user password, then the below steps will guide you on how to reset the root password safely. Resetting/changing the database server root password occasionally is also an important security factor, which helps protect against unauthorized intrusion attempts.
The below steps can be followed to reset the root password for MySQL & MariaDB respectively.
MySQL
Stop MySQL service using the below command.
# systemctl stop mysqld
Restart MySQL server in safe mode by executing the below commands.
# systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
# systemctl start mysqld
NOTE : --skip-grant-tables option allows you to connect to the service without a password and with all privileges.
Log in to MySQL server as the root user.
# mysql -u root
The below screenshot shows the entire process.
In the MySQL prompt, run the following command to set the new password for the root user.
# UPDATE mysql.user SET authentication_string = PASSWORD('NewPassword') WHERE User = 'root';
NOTE : Replace NewPassword in the above query with your own password.
Then execute the following queries.
# FLUSH PRIVILEGES;
# quit
Stop the MySQL service.
# systemctl stop mysqld
Unset the MySQL environment option.
# systemctl unset-environment MYSQLD_OPTS
Start MySQL normally.
# systemctl start mysqld
Confirm the working of the new password by executing the below command, which will prompt you to enter the new password.
# mysql -u root -p
MariaDB
Stop MariaDB service by executing the below command.
# systemctl stop mariadb
Run the below command to start the MariaDB service in safe mode.
# mysqld_safe --skip-grant-tables &
NOTE : --skip-grant-tables option allows you to connect to the service without a password and with all privileges.
The below output will be displayed once you execute the above command. Press enter to exit back to the command console.
Log in to the MariaDB server as the root user.
# mysql
In the MySQL prompt, run the following command to set the new password for the root user.
# UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
NOTE : Replace newpassword in the above query with your own password.
Then execute the following queries.
# FLUSH PRIVILEGES;
# exit
Stop the MariaDB service and exit the safe mode of MariaDB by executing the below command. You would be prompted to enter the password you set in the previous step.
# mysqladmin -u root -p shutdown
Start MariaDB service.
# systemctl start mariadb
Confirm the working of the new password by executing the below command, which will prompt you to enter the new password.
# mysql -u root -p
Related Tutorials