如何实现Android应用远程连接本地24小时联网的MySQL数据库？

Hey there! Let's break down how to make your Android app connect to your local MySQL database (running on your always-on, internet-connected SQL Server 2008 system) from anywhere. Directly exposing your local database to the open internet isn't ideal for security, but there are safe, practical ways to pull this off—let's walk through the best options:

1. Build a Middle-Tier API (Most Recommended: Secure & Scalable) #

This is the gold standard because it adds a security layer between your Android app and local database, plus it gives you control over data access. Here's how to set it up:

• Pick a simple backend framework: For quick setup, go with Python's Flask, Node.js's Express, or Java's Spring Boot. Flask is great if you're new to backend development.
• Write API endpoints: Create a basic endpoint (like /fetch-data) that connects to your local MySQL database, runs your query, and returns data in JSON format (easy for Android to parse).
• Host the API locally: Run the API on your SQL Server 2008 system, configuring it to accept external connections (set the host to 0.0.0.0 instead of localhost).
• Set up port forwarding on your router: Map an external port (e.g., 8080) to the port your API is running on (e.g., 5000). This lets external requests reach your local API.
• Use dynamic DNS: Since home internet IPs change regularly, use a service like No-IP to bind your dynamic IP to a fixed domain (e.g., my-local-db.ddns.net). Your Android app can use this domain instead of a changing IP.
• Connect Android to the API: Use libraries like Retrofit or OkHttp to send HTTP requests to your domain's endpoint, then parse the JSON response.

Quick Flask API Example #

from flask import Flask, jsonify
import mysql.connector

app = Flask(__name__)

# Local MySQL connection details
db_config = {
    "host": "localhost",
    "user": "your_db_username",
    "password": "your_db_password",
    "database": "your_db_name"
}

@app.route("/fetch-data", methods=["GET"])
def fetch_data():
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM your_target_table")
    results = cursor.fetchall()
    
    cursor.close()
    conn.close()
    return jsonify(results)

if __name__ == "__main__":
    # Allow external connections
    app.run(host="0.0.0.0", port=5000)

Android OkHttp Example #

OkHttpClient client = new OkHttpClient();

Request request = new Request.Builder()
        .url("http://my-local-db.ddns.net:8080/fetch-data")
        .build();

client.newCall(request).enqueue(new Callback() {
    @Override
    public void onFailure(Call call, IOException e) {
        // Handle connection errors
        e.printStackTrace();
    }

    @Override
    public void onResponse(Call call, Response response) throws IOException {
        if (response.isSuccessful()) {
            String jsonResponse = response.body().string();
            // Parse JSON using Gson or Moshi
        }
    }
});

2. Use a Tunnel Service (For Quick Testing) #

If you need a temporary solution for testing, use a tunnel tool like ngrok to expose your local database port to the internet:

• Download ngrok to your local system, sign up for a free account, and authenticate with your token.
• Run ngrok tcp 3306 (3306 is MySQL's default port). Ngrok will generate a public address like tcp://0.tcp.ngrok.io:12345.
• In your Android app's MySQL connection config, use 0.tcp.ngrok.io as the host and 12345 as the port, plus your local database credentials.

⚠️ Note: Free ngrok addresses change every time you restart the tool, and there are traffic limits. This is only for testing, not long-term use.

3. Directly Expose the Database Port (Not Recommended) #

This is the riskiest option because it puts your entire database on the open internet, making it vulnerable to brute-force attacks and data breaches. If you absolutely must try it:

• Configure MySQL to allow remote connections: Edit my.ini (Windows) or my.cnf (Linux) to set bind-address = 0.0.0.0, then run this SQL command to grant remote access:

  GRANT ALL PRIVILEGES ON your_db.* TO 'your_user'@'%' IDENTIFIED BY 'your_strong_password';
  FLUSH PRIVILEGES;
  

• Set up port forwarding on your router to map external port 3306 to your local system's 3306 port.
• Use dynamic DNS to get a fixed domain, then connect Android directly via JDBC.

⚠️ Strong Warning: Only do this if you've enabled strict firewall rules, use an extremely strong password, and limit access to specific IPs. Even then, it's not a safe long-term solution.

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