Настройка базы данных MySQL/MariaDB для веб-приложения

Настройка базы данных MySQL/MariaDB для веб-приложения

MySQL и MariaDB остаются широко распространёнными выборами для веб-приложений — особенно в экосистеме PHP/Laravel, при миграции legacy-проектов и когда нужна проверенная схема репликации с читаемой документацией.

Установка

MySQL 8.0 на Ubuntu:

apt install -y mysql-server
mysql_secure_installation

MariaDB 11.x (рекомендуется для новых проектов — лучше производительность, open source лицензия):

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
apt install -y mariadb-server mariadb-client

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

CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp'@'localhost';
FLUSH PRIVILEGES;

utf8mb4 обязателен — это настоящий UTF-8 с поддержкой emoji. Старый utf8 в MySQL — трёхбайтный, что вызывает проблемы с Unicode за пределами BMP.

Настройка my.cnf

[mysqld]
# Основные настройки (для 8 ГБ RAM)
innodb_buffer_pool_size = 5G          # 60-70% RAM
innodb_buffer_pool_instances = 4      # по одной на каждые 1-2 ГБ
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2    # компромисс между надёжностью и скоростью
innodb_flush_method = O_DIRECT

# Подключения
max_connections = 200
thread_cache_size = 32
table_open_cache = 4000

# Запросы
query_cache_type = 0                  # query cache устарел, отключить
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M

# Логирование
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# Репликация
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7

Индексы и оптимизация запросов

-- Составные индексы — порядок имеет значение
-- Запрос: WHERE user_id = ? AND status = ? ORDER BY created_at DESC
CREATE INDEX idx_orders_user_status_date
ON orders(user_id, status, created_at DESC);

-- Покрывающий индекс — ответ из индекса без обращения к таблице
CREATE INDEX idx_products_listing
ON products(category_id, is_active, price, id, name)
WHERE deleted_at IS NULL;

-- Полнотекстовый поиск
ALTER TABLE articles ADD FULLTEXT INDEX ft_search(title, body);

-- Проверка использования индексов
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 10;

Connection pooling — ProxySQL

ProxySQL мощнее простых пулеров: умеет маршрутизировать SELECT на реплики, а INSERT/UPDATE на primary:

# /etc/proxysql.cnf (ключевые секции)
mysql_servers =
(
    { address="10.0.0.1", port=3306, hostgroup=0, max_connections=100 },  # primary
    { address="10.0.0.2", port=3306, hostgroup=1, max_connections=100 },  # replica
    { address="10.0.0.3", port=3306, hostgroup=1, max_connections=100 }   # replica
)

mysql_query_rules =
(
    { rule_id=1, active=1, match_pattern="^SELECT", destination_hostgroup=1, apply=1 },
    { rule_id=2, active=1, match_digest="^SELECT.*FOR UPDATE", destination_hostgroup=0, apply=1 }
)

Репликация primary-replica

-- На primary
CREATE USER 'replicator'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- запомнить File и Position

-- На replica
CHANGE MASTER TO
    MASTER_HOST='10.0.0.1',
    MASTER_USER='replicator',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=4567;
START SLAVE;
SHOW SLAVE STATUS\G

Для GTID-репликации (проще управление после failover):

-- primary: my.cnf
gtid_mode = ON
enforce_gtid_consistency = ON

-- replica
CHANGE MASTER TO
    MASTER_HOST='10.0.0.1',
    MASTER_USER='replicator',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION=1;

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

# Логический бэкап через mysqldump
mysqldump --single-transaction --routines --triggers \
    --master-data=2 myapp | gzip > backup_$(date +%Y%m%d).sql.gz

# Физический бэкап через Percona XtraBackup (без блокировок)
xtrabackup --backup --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full

# Автоматизация через cron
0 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Мониторинг

Ключевые метрики для отслеживания:

-- Статус InnoDB буфера
SHOW ENGINE INNODB STATUS\G

-- Медленные запросы
SELECT query_time, lock_time, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC LIMIT 20;

-- Текущие подключения и их состояние
SELECT user, host, db, command, time, state, left(info, 80) AS query
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

Сроки

Установка, hardening, настройка под нагрузку: 1 день. Репликация primary-replica с ProxySQL: 1–2 дня. Миграция данных из другой СУБД с проверкой целостности: 2–5 дней в зависимости от объёма и сложности схемы.