how can i switch to another user in mysql via cmd

Question

hello everyone i'm try very hard to switch to other user created by me with password but i am not able to switch it, i've searched on google but i don't know what type of error is it it says access denied for user ''@'localhost' to database '*', and i get one more error when i try to switch user and it says, you have an error in your sql syntax; check the manual that corresponds to your MYSQL server version for the right syntax to use near 'mysql and atlast when i listed current user it showed me @localhost in current user column...please help..thx in advance

Answer 1

Seems like the username is empty. Please note you can't switch users when you're in MySQL shell. You'd have to exit to the Linux shell, then connect to mysql again with the other username, e.g.:

mysql -u myuser -p

Hope this helps. If not, please be more specific with your question.

Answer 2

As stated above to connect to your database you need to use the command mysql -u USER -p.

This means connect as USER and require a password prompt. I have also noticed that when I am adding new users you have to make sure to grant permissions to connect. Many times you will see that hostnames aren't working properly so when you create your user you should do something like this:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

Notice the second line where it says % - that means you can connect to your database from anywhere. If this is a production environment that can be a bad thing though because you could end up letting someone else connect and modify your database.

Have a look at this page for more info.