You may have MySQL running somewhere in your data center. If that's the case, there may be a time when you need to set or change the root user's password. This can happen when you forgot the password or when you search for your security game after remembering that you set the original MySQL password to something too simple.
The process is handled entirely via the command line and works with MySQL or MariaDB installations. The Linux distribution used does not matter as long as you have administrator access via su either sudo.
SEE: A fast and furious guide to MySQL database engines
How to set MySQL password for the first time
Please note that I will refer to MySQL with the idea that everything will work for both MySQL and MariaDB.
Typically, during the installation of MySQL and MariaDB, you are prompted to set an initial password. If that didn't happen, you will need to set a password for the first time. To do that, open a terminal window and run the following command:
mysqladmin -u root password NEWPASSWORD
In this case, NEWPASSWORD is the placeholder password. Then when you log into MySQL with the command mysql -u root -pYou will be prompted to enter the newly set password.
An alternative method to set the root password for the first time (which adds a bit of security to your MySQL database) is to use the mysql_secure_connection domain. This command will set the password for the root user and allow you to delete anonymous users, disallow remote login as root, and delete the test database. To use this command, simply type:
mysql_secure_connection
Answer the questions presented and your password will be set, making your database a little more secure.
SEE: Password management policy
How to change MySQL root user password
To reset the MySQL password you must first create a new file with the following content:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';
PASSWORD is the new password that will be used. Save that file as ~/mysql-pwd.
Then stop the MySQL daemon with the command:
sudo systemctl stop mysql
With the daemon stopped, issue the command:
sudo mysqld -init-file=~/mysql-pwd
Once your command prompt returns, restart the MySQL daemon with the command:
sudo systemctl start mysql
You should now be able to log in to the MySQL command prompt with the new administrator password like this:
mysql -u root -p
When prompted, enter the administrator password and you're good to go.
How to change MySQL user password
To change the password for a non-root user, once logged in, run the following query:
ALTER USER ‘username’@‘host’ IDENTIFIED BY ‘PASSWORD’;
Where username is the MySQL username, host is the host from which the user can connect, and PASSWORD is the new password of choice.
Then apply the changes by running the command:
FLUSH PRIVILEGES;
How to recover your MySQL password
What if you have forgotten your MySQL root user password? To recover the password you simply have to follow these steps:
- Stop the MySQL server process with the command
sudo service mysql stop - Start MySQL server with the command
sudo mysqld_safe –skip-grant-tables –skip-networking & - Connect to MySQL server as root user with command
mysql -u root
At this point, you need to run the following MySQL commands to reset the root password:
mysql> use mysql;mysql> update user set authentication_string=password('NEWPASSWORD') where user="root";mysql> flush privileges;mysql> quit
Where NEWPASSWORD is the new password that will be used.
Restart the MySQL daemon with the command sudo service mysql restart. You should now be able to log in to MySQL with the new password.
And that's all. You can now set, reset and recover your MySQL password.
SEE: How to query multiple tables in SQL
How to show MySQL users and passwords
Occasionally, you may want to generate a list of MySQL users and passwords to, for example, audit or backup credentials. You can do this by consulting the mysql.user table, but note that passwords are stored in hash format, so they cannot be retrieved directly in plain text.
Run the following query to retrieve the user and password columns:
SELECT User, Host, authentication_string FROM mysql.user;
Where User is the MySQL username, Host is the host from which the user can connect, such as localhostand authentication_string is the hashed password.
Set a hard password for your MySQL root user
I want to remind you how important it is to set a hard password for the MySQL root user. Given the current state of attacks in the IT landscape, I strongly recommend that you use strong passwords for your databases. Instead of using an easy-to-memorize password, use a random password generator and save it in a password manager. Be safer than safe.
Fiona Jackson updated this article in January 2025.
