How to Retrieve MySQL Username and Password

Often users forget or misplace their MySQL username and password, and are unable to connect to their databases. If you are looking to retrieve MySQL username and password, this article is meant for you. In this article, we will learn what you should do if you have lost or forgotten your MySQL username and password.

How to Retrieve MySQL Username and Password

Let’s get to the point straight away. If you have lost or forgotten your MySQL username and password, you cannot retrieve your password, even if you get back your username. You can only reset a MySQL password. This is because MySQL stores all password as a one way hash which cannot be reversed. If you want to get your username run the following query. Please note, you will not be able to run these queries by default and need to log into MySQL using –skip-grant-tables option as described below.

mysql> SELECT * FROM mysql.user;

The above query will list all database users in your MySQL server.

If you have a partial recollection of your username then you can run the following query. Replace <part_string> below with whatever part of your username you remember.

mysql> SELECT * FROM mysql.user where user like '%<part_string>%';

To run the above command, stop MySQL process. Run it with –skip-grant-tables option, and start MySQL console client with -uroot option. –skip-grant-tables allows you to connect to your MySQL database without specifying password, allowing you to reset your password.

Once you know your MySQL username, run the following command to reset your MySQL password.

mysql> UPDATE mysql.user SET Password=PASSWORD('[password]') WHERE User='[username]';
mysql> FLUSH PRIVILEGES

When you run the above command, MySQL will create a new one way hash for your new password and store it.

Then stop MySQL process, and start it again without using –skip-grant-tables option. If you don’t perform this step, then your database security may become compromised.

In this article, we have learnt what to do if you forget or misplace your MySQL username and password.

Also read:

How to Find & Replace Text in Entire Table in MySQL
How to Take Backup of Single Table in MySQL
How to Show Indexes on Table or Database in MySQL
How to Set Global SQL Mode in MySQL
How to Check if Column is Empty or Null in MySQL