phpMyAdmin is a pretty cool piece of software, and a very useful tool. If you haven’t used it before, it’s a full featured web-based interface to MySQL databases. You can use it to add data, update data, delete data, export data, and basically any other management of the database you might need.
I ran into an issue the other day. I was trying to allow phpMyAdmin to access a database on a remote host. The user that phpMyAdmin was connecting as didn’t have the “show databases” privilege (I imagine this is common in shared hosting environments, which is what this was). This, apparently, is what phpMyAdmin uses to populate the drop-down of databases on the left-hand side after you login. Since it didn’t display that drop-down, there is no way of selecting the database to which this user did have access.
I searched for a while, but couldn’t find anyone else with this problem. So I thought I would post the solution I found.
The solution is to hard code authentication data for the remote server in my config.inc.php
file. Then, you append the server and the database that you want to connect to the phpMyAdmin url.
In the config.inc.php
file:
$cfg['Servers'][$i]['host'] = 'xxx.xxx.xx.xx';
$cfg['Servers'][$i]['user'] = 'user';
$cfg['Servers'][$i]['password'] = 'pass';
In the url that normal users use to access the database:
http://phpMyAdmin.example.com/phpMyAdmin/index.php?db=databasename&server=xxx.xxx.xxx.xxx
The left hand side still isn’t populated with any databases. But, this allows you to go directly to the database to which you do have access, and perform whatever tasks you want (and have permissions for). I tried adding the “db” parameter to the config.inc.php
file, but that didn’t seem to work. I didn’t try using any other form of authentication than hardcoded, as I wanted to make this as simple as possible for the phpMyAdmin users.
I was running an older version of phpMyAdmin (2.11.9.5); I didn’t test this on newer versions. If you do, please post a comment and let me know if this still works.
[tags]phpmyadmin,mysql privileges,remote database access[/tags]
I’m having the same issue except with MS Excel… I’m trying to connect with Excel but I get the same error do you think your solution would work for that too? Thanks for the help.
Yes, if I were using excel to connect to mysql, I’d try to specify the database in the url. Let me know how that works for you.