How to log in as a different user on MySQL?


If you want to login as a different user on MySQL, you need to use “mysql -u -p command”. The syntax is as follows to login as a different user.

>mysql -u yourUsername -p After pressing enter key
Enter password −

To understand the above syntax, let us create a user in MySQL. The syntax is as follows −

CREATE USER 'yourUserName'@'localhost' IDENTIFIED BY 'yourPassword';

Now I am going to create a user with name ‘John’ and password is ‘john123456’. The query is as follows −

mysql> CREATE USER 'John'@'localhost' IDENTIFIED BY 'john123456';
Query OK, 0 rows affected (0.15 sec)

Now check the user has been created in MySQL.user table or not. The query is as follows to list all users from MySQL.user table −

mysql> select user from MySQl.user;

The following is the output −

+------------------+
| user             |
+------------------+
| Manish           |
| User2            |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
| Adam Smith       |
| John             |
| User1            |
| am               |
+------------------+
10 rows in set (0.00 sec)

Now you need to give all privileges to the user John. The query is as follows:

mysql> GRANT ALL PRIVILEGES ON * . * TO 'John'@'localhost';
Query OK, 0 rows affected (0.19 sec)

Now to login as a different user, first open the command prompt by pressing Windows+R shortcut key and type CMD. Now you need to press OK button. The screenshot is as follows −

After pressing OK button, you will get a command prompt. The screenshot is as follows −

After that reach the location of bin. Follow the below given steps:

Step 1 −

Step 2 −

Now use the above syntax discussed at the beginning. Give the username ‘John’ and password ‘john123456’ which we created above to login. The command is as follows −

After pressing enter key you need to give password. The query is as follows −

Updated on: 30-Jun-2020

13K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements