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
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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.
Method 2
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
Method 3
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';
Method 4
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`;
Method 5
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.
Method 6
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.
Method 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:
<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="761017141f193615040318151e14171811">[email protected]</a> ~ $ mysql -h 127.0.0.1 -u fabio -p fabiodb
Method 8
@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!
Method 9
connect mysql with sudo & gives permission for the necessary user using,
sudo mysql -u user; GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';
Method 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
Method 11
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!
Method 12
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';
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0