Chances are, you have MySQL running somewhere in your datacenter. If that's the case, there might be a time when you need to set or change the root user password. This can happen when you've forgotten the password or when you're looking to up your security game (remembering you set the original MySQL password to something far too simple).
As you might expect, the process is handled entirely through the command line and works with with either MySQL or MariaDB installations. The Linux distribution being used doesn't matter (as long as you have admin access, either by way of su or sudo).
A word of warning: Given the current state of attacks, across the landscape of IT, I highly recommend you using seriously strong passwords for your databases. Use a random password generator and then store that in a password manager (instead of using an easily memorized password). Be safer than safe.
With that said, let's get to work.
Setting the password for the first time
Do note, throughout this article, 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 asked to set an initial password. If, for whatever reason that didn't happen, you will need to set a password for the first time. To do that, open up a terminal window and issue the following command:
mysqladmin -u root password NEWPASSWORD
Where NEWPASSWORD is the password to be used. Now, when you log into MySQL, with the command mysql -u root -p, you will be prompted to enter the newly configured password.
An alternative method for setting the root password for the first time, one that also adds a bit of security to your MySQL database, is to use the mysql_secure_connection command. Not only will this command set the root user password, but it will allow you to remove anonymous users, disallow remote root login, and remove the test database. To use this command, simply type:
Answer the presented questions and your password will be set (as well as your database being a bit more secure).
SEE: How to set, change, and recover a MySQL root password (Tech Pro Research)
Changing the MySQL root user password
If you've set the password, and want to change it to something different (hint, hint ... more challenging), you can do that. This does require that you know the current password. With that password in hand, the command to change the root user password is:
mysqladmin -u root -p'OLDPASSWORD' password NEWPASSWORD
In the above command, there is no space between -p and 'OLDPASSWORD'. If you put a space between them, the command will fail.
After you issue that command, you will be prompted for your sudo (or administrator) password. This is not the MySQL root user password, but the system (or sudo) admin password.
Recover your MySQL password
What if you've forgotten your MySQL root user password? This could be quite the predicament ... had the developers not thought of that eventuality. In order to recover the password, you simply have 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 is the new password to be used.
Restart the MySQL daemon with the command sudo service mysql restart. You should now be able to log into MySQL with the new password.
And that's it. You can now set, reset, and recover your MySQL password.
SEE: Special report: The cloud v. data center decision (free PDF) (TechRepublic)
A second warning
Just to be certain, I wanted to remind you how important it is to set a very difficult password for the MySQL root user. Those databases hold sensitive information, so the last thing you need is to make use of a weak password that can be easily cracked. Use a long password that you cannot memorize and store it in a password vault. You'll be glad you did.
- How to deploy and use a MySQL Docker container (TechRepublic)
- How to set up a MariaDB Galera Cluster on Ubuntu 16.04 (TechRepublic)
- How to connect LibreOffice Base to a remote MySQL database (TechRepublic)
- How to install mycli for suggestion-based MySQL typing (TechRepublic)
- Python Network Programming Bundle (TechRepublic Academy)
Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website jackwallen.com.