Базы данных: MariaDB/MySQL и PostgreSQL

В этом руководстве описано, как установить на сервер MariaDB (совместима с MySQL) или PostgreSQL, создать базу данных с отдельным пользователем, подключиться к ней со своего компьютера через SSH-туннель и настроить резервное копирование. Команды выполняются от root в SSH-сеансе (Подключение к серверу по SSH (Windows, macOS, Linux)). IP-адрес сервера указан в панели app.blackbox.md: «Инфраструктура» → «Мои серверы» (app.blackbox.md/my-servers).

Доступно: владельцу VPS-сервера с Ubuntu, Debian, AlmaLinux или CentOS Stream. Если на сервере есть панель управления (Тарифы «VPS hosting» с панелью управления), базы данных обычно создают в ней.

MariaDB или PostgreSQL

Устанавливайте ту СУБД, которая нужна вашему приложению.

Для большинства приложений MariaDB заменяет MySQL без каких-либо изменений. Если приложению нужен именно MySQL, установите mysql-server в Ubuntu, AlmaLinux или CentOS Stream (в стандартных репозиториях Debian его нет). Тогда команды называются mysql и mysql_secure_installation, а имя службы и пути к файлам настроек отличаются от приведённых в этом руководстве. Не ставьте MySQL и MariaDB на один сервер.

MariaDB: установка и защита

Ubuntu и Debian

apt update
apt install -y mariadb-server

Служба запускается автоматически и слушает только 127.0.0.1.

AlmaLinux и CentOS Stream

dnf install -y mariadb-server
systemctl enable --now mariadb

Здесь MariaDB по умолчанию слушает все сетевые интерфейсы. Привяжите её к 127.0.0.1 с помощью файла 99-local.cnf, описанного ниже.

Файл с вашими настройками

Свои настройки храните в отдельном файле 99-local.cnf (создайте его в nano или vi): в Ubuntu и Debian — в каталоге /etc/mysql/mariadb.conf.d/, в AlmaLinux и CentOS Stream — в /etc/my.cnf.d/. В AlmaLinux и CentOS Stream начните его так (в Ubuntu и Debian bind-address = 127.0.0.1 уже задан):

[mysqld]
bind-address = 127.0.0.1

После каждого изменения перезапускайте службу и проверяйте, что в выводе есть 127.0.0.1:3306, а не 0.0.0.0:3306 или *:3306:

systemctl restart mariadb
ss -tlnp | grep 3306

mariadb-secure-installation

mariadb-secure-installation

Пользователь root в MariaDB использует аутентификацию через сокет (unix_socket): войти под ним может только системный root командой mariadb, без пароля. В Ubuntu 20.04 (MariaDB 10.3) команды называются mysql_secure_installation и mysql, а на вопрос «Set root password?» ответьте n.

База данных и её пользователь

Каждому приложению — своя база и пользователь с правами только на неё. Сгенерируйте надёжный пароль и откройте консоль MariaDB:

openssl rand -base64 24
mariadb

Выполните команды, подставив ИМЯ_БАЗЫ, ИМЯ_ПОЛЬЗОВАТЕЛЯ и ПАРОЛЬ:

CREATE DATABASE ИМЯ_БАЗЫ CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'ИМЯ_ПОЛЬЗОВАТЕЛЯ'@'localhost' IDENTIFIED BY 'ПАРОЛЬ';
GRANT ALL PRIVILEGES ON ИМЯ_БАЗЫ.* TO 'ИМЯ_ПОЛЬЗОВАТЕЛЯ'@'localhost';
EXIT;

ИМЯ_БАЗЫ.* ограничивает права этой базой, а 'localhost' разрешает подключение только с самого сервера и через SSH-туннель. Не используйте ON *.* и '%'. Проверьте подключение под новым пользователем:

mariadb -u ИМЯ_ПОЛЬЗОВАТЕЛЯ -p ИМЯ_БАЗЫ

В настройках приложения укажите хост localhost, порт 3306, имя базы, пользователя и пароль.

PostgreSQL: установка и пользователи

Ubuntu и Debian

apt update
apt install -y postgresql

Служба запускается автоматически и слушает только localhost.

AlmaLinux и CentOS Stream

dnf install -y postgresql-server
postgresql-setup --initdb
systemctl enable --now postgresql

Откройте /var/lib/pgsql/data/pg_hba.conf. Если в строках host для 127.0.0.1/32 и ::1/128 указан метод ident, замените его на md5 (он принимает и пароли, сохранённые в формате SCRAM). Иначе приложения будут получать ошибку Ident authentication failed. Примените изменения:

systemctl reload postgresql

Пользователь и база данных

sudo -u postgres createuser --pwprompt ИМЯ_ПОЛЬЗОВАТЕЛЯ
sudo -u postgres createdb --owner=ИМЯ_ПОЛЬЗОВАТЕЛЯ ИМЯ_БАЗЫ

На запросы «Enter password for new role:» и «Enter it again:» введите пароль. Пользователь не будет суперпользователем: полные права у него только в базе, которой он владеет. Предупреждение could not change directory to "/root": Permission denied безвредно; его не будет, если выполнять команды из /tmp. Проверьте подключение (выход — \q):

psql -h 127.0.0.1 -U ИМЯ_ПОЛЬЗОВАТЕЛЯ ИМЯ_БАЗЫ

Удалённый доступ через SSH-туннель (рекомендуется)

Чтобы работать с базой из DBeaver, HeidiSQL, MySQL Workbench, pgAdmin или другого клиента на компьютере, не открывайте порт базы данных. SSH-туннель идёт через уже открытый порт 22 и шифрует трафик, а база остаётся привязанной к 127.0.0.1.

1. На компьютере, в терминале (macOS, Linux) или PowerShell (Windows), запустите туннель. Для MariaDB:

   ssh -N -L 3306:127.0.0.1:3306 root@IP_СЕРВЕРА

   Для PostgreSQL:

   ssh -N -L 5432:127.0.0.1:5432 root@IP_СЕРВЕРА

2. Не закрывайте окно: туннель работает, пока выполняется команда. Закрыть его можно сочетанием Ctrl + C.

3. В клиенте подключайтесь к хосту 127.0.0.1, порт 3306 или 5432, с пользователем и паролем базы данных.

Туннель использует ту же аутентификацию, что и обычное SSH-подключение: на сервере «Только ключ», если ключ лежит не в стандартном месте, укажите его через -i. Если этот порт на компьютере уже занят локальной базой, возьмите другой локальный порт, например -L 13306:127.0.0.1:3306, и подключайтесь к 127.0.0.1:13306. Пишите 127.0.0.1, а не localhost: клиент mariadb/mysql воспринимает localhost как локальный сокет и идёт в обход туннеля. Во многих графических клиентах SSH-туннель встроен в настройки подключения: SSH-хост IP_СЕРВЕРА, порт 22, пользователь root и ключ или пароль, а хост базы — 127.0.0.1.

Открытие порта базы данных (не рекомендуется)

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

1. Сетевая фильтрация, которой управляет BLACKBOX, по умолчанию пропускает только SSH (22), HTTP (80), HTTPS (443) и ping, так что порты 3306 и 5432 закрыты. Настроек файрвола в панели нет: напишите в поддержку, указав IP-адрес сервера и порт (Как связаться с поддержкой BLACKBOX).

2. Файрвол в системе должен быть включён (см. примечание ниже) и пропускать порт только с вашего IP-адреса — его покажет Инструмент «Ваш публичный IP-адрес». Для PostgreSQL замените 3306 на 5432. С ufw (Ubuntu, Debian):

   ufw allow from ВАШ_IP to any port 3306 proto tcp

   С firewalld (AlmaLinux, CentOS Stream):

   firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="ВАШ_IP/32" port port="3306" protocol="tcp" accept'
   firewall-cmd --reload

3. MariaDB: в 99-local.cnf, в секции [mysqld], укажите bind-address = 0.0.0.0, перезапустите службу и создайте пользователя 'ИМЯ_ПОЛЬЗОВАТЕЛЯ'@'ВАШ_IP' теми же командами CREATE USER и GRANT.

4. PostgreSQL: в pg_hba.conf (Ubuntu и Debian: /etc/postgresql/ВЕРСИЯ/main/pg_hba.conf; AlmaLinux и CentOS Stream: /var/lib/pgsql/data/pg_hba.conf) добавьте строку host ИМЯ_БАЗЫ ИМЯ_ПОЛЬЗОВАТЕЛЯ ВАШ_IP/32 МЕТОД, где метод тот же, что в строках для 127.0.0.1/32 (scram-sha-256 или md5), затем выполните:

   sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses = '*'"
   systemctl restart postgresql

Если ваш IP-адрес сменится, правило придётся обновить. Без настроенного в базе TLS данные передаются в открытом виде — ещё один довод в пользу туннеля.

Внимание На новом сервере ufw и firewalld не включены. Прежде чем включать их, разрешите SSH и держите открытым второй SSH-сеанс: веб-консоли и VNC для клиентов нет. Порядок действий: Файрвол: сетевая фильтрация и ufw/firewalld на сервере. Если вы потеряли доступ, напишите в поддержку.

База данных на другом сервере BLACKBOX

Серверы BLACKBOX не связаны между собой частной сетью: приложение обращается к базе на другом сервере по его публичному IP-адресу, как любой компьютер из интернета. Не рассчитывайте, что порт базы данных доступен с другого сервера. Рекомендуемый вариант — постоянный SSH-туннель, который запускается на сервере приложения как служба systemd: база остаётся привязанной к 127.0.0.1, а трафик идёт в зашифрованном виде через порт 22.

Постоянный SSH-туннель (рекомендуется)

Команды приведены для MariaDB; для PostgreSQL везде замените 3306 на 5432.

1. На сервере приложения создайте SSH-ключ только для туннеля, без парольной фразы, чтобы его могла использовать служба:

   ssh-keygen -t ed25519 -f /root/.ssh/db_tunnel_ed25519 -N "" -C db-tunnel
   cat /root/.ssh/db_tunnel_ed25519.pub

2. На сервере базы данных создайте отдельного пользователя без командной оболочки и откройте его файл authorized_keys:

   useradd -m -s /usr/sbin/nologin dbtunnel
   mkdir -p /home/dbtunnel/.ssh
   nano /home/dbtunnel/.ssh/authorized_keys

   Запишите одну строку: ограничения, пробел и ключ, показанный на шаге 1.

   restrict,port-forwarding,permitopen="127.0.0.1:3306" ssh-ed25519 AAAA... db-tunnel

   Задайте права:

   chown -R dbtunnel:dbtunnel /home/dbtunnel/.ssh
   chmod 700 /home/dbtunnel/.ssh
   chmod 600 /home/dbtunnel/.ssh/authorized_keys

   В AlmaLinux и CentOS Stream восстановите также метки SELinux:

   restorecon -R /home/dbtunnel/.ssh

   С такими ограничениями ключ открывает только туннель к порту базы: командной оболочки и доступа к другим портам у него нет. Не добавляйте его через «Добавить SSH-ключ» в панели: панель добавляет ключ пользователю root без ограничений.

3. На сервере приложения один раз запустите туннель вручную. При первом подключении подтвердите ключ сервера базы, ответив yes. Пока туннель открыт, команда ничего не выводит; остановите её сочетанием Ctrl + C:

   ssh -N -i /root/.ssh/db_tunnel_ed25519 -L 3306:127.0.0.1:3306 dbtunnel@IP_СЕРВЕРА_БД

4. Создайте службу:

   nano /etc/systemd/system/db-tunnel.service

   [Unit]
   Description=SSH tunnel to the database
   Wants=network-online.target
   After=network-online.target
   
   [Service]
   ExecStart=/usr/bin/ssh -N -i /root/.ssh/db_tunnel_ed25519 -o BatchMode=yes -o ExitOnForwardFailure=yes -o ServerAliveInterval=30 -o ServerAliveCountMax=3 -L 3306:127.0.0.1:3306 dbtunnel@IP_СЕРВЕРА_БД
   Restart=always
   RestartSec=10
   
   [Install]
   WantedBy=multi-user.target

5. Включите службу и проверьте, что туннель слушает 127.0.0.1:3306:

   systemctl daemon-reload
   systemctl enable --now db-tunnel
   systemctl status db-tunnel
   ss -tlnp | grep 3306

6. В настройках приложения укажите хост 127.0.0.1 (не localhost), порт 3306, имя базы, пользователя и пароль. На сервере базы достаточно пользователя, созданного для 'localhost'.

ExitOnForwardFailure закрывает подключение, если туннель не удалось открыть, а ServerAliveInterval замечает оборвавшееся соединение; в обоих случаях systemd перезапускает службу через 10 секунд. BatchMode=yes не даёт службе ждать подтверждения или пароля. Если порт 3306 на сервере приложения занят локальной базой, возьмите другой локальный порт, например -L 13306:127.0.0.1:3306, и укажите его в настройках приложения. Если служба не запускается, посмотрите журнал:

journalctl -u db-tunnel -n 50

Порт, открытый только для сервера приложения

Если туннель вам не подходит, попросите поддержку открыть порт 3306 или 5432 на сервере базы и выполните шаги из раздела «Открытие порта базы данных (не рекомендуется)», указав вместо ВАШ_IP IP-адрес сервера приложения: в правиле ufw или firewalld, в пользователе MariaDB ('ИМЯ_ПОЛЬЗОВАТЕЛЯ'@'IP_СЕРВЕРА_ПРИЛОЖЕНИЯ') и в строке pg_hba.conf. Без TLS данные между серверами передаются в открытом виде. Прежде чем включать файрвол, держите открытым второй SSH-сеанс: веб-консоли нет (см. примечание выше).

Резервные копии

Не копируйте файлы работающей базы: такая копия может оказаться несогласованной. Делайте выгрузку (дамп) через mysqldump или pg_dump со сжатием gzip.

1. Создайте скрипт /usr/local/bin/backup-db.sh и оставьте в нём только строку для вашей СУБД:

   #!/bin/bash
   set -euo pipefail
   cd /tmp
   DIR=/var/backups/db
   DATE=$(date +%F)
   mkdir -p "$DIR"
   chmod 700 "$DIR"
   # MariaDB / MySQL
   mysqldump --single-transaction --routines ИМЯ_БАЗЫ | gzip > "$DIR/mariadb-ИМЯ_БАЗЫ-$DATE.sql.gz"
   # PostgreSQL
   sudo -u postgres pg_dump ИМЯ_БАЗЫ | gzip > "$DIR/pg-ИМЯ_БАЗЫ-$DATE.sql.gz"
   # удалить выгрузки старше 7 дней
   find "$DIR" -name '*.sql.gz' -mtime +7 -delete

2. Сделайте его исполняемым, запустите один раз и проверьте файлы:

   chmod 700 /usr/local/bin/backup-db.sh
   /usr/local/bin/backup-db.sh
   ls -lh /var/backups/db

3. Откройте список заданий cron командой ниже и добавьте строку, которая запускает скрипт каждую ночь в 02:30 по времени сервера (его показывает timedatectl):

   crontab -e

   30 2 * * * /usr/local/bin/backup-db.sh

--single-transaction выгружает таблицы InnoDB в согласованном состоянии, не блокируя их. Скрипт работает от root, который входит в MariaDB через сокет. Если вы задали пароль для root в MariaDB, сохраните его в /root/.my.cnf, в секции [client], с правами 600. Если команды crontab нет, установите пакет cron (Ubuntu, Debian) или cronie (AlmaLinux, CentOS Stream) и запустите службу. Выгрузки занимают место: следите за диском с помощью df -h (Диск сервера заполнен).

Важно Выгрузки в /var/backups/db лежат на том же диске, что и база: если диск выйдет из строя или сервер будет удалён, пропадут и они. BLACKBOX не делает автоматических резервных копий сервера, а кнопка «Резервные копии» на карточке сервера не работает. Регулярно копируйте выгрузки в другое место: Стратегия резервного копирования для вашего сервера

Проверяйте восстановление

Непроверенная копия может не сработать. Хотя бы раз в месяц восстанавливайте выгрузку в тестовую базу, проверяйте таблицы и удаляйте эту базу. MariaDB:

mariadb -e "CREATE DATABASE test_restore"
gunzip -c /var/backups/db/mariadb-ИМЯ_БАЗЫ-2026-10-09.sql.gz | mariadb test_restore
mariadb -e "SHOW TABLES" test_restore
mariadb -e "DROP DATABASE test_restore"

PostgreSQL:

cd /tmp
sudo -u postgres createdb test_restore
gunzip -c /var/backups/db/pg-ИМЯ_БАЗЫ-2026-10-09.sql.gz | sudo -u postgres psql -q test_restore
sudo -u postgres psql -d test_restore -c '\dt'
sudo -u postgres dropdb test_restore

Перед настоящим восстановлением остановите приложение. В PostgreSQL восстанавливайте выгрузку в пустую базу, созданную командой createdb --owner=ИМЯ_ПОЛЬЗОВАТЕЛЯ.

Память на небольших тарифах

Значения по умолчанию — 128 МБ для innodb_buffer_pool_size (MariaDB) и для shared_buffers (PostgreSQL) — подходят для 1 ГБ RAM. На серверах с большим объёмом памяти их можно увеличить, но осторожно, особенно если на том же сервере работают веб-сервер или приложение. Объём памяти показывает free -h.

Если на сервере работает только база данных, под innodb_buffer_pool_size можно выделить до половины RAM, а под shared_buffers — до четверти. Каждое подключение тоже расходует память: не увеличивайте max_connections без необходимости.

MariaDB: запишите значение в 99-local.cnf (если файл уже есть, добавьте только вторую строку в секцию [mysqld]), затем перезапустите службу и проверьте результат:

[mysqld]
innodb_buffer_pool_size = 256M

systemctl restart mariadb
mariadb -e "SELECT @@innodb_buffer_pool_size/1024/1024 AS MB"

PostgreSQL (shared_buffers применяется только после перезапуска):

sudo -u postgres psql -c "ALTER SYSTEM SET shared_buffers = '256MB'"
systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers"

На тарифах с 1–2 ГБ RAM добавьте и swap: Добавление файла подкачки (swap). Если база данных останавливается сама по себе, проверьте, не завершила ли её система из-за нехватки памяти:

journalctl -k | grep -i "out of memory"

Если это повторяется, серверу нужно больше RAM: Апгрейд сервера: переход на больший тариф.

Частые вопросы

Приложение получает «Access denied for user». Что проверить? Имя базы, пользователя, пароль и хост: пользователь, созданный для 'localhost', подключается только с самого сервера или через туннель. Если в сообщении указано 'ИМЯ_ПОЛЬЗОВАТЕЛЯ'@'127.0.0.1', создайте пользователя и для '127.0.0.1' теми же командами CREATE USER и GRANT.

Забыли пароль пользователя базы данных. Как его сменить? От root на сервере. В консоли mariadb:

ALTER USER 'ИМЯ_ПОЛЬЗОВАТЕЛЯ'@'localhost' IDENTIFIED BY 'НОВЫЙ_ПАРОЛЬ';

В PostgreSQL команда ниже дважды запросит новый пароль:

sudo -u postgres psql -c "\password ИМЯ_ПОЛЬЗОВАТЕЛЯ"

Затем обновите пароль в настройках приложения.

Как скачать выгрузку на компьютер? Выполните на компьютере команду ниже, указав нужный файл. Другие способы: Передача файлов на сервер (scp, rsync, SFTP).

scp root@IP_СЕРВЕРА:/var/backups/db/ФАЙЛ.sql.gz .

Можно ли держать MariaDB и PostgreSQL на одном сервере? Да, они используют разные порты. Но память у них общая, поэтому для обеих выбирайте значения меньше, чем в таблице.