PHPMyAdmin连接远程MySQL服务器配置及连接问题求助

Let’s work through your connection problem step by step—starting with your configuration, then moving to server-side checks and network validation.

1. Fix Key Issues in Your PHPMyAdmin Config #

Looking at your current setup, there are a few adjustments needed for remote TCP connections:

$cfg['Servers'][$i]['host'] = '<IP>';
$cfg['Servers'][$i]['port'] = '3306';
// Remove this line - socket is only for local Unix connections
// $cfg['Servers'][$i]['socket'] = '/var/run/mysqld/mysqld.sock';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
// Replace deprecated 'mysql' extension with modern 'mysqli'
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['compress'] = FALSE;
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'pass';

• Socket setting: Irrelevant for remote TCP connections—comment or delete it entirely.
• Extension: The mysql extension was deprecated in PHP 5.5 and removed in PHP 7+. Switching to mysqli ensures compatibility and proper connection handling.
• Security note: Storing plaintext passwords with auth_type = 'config' is risky. Set your config file permissions to 640 (readable only by the web server user and root) to prevent unauthorized access.

2. Grant Remote Access to Your MySQL User #

By default, MySQL’s root user is restricted to local connections only. You need explicitly allow remote access:

1. Log into your MySQL server locally (via SSH or terminal):

   mysql -u root -p
   

2. Grant access to root from your PHPMyAdmin server’s IP (replace <phpmyadmin-server-ip> with the actual IP; use % for any IP, but this is less secure):

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'<phpmyadmin-server-ip>' IDENTIFIED BY 'pass' WITH GRANT OPTION;
   

3. Apply the changes:

   FLUSH PRIVILEGES;
   

3. Ensure MySQL Listens on All Interfaces #

MySQL might be configured to only listen to localhost, blocking remote connections:

1. Open your MySQL config file (usually /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf):
2. Find the bind-address line and update it to:

   bind-address = 0.0.0.0
   

   (Or set it to your MySQL server’s public IP if you want to restrict to that interface.)
3. Restart MySQL to apply changes:

   sudo systemctl restart mysql
   

4. Validate Network Connectivity #

Even if you opened port 3306, there might be hidden blocks:

• Test if the port is reachable from your PHPMyAdmin server:

  telnet <remote-mysql-ip> 3306
  # Or use nc for a quicker check:
  nc -zv <remote-mysql-ip> 3306
  

  If this fails, double-check:
  • Your server’s firewall (ufw/iptables) is truly allowing incoming 3306 traffic.
  • Cloud provider firewalls (like AWS Security Groups) have the port open for your PHPMyAdmin server’s IP.
  • There’s no routing issue between the two servers.

5. Confirm the mysqli Extension is Enabled #

Make sure PHP has the mysqli extension loaded:

• Create a quick phpinfo file on your PHPMyAdmin server:

  <?php phpinfo(); ?>
  

• Access it via a browser and search for mysqli to confirm it’s active. If not, enable it in your php.ini file (e.g., extension=mysqli) and restart your web server.

After working through these steps, try connecting again with PHPMyAdmin. If you still hit issues, check your web server logs (e.g., /var/log/apache2/ or /var/log/nginx/) for specific error messages that can narrow down the problem.

内容的提问来源于stack exchange，提问作者shamon shamsudeen