Windows下MySQL 8.0.15无法通过PHP远程连接特定端口求助

Hey Martin, let’s work through this 2002 "Connection refused" error together—remote MySQL connections can be tricky, but we’ll cover all the common culprits step by step.

1. Make Sure MySQL is Listening for Remote Connections #

By default, MySQL 8.0 on Windows only binds to localhost (127.0.0.1), which blocks remote access. Here’s how to fix that:

• Open your MySQL configuration file (my.ini—usually located in C:\ProgramData\MySQL\MySQL Server 8.0\; note that ProgramData is hidden by default).
• Find the line starting with bind-address and change it to:

  bind-address = 0.0.0.0
  

  This allows MySQL to listen on all network interfaces. If you want to restrict access to a specific IP, replace 0.0.0.0 with that IP instead.
• Double-check that the port setting in my.ini matches the 9999 you’re using in your code—if it’s set to the default 3306, that’s a mismatch!
• Restart the MySQL service: Open Services (search for it in the Start menu), find MySQL80, right-click it, and select Restart.

To confirm MySQL is listening on port 9999, open Command Prompt and run:

netstat -ano | findstr :9999

You should see a line with LISTENING—the PID should match the MySQL process ID (you can check this in Task Manager).

2. Grant Remote Access Permissions to Your MySQL User #

The root user (or any user you’re using) is likely restricted to local connections by default. Let’s fix that:

• Open the MySQL Command Line Client (or use MySQL Workbench) and log in as a local admin user.

• Run these SQL commands to grant remote access to your root user for your target database:

  GRANT ALL PRIVILEGES ON DBNAME.* TO 'root'@'%' IDENTIFIED BY 'password';
  FLUSH PRIVILEGES;
  

  The % wildcard allows access from any remote IP—if you want to lock it down to a specific server, replace % with the PHP server’s IP address.

• Note for MySQL 8.0+: If your PHP version is older, it might not support the default caching_sha2_password authentication method. Switch the user to use mysql_native_password instead:

  ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
  FLUSH PRIVILEGES;
  

3. Check Firewall Settings (Both Servers!) #

Firewalls are the most common culprit here:

• Windows Firewall on your MySQL server: You need to allow inbound traffic on port 9999.
  1. Open Windows Defender Firewall > Advanced Settings.
  2. Create a new Inbound Rule: Select Port, enter 9999, choose Allow the connection, and apply it to all network profiles.
• Firewall on the PHP server: If your website is hosted on a separate server, make sure its firewall allows outbound traffic to your MySQL server’s IP and port 9999.

4. Validate Your Connection Code & Network Reachability #

A few quick checks on your code and network:

• Ensure HOST is set to your MySQL server’s public or local network IP (not localhost or 127.0.0.1—those only work for local connections).
• Try removing the quotes from your port variable: Change $dbPort = "9999"; to $dbPort = 9999; (mysqli_connect accepts integer ports more reliably).
• Test basic network connectivity: From the PHP server (if you have access to its command line), run telnet HOST 9999. If this fails, the issue is network-related (firewall, routing) not code-related.

5. Confirm MySQL Service is Running #

Double-check that the MySQL service is active on your Windows machine:

• Open Services, find MySQL80, and ensure its status is Running. If not, start it and set its startup type to Automatic to avoid future issues.

If you’ve tried all these steps and still get the error, check MySQL’s error log (located in the data folder of your MySQL installation, usually named [your-computer-name].err). It might have specific details about why connections are being rejected.

内容的提问来源于stack exchange，提问作者Martin Varta