Адміністрування бази даних 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  # по одному на кожний гігабайт

# Розмір файлів логів — більше = рідше контрольні точки, вища продуктивність запису
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;

Регулярні завдання