Ubuntu VPS上MySQL连接被拒，请求协助XenForo对接数据库

Hey, let's work through this connection issue step by step—this is a common problem with MySQL and application connections, so we'll cover the most likely fixes:

1. Confirm MySQL is Running on Your VPS #

First, make sure the MySQL service isn't stopped or crashed. Run this command in your Ubuntu VPS terminal:

systemctl status mysql

If you see "inactive (dead)" or similar output, start the service and set it to auto-run on boot:

systemctl start mysql
systemctl enable mysql

2. Check MySQL's Bind Address #

By default, MySQL often binds only to 127.0.0.1 (the local loopback), which means it won't accept connections from outside the VPS (even from services on the same VPS if they use the public IP). To adjust this:

• Open the MySQL config file (location varies, try one of these):

  nano /etc/mysql/my.cnf
  # OR
  nano /etc/mysql/mysql.conf.d/mysqld.cnf
  

• Look for the line starting with bind-address. Update it to:
  • 0.0.0.0 (allows connections from any IP—use cautiously for security)
  • Or your VPS's specific private/public IP if you want to restrict access
• Save the file (Ctrl+O, press Enter, then Ctrl+X in nano) and restart MySQL:

  systemctl restart mysql
  

3. Ensure Your MySQL User Has Proper Access Permissions #

Even if MySQL is listening, the user you created for XenForo might not have permission to connect from your XenForo's location:

• If XenForo runs on the same VPS, your user should be allowed to connect from localhost or 127.0.0.1.
• If XenForo runs on a different machine, your user needs access from that machine's public IP (or % for any IP—note this is less secure).

Log into MySQL first with mysql -u root -p, then run these commands (replace placeholders with your actual details):

GRANT ALL PRIVILEGES ON your_xenforo_database.* TO 'your_db_user'@'xenforo_ip_or_localhost' IDENTIFIED BY 'your_db_password';
FLUSH PRIVILEGES;

For example, if XenForo is on the same VPS, use 'your_db_user'@'localhost'. For a remote XenForo, use 'your_db_user'@'192.168.1.50' (replace with your local machine's IP).

4. Verify Firewall and Security Group Rules #

Ubuntu UFW Firewall #

Your VPS's UFW firewall might be blocking port 3306 (MySQL's default port). Check the current rules:

ufw status

If 3306 isn't listed as allowed, add it:

ufw allow 3306/tcp
ufw reload

Cloud Provider Security Group #

If your VPS is with AWS, DigitalOcean, Alibaba Cloud, etc., you also need to open port 3306 in the cloud provider's security group settings. Add an inbound rule allowing TCP traffic on port 3306 from your XenForo machine's IP (or 0.0.0.0/0 for open access—only do this if necessary).

5. Double-Check XenForo's Database Configuration #

Finally, confirm your XenForo setup has the correct details:

• Database Host: Use localhost or 127.0.0.1 if XenForo is on the same VPS; use your VPS's public IP if it's remote.
• Port: Ensure it's set to 3306 (unless you changed MySQL's default port).
• Username/Password/Database Name: Triple-check these match what you created in MySQL—typos here are a super common culprit!

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