ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'db'
MysqlMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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
Solution 3 - Mysql
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';
Solution 4 - Mysql
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`;
Solution 5 - Mysql
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.
Solution 6 - Mysql
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.
Solution 7 - Mysql
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
Solution 8 - Mysql
@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!
Solution 9 - Mysql
connect mysql with sudo & gives permission for the necessary user using,
sudo mysql -u user;
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';
Solution 10 - Mysql
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
Solution 11 - Mysql
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!
Solution 12 - Mysql
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';