I am continuously receiving this error.
I am using mySQL Workbench and from what I am finding is that root’s schema privileges are null. There are no privileges at all.
I am having troubles across platforms that my server is used for and this has been all of a sudden issue.
[email protected] apparently has a lot of access but I am logged in as that, but it just assigns to localhost anyways – localhost has no privileges.
I have done a few things like FLUSH HOSTS
, FLUSH PRIVILEGES
, etc
but have found no success from that or the internet.
How can I get root its access back? I find this frustrating because when I look around people expect you to “have access” but I don’t have access so I can’t go into command line or anything and GRANT
myself anything.
When running SHOW GRANTS FOR root
this is what I get in return:
Error Code: 1141. There is no such grant defined for user ‘root’ on host ‘%’
20 Answers
Use the instructions for resetting the root password – but instead of resetting the root password, we’ll going to forcefully INSERT a record into the mysql.user table
In the init file, use this instead
INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD'));
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
If you have that same problem in MySql 5.7.+ :
Access denied for user 'root'@'localhost'
it’s because MySql 5.7 by default allow to connect with socket, which means you just connect with sudo mysql
. If you run sql :
SELECT user,authentication_string,plugin,host FROM mysql.user;
then you will see it :
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | | auth_socket | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
To allow connection with root and password, then update the values in the table with command :
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';
FLUSH PRIVILEGES;
Then run the select command again and you’ll see it has changed :
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | *2F2377C1BC54BE827DC8A4EE051CBD57490FB8C6 | mysql_native_password | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
And that’s it. You can run this process after running and completing the sudo mysql_secure_installation
command.
For mariadb, use
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('manager');
to set password. More at https://mariadb.com/kb/en/set-password/
It didn’t like my user privilege so I SUDO it. (in bash << sudo set user and password) (this gives username of root and sets the password to nothing) (On Mac)
sudo mysql -uroot -p
Try the following commands
~$ sudo /etc/init.d/mysql stop
~$ sudo mysqld_safe --skip-grant-tables &
~$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>
mysql> use mysql;
mysql> update user set password=PASSWORD("root") where User='root';
mysql> flush privileges;
mysql> quit
~$ sudo /etc/init.d/mysql stop
Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+ Done mysqld_safe --skip-grant-tables
~$ sudo /etc/init.d/mysql start
~$ mysql -u root -p
* MySQL Community Server 5.6.35 is started
~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
for the people who are facing below error in mysql 5.7+ version –
Access denied for user 'root'@'localhost' (using password: YES)
-
Open new terminal
-
sudo /etc/init.d/mysql stop
… MySQL Community Server 5.7.8-rc is stopped -
sudo mysqld_safe --skip-grant-tables &
this will skipp all grant level privileges and start the mysql in safe mode Sometimes the process got stucked just because of
grep: write error: Broken pipe 180102 11:32:28 mysqld_safe Logging to ‘/var/log/mysql/error.log’.
Simply press Ctrl+Z or Ctrl+C to interrupt and exit process
mysql -u root
Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.7.8-rc MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
- mysql>
use mysql;
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed
-
mysql>
update user set authentication_string=password('password') where user='root';
Query OK, 4 rows affected, 1 warning (0.03 sec) Rows matched: 4 Changed: 4 Warnings: 1 -
mysql>
flush privileges;
Query OK, 0 rows affected (0.00 sec) -
mysql>
quit
Bye -
sudo /etc/init.d/mysql stop
..180102 11:37:12 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended . * MySQL Community Server 5.7.8-rc is stopped [email protected]:~$ sudo /etc/init.d/mysql start .. * MySQL Community Server 5.7.8-rc is started
-
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.7.8-rc MySQL Community Server (GPL)
after mysql 5.7+ version the column password replaced by name authentication_string from the mysql.user table.
hope these steps will help anyone, thanks.
I was using ubuntu 18 and simply installed MySQL (password:root) with the following commands.
sudo apt install mysql-server
sudo mysql_secure_installation
When I tried to log in with the normal ubuntu user it was throwing me this issue.
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
But I was able to login to MySQL via the super user. Using the following commands I was able to log in via a normal user.
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
exit;
Then you should be able to login to Mysql with the normal account.
A simple way to reset root password on Linux systems :
sudo dpkg-reconfigure mysql-server-5.5
Checkout some other reasons for Access denied :
https://dev.mysql.com/doc/refman/5.7/en/problems-connecting.html
If you are getting this error in Workbench but you are able to log in from terminal then follow this steps.
First simply log in with your current password:
sudo mysql -u root -p
Then change your password because having low strength password gives error sometimes.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-strong-password';
FLUSH PRIVILEGES;
Then simply exit and again login with your new password:
quit
sudo mysql -u root -p
Once you successfully logged in type the command:
use mysql;
It should show a message like ‘Database changed’ then type:
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
After that type:
UPDATE mysql.user set authentication_string=PASSWORD('new-strong-password') where user='root';
Then type:
FLUSH PRIVILEGES;
Then simply exit:
quit
Now try to log in with your new password in your WORKBENCH. Hope it will work. Thank you.
I faced this problem while installing Testlink on Ubuntu server, I followed these steps
mysql -u root
use mysql;
update user set password=PASSWORD("root") where User='root';
flush privileges;
quit
Now stop the instance and start again i.e
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start
Well the easiest way to reset root password is:
-
restart mysqld –skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use –skip-grant-tables in conjunction with –skip-networking to prevent remote clients from connecting.
-
Connect to the mysqld server with this command:
-
shell> mysql Issue the following statements in the mysql client. Replace the password with the password that you want to use.
-
mysql> UPDATE mysql.user SET Password=PASSWORD(‘MyNewPass’) -> WHERE User=’root’; mysql> FLUSH PRIVILEGES;
-
Stop the server, then restart it normally (without the –skip-grant-tables and –skip-networking options).
Source Mysql documentation and personal experience:
http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html
in mysql 5.7 the password field has been replaced with authentication_string so you would do something like this instead
update user set authentication_string=PASSWORD("root") where User='root';
See this link MySQL user DB does not have password columns – Installing MySQL on OSX
I resolved the same issue by running Workbench as administrator.
…I guess it’s because of restrictions on company computers, in my case…
Try out the following steps to overcome this issue:
- Open terminal / command prompt and navigate to the bin folder of the MySQL installation folder. Then run the command
mysqld --console
. - If you can see that line
171010 14:58:22 [Note] --secure-file-priv
is set to NULL. Operations related to importing and exporting data are disabled, after executing the above command from the command prompt. - Then you need to check that the
mysqld
is either blocked by the Windows Firewall or another program. - If it’s blocked by Windows Firewall then need to unblock from it and save settings.
- To unblock the
mysqld
ormysql
application, follow the below steps:- Go to command prompt and type
wf.msc
to open the firewall settings. - Click on Allow an app or feature through Windows Firewall.
- Check the
mysqld
ormysqld
instances are available in the list and check the checkbox for the domain, public and private and save the settings.
- Go to command prompt and type
- Return to the bin folder and try the command from step 1 again.
- It should work fine and not show any errors.
It should be possible to run the MySQL console without any problems now!
I resolved the same issue using next sql and restarting MySQL server:
update mysql.user set Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',Create_tablespace_priv='Y'
where user='root';
I worked on Access Denied for User ‘root’@’localhost’ (using password: YES) for several hours, I have found following solution,
The answer to this problem was that in the my.cnf located within
/etc/mysql/my.cnf
the line was either
bind-address = 127.0.0.1
(or)
bind-address = localhost
(or)
bind-address = 0.0.0.0
I should prefer that 127.0.0.1
I should also prefer 0.0.0.0, it is more flexible
because which will allow all connections
I don’t think you have to escape the --init-file
parameter:
"C:Program FilesMySQLMySQL Server 5.6binmysqld.exe" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.6\my.ini" --init-file=C:\mysql-init.txt
Should be:
"C:Program FilesMySQLMySQL Server 5.6binmysqld.exe" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.6\my.ini" --init-file=C:mysql-init.txt
for the above problem ur password in the system should matches with the password u have passed in the program because when u run the program it checks system’s password as u have given root as a user so gives u an error and at the same time the record is not deleted from the database.
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
class Delete
{
public static void main(String []k)
{
String url="jdbc:mysql://localhost:3306/student";
String user="root";
String pass="jacob234";
try
{
Connection myConnection=DriverManager.getConnection(url,user,pass);
Statement myStatement=myConnection.createStatement();
String deleteQuery="delete from students where id=2";
myStatement.executeUpdate(deleteQuery);
System.out.println("delete completed");
}catch(Exception e){
System.out.println(e.getMessage());
}
}
}
Keep ur system password as jacob234 and then run the code.
With me was the same problem, but it was caused, because i was using the mysql server on 32 (bit) and the workbench was running on 64(bit) version. the server and the workbench need to has the same version.
xpress
For me i was using MYSQLWorkbench and the port was 3306 MAMP using 8889
I was facing the same problem when I’m trying to connecting Mysql database using the Laravel application. I would like to recommend please check the password for the user. MySQL password should not have special characters like #, &, etc…