ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'db'

Question

I want to begin writing queries in MySQL.

show grants shows:

+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+

I do not have any user-id but when I want to make a user I don't have privilleges, also I don't know how to make privileges when even I don't have one user!

mysql> CREATE USER 'parsa'@'localhost' IDENTIFIED BY 'parsa';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER pr
ivilege(s) for this operation

I tried to sign in as root:

mysql> mysql -u root -p;
ERROR 1064 (42000): 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
 -u root -p' at line 1
mysql> mysql -u root -p root;
ERROR 1064 (42000): 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
 -u root -p root' at line 1

Answer 1

No, you should run mysql -u root -p in bash, not at the MySQL command-line. If you are in mysql, you can exit by typing exit.

Answer 2

@Nickparsa … you have 2 issues:

1). mysql -uroot -p should be typed in bash (also known as your terminal) not in MySQL command-line. You fix this error by typing

exit

in your MySQL command-line. Now you are back in your bash/terminal command-line.

2). You have a syntax error:

mysql -uroot -p; 

the semicolon in front of -p needs to go. The correct syntax is:

mysql -uroot -p

type the correct syntax in your bash commandline. Enter a password if you have one set up; else just hit the enter button. You should get a response that is similar to this:

Hope this helps!

Answer 3

I got the same error below:

ERROR 1044 (42000): Access denied for user 'john'@'localhost' to database 'apple'

When I tried to select apple database by login with the user john as shown below:

use apple

So, I gave all privileges on all the tables in apple database (apple.*) to the user john with GRANT by login with the user root as shown below, then I could solve the error:

GRANT ALL ON apple.* TO 'john'@'localhost';

Or:

GRANT ALL PRIVILEGES ON apple.* TO 'john'@'localhost';

Answer 4

Just as a side note, if you just followed all grant privilege instruction and you are not seeing the effect, restart your sql workbench or any sql client you are using. You can also restart your server.

Answer 5

I'm using roles to confer least privilege on my database application users. I kept getting 'ERROR 1044 (42000): Access denied for user...' until I RTFM and discovered I had to give each user a default role(s) in order their account could be authenticated when they logged in.

#create a role
CREATE ROLE 'rolename';

#give necessary privileges to role
GRANT INSERT, UPDATE, DELETE, SELECT ON database.table TO 'rolename';

#create user
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

#give the user a role(s)
GRANT 'rolename' TO 'username'@'host';

#set the user's default otherwise it's ERROR 1044
SET DEFAULT ROLE 'rolename' FOR 'username'@'host';

Answer 6

connect mysql with sudo & gives permission for the necessary user using,

sudo mysql -u user;
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';

Answer 7

You may need to set up a root account for your MySQL database:

In the terminal type:

mysqladmin -u root password 'root password goes here'

And then to invoke the MySQL client:

mysql -h localhost -u root -p

Answer 8

I had the command correct per above answers, what I missed on was on the Workbench, where we mention 'Limit Connectivity from Host' for the user, it defaults to "%" - change this to "localhost" and it connects fine thereafter!

Answer 9

If you are in a MySQL shell, exit it by typing exit, which will return you to the command prompt.

Now start MySQL by using exactly the following command:

sudo mysql -u root -p

If your username is something other than root, replace 'root' in the above command with your username:

sudo mysql -u <your-user-name> -p

It will then ask you the MySQL account/password, and your MySQL won't show any access privilege issue then on.

Answer 10

Most Developers log-in to server(I assume you r having user-name and password for mysql database) then from Bash they switch to mysql> prompt then use the command below(which doesn’t work

mysql -h localhost -u root -p

What needs to be done is use the above command in the bash prompt--> on doing so it will ask for password if given it will take directly to mysql prompt and

then database, table can be created one by one

I faced similar deadlock so sharing the experience

Answer 11

I was brought here by a different problem. Whenever I tried to login, i got that message because instead of authenticating correctly I logged in as anonymous user. The solution to my problem was:

To see which user you are, and whose permissions you have:

select user(), current_user();

To delete the pesky anonymous user:

drop user ''@'localhost';

Answer 12

You might want to try the full login command:

mysql -h host -u root -p 

where host would be 127.0.0.1.

Do this just to make sure cooperation exists.

Using mysql -u root -p allows me to do a a lot of database searching, but refuses any database creation due to a path setting.

Answer 13

First, if you are unfamiliar with the command line, try using phpmyadmin from your webbrowser. This will make sure you actually have a mysql database created and a username.

This is how you connect from the command line (bash):

mysql -h hostname -u username -p database_name

For example:

fabio@crunchbang ~ $ mysql -h 127.0.0.1 -u fabio -p fabiodb

Answer 14

This is something to do with user permissions. Giving proper grants will solve this issue.

Step [1]: Open terminal and run this command

$ mysql -uroot -p

Output [1]: This should give you mysql prompt shown below

Step [2]:

mysql> CREATE USER 'parsa'@'localhost' IDENTIFIED BY 'your_password';
mysql> grant all privileges on *.* to 'parsa'@'localhost';

Syntax:

mysql> grant all privileges on `database_name`.`table_name` to 'user_name'@'hostname';

Note:

• hostname can be IP address, localhost, 127.0.0.1
• In database_name/table_name, * means all databases
• In hostname, to specify all hosts use '%'

Step [3]: Get out of current mysql prompt by either entering quit / exit command or press Ctrl+D.

Step [4]: Login to your new user

$ mysql -uparsa -pyour_password

Step [5]: Create the database

mysql> create database `database_name`;