This process is handled entirely via the command line and works with MySQL or MariaDB installations. The Linux distribution used doesn’t matter as long as you have admin access. su or sudo.
See: A quick and easy guide to MySQL database engines
How to set a MySQL password for the first time
Please note that I will refer to MySQL with the assumption that everything will work for both MySQL and MariaDB.
Normally, during the installation of MySQL and MariaDB, you are asked to set an initial password. If not, you will need to set a password for the first time. To do this, open a Terminal window and issue the following command:
mysqladmin -u root password NEWPASSWORD
In this case, NEWPASSWORD The placeholder is the password. Next, when you log into MySQL with the command mysql -u root -pyou will be prompted to enter the newly configured password.
An alternative way to set a root password for the first time – one that adds a little extra to your password. MySQL database – To be used. mysql_secure_connection Commands This command will set the root user password and allow you to remove anonymous users, allow remote root logins, and remove 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 The new password used is Save this file as ~/mysql-pwd.
Next, 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 login to the MySQL command prompt with the new admin password like this:
mysql -u root -p
When prompted, type the admin password, and you’re good to go.
How to Change MySQL User Password
To change the password of a non-root user, once logged in, run the following query:
ALTER USER ‘username’@‘host’ IDENTIFIED BY ‘PASSWORD’;
where username MySQL username is, host is the host that the user can connect to, and PASSWORD Choose a new password.
Then apply the changes by running the command:
FLUSH PRIVILEGES;
How to Recover Your MySQL Password
What if you forgot your MySQL root user password? To recover the password, you just need to follow these steps:
- Stop the MySQL server process with the command
sudo service mysql stop - Start the MySQL server with the command
sudo mysqld_safe –skip-grant-tables –skip-networking & - Connect to the MySQL server as the root user with the command
mysql -u root
At this point, you need to issue 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 The new password used is
Restart the MySQL daemon with the command sudo service mysql restart. You should now be able to login to MySQL with the new password.
And that’s it. Now you can 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 create a list of MySQL users and passwords, say, for audit or backup credentials. You can do this by making a query. mysql.user table, but remember that passwords are stored in hashed 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 MySQL username is, Host is the host that the user can connect to, eg localhostand authentication_string is the hashed password.
Set a strong password for your MySQL root user.
I want to remind you how important it is to set a strong password for the MySQL root user. given Current state of attacks In an IT scenario, I highly recommend you use Strong passwords For your database. Instead of using an easily memorized password, use a random password generator and store it in a Password manager. Be safer than safe.
Fiona Jackson updated this article in January 2025.