How do i find my mysql username and password?

In this article i will show how to list MySQL users, their passwords and granted privileges from the command-line prompt.

MySQL account consists of two components: user and host.

This allows the same user to use different MySQL accounts with different privileges, depending on which host they are connecting from.

In the Host field, besides of the common IP addresses and host names, you can see the % sign, that is a wildcard character that means “any” host.

Important Notice: The % character does not include the localhost, as the localhost means a connection over a UNIX socket instead of a standard TCP/IP.

Show all MySQL users:

mysql> SELECT user FROM mysql.user;

List only unique user names:

mysql> SELECT DISTINCT user FROM mysql.user;

Show MySQL users and hosts they are allowed to connect from:

mysql> SELECT user,host FROM mysql.user;

Show MySQL users, their passwords and hosts:

mysql> SELECT user,host,password FROM mysql.user;

in MySQL 5.7 and higher:

mysql> SELECT host,user,authentication_string FROM mysql.user;

Cool Tip: Need to change MySQL user password? This can be easily done from the command-line prompt! Read more →

Show User Privileges In MySQL

In MySQL, you can use the SHOW GRANTS command to show privileges granted to a user.

Without any additional parameters, the SHOW GRANTS command lists the privileges granted to the current user account with which you have connected to the server.

The SHOW GRANTS requires the SELECT privilege for the mysql database, except to see the privileges for the current user, so if you have such privilege you can also list the privileges granted to the other MySQL users.

Cool Tip: Create a MySQL database and GRANT ALL PRIVILEGES on it to a user! Simple and clear MySQL tutorial with good examples! Read more →

Show privileges granted to the current MySQL user:

mysql> SHOW GRANTS;

Show privileges granted to the MySQL user (if you don’t specify a host for the user name, MySQL assumes % as the host):

mysql> SHOW GRANTS FOR 'user_name';

Show privileges granted to a particular MySQL user account from a given host:

mysql> SHOW GRANTS FOR 'user_name'@'host';

– e.g. –

mysql> SHOW GRANTS FOR 'root'@'localhost'; mysql> SHOW GRANTS FOR 'root'@'%'; mysql> SHOW GRANTS FOR 'admin'@'192.168.0.1';

The default user for MySQL is root and by default it has no password.

If you set a password for MySQL and you can’t recall it, you can always reset it and choose another one.

Windows

Make sure that MySQL Server is not running. Open Task Manager, search for the MySQL process and force stop it.
Create a new text file that will contain the statement below:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Replace the password with the new one
Save the file with the mysql-init name in C:. The path should look like this:
C:\mysql-init.txt
Open the Start menu, enter Run then write cmd to open the command prompt
Go to the MySQL server bin folder
cd "C:\Program Files\MySQL\MySQL Server 5.6\bin"
If you installed MySQL with a different path, adjust the cd
Run it with the mysql-init file
mysqld --init-file=C:\\mysql-init.txt
If MySQL was installed using the Wizard, add the defaults file command:
mysqld --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 5.6\\my.ini" --init-file=C:\\mysql-init.txt
After MySQL server started, delete the mysql-init file.

General

Alternatively, you can use a more general method that works on every system, but it’s less safe.

Stop MySQL
Restart it with the --skip-grant-tables option
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
Connect to MySQL server using the mysql client
mysql -u root
Reload all grant tables by executing:
FLUSH PRIVILEGES;
Set the new password for your account:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Stop the server and restart it normally. Now you should be able to connect using the root username and your new password.

Share this blog post on Twitter, Facebook, and LinkedIn

DbSchema | MySql GUI Client

DbSchema is a Free visual database manager for MySql. You can create new tables, columns and foreign keys directly in diagrams.

The schema design can be saved to the model file and deployed or compared with any other database.

Generate HTML5 documentation with the diagram vector image.

Use the SQL Editor to edit and execute SQL queries.

Tools like Relational Data Explorer, Query Builder, Data Generator, and Reports Designer will let you easily manage MySql databases.

DbSchema can be downloaded for free. No registration is required.

How do I find MySQL username and password?

So for example, to show MySQL users' username, password and host, we'll modify the sql query to accordingly as such: mysql> select user, password, host from mysql. user; The above sql query will present you with a list of users and their respective user name, password and database host.