How to switch users on MySQL?

Question

What is the windows command to switch users on MySQL? I'm having a little problem in using MySQL on windows. I know how to create a new user and giving grants to the new user and all. But I don't know how to switch to new user from MySQL console!

Answer 1

For example, after login, you can switch the user to other user john as shown below:

system mysql -u john -p

Or:

SYSTEM mysql -u john -p

Or:

\! mysql -u john -p

Answer 2

Go to Windows command prompt

Microsoft Windows [Version 10.0.10586]
(c) 2015 Microsoft Corporation. All rights reserved.

C:\Users\avinash>cd/
 
C:\>cd "Program Files\MySQL\MySQL Server 5.7\bin"
 
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u xyzzzz -p 
Enter password: ***** ERROR 1045 (28000): Access denied for user 'xyzzzz'@'localhost' (using password: YES)
 
 C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u xyzzzz -p 
Enter password:

Answer 3

Creating user syntax : http://dev.mysql.com/doc/refman/5.1/en/create-user.html

Code :

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

To switch the only way in command line is :

mysql -u user -p

Answer 4

The MySQL CLI session is bound to the user which started it. Thus, you'll have to open a new session with the desired user after you've created it and set needed grants. You can open a new session and keep the current one. Or, you can end the current session and then start new a one:

mysql -uUSER -p DATABASE

where USER is name of new user and DATABASE is database to switch to when you log on (-p points that you'll prompted to enter password). To exit the session, just use exit.

Answer 5

I figured out solution. There is system command, where we can execute shell command like 'ls, cd, mv, mkdir etc.. Here we can login as different user. first I logged in as regular user.

[madegow@fedora20 ~]$ mysql -u root -p
Enter password: 
...
MariaDB [(none)]> select user()
-> ;
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> system mysql -u madegow -p
Enter password: 
...
MariaDB [(none)]> select user();
+-------------------+
| user()            |
+-------------------+
| madegow@localhost|

Now user got changed to madegow user. but when you run quit/exit it will not go back to root. we need to bare with that.

Answer 6

I was looking for an equivalence of the CONNECT command of Oracle Database and just found it with the command \CONNECT available on MySQL Shell (since version 5.7.12), but that's another story.

So, as said years ago by our friend Shivakumara, the answer is:

MySQL> SYSTEM mysql -u user -p

Which is nothing but calling an OS shell command from MySQL prompt.