How to switch users on MySQL

Question

what is the windows command to switch to users on MySQL Im 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

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 2

MySQL CLI session is bound to the user which started it. Thus, you'll have to open new session with using desired user after you've created it & set needed grants. You can open new session (i.e. keep current one) or end current session and then start new:

mysql -uUSER -p DATABASE

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

Answer 3

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.

Answer 4

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