Marzban: миграция с sqlite3 на MySQL

Основной причиной перехода с SQLite на MySQL базу данных является внезапное повреждение файла .db, после которого невозможно ни открыть файл, ни восстановить оставшиеся данные — ни в этот, ни в новый файл базы.
В данной публикации описан пошаговый алгоритм миграции действующей базы данных без использования docker.

sqlite> .tables Error: file is not a database

database disk image is malformed

Подробнее можно почитать тут: https://www.sqlite.org/faq.html#q21

Установка MySQL на Ubuntu версии 20+

sudo apt install mysql-server -y ;\ mysql --version ;\ sudo mysql_secure_installation ;\ systemctl status mysql

Создание MySQL пользователя

Генерируем рандомный пароль, например через uuid.uuid4() и локально подключаемся к базе через командную строку

sudo mysql

Создаем пользователя и базу данных, значения <user> и <password> нужно заменить на свои

CREATE USER '<user>'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>'; GRANT ALL PRIVILEGES ON *.* TO '<user>'@'localhost' WITH GRANT OPTION;  CREATE USER '<user>'@'%' IDENTIFIED WITH mysql_native_password BY '<password>'; GRANT ALL PRIVILEGES ON *.* TO '<user>'@'%' WITH GRANT OPTION;  FLUSH PRIVILEGES;  create database marzban;

Для того, чтобы база была видна извне — нужно изменить строку в /etc/mysql/mysql.conf.d/mysqld.cnf

#bind-address           = 127.0.0.1  # Старое значение bind-address            = 0.0.0.0    # Новое значение

Установка sqlite3 и снятие дампа

cd ~ ;\ wget https://github.com/nalgeon/sqlite/releases/download/3.47.0/sqlite3-ubuntu ;\ mv sqlite3-ubuntu sqlite3 ;\ chmod +x sqlite3

Снимаем дамп текущей базы (sqlite):
db_file — файл базы Marzban панели
dump_file — файл будущего дампа

mkdir -p $HOME/dump ;\ db_file=$HOME/sqlite3_backups/db_backup_2024-12-24_10-30-01.sqlite3 dump_file=$HOME/dump/marzban_dump.sql $HOME/sqlite3 $db_file '.dump --data-only' | sed "s/INSERT INTO \([^ ]*\)/REPLACE INTO \`\\1\`/g" > $dump_file ls -la $dump_file

Восстановление и переключение на MySQL

Нужно заменить строку подключения в .env файле marzban.
У меня он находится в $HOME/code/Marzban/.env, новая строка:

SQLALCHEMY_DATABASE_URL = "mysql+pymysql://<user>:<password>@127.0.0.1:3306/marzban"

Значения <user> и <password> нужно заменить на созданные выше (логин и пароль пользователя в базе MySQL).

Далее нужно применить миграции к новой, пустой базе, предварительно активировав виртуальное окружение, либо запустить команду внутри контейнера (если вы работаете с панелью через docker)

alembic upgrade head

Восстановление дампа в MySQL

Внимание!

Формируемый с помощью SQLite файл бэкапа не оптимизирован под быстрое восстановление: вместо конструкции INSERT там используется REPLACE. Подозреваю, что восстановление проходит с построчным коммитом, что сильно замедляет процесс.
Для примера: мой файл db.sqlite3 занимал 230 МБ, а после конвертации в MySQL формат размер файла уменьшился до 167 МБ. На обработку такого файла у MySQL ушло больше часа.

Проблема в таблице node_user_usages, в которой хранится история «утилизации трафика» пользователями. У меня в ней было ~1,5 млн строк.

Рекомендация следующая: если у вас уже есть пользователи, и их количество больше 1000, то есть переход выполняется не между пустыми базами, — нужно подготовить сервисы к бесшовному переходу, так как окно технических работ может привести к негативному опыту у ваших клиентов.

Ориентировочно ваш план должен быть следующим:

• Остановка бота

• Остановка marzban — панели

• Снятие бэкапа

• Конвертация

• Включение панели marzban (откат в .env файле на sqlite)

• Восстановление бэкапа в MySQL базу данных

• Изменение .env на pymysql

• Перезапуск панели marzban

• Синхронизация изменений панели

mysql -u <user> -p<password> -h 127.0.0.1 marzban -e "SET FOREIGN_KEY_CHECKS = 0; SET NAMES utf8mb4; SOURCE /root/dump/marzban_dump.sql;"

Обратите внимание, что параметр -p вводится слитно со значением пароля. Далее включение marzban панели и проверка корректности подключения

systemctl restart marzban

На этом этапе переход на MySQL базу данных завершен! 🎉Рекомендую сразу настроить регулярный бэкап базы и выгрузку дамп файла на сторонний сервер.

Большое спасибо всем за внимание! Если вам интересны подобные рассуждения — подписывайтесь на мой канал artydev & Co.