Ubuntu 16.04下如何通过私有IP从应用服务器访问远程MySQL数据库

云原生阿岚

2026-5-19

Got it, let's get your app server talking to that MySQL database over its private IP on Ubuntu 16.04. Since you already have SSH access set up and a user tied to your app server's IP, we just need to adjust a few MySQL settings and double-check permissions/firewall rules.

1. Update MySQL's Bind Address

By default, MySQL only listens on the local loopback (127.0.0.1), which means it won't accept connections from other machines on the private network. Let's fix that:

Open the MySQL config file with your preferred editor (I'll use nano here):
```
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
Look for the line starting with bind-address. Replace the existing value with your database server's private IP (e.g., 192.168.1.100). If you want to allow connections from any private IP on your subnet, you can use 0.0.0.0, but using the specific private IP is more secure.
```
bind-address = 192.168.1.100
```
Save the file (Ctrl+O, then Enter) and exit nano (Ctrl+X).

2. Restart MySQL to Apply Changes

Your config change won't take effect until you restart the MySQL service:

sudo systemctl restart mysql

You can verify it's running with:

sudo systemctl status mysql

3. Double-Check Your MySQL User's Host Permission

You mentioned creating a user linked to the app server's IP, but let's confirm that the permission is correctly set up:

Log into MySQL on your database server (use your root credentials):
```
mysql -u root -p
```
Run this query to check the user's allowed host:
```
SELECT user, host FROM mysql.user WHERE user = 'your_app_username';
```
Make sure the host column matches your app server's private IP (e.g., 192.168.1.200) or a subnet wildcard like 192.168.1.% if you want to allow any machine on that subnet.

If the host is incorrect, update the permission with this command (replace placeholders with your actual values):

GRANT ALL PRIVILEGES ON your_target_database.* TO 'your_app_username'@'192.168.1.200' IDENTIFIED BY 'your_app_user_password';

Flush the privileges to apply the change immediately:
```
FLUSH PRIVILEGES;
```
Exit MySQL with exit;.

4. Allow MySQL Traffic Through UFW (Firewall)

Ubuntu 16.04 uses UFW by default, so we need to let traffic from your app server reach MySQL's default port (3306):

On the database server, run this command to allow the app server's IP access to port 3306:
```
sudo ufw allow from 192.168.1.200 to any port 3306
```
Verify the rule was added correctly:
```
sudo ufw status
```
You should see a line like 3306/tcp ALLOW 192.168.1.200.

5. Test the Connection From Your App Server

Now it's time to test if the connection works from the app server:

First, if you don't have the MySQL client installed on the app server, install it:
```
sudo apt-get update && sudo apt-get install mysql-client
```
Run this command to connect to the database server (replace placeholders with your details):
```
mysql -u your_app_username -h 192.168.1.100 -p
```
Enter the user's password when prompted. If you get into the MySQL shell, you're good to go!

If you run into issues, double-check:

The private IPs of both servers are correct and they're on the same private network
The MySQL user's host matches the app server's IP
UFW rules are correctly configured
MySQL is running and listening on the private IP

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