Ubuntu Server 16.04下MySQL远程连接配置失败求助

Hey there, let's troubleshoot that remote MySQL connection issue you're facing on Ubuntu 16.04. I've helped dozens of folks work through this exact problem, so let's go step by step to get your Workbench connected.

By default, MySQL users are restricted to local connections only. Let's make sure your globalAdmin account has permission to connect from your local machine:

1. Log into MySQL as root via SSH:

   mysql -u root -p
   

2. Check which hosts globalAdmin can connect from:

   SELECT user, host FROM mysql.user WHERE user = 'globalAdmin';
   

   If the host column shows localhost or 127.0.0.1, that's why your remote connection is failing.
3. Grant access from your specific local IP (replace <YOUR_LOCAL_IP> with the IP of the machine running Workbench):

   GRANT ALL PRIVILEGES ON *.* TO 'globalAdmin'@'<YOUR_LOCAL_IP>' IDENTIFIED BY 'your-globaladmin-password';
   

   Note: If you need temporary access from any IP (not recommended for production), use % instead of <YOUR_LOCAL_IP>.
4. Flush privileges to apply the changes:

   FLUSH PRIVILEGES;
   

5. Exit MySQL with exit;

MySQL is configured to only listen for local connections by default. We need to change this to allow remote connections:

1. Open the MySQL configuration file with nano:

   nano /etc/mysql/mysql.conf.d/mysqld.cnf
   

2. Find the line starting with bind-address (it's usually under the [mysqld] section). Change it from:

   bind-address = 127.0.0.1
   

   To:

   bind-address = 0.0.0.0
   

3. Save the file (press Ctrl+O, then Enter) and exit nano (Ctrl+X).
4. Restart MySQL to apply the change:

   systemctl restart mysql
   

Ubuntu 16.04 uses UFW by default, so we need to allow incoming traffic on MySQL's default port (3306):

1. Check if UFW is active:

   ufw status
   

2. If it's active, allow traffic from your local IP to port 3306 (most secure option):

   ufw allow from <YOUR_LOCAL_IP> to any port 3306
   

   For testing, you can allow all IPs temporarily with ufw allow 3306, but remember to restrict this later for security.
3. Verify the rule was added:

   ufw status
   

VpsDime might have an additional network-level firewall (like a security group) in their control panel. Log into your VpsDime account and make sure port 3306 is open for incoming traffic from your local IP.

Before jumping back to Workbench, let's verify the port is reachable:

From your local machine, run this command (replace <YOUR_VPS_IP> with your VPS's public IP):

telnet <YOUR_VPS_IP> 3306

If you see a response starting with your MySQL version (e.g., 5.7.42...), the port is open and ready. If it times out, double-check your firewall rules and MySQL bind address.

Now try connecting again with Workbench using your VPS's public IP, globalAdmin as the username, your password, and port 3306. That should do the trick!

内容的提问来源于stack exchange，提问作者José Caballero