Of course! Using Python with MySQL on OpenWrt is a powerful combination, perfect for tasks like logging network data, creating a small home server application, or automating network monitoring.

Here’s a comprehensive guide covering everything from installation to a complete working example.

Prerequisites: The OpenWrt Environment

First, you need a working OpenWrt installation. This guide assumes you have:

• OpenWrt installed on a device (like a Raspberry Pi, an x86 PC, or a router with enough storage).
• Network access to the device (SSH is the standard way to connect).
• opkg, the OpenWrt package manager, is available.

Step-by-Step Installation Guide

We'll install three main components:

1. Python 3: The programming language.
2. python3-pip: The package installer for Python, making it easy to add libraries.
3. MySQL Server (mysql-server): The database to store our data.
4. Python MySQL Library (mysql-connector-python): The Python module to talk to MySQL.

Step 1: Update Package Lists and Install Python

It's always good practice to update your package lists before installing anything new.

# Log in to your OpenWrt device via SSH
# Update the list of available packages
opkg update
# Install Python 3 and pip (Python's package installer)
opkg install python3 python3-pip

Step 2: Install the MySQL Server

This will install the MariaDB server, which is the default, fully-compatible drop-in replacement for MySQL on OpenWrt.

# Install the MySQL/MariaDB server
opkg install mysql-server
# Enable and start the MySQL service so it runs on boot
/etc/init.d/mysqld enable
/etc/init.d/mysqld start

Step 3: Secure Your MySQL Installation (Important!)

Run the secure installation script. This will help you set a root password, remove anonymous users, disallow remote root login, and remove test databases. It's highly recommended you do this.

mysql_secure_installation

You will be prompted through a series of questions. For a typical setup, the answers are:

• Enter current password for root (enter for none): Press Enter.
• Switch to unix_socket authentication [Y/n] Y (This is the default and recommended method for OpenWrt).
• Set root password? [Y/n] Y. Choose a strong password.
• Remove anonymous users? [Y/n] Y.
• Disallow root login remotely? [Y/n] Y.
• Remove test database and access to it? [Y/n] Y.
• Reload privilege tables now? [Y/n] Y.

Step 4: Install the Python MySQL Library

Now, use pip3 to install the official MySQL driver from Oracle.

# Install the MySQL connector for Python
pip3 install mysql-connector-python

You're all set! Your OpenWrt device now has Python, MySQL, and the necessary library to connect them.

Creating a Simple Python Script

Let's create a Python script that connects to the MySQL database, creates a table, inserts some data, and then reads it back.

Step 1: Log in to MySQL and Create a Database

First, we need a database and a user for our Python script to use.

# Log in to the MySQL server as root
mysql -u root -p
# You will be prompted for the root password you set earlier
# Once inside the MySQL shell, create a new database
CREATE DATABASE openwrt_db;
# Create a new user and grant it privileges on the new database
# Replace 'your_python_user' and 'your_strong_password' with your own values
CREATE USER 'your_python_user'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT ALL PRIVILEGES ON openwrt_db.* TO 'your_python_user'@'localhost';
FLUSH PRIVILEGES;
# Exit the MySQL shell
EXIT;

Step 2: Write the Python Script

Create a Python file on your OpenWrt device. You can use vi, nano, or even cat with a heredoc.

# Create a file named mysql_test.py
vi mysql_test.py

Paste the following code into the file. Remember to update the database connection details with the ones you created.

import mysql.connector
from mysql.connector import Error
# --- Database Configuration ---
# Replace with your actual database details
DB_CONFIG = {
    'host': 'localhost',
    'database': 'openwrt_db',
    'user': 'your_python_user',
    'password': 'your_strong_password'
}
def create_connection():
    """Create a database connection to the MySQL server."""
    connection = None
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        if connection.is_connected():
            print("Successfully connected to the database")
            return connection
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
    return None
def create_table(connection):
    """Create a sample table if it doesn't exist."""
    create_table_query = """
    CREATE TABLE IF NOT EXISTS network_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        device_ip VARCHAR(15),
        action VARCHAR(50)
    )
    """
    cursor = connection.cursor()
    try:
        cursor.execute(create_table_query)
        print("Table 'network_logs' created successfully or already exists.")
    except Error as e:
        print(f"Error creating table: {e}")
    finally:
        cursor.close()
def insert_log(connection, device_ip, action):
    """Insert a new log entry into the network_logs table."""
    insert_query = "INSERT INTO network_logs (device_ip, action) VALUES (%s, %s)"
    log_data = (device_ip, action)
    cursor = connection.cursor()
    try:
        cursor.execute(insert_query, log_data)
        connection.commit() # Commit the transaction
        print(f"Successfully inserted log: {log_data}")
    except Error as e:
        print(f"Error inserting data: {e}")
    finally:
        cursor.close()
def read_logs(connection):
    """Read and print all log entries from the network_logs table."""
    select_query = "SELECT * FROM network_logs"
    cursor = connection.cursor(dictionary=True) # dictionary=True gives named columns
    try:
        cursor.execute(select_query)
        records = cursor.fetchall()
        print("\n--- Current Logs ---")
        if not records:
            print("No logs found.")
        else:
            for row in records:
                print(f"ID: {row['id']}, Timestamp: {row['timestamp']}, IP: {row['device_ip']}, Action: {row['action']}")
    except Error as e:
        print(f"Error reading data: {e}")
    finally:
        cursor.close()
def main():
    """Main function to demonstrate database operations."""
    connection = create_connection()
    if connection is None:
        return
    # 1. Create the table
    create_table(connection)
    # 2. Insert some sample data
    insert_log(connection, "192.168.1.101", "Connected to WiFi")
    insert_log(connection, "192.168.1.102", "Disconnected")
    insert_log(connection, "192.168.1.101", "DNS Query")
    # 3. Read and display all data
    read_logs(connection)
    # 4. Close the connection
    if connection.is_connected():
        connection.close()
        print("\nMySQL connection is closed.")
if __name__ == "__main__":
    main()

Step 3: Run the Script

Execute the script from your OpenWrt command line.

python3 mysql_test.py

You should see output similar to this:

Successfully connected to the database
Table 'network_logs' created successfully or already exists.
Successfully inserted log: ('192.168.1.101', 'Connected to WiFi')
Successfully inserted log: ('192.168.1.102', 'Disconnected')
Successfully inserted log: ('192.168.1.101', 'DNS Query')
--- Current Logs ---
ID: 1, Timestamp: 2025-10-27 10:30:00, IP: 192.168.1.101, Action: Connected to WiFi
ID: 2, Timestamp: 2025-10-27 10:30:01, IP: 192.168.1.102, Action: Disconnected
ID: 3, Timestamp: 2025-10-27 10:30:02, IP: 192.168.1.101, Action: DNS Query
MySQL connection is closed.

If you run the script again, it will insert new logs and show all previous ones, demonstrating that the data persists.

Important Considerations for OpenWrt

• Storage and Performance: OpenWrt devices often have limited flash storage (e.g., 256MB). MySQL's default configuration (/etc/my.cnf) is designed for servers with lots of