Оптимизация MySQL/MariaDB

Руководство по настройке и оптимизации MySQL/MariaDB для повышения производительности.

Основные настройки

Конфигурация my.cnf

[mysqld]
# Основные настройки
port = 3306
socket = /var/run/mysqld/mysqld.sock
user = mysql
pid-file = /var/run/mysqld/mysqld.pid

# Кодировка
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Буферы и кеши
key_buffer_size = 256M
max_allowed_packet = 64M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
query_cache_limit = 1M
query_cache_size = 16M

# InnoDB настройки
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

# Соединения
max_connections = 151
connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 32M
max_heap_table_size = 32M

Мониторинг производительности

Проверка статуса

-- Общий статус сервера
SHOW GLOBAL STATUS;

-- Процессы
SHOW PROCESSLIST;

-- Статус InnoDB
SHOW ENGINE INNODB STATUS\G

-- Медленные запросы
SHOW VARIABLES LIKE 'slow_query%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Анализ запросов

-- Включение логирования медленных запросов
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Анализ плана выполнения
EXPLAIN SELECT * FROM table_name WHERE condition;
EXPLAIN FORMAT=JSON SELECT * FROM table_name WHERE condition;

-- Профилирование запросов
SET profiling = 1;
SELECT * FROM table_name WHERE condition;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

Оптимизация схемы

Индексы

-- Создание составного индекса
CREATE INDEX idx_user_email_created ON users(email, created_at);

-- Анализ использования индексов
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SUB_PART,
    PACKED,
    NULLABLE,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database';

-- Поиск неиспользуемых индексов
SELECT
    s.TABLE_SCHEMA,
    s.TABLE_NAME,
    s.INDEX_NAME,
    s.CARDINALITY
FROM information_schema.STATISTICS s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage t
    ON s.TABLE_SCHEMA = t.OBJECT_SCHEMA
    AND s.TABLE_NAME = t.OBJECT_NAME
    AND s.INDEX_NAME = t.INDEX_NAME
WHERE t.INDEX_NAME IS NULL
    AND s.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
    AND s.INDEX_NAME != 'PRIMARY';

Партиционирование

-- Партиционирование по диапазону дат
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Просмотр информации о партициях
SELECT
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_ORDINAL_POSITION,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database'
    AND TABLE_NAME = 'orders';

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

mysqldump

# Полный бэкап базы данных
mysqldump -u root -p --single-transaction --routines --triggers database_name > backup.sql

# Бэкап с сжатием
mysqldump -u root -p --single-transaction database_name | gzip > backup.sql.gz

# Бэкап только структуры
mysqldump -u root -p --no-data database_name > schema.sql

# Бэкап только данных
mysqldump -u root -p --no-create-info database_name > data.sql

# Восстановление
mysql -u root -p database_name < backup.sql

Автоматизация бэкапов

#!/bin/bash
# backup_mysql.sh

DB_USER="backup_user"
DB_PASS="backup_password"
DB_NAME="your_database"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

# Создание бэкапа
mysqldump -u$DB_USER -p$DB_PASS \
    --single-transaction \
    --routines \
    --triggers \
    $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Удаление старых бэкапов (старше 30 дней)
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +30 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"

Репликация

Master-Slave настройка

# Master конфигурация (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = your_database

# Slave конфигурация (my.cnf)
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 1

-- На Master сервере
CREATE USER 'replication'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;

-- На Slave сервере
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='replication',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUS\G

Безопасность

Хардение MySQL

-- Удаление анонимных пользователей
DELETE FROM mysql.user WHERE User='';

-- Удаление тестовой базы данных
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

-- Ограничение root доступа
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Создание пользователя с ограниченными правами
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'app_user'@'localhost';

FLUSH PRIVILEGES;

SSL соединения

# my.cnf
[mysqld]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
require_secure_transport=ON

-- Создание пользователя с обязательным SSL
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
GRANT SELECT ON database.* TO 'secure_user'@'%';

-- Проверка SSL соединения
SHOW STATUS LIKE 'Ssl_cipher';

Мониторинг и алерты

Полезные запросы для мониторинга

-- Размер баз данных
SELECT
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;

-- Топ таблиц по размеру
SELECT
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'your_database'
ORDER BY (data_length + index_length) DESC;

-- Активные соединения
SELECT
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

Скрипт мониторинга

#!/bin/bash
# mysql_monitor.sh

MYSQL_USER="monitor"
MYSQL_PASS="password"
THRESHOLD_CONNECTIONS=80
THRESHOLD_SLOW_QUERIES=100

# Проверка количества соединений
CONNECTIONS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2 {print $2}')

if [ $CONNECTIONS -gt $THRESHOLD_CONNECTIONS ]; then
    echo "WARNING: High number of connections: $CONNECTIONS"
fi

# Проверка медленных запросов
SLOW_QUERIES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Slow_queries';" | awk 'NR==2 {print $2}')

if [ $SLOW_QUERIES -gt $THRESHOLD_SLOW_QUERIES ]; then
    echo "WARNING: High number of slow queries: $SLOW_QUERIES"
fi

echo "MySQL monitoring completed"