如何通过本地代码连接局域网内树莓派上的MariaDB服务器

Nice work getting IntelliJ's Database Tool connected via SSH! Now let's get your local code talking directly to that MariaDB instance on your Raspberry Pi. Here's what you need to do:

By default, MariaDB only listens for connections from the Pi itself (localhost). We need to update its config to allow connections over your local network:

• SSH into your Pi and open the MariaDB server config file (location varies slightly by version, but this is common):

  sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
  

• Find the line starting with bind-address and change its value to either:
  • Your Pi's local network IP (e.g., 192.168.1.105 – find this with hostname -I on the Pi) for restricted access, or
  • 0.0.0.0 to allow connections from any IP (use this only on a trusted network)
• Save the file (Ctrl+O, then Enter) and exit nano (Ctrl+X)
• Restart MariaDB to apply changes:

  sudo systemctl restart mariadb
  

Your existing database user is probably restricted to localhost connections. Let's update its permissions:

• Log into MariaDB on the Pi:

  mysql -u root -p
  

• Run this SQL command, replacing the placeholders with your actual details:

  GRANT ALL PRIVILEGES ON your_database_name.* TO 'your_db_username'@'your_local_machine_ip' IDENTIFIED BY 'your_db_password';
  

  • If you want to allow access from any device on your network, replace your_local_machine_ip with % (not recommended for untrusted networks)
• Flush privileges to make the change take effect:

  FLUSH PRIVILEGES;
  

• Exit MariaDB with EXIT;

If your Pi uses ufw (common on Raspberry Pi OS), you need to allow traffic to MariaDB's default port (3306):

• Allow connections from your local machine only (most secure):

  sudo ufw allow from your_local_machine_ip to any port 3306
  

• Or allow all local network connections (less secure):

  sudo ufw allow 3306
  

• Verify the rule is active with:

  sudo ufw status
  

Now you can use your language's database driver to connect directly. Here are examples for common languages:

Java (JDBC) #

First, add the MariaDB JDBC dependency to your project (e.g., via Maven or Gradle). Then use this code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class PiDBConnector {
    public static void main(String[] args) {
        String dbUrl = "jdbc:mariadb://your_pi_lan_ip:3306/your_database_name";
        String username = "your_db_username";
        String password = "your_db_password";

        try (Connection conn = DriverManager.getConnection(dbUrl, username, password)) {
            System.out.println("Successfully connected to the Pi's MariaDB!");
        } catch (SQLException e) {
            System.err.println("Connection failed: " + e.getMessage());
        }
    }
}

Python (pymysql) #

Install the dependency first:

pip install pymysql

Then use this code:

import pymysql

try:
    connection = pymysql.connect(
        host="your_pi_lan_ip",
        port=3306,
        user="your_db_username",
        password="your_db_password",
        db="your_database_name"
    )
    print("Successfully connected to the Pi's MariaDB!")
    connection.close()
except Exception as e:
    print(f"Connection failed: {str(e)}")

Python (SQLAlchemy) #

If you prefer using an ORM:

pip install sqlalchemy pymysql

Then:

from sqlalchemy import create_engine

engine = create_engine(
    "mariadb+pymysql://your_db_username:your_db_password@your_pi_lan_ip:3306/your_database_name"
)

try:
    with engine.connect() as conn:
        print("Successfully connected to the Pi's MariaDB!")
except Exception as e:
    print(f"Connection failed: {str(e)}")

• Verify you can ping your Pi's local IP from your machine (e.g., ping 192.168.1.105)
• Check if MariaDB is running on the Pi: sudo systemctl status mariadb
• Confirm your user's allowed hosts with SELECT user, host FROM mysql.user; in MariaDB
• If you still run into issues, you can use an SSH tunnel in code (similar to IntelliJ's setup) – libraries like JSch for Java or sshtunnel for Python can handle this

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