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

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

MySQL и MariaDB — наиболее распространённый стек для веб-приложений на PHP. LAMP-проекты, WordPress, Magento, Laravel с MySQL — всё это требует системного обслуживания. Без него: фрагментированные таблицы MyISAM, переполненный binary log, медленные запросы без индексов, неоптимальный InnoDB buffer pool.

Начальный аудит

-- Версия и движок по умолчанию
SELECT VERSION();
SHOW ENGINES;

-- Размер баз данных
SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

-- Фрагментация таблиц (Data_free > 0 — можно оптимизировать)
SELECT table_schema, table_name, engine,
       ROUND(data_length / 1024 / 1024, 1)  AS data_mb,
       ROUND(index_length / 1024 / 1024, 1) AS index_mb,
       ROUND(data_free / 1024 / 1024, 1)    AS free_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
  AND data_free > 0
ORDER BY data_free DESC
LIMIT 20;

InnoDB: ключевые параметры

# /etc/mysql/conf.d/optimized.cnf
[mysqld]
# Буферный пул — выделить 70-80% RAM на выделенном сервере
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4  # по одному на каждый гигабайт

# Размер лог-файлов — больше = реже checkpoint, выше производительность записи
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M

# Синхронизация: 2 = безопасно при потере питания, но O_DSYNC
innodb_flush_log_at_trx_commit = 1  # 1 — полная ACID, 2 — чуть быстрее

# Файл на таблицу — удобнее для резервного копирования и OPTIMIZE
innodb_file_per_table = ON

# Параллелизм
innodb_read_io_threads  = 8
innodb_write_io_threads = 8
innodb_io_capacity      = 2000  # для SSD
innodb_io_capacity_max  = 4000

Управление binary log

Binary log нужен для репликации и PITR. Без ротации занимает всё место на диске:

[mysqld]
log_bin            = /var/log/mysql/mysql-bin
binlog_format      = ROW       # ROW безопаснее STATEMENT для репликации
expire_logs_days   = 7         # MySQL 5.7
binlog_expire_logs_seconds = 604800  # MySQL 8.0+
max_binlog_size    = 100M

Ручная очистка:

-- Посмотреть текущие binlog файлы
SHOW BINARY LOGS;

-- Удалить старее конкретного
PURGE BINARY LOGS BEFORE '2025-01-01 00:00:00';
-- или
PURGE BINARY LOGS TO 'mysql-bin.000150';

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

mysqldump — для большинства проектов:

# Горячий бэкап InnoDB с консистентным снапшотом
mysqldump \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  --flush-logs \
  -u backup -p'password' mydb \
  | gzip > /backups/mydb_$(date +%Y%m%d_%H%M).sql.gz

--single-transaction открывает транзакцию для InnoDB — бэкап без блокировки таблиц. Для MyISAM требуется --lock-tables.

Percona XtraBackup — физический бэкап для больших баз (>10 ГБ) без падения производительности:

# Полный бэкап
xtrabackup --backup \
  --user=backup --password='password' \
  --target-dir=/backups/full_$(date +%Y%m%d)

# Подготовка к восстановлению
xtrabackup --prepare --target-dir=/backups/full_20250101

# Инкрементальный (от последнего полного)
xtrabackup --backup --incremental-basedir=/backups/full_20250101 \
  --target-dir=/backups/incr_$(date +%Y%m%d)

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

# master: my.cnf
[mysqld]
server-id = 1
log_bin   = /var/log/mysql/mysql-bin

-- На master: создать пользователя для репликации
CREATE USER 'replication'@'10.0.0.2' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.0.0.2';
FLUSH PRIVILEGES;

-- Снять снапшот для инициализации реплики
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS; -- запомнить File и Position
-- (в другой сессии выполнить mysqldump)
UNLOCK TABLES;

# replica: my.cnf
[mysqld]
server-id        = 2
relay_log        = /var/log/mysql/mysql-relay
read_only        = ON
super_read_only  = ON  # MySQL 5.7+

-- На реплике после восстановления дампа
CHANGE MASTER TO
  MASTER_HOST     = '10.0.0.1',
  MASTER_USER     = 'replication',
  MASTER_PASSWORD = 'strong_password',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS  = 12345;
START SLAVE;
SHOW SLAVE STATUS\G

ProxySQL: управление соединениями и роутинг

ProxySQL стоит между приложением и MySQL: разделяет READ/WRITE запросы, ограничивает пул соединений:

-- В консоли ProxySQL (порт 6032)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
  (1, '10.0.0.1', 3306, 1000),  -- hostgroup 1 = writer
  (2, '10.0.0.2', 3306, 1000);  -- hostgroup 2 = reader

-- Правила: SELECT на reader, остальное на writer
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup) VALUES
  (1, 1, '^SELECT.*FOR UPDATE',  1),
  (2, 1, '^SELECT',               2);

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

OPTIMIZE TABLE и дефрагментация

-- Дефрагментация конкретной таблицы (блокирует, выполнять в окно)
OPTIMIZE TABLE orders;

-- Проверка и восстановление для MyISAM
CHECK TABLE old_table;
REPAIR TABLE old_table;

Для InnoDB OPTIMIZE TABLE пересоздаёт таблицу — аналог VACUUM FULL в PostgreSQL. На больших таблицах занимает долго. Альтернатива без блокировки:

# pt-online-schema-change от Percona Toolkit
pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  --execute \
  D=mydb,t=orders \
  u=root,p=password,h=localhost

Мониторинг через performance_schema

-- Топ медленных запросов
SELECT digest_text,
       count_star,
       ROUND(avg_timer_wait / 1e12, 3)  AS avg_sec,
       ROUND(sum_timer_wait / 1e12, 3)  AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'mydb'
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Текущие блокировки
SELECT waiting_trx_id, blocking_trx_id,
       waiting_query, blocking_query
FROM sys.innodb_lock_waits;

Регулярные задачи