If your CURRENT_USER() does not say root@localhost, then you do not have root privileges and not being allowed to connect as such.
To see what actual privileges you do have after getting connected, run this
SHOW GRANTS;
When you connect to a specific database, you should be able to see
- the tables of that database
- metadata in the INFORMATION_SCHEMA for just that database
Let's say, for example, you connected to buyspace_systdb
The output from SHOW GRANTS; would most likely appear something like this
GRANT USAGE ON *.* ...
GRANT ALL PRIVILEGES ON buyspace_systdb.* TO ...
The user root@localhost would work if you login from within that local server. If you are logging in remotely, root@localhost would not allow USER() to authenticate as CURRENT_USER(). The proof of this is in the first display in your question. Note that you can only see information_schema. It will virtually empty with the exception of information_schema.schemata which would have 1 or 2 entries. If root@'%' existed in mysql.user and had the same privileges as root@localhost, then it would be allowed to see everything like root@localhost.
MySQL has an authentication scheme that has a downward spiral effect. If the user you try to connect with does not exist, it will try usernames that are more vague ( allowing for wildcards or limited privileges ). See my DBA StackExchange post MySQL error: Access denied for user 'a'@'localhost' (using password: YES) for further details on this.
Bottom Line: You cannot connect as root@localhost from a remote location
just down vote without comment? any advice would be dandy. – TomFirth – 2015-04-20T08:10:50.043
(DISCLAIMER: Not a phpmyadmin person) Please run the following query :
SELECT USER() AttemptedToConnectAsUser,CURRENT_USER() AllowedToConnectAsUser;What is the output ??? – RolandoMySQLDBA – 2015-05-07T16:22:48.043AttemptedToConnectAsUser AllowedToConnectAsUser cpses_******@localhost cpses_******@localhost
I'm quite inexperienced with phpmyadmin, i'm assuming this is randomly generated but should this display as 'root@localhost'? – TomFirth – 2015-05-08T08:51:51.177
It is not randomly generated at all. See my post http://superuser.com/questions/266758/how-do-i-see-which-user-i-am-logged-in-as-in-mysql/271516#271516 for more details.
– RolandoMySQLDBA – 2015-05-09T19:50:49.423In your config file, do you have values for the options $cfg['Servers'][$i]['hide_db'] or $cfg['Servers'][$i]['only_db'] ? – smokes2345 – 2015-05-11T21:41:17.443
@Rolando So i'm able to log in as root but i'm not actually logging in as root? I can't understand how to resolve this from the related question. – TomFirth – 2015-05-12T08:25:45.707
@smokes2345 I don't maintain the server and so can't check straight away, however these databases are only read once a day by root access and although I can't confirm, it's unlikely that any configs would have been changed by us and no major server updates happened that week. – TomFirth – 2015-05-12T08:25:48.937