Home » Как хранить и извлекать данные в MariaDB с помощью Python на Ubuntu 24

Как хранить и извлекать данные в MariaDB с помощью Python на Ubuntu 24

Если ты настраиваешь серверы и работаешь с базами данных, то наверняка сталкивался с задачей подключения Python-приложений к MariaDB. Это не просто классическая связка, а мощнейший инструмент для автоматизации, мониторинга и управления данными на серверах. В этой статье разберём, как правильно настроить связку Python + MariaDB на Ubuntu 24, рассмотрим лучшие практики и подводные камни, с которыми можно столкнуться в реальных проектах.

Зачем это нужно? Представь, что у тебя есть сервер мониторинга, который собирает метрики, логи и статусы всех твоих серверов. Или система автоматического развёртывания, которая должна хранить информацию о конфигурациях. MariaDB даёт надёжное хранилище, а Python — гибкость в обработке данных.

Как это работает: архитектура и принципы

MariaDB — это форк MySQL, который полностью совместим с оригиналом, но развивается активнее и предлагает больше возможностей. Особенно это заметно в области производительности и расширенных типов данных.

Связка Python + MariaDB работает через специальные драйверы-коннекторы:

mysql-connector-python — официальный драйвер Oracle
PyMySQL — чистый Python-драйвер
mysqlclient — C-расширение, наследник MySQLdb
mariadb — официальный драйвер MariaDB Foundation

Каждый из них имеет свои особенности производительности и совместимости. Но обо всём по порядку.

Пошаговая настройка: от установки до первого запроса

Начнём с установки MariaDB на Ubuntu 24. Свежая версия системы уже включает MariaDB 10.11 в репозиториях:

sudo apt update
sudo apt install mariadb-server mariadb-client python3-pip python3-venv

Сразу после установки запускаем скрипт безопасности — это критически важно для продакшена:

sudo mysql_secure_installation

Отвечаем на вопросы скрипта:

Устанавливаем пароль для root
Удаляем анонимные учётные записи
Запрещаем удалённый доступ для root
Удаляем тестовую базу
Перезагружаем таблицы привилегий

Создаём виртуальное окружение для Python (это хорошая практика для изоляции зависимостей):

python3 -m venv mariadb_env
source mariadb_env/bin/activate

Теперь устанавливаем необходимые пакеты. Я рекомендую начать с официального драйвера MariaDB:

pip install mariadb

Для установки mariadb-коннектора может потребоваться установка дополнительных системных пакетов:

sudo apt install libmariadb-dev

Создание базы данных и пользователя

Подключаемся к MariaDB как root и создаём рабочую базу:

sudo mysql -u root -p

В консоли MariaDB выполняем:

CREATE DATABASE server_monitoring;
CREATE USER 'python_user'@'localhost' IDENTIFIED BY 'secure_password_123';
GRANT ALL PRIVILEGES ON server_monitoring.* TO 'python_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Для продакшена лучше создать пользователя с ограниченными правами:

CREATE USER 'monitoring_app'@'localhost' IDENTIFIED BY 'app_password_456';
GRANT SELECT, INSERT, UPDATE, DELETE ON server_monitoring.* TO 'monitoring_app'@'localhost';
FLUSH PRIVILEGES;

Первый Python-скрипт: подключение и базовые операции

Создаём базовый скрипт для проверки соединения:

import mariadb
import sys

try:
    conn = mariadb.connect(
        user="python_user",
        password="secure_password_123",
        host="localhost",
        port=3306,
        database="server_monitoring"
    )
    
    cursor = conn.cursor()
    cursor.execute("SELECT VERSION()")
    version = cursor.fetchone()
    print(f"MariaDB version: {version[0]}")
    
    conn.close()
    
except mariadb.Error as e:
    print(f"Error connecting to MariaDB: {e}")
    sys.exit(1)

Если всё настроено правильно, увидишь версию MariaDB.

Практические примеры: создание и работа с таблицами

Создадим таблицу для мониторинга серверов:

import mariadb
from datetime import datetime

def create_connection():
    try:
        conn = mariadb.connect(
            user="python_user",
            password="secure_password_123",
            host="localhost",
            port=3306,
            database="server_monitoring"
        )
        return conn
    except mariadb.Error as e:
        print(f"Error: {e}")
        return None

def create_servers_table():
    conn = create_connection()
    if conn is None:
        return
    
    cursor = conn.cursor()
    
    create_table_query = """
    CREATE TABLE IF NOT EXISTS servers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        hostname VARCHAR(255) NOT NULL,
        ip_address VARCHAR(45) NOT NULL,
        status ENUM('online', 'offline', 'maintenance') DEFAULT 'offline',
        cpu_usage DECIMAL(5,2),
        memory_usage DECIMAL(5,2),
        disk_usage DECIMAL(5,2),
        last_check TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    """
    
    try:
        cursor.execute(create_table_query)
        conn.commit()
        print("Table 'servers' created successfully")
    except mariadb.Error as e:
        print(f"Error creating table: {e}")
    finally:
        conn.close()

create_servers_table()

Вставка данных: лучшие практики

Правильная вставка данных с использованием параметризованных запросов:

def add_server(hostname, ip_address, status='offline'):
    conn = create_connection()
    if conn is None:
        return False
    
    cursor = conn.cursor()
    
    insert_query = """
    INSERT INTO servers (hostname, ip_address, status)
    VALUES (?, ?, ?)
    """
    
    try:
        cursor.execute(insert_query, (hostname, ip_address, status))
        conn.commit()
        server_id = cursor.lastrowid
        print(f"Server added with ID: {server_id}")
        return True
    except mariadb.Error as e:
        print(f"Error inserting server: {e}")
        return False
    finally:
        conn.close()

# Пример использования
add_server("web-server-01", "192.168.1.100", "online")
add_server("db-server-01", "192.168.1.101", "online")
add_server("backup-server", "192.168.1.102", "maintenance")

Массовая вставка данных

Для больших объёмов данных используем executemany():

def bulk_insert_servers(servers_data):
    conn = create_connection()
    if conn is None:
        return False
    
    cursor = conn.cursor()
    
    insert_query = """
    INSERT INTO servers (hostname, ip_address, status, cpu_usage, memory_usage, disk_usage)
    VALUES (?, ?, ?, ?, ?, ?)
    """
    
    try:
        cursor.executemany(insert_query, servers_data)
        conn.commit()
        print(f"Inserted {cursor.rowcount} servers")
        return True
    except mariadb.Error as e:
        print(f"Error in bulk insert: {e}")
        return False
    finally:
        conn.close()

# Пример данных для массовой вставки
servers_data = [
    ("web-01", "10.0.1.10", "online", 45.5, 67.8, 23.4),
    ("web-02", "10.0.1.11", "online", 38.2, 72.1, 45.6),
    ("worker-01", "10.0.1.20", "online", 89.1, 56.7, 67.8),
    ("worker-02", "10.0.1.21", "maintenance", 12.3, 34.5, 78.9)
]

bulk_insert_servers(servers_data)

Извлечение данных: запросы и обработка результатов

Различные способы получения данных из базы:

def get_all_servers():
    conn = create_connection()
    if conn is None:
        return []
    
    cursor = conn.cursor()
    
    try:
        cursor.execute("SELECT * FROM servers ORDER BY last_check DESC")
        servers = cursor.fetchall()
        return servers
    except mariadb.Error as e:
        print(f"Error fetching servers: {e}")
        return []
    finally:
        conn.close()

def get_servers_by_status(status):
    conn = create_connection()
    if conn is None:
        return []
    
    cursor = conn.cursor()
    
    try:
        cursor.execute("SELECT hostname, ip_address, cpu_usage, memory_usage FROM servers WHERE status = ?", (status,))
        servers = cursor.fetchall()
        return servers
    except mariadb.Error as e:
        print(f"Error fetching servers by status: {e}")
        return []
    finally:
        conn.close()

def get_server_stats():
    conn = create_connection()
    if conn is None:
        return None
    
    cursor = conn.cursor()
    
    query = """
    SELECT 
        status,
        COUNT(*) as count,
        AVG(cpu_usage) as avg_cpu,
        AVG(memory_usage) as avg_memory,
        AVG(disk_usage) as avg_disk
    FROM servers 
    WHERE cpu_usage IS NOT NULL
    GROUP BY status
    """
    
    try:
        cursor.execute(query)
        stats = cursor.fetchall()
        return stats
    except mariadb.Error as e:
        print(f"Error fetching stats: {e}")
        return None
    finally:
        conn.close()

# Использование
all_servers = get_all_servers()
online_servers = get_servers_by_status('online')
stats = get_server_stats()

print("Server statistics:")
for stat in stats:
    print(f"Status: {stat[0]}, Count: {stat[1]}, Avg CPU: {stat[2]:.2f}%")

Обновление данных и транзакции

Обновление данных с использованием транзакций:

def update_server_metrics(server_id, cpu_usage, memory_usage, disk_usage):
    conn = create_connection()
    if conn is None:
        return False
    
    cursor = conn.cursor()
    
    update_query = """
    UPDATE servers 
    SET cpu_usage = ?, memory_usage = ?, disk_usage = ?, last_check = NOW()
    WHERE id = ?
    """
    
    try:
        cursor.execute(update_query, (cpu_usage, memory_usage, disk_usage, server_id))
        conn.commit()
        
        if cursor.rowcount > 0:
            print(f"Server {server_id} updated successfully")
            return True
        else:
            print(f"Server {server_id} not found")
            return False
            
    except mariadb.Error as e:
        print(f"Error updating server: {e}")
        conn.rollback()
        return False
    finally:
        conn.close()

def batch_update_server_status():
    conn = create_connection()
    if conn is None:
        return False
    
    cursor = conn.cursor()
    
    try:
        # Начинаем транзакцию
        conn.autocommit = False
        
        # Обновляем статус серверов, которые не отвечают более 5 минут
        cursor.execute("""
            UPDATE servers 
            SET status = 'offline' 
            WHERE last_check < NOW() - INTERVAL 5 MINUTE 
            AND status = 'online'
        """)
        
        # Логируем изменения
        cursor.execute("""
            INSERT INTO server_log (action, details, timestamp)
            SELECT 'status_change', CONCAT('Server ', hostname, ' marked as offline'), NOW()
            FROM servers 
            WHERE status = 'offline' AND last_check < NOW() - INTERVAL 5 MINUTE
        """)
        
        conn.commit()
        print("Batch update completed successfully")
        return True
        
    except mariadb.Error as e:
        print(f"Error in batch update: {e}")
        conn.rollback()
        return False
    finally:
        conn.autocommit = True
        conn.close()

Сравнение драйверов: выбираем лучший

Вот сравнительная таблица основных драйверов:

Драйвер	Производительность	Совместимость	Активность разработки	Рекомендации
mariadb	Высокая	MariaDB 10.2+	Активная	Лучший выбор для новых проектов
mysqlclient	Высокая	MySQL/MariaDB	Стабильная	Хорошо для миграции с Django
PyMySQL	Средняя	MySQL/MariaDB	Стабильная	Простота установки
mysql-connector-python	Низкая	MySQL/MariaDB	Активная	Только если нужна поддержка Oracle

По моему опыту, для новых проектов с MariaDB лучше использовать официальный драйвер mariadb. Он показывает лучшую производительность и полную совместимость с новыми возможностями.

Обработка ошибок и мониторинг соединений

Правильная обработка ошибок критически важна для стабильной работы:

import mariadb
import time
import logging
from contextlib import contextmanager

# Настройка логирования
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class DatabaseManager:
    def __init__(self, host='localhost', user='python_user', password='secure_password_123', database='server_monitoring'):
        self.connection_params = {
            'host': host,
            'user': user,
            'password': password,
            'database': database,
            'port': 3306
        }
        self.max_retries = 3
        self.retry_delay = 1
    
    @contextmanager
    def get_connection(self):
        conn = None
        for attempt in range(self.max_retries):
            try:
                conn = mariadb.connect(**self.connection_params)
                yield conn
                break
            except mariadb.Error as e:
                logger.error(f"Connection attempt {attempt + 1} failed: {e}")
                if attempt < self.max_retries - 1:
                    time.sleep(self.retry_delay)
                else:
                    raise
            finally:
                if conn:
                    conn.close()
    
    def execute_query(self, query, params=None):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            try:
                cursor.execute(query, params)
                conn.commit()
                return cursor.fetchall()
            except mariadb.Error as e:
                logger.error(f"Query execution failed: {e}")
                conn.rollback()
                raise

# Использование
db = DatabaseManager()

try:
    servers = db.execute_query("SELECT * FROM servers WHERE status = ?", ('online',))
    for server in servers:
        print(f"Server: {server[1]}, IP: {server[2]}")
except mariadb.Error as e:
    logger.error(f"Database operation failed: {e}")

Пул соединений для высоконагруженных приложений

Для приложений с высокой нагрузкой рекомендую использовать пул соединений:

import mariadb
from mariadb.pool import ConnectionPool

class DatabasePool:
    def __init__(self, pool_name='web_app', pool_size=10):
        self.pool_config = {
            'pool_name': pool_name,
            'pool_size': pool_size,
            'user': 'python_user',
            'password': 'secure_password_123',
            'host': 'localhost',
            'database': 'server_monitoring'
        }
        self.pool = mariadb.ConnectionPool(**self.pool_config)
    
    def get_connection(self):
        return self.pool.get_connection()
    
    def execute_query(self, query, params=None):
        conn = self.get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute(query, params)
            result = cursor.fetchall()
            conn.commit()
            return result
        except mariadb.Error as e:
            conn.rollback()
            raise
        finally:
            conn.close()

# Инициализация пула
db_pool = DatabasePool()

# Использование
try:
    servers = db_pool.execute_query("SELECT hostname, status FROM servers LIMIT 10")
    for server in servers:
        print(f"Server: {server[0]}, Status: {server[1]}")
except mariadb.Error as e:
    print(f"Error: {e}")

Интеграция с ORM: SQLAlchemy

Для более сложных проектов можно использовать SQLAlchemy:

pip install sqlalchemy

from sqlalchemy import create_engine, Column, Integer, String, DECIMAL, DateTime, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime

# Создаём движок
engine = create_engine('mariadb+mariadb://python_user:secure_password_123@localhost/server_monitoring')
Base = declarative_base()

class Server(Base):
    __tablename__ = 'servers_orm'
    
    id = Column(Integer, primary_key=True)
    hostname = Column(String(255), nullable=False)
    ip_address = Column(String(45), nullable=False)
    status = Column(Enum('online', 'offline', 'maintenance'), default='offline')
    cpu_usage = Column(DECIMAL(5,2))
    memory_usage = Column(DECIMAL(5,2))
    disk_usage = Column(DECIMAL(5,2))
    last_check = Column(DateTime, default=datetime.utcnow)
    created_at = Column(DateTime, default=datetime.utcnow)

# Создаём таблицы
Base.metadata.create_all(engine)

# Создаём сессию
Session = sessionmaker(bind=engine)
session = Session()

# Добавляем сервер
new_server = Server(
    hostname='orm-server-01',
    ip_address='192.168.1.200',
    status='online',
    cpu_usage=45.7,
    memory_usage=67.2,
    disk_usage=23.1
)

session.add(new_server)
session.commit()

# Запрос серверов
online_servers = session.query(Server).filter(Server.status == 'online').all()
for server in online_servers:
    print(f"Server: {server.hostname}, CPU: {server.cpu_usage}%")

session.close()

Автоматизация и скрипты мониторинга

Практический пример скрипта для автоматического мониторинга:

#!/usr/bin/env python3
import mariadb
import psutil
import socket
import json
import time
from datetime import datetime

class ServerMonitor:
    def __init__(self):
        self.db_config = {
            'host': 'localhost',
            'user': 'monitoring_app',
            'password': 'app_password_456',
            'database': 'server_monitoring'
        }
        self.hostname = socket.gethostname()
        self.ip_address = socket.gethostbyname(self.hostname)
    
    def get_system_metrics(self):
        return {
            'cpu_usage': psutil.cpu_percent(interval=1),
            'memory_usage': psutil.virtual_memory().percent,
            'disk_usage': psutil.disk_usage('/').percent,
            'timestamp': datetime.now()
        }
    
    def save_metrics(self, metrics):
        try:
            conn = mariadb.connect(**self.db_config)
            cursor = conn.cursor()
            
            # Обновляем или вставляем запись о сервере
            upsert_query = """
                INSERT INTO servers (hostname, ip_address, status, cpu_usage, memory_usage, disk_usage, last_check)
                VALUES (?, ?, 'online', ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE
                status = 'online',
                cpu_usage = VALUES(cpu_usage),
                memory_usage = VALUES(memory_usage),
                disk_usage = VALUES(disk_usage),
                last_check = VALUES(last_check)
            """
            
            cursor.execute(upsert_query, (
                self.hostname,
                self.ip_address,
                metrics['cpu_usage'],
                metrics['memory_usage'],
                metrics['disk_usage'],
                metrics['timestamp']
            ))
            
            conn.commit()
            print(f"Metrics saved for {self.hostname}")
            
        except mariadb.Error as e:
            print(f"Error saving metrics: {e}")
        finally:
            if conn:
                conn.close()
    
    def run_monitoring(self, interval=60):
        while True:
            try:
                metrics = self.get_system_metrics()
                self.save_metrics(metrics)
                time.sleep(interval)
            except KeyboardInterrupt:
                print("Monitoring stopped")
                break
            except Exception as e:
                print(f"Error in monitoring loop: {e}")
                time.sleep(10)

if __name__ == "__main__":
    monitor = ServerMonitor()
    monitor.run_monitoring()

Безопасность и производительность

Важные аспекты безопасности при работе с MariaDB:

Используй подготовленные запросы — всегда параметризуй запросы для защиты от SQL-инъекций
Ограничивай привилегии пользователей — создавай отдельных пользователей для каждого приложения
Используй SSL-соединения для удалённых подключений
Настрой правильные права доступа на файлы конфигурации

Для SSL-соединения:

conn = mariadb.connect(
    user="python_user",
    password="secure_password_123",
    host="remote-server.com",
    database="server_monitoring",
    ssl_ca="/path/to/ca-cert.pem",
    ssl_cert="/path/to/client-cert.pem",
    ssl_key="/path/to/client-key.pem"
)

Оптимизация производительности

Несколько советов для повышения производительности:

Используй индексы для часто запрашиваемых полей
Оптимизируй запросы — избегай SELECT *
Используй пул соединений для высоконагруженных приложений
Настрой буферы MariaDB в соответствии с объёмом RAM

# Создание индексов
CREATE INDEX idx_hostname ON servers(hostname);
CREATE INDEX idx_status_lastcheck ON servers(status, last_check);
CREATE INDEX idx_timestamp ON servers(last_check);

Отладка и логирование

Включи логирование запросов для отладки:

import mariadb
import logging

# Настройка логирования SQL-запросов
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)

def execute_with_logging(cursor, query, params=None):
    logger.debug(f"Executing query: {query}")
    if params:
        logger.debug(f"Parameters: {params}")
    
    start_time = time.time()
    cursor.execute(query, params)
    execution_time = time.time() - start_time
    
    logger.debug(f"Query executed in {execution_time:.4f} seconds")
    return cursor.fetchall()

Интересные возможности MariaDB

MariaDB предлагает несколько уникальных фич, которые стоит использовать:

JSON-поля — можно хранить и индексировать JSON-данные
Виртуальные столбцы — автоматически вычисляемые поля
Временные таблицы — System Versioned Tables для отслеживания изменений
Партиционирование — для больших таблиц

Пример с JSON-полем:

CREATE TABLE server_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hostname VARCHAR(255),
    config JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO server_config (hostname, config) VALUES 
('web-01', '{"nginx": {"worker_processes": 4, "worker_connections": 1024}, "php": {"memory_limit": "256M"}}');

SELECT hostname, JSON_EXTRACT(config, '$.nginx.worker_processes') as nginx_workers 
FROM server_config;

Развёртывание на VPS

Для развёртывания такой системы в продакшене тебе понадобится надёжный VPS. Рекомендую присмотреться к аренде VPS с SSD-дисками и достаточным объёмом RAM для буферов MariaDB. Для более серьёзных нагрузок можешь рассмотреть выделенный сервер.

Альтернативные решения

Стоит упомянуть альтернативы для разных сценариев:

PostgreSQL + psycopg2 — для сложных запросов и JSON-данных
SQLite + sqlite3 — для небольших проектов и прототипов
Redis + redis-py — для кэширования и сессий
InfluxDB + influxdb-client — для временных рядов и метрик

Каждое решение имеет свои сильные стороны. MariaDB отлично подходит для веб-приложений, систем управления контентом и бизнес-приложений.

Статистика и бенчмарки

По данным различных бенчмарков, MariaDB показывает:

На 20-30% лучшую производительность по сравнению с MySQL в read-операциях
Более стабильную работу под высокой нагрузкой
Лучшую поддержку современных возможностей SQL
Активное развитие и регулярные обновления безопасности

Официальный драйвер mariadb показывает до 40% лучшую производительность по сравнению с PyMySQL в тестах на вставку данных.

Заключение и рекомендации

MariaDB + Python — это мощная связка для серверных приложений. Она даёт надёжность хранения данных, гибкость в разработке и хорошую производительность. Основные рекомендации:

Используй официальный драйвер mariadb для новых проектов
Всегда применяй параметризованные запросы для безопасности
Настрой пул соединений для высоконагруженных приложений
Используй транзакции для связанных операций
Настрой мониторинг производительности и логирование

Это решение отлично подходит для:

Систем мониторинга серверов
Веб-приложений с пользовательскими данными
Систем логирования и аудита
API-сервисов с реляционными данными
Инструментов автоматизации и DevOps

MariaDB продолжает активно развиваться и остаётся одним из лучших выборов для проектов, где нужна надёжная реляционная база данных с хорошей производительностью и богатыми возможностями.

В этой статье собрана информация и материалы из различных интернет-источников. Мы признаем и ценим работу всех оригинальных авторов, издателей и веб-сайтов. Несмотря на то, что были приложены все усилия для надлежащего указания исходного материала, любая непреднамеренная оплошность или упущение не являются нарушением авторских прав. Все упомянутые товарные знаки, логотипы и изображения являются собственностью соответствующих владельцев. Если вы считаете, что какой-либо контент, использованный в этой статье, нарушает ваши авторские права, немедленно свяжитесь с нами для рассмотрения и принятия оперативных мер.

Данная статья предназначена исключительно для ознакомительных и образовательных целей и не ущемляет права правообладателей. Если какой-либо материал, защищенный авторским правом, был использован без должного упоминания или с нарушением законов об авторском праве, это непреднамеренно, и мы исправим это незамедлительно после уведомления. Обратите внимание, что переиздание, распространение или воспроизведение части или всего содержимого в любой форме запрещено без письменного разрешения автора и владельца веб-сайта. Для получения разрешений или дополнительных запросов, пожалуйста, свяжитесь с нами.