200

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
Improve this question
12
  • 3
    You need to log in as a user with permissions. At least root should have been created when you installed MySQL. Commented Jan 12, 2012 at 16:46
  • during installation it tells me to set password and i set it to root now what can I do? Commented Jan 12, 2012 at 16:47
  • 11
    You need to log in as root—e.g., by running mysql -u root -p. Then you'll have full permissions on the database server, and you can create other users. Commented Jan 12, 2012 at 16:49
  • when you log in using command line, do the following to log in as root: mysql -u root -p Commented Jan 12, 2012 at 16:49
  • 2
    @nikparsa: No, you'd run that instead of 'mysql'—from the shell prompt, not at the mysql prompt. Commented Jan 12, 2012 at 16:57

15 Answers 15

Reset to default
242

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

how can I go to bash?I had problem installing mysql see here superuser.com/questions/377679/… I just know abt it in command line
use 'quit' to exit the mysql,then you will be in bash
FYI by default the password is blank
What if I am using windows?
exit or quit is not working for me, please provide another way. I'm on windows 10
|
55

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
Improve this answer

3 Comments

This one worked for me, it was working fine initially and then suddenly stopped working one day. Any idea, why this happens in the first place?
@soleshoe i m getting error : sudo: mysqladmin: command not found please help me what can i do?? mysql is alreay running in System Preference.
I get: mysqladmin: Can't turn off logging; error: 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation'
33

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

enter image description here

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`;
Improve this answer

5 Comments

when I do CREATE USER 'parsa'@'localhost' IDENTIFIED BY 'your_password'; there get error: ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
Have you logged into mysql client using root user
This solved my issue: grant all privileges on *.* to 'parsa'@'%'; . I'm using docker-compose, is there a way I can pass this command after build?
I encountered the same problem when I migrated my existing database from phpmyadmin xampp to mysql. This finally worked for my node.js mysql app
This is the best answer on this topic!
32

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';
Improve this answer

4 Comments

This solved the problem for me even though I was using "mysql -u username -p". Very strange: user() was my username, but current_user() was localhost... dont know why?
@Leftteris hi .. i m getting this error : Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation When i try to drop user ''@'localhost';
+-----------------+----------------+ | user() | current_user() | +-----------------+----------------+ | fantomx1@localhost | @localhost | +-----------------+----------------+ | thx, I got this, when the anonymous user existing, I could login with whatever existing user without password even when they had password but their password didnt work, but they didnt have privileges, or even with any non existing thinked out string user 'sdasdsa', but the current user was always anonymous, after dropping him it started to work
this site has info on how to drop the anonymous user. download.nust.na/pub6/mysql/doc/refman/5.1/en/…
11

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.

Improve this answer

Comments

8

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

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

2 Comments

this fixed mine
but i use this . to grant access to all database/tables
7

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
Improve this answer

1 Comment

-p is for password and you are using it to pass database name. Also, for password, there shouldn't be a space b/w -p and the actual password. Use -D to connect to a specific database.
7

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.

Improve this answer

2 Comments

after enter password i getting "sudo: mysql: command not found" Please help me out on this
it shouldn't be "sudo:", it should be "sudo" ... If it is not a typo, I think this is the issue.
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: enter image description here

Hope this helps!

Improve this answer

1 Comment

Hi i got the abow screen direcly after fire this commend. /usr/local/mysql/bin/mysql but after that when i try to create database using " CREATE DATABASE books; " i am getting error : ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'books' please help me
2

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.

Improve this answer

Comments

1

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';
Improve this answer

Comments

0

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

Improve this answer

Comments

0

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!

Improve this answer

Comments

0

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';
Improve this answer

1 Comment

How does this substantially differ from your answer here?
0

After install of MariaDB 10.6, I could login with (but no permissions)

$ mariadb
MariaDB [(none)]> select user(), current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| me@localhost | @localhost     |
+----------------+----------------+

Could not login as root with

$ mariadb -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

This worked for root access

$ sudo mariadb
[sudo] password for me: 
MariaDB [(none)]> select user(), current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

This also worked $ sudo mariadb -u root but the password is of a sudo. Then documentation reads: From MariaDB 10.4, Unix socket authentication is applied by default, and there is usually no need to create a root password.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.