Just my 2 cents on the subject. I was having the exact same issue with trying to connect from MySQL Workbench. I'm running a bitnami-mysql virtual machine to set up a local sandbox for development.
Bitnami's tutorial said to run the 'Grant All Privileges' command:
/opt/bitnami/mysql/bin/mysql -u root -p -e "grant all privileges on *.* to 'root'@'%' identified by 'PASSWORD' with grant option";
This was clearly not working, I finally got it to work using Mike Lischke's answer.
What I think happened was that the root@% user had the wrong credentials associated to it. So if you've tried to modify the user's privileges and with no luck try:
- Dropping the user.
- Create the user again.
- Make sure you have the correct binding on your my.cnf config file. In my case I've commented the line out since it's just for a sandbox environment.
From Mysql Console:
List Users (helpful to see all your users):
select user, host from mysql.user;
Drop Desired User:
drop user '{{ username }}'@'%';
Create User and Grant Permissions:
CREATE USER '{{ username }}'@'%' IDENTIFIED BY '{{ password }}';
GRANT ALL PRIVILEGES ON *.* TO '{{ username }}'@'%' WITH GRANT OPTION;
Run this command:
FLUSH PRIVILEGES;
Locate your mysql config file 'my.cnf' and look for a line that looks like this:
bind-address=127.0.0.1
and comment it using a '#':
#bind-address=127.0.0.1
Then restart your mysql service.
Hope this helps someone having the same issue!
mysql -u root -p
, then entering root password. Then I triedGRANT GRANT OPTION ON *.* TO 'root'@'%';
and I get the errorERROR 1410 (42000): You are not allowed to create a user with GRANT
– aiman