1

Working on a Ubuntu-14.04 virtual machine.

Theres a MySQL-server running on there. I noticed that the root user doesnt have full access to the performance_schema database.

That leads me to my first question about it: Is the root user supposed to have access to this? What is the default? Is not having access an indicator that the database configuration is not good?

Trying to debug this by giving the root user the missing privileges didnt want to work eighter;

Access denied for 'root'@'localhost'

Then I noticed that there are 2 users called 'root' in the mysql-usertable:

the duplicate user

The one thats marked gray is the one in question. I dont have the password for it eighter. How did that get there? And how do I resolve this issue? How can there even be 2 users with matching user names?

UPDATE: Now at least both root-users have the same, known password.

But I am still getting Access Denied-Errors, with both of them. And when I look at the grants for each users, it is clear that they are two different users. But they both have the GRANT option.

Why am I still getting Access Denied?

mysql-output

2

Is the root user supposed to have access to this?

What you want. As an Ubuntu user I would myself never use "root". It is too obvious. I would also not use my username for the system for the same reason and always use a completly new name. And also a different password than any other procedure that requires a password.

What is the default?

The user you created during installation of MySQL.

How did that get there?

You probably created them. debian-sys-main is created by the system. root is not but I am not 100% sure if "root" is offered as a default.

How can there even be 2 users with matching user names?

Why can't there be? There is a username root with hosts localhost and 127.0.0.1. You can grant access per host to a user. Granted... localhost and 127.0.0.1 are the same (your /etc/hosts should have a line connecting them together).

You also have 2 "butler" and 2 "wordpres" and 2 "redmine".

And how do I resolve this issue?

What issue? ;)

  • the issue is that i dont have full access to the performance_schema database and i have no user that has. i cant give myself the needed privileges eighter... so practically, i cannot access this database. any idea about how to resolve it? – Master Azazel Jan 17 '17 at 8:12
2

Login MySQL and run the following below commands which will provide full privileges to root user.

grant all privileges on . to 'root'@'localhost' with grant option;

flush privileges;

Added info:

GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY 'admin' WITH GRANT OPTION; FLUSH PRIVILEGES;

Note : admin is the mysql login password

After the above process, If you are getting the same error check the full privileges for the IP 127.0.0.1, since localhost and the IP denotes same local machine. If the IP don't have privileges then that too might be an issue. try % in command which provide privileges to all users

GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'admin' WITH GRANT OPTION; FLUSH PRIVILEGES;

  • first command gives me ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES), but if i check the usertable it says i have full rights... – Master Azazel Jan 16 '17 at 14:47
  • same error for both commands – Master Azazel Jan 17 '17 at 8:05
0

This is the answer -> https://serverfault.com/a/664829/331477

Its a bug in the version of MySQL that I use. Upgrading to newest MySQL version should solve the problem.

Will do that when I got the ok from the sysadmin.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.