If you’ve never set up, forgotten, or need to change your MySQL password, you’re in luck. This article walks you through those steps, so you’ll never run out of that database root user password.
Chances are you have MySQL running somewhere in your data center. If so, there may come a time when you need to set or change the root user password. This can happen when you’ve forgotten your password, or when you’re trying to up your security game (remembering that you set the original MySQL password to something too simple).
As you might expect, the process is handled entirely via the command line and works with MySQL or MariaDB installations. Your Linux distribution doesn’t matter as long as you have administrator access via su or sudo.
SEE: A fast and furious guide to MySQL database engines (TechRepublic)
A word of warning: Given the current state of attacks, across the IT landscape, I strongly recommend using really strong passwords for your databases. Instead of using an easy-to-memorize password, use a random password generator and then store it in a password manager. Be safer than sure.
With that said, let’s get to work.
How to set MySQL password for the first time
Note, I’m going to reference MySQL with the idea that everything will work for both MySQL and MariaDB.
Typically, you are prompted to set an initial password when installing MySQL and MariaDB. If, for whatever reason, this has not happened, you will need to set up a password for the first time. To do this, open a terminal window and issue the following command:
mysqladmin -u root password NEWPASSWORD
Where NEWPASSWORD is the password to use. Now, when you log into MySQL, with the mysql -u root -p command, you will be asked to enter the password you just configured.
An alternative method for setting the root password for the first time, which also adds some security to your MySQL database, is to use the mysql_secure_connection command. This command will not only set the root user password, but will remove anonymous users, disallow remote root access, and remove the test database. To use this command, simply type:
Answer the questions presented and your password will be set, making your database a little more secure.
SEE: Password Management Policy (TechRepublic Premium)
How to change MySQL root user password
To reset the password for MySQL you must first create a new file with the following contents:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';
Where PASSWORD is the new password to use. 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 the command prompt returns, restart the MySQL daemon with the command:
sudo systemctl start mysql
You should now be able to access the MySQL command prompt with the new admin password like so:
mysql -u root -p
When prompted, type in your admin password and you’re good to go.
How to recover your MySQL password
What if you forgot your MySQL root user password? This could be a difficult situation if the developers hadn’t thought about this eventuality. To recover your password, simply 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 MySQL server as 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;
Where NEWPASSWORD is the new password to use.
Restart the MySQL daemon with the command restarting sudo mysql service. You should now be able to log into MySQL with the new password.
And this is all. You can now set, reset and recover your MySQL password.
Set a strong password for your MySQL root user
Just to be sure, I wanted to remind you how important it is to set a strong password for the MySQL root user. Those databases contain sensitive information, so the last thing you need is to use a weak password that can be easily cracked. Use a long password that you can’t remember and store it in a password vault. You’ll be glad you did.
TechRepublic Academy Password Manager Offers
These TechRepublic Academy password managers will keep your information safe: