如何让Windows系统安装的MySQL支持远程访问？求详细操作步骤

Got it, let's walk through exactly how to set up MySQL for remote access so your PC 2 can connect via PHP, Python, or any other language. I’ll break this down into clear, actionable steps that work for most setups:

First, you need to make sure your MySQL user is allowed to connect from PC 2’s IP (or any IP, if you want broader access).

1. Log into your MySQL server on PC 1 via the terminal/command prompt:

   mysql -u root -p
   

   Enter your root password when prompted.

2. Grant access to your user. Replace your_user with your actual MySQL username, your_password with its password, and % with PC 2’s specific IP if you want to restrict access (e.g., 192.168.1.100 instead of %):

   GRANT ALL PRIVILEGES ON *.* TO 'your_user'@'%' IDENTIFIED BY 'your_password';
   

   Note: *.* means access to all databases and tables—adjust this to specific databases (e.g., your_db.*) if you want to limit permissions for security.

3. For MySQL 8.0+ users: The default authentication method (caching_sha2_password) might cause issues with older clients (like some PHP/Python libraries). Switch to mysql_native_password for compatibility:

   ALTER USER 'your_user'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
   

4. Flush the privileges to apply the changes immediately:

   FLUSH PRIVILEGES;
   

   Then exit MySQL with EXIT;.

By default, MySQL only listens for connections from the local machine (127.0.0.1). You need to change this to allow external connections:

On Linux/macOS: #

1. Locate the MySQL config file. Common paths are:

   • /etc/mysql/my.cnf
   • /etc/mysql/mysql.conf.d/mysqld.cnf
   • /usr/local/mysql/etc/my.cnf (for macOS Homebrew installs)

2. Open the file with a text editor (e.g., sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf).

3. Find the line starting with bind-address and change its value to 0.0.0.0 (this tells MySQL to listen on all network interfaces):

   bind-address = 0.0.0.0
   

   If you see skip-networking in the config, comment it out with a #—this line disables remote connections entirely.

4. Restart the MySQL service to apply changes:

   sudo systemctl restart mysql
   

On Windows: #

1. Open the MySQL config file (my.ini), usually located at C:\ProgramData\MySQL\MySQL Server X.X\my.ini (replace X.X with your MySQL version).

   Note: ProgramData is a hidden folder, so you might need to enable hidden items in File Explorer.

2. Find the [mysqld] section, then modify or add bind-address = 0.0.0.0.

3. Restart the MySQL service:

   • Open the Services app (press Win+R, type services.msc).
   • Find MySQLXX (XX is your version), right-click it, and select Restart.

Your PC 1’s firewall is likely blocking incoming connections to MySQL’s default port (3306). You need to open this port:

On Linux: #

Using UFW (common on Ubuntu/Debian):

sudo ufw allow 3306/tcp
sudo ufw reload

Using Firewalld (common on RHEL/CentOS/Fedora):

sudo firewall-cmd --add-port=3306/tcp --permanent
sudo firewall-cmd --reload

On Windows: #

1. Open Windows Defender Firewall with Advanced Security.
2. Go to Inbound Rules > New Rule.
3. Select Port > Next > Choose TCP and enter 3306 in Specific local ports.
4. Select Allow the connection > Next > Check all network types (or restrict to private if you’re on a local network).
5. Name the rule (e.g., "MySQL Remote Access") and click Finish.

Now verify that PC 2 can connect to PC 1’s MySQL server:

Using MySQL Client: #

On PC 2, open a terminal/command prompt and run:

mysql -u your_user -h PC1_IP_ADDRESS -p

Replace PC1_IP_ADDRESS with the actual IP of PC 1 (e.g., 192.168.1.50). If you can log in, the setup works!

Using Python (with pymysql): #

First install pymysql if you haven’t:

pip install pymysql

Then run this test script:

import pymysql

try:
    conn = pymysql.connect(
        host="PC1_IP_ADDRESS",
        user="your_user",
        password="your_password",
        database="your_database_name"  # Optional: specify a database to connect to
    )
    print("✅ Remote connection successful!")
except Exception as e:
    print(f"❌ Connection failed: {str(e)}")
finally:
    if 'conn' in locals() and conn.open:
        conn.close()

Using PHP: #

Create a PHP file on PC 2 and run it in a web server or via CLI:

<?php
$host = 'PC1_IP_ADDRESS';
$user = 'your_user';
$pass = 'your_password';
$db = 'your_database_name'; // Optional

try {
    $conn = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "✅ Remote connection successful!";
} catch(PDOException $e) {
    echo "❌ Connection failed: " . $e->getMessage();
}
$conn = null;
?>

Quick Security Notes: #

• Don’t use the root user for remote access: Always create a dedicated user with limited permissions (e.g., only access to the database your app needs).
• Restrict IP access if possible: Instead of using % in the user grant, specify PC 2’s exact IP for better security.
• For public networks: If PC 1 is on the internet, use a VPN or restrict access to specific IPs—opening 3306 to the whole internet is risky.

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