Connecting phpMyAdmin to DB on VM - DB login details or Server login details?

Question

I have phpMyAdmin running on my local machine (mac) and I am trying to connect it to my database located on a college VM

I have been following some of the questions and linked guides on this question but they seem to give conflicting advice

Should I be using my database login username and password for the below or my server username and password (used for SSH, FileZilla)?

$cfg['Servers'][$i]['user']          = '';
$cfg['Servers'][$i]['password']      = '';

I am having some issues connecting to the remote DB via phpmyadmin and want to make sure I have this right while I go through each of the other possible issues. Thanks

Answer 1

The following variables in your PHPMyAdmin's config.inc.php are relevant:

$cfg['Servers'][$i]['host'] = '11.22.33.44';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'YourSQLUserName';
$cfg['Servers'][$i]['password'] = 'YourSQLPassword';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';

For user and password, you will want to add your MySQL database credentials. FTP and SSH are not relevant here. Then, for host your remote server's IP address. Change defaults for port, socket and connect_type if your remote server is otherwise configured.

Note: If you have problems with the above, you need to verify that your remote server accepts remote connections (for your username). You will also want to make sure that your local firewall lets you create such connections (ie. that PHP can make remote connections). Any problems: please post the specific errors or descriptions of issues you may encounter.

If you need to create a new MySQL user with remote access from any IP, use @'%' as host name:

CREATE USER 'userName'@'%' IDENTIFIED BY 'passWord';
GRANT ALL PRIVILEGES ON * . * TO 'userName'@'%';

I did a test connect with PHPMyAdmin to my new server, and realized port 3306 was not open for remote connections at the target server's iptables rules. If the port is closed to incoming connections, open it up for connections as follows (need root access):

iptables -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
iptables-save | tee /etc/sysconfig/iptables
service iptables restart

Aside the two considerations above, there are other possible reasons why the connection may fail incl. remote MySQL configuration, related to your target server. Since the DB server is hosted by your college, you probably don't have root access, and may simply want to bounce this off your network admin if you can't get it going despite having everything straight in PHPMyAdmin.

To verify it's not a PHPMyAdmin issue, you can try connecting to the remote server with your local MySQL command line tool; e.g. C:/server/mysql/bin/mysql.exe -h 11.22.33.44 -u myUser -p ... and enter password. If you can't get a response, it's a network or remote server issue.