Alright, so you’ve created a new MySQL Database, you’ve added a user, even granted permissions to the user. Yet, that user forgot the password to their MySQL user account. How do you change the user’s password? There are several ways to do this, one of which we can do without even entering the MySQL command console. This quick tutorial will show you three separate ways to change a MySQL user password
Syntax for changing password
The first thing we need to get right is the different types of syntax/commands that we can use to change the MySQL user password. What’s important to understand from this concept is that the password and user are also stored in MySQL tables. So one method is as simple as updating the table where the row=user.
Update user row with new password
MySQL > UPDATE mysql.user SET Password=PASSWORD(‘text password’) WHERE user=”username” AND Host=”hostname”;
What are the important takeaways from the command above?
- We are updating the user table in the MySQL Database.
- We are setting the Password field equal to PASSWORD(‘text password’)
- PASSWORD(‘text password’) function automatically encrypts the mysql password for us.
- We are updating the password field where user=”username” and host=”hostname”
- You must specify the hostname because MySQL can have multiple users with the exact same username but separate hostnames.
Command line method number 2
MySQL > SET PASSWORD FOR ‘anthony’@'localhost’ = PASSWORD(‘text password’);
What are the important takeaways from this command?
- Using SET PASSWORD command as part of mysqladmin function
- Must specify user@host or it will not work correctly
- PASSWORD(‘text password’) function auto encrypts the password for us.
Granting permissions on a database to set the password
After you first create a database you have to “grant” a user permissions to use that database. But let’s say your user not only forgot their username and password but they need a new database setup. You can save yourself a few commands and create a new database and grant permissions while resetting the password all at the same time.
MySQL > create database newdb;
MySQL > GRANT USAGE ON newdb.* TO ‘anthony’@'localhost’ IDENTIFIED BY ‘text password’;
Important takeaways from the command above
- You’re granting permissions to a user on a new database.
- Still have to use user and hostname to specify which user your giving permissions to. anthony@localhost is not the same as firstname.lastname@example.org
- using IDENTIFIED BY instead of password()
Last step flush your privileges
Whenever you change anything about MySQL permissions or user privileges you need to “flush” out the old ones, or essentially clear the cache and you do that with the simple command below.
MySQL > flush privileges;
Neat Trick & Tip
So let’s say you’ve set up your .my.cnf file and you can access the MySQL command prompt without a username and password. Let’s pretend it’s way too much work to open the command prompt and type a few commands. You can actually perform any command from your regular BASH command prompt.
echo SET PASSWORD FOR ‘anthony’@'localhost’ = PASSWORD(‘text password’); | mysql
echo “command here” | mysql will “pipe” the command inside of mysql to be executed and the results will output via standard output to your BASH shell!