Перейти к основному содержимому

MySQL администрирование

Практические команды и скрипты для администрирования MySQL/MariaDB.

Установка и первоначальная настройка​

Установка MySQL​

# Ubuntu/Debian
sudo apt update
sudo apt install mysql-server

# CentOS/RHEL
sudo dnf install mysql-server

# Запуск и автозагрузка
sudo systemctl start mysql
sudo systemctl enable mysql

# Безопасная настройка
sudo mysql_secure_installation

Первоначальная конфигурация​

-- Подключение к MySQL
mysql -u root -p

-- Создание пользователя администратора
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

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

-- Применение изменений
FLUSH PRIVILEGES;

Управление базами данных​

Создание и удаление баз данных​

-- Создание базы данных
CREATE DATABASE my_app
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Просмотр всех баз данных
SHOW DATABASES;

-- Выбор базы данных
USE my_app;

-- Просмотр таблиц
SHOW TABLES;

-- Удаление базы данных
DROP DATABASE my_app;

Работа с таблицами​

-- Создание таблицы
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Добавление индексов
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);

-- Изменение таблицы
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN username VARCHAR(100);
ALTER TABLE users DROP COLUMN phone;

-- Просмотр структуры таблицы
DESCRIBE users;
SHOW CREATE TABLE users;

Резервное копирование и восстановление​

Полный бэкап всех баз данных​

# Бэкап всех баз данных
mysqldump -u root -p --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --flush-logs \
    --master-data=2 > full_backup_$(date +%Y%m%d).sql

# Сжатый бэкап
mysqldump -u root -p --all-databases \
    --single-transaction \
    --routines \
    --triggers | gzip > full_backup_$(date +%Y%m%d).sql.gz

Бэкап отдельной базы данных​

# Бэкап одной базы данных
mysqldump -u root -p my_app \
    --single-transaction \
    --routines \
    --triggers > my_app_backup_$(date +%Y%m%d).sql

# Бэкап только структуры (без данных)
mysqldump -u root -p --no-data my_app > my_app_structure.sql

# Бэкап только данных (без структуры)
mysqldump -u root -p --no-create-info my_app > my_app_data.sql

Восстановление из бэкапа​

# Восстановление полного бэкапа
mysql -u root -p < full_backup_20231201.sql

# Восстановление отдельной базы данных
mysql -u root -p my_app < my_app_backup_20231201.sql

# Восстановление из сжатого архива
gunzip < full_backup_20231201.sql.gz | mysql -u root -p

Мониторинг и оптимизация​

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

-- Просмотр процессов
SHOW PROCESSLIST;

-- Статус сервера
SHOW STATUS;

-- Переменные конфигурации
SHOW VARIABLES;

-- Статус движков хранения
SHOW ENGINE INNODB STATUS;

-- Информация о размере баз данных
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
ORDER BY SUM(data_length + index_length) DESC;

Анализ производительности​

-- Медленные запросы
SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- Анализ использования индексов
SELECT
    t.table_schema,
    t.table_name,
    s.index_name,
    s.column_name,
    s.seq_in_index,
    s.cardinality
FROM information_schema.tables t
LEFT JOIN information_schema.statistics s ON t.table_schema = s.table_schema
AND t.table_name = s.table_name
WHERE t.table_schema = 'my_app'
ORDER BY t.table_name, s.seq_in_index;

-- Проверка фрагментации таблиц
SELECT
    table_schema,
    table_name,
    data_free / 1024 / 1024 AS 'Fragmentation (MB)'
FROM information_schema.tables
WHERE data_free > 0
ORDER BY data_free DESC;

Оптимизация таблиц​

-- Анализ таблицы
ANALYZE TABLE users;

-- Оптимизация таблицы
OPTIMIZE TABLE users;

-- Восстановление таблицы
REPAIR TABLE users;

-- Проверка таблицы
CHECK TABLE users;

Управление пользователями​

Создание пользователей​

-- Создание пользователя для локального доступа
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'secure_password';

-- Создание пользователя для удаленного доступа
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'secure_password';
CREATE USER 'admin'@'192.168.1.%' IDENTIFIED BY 'admin_password';

-- Установка срока действия пароля
CREATE USER 'temp_user'@'localhost' IDENTIFIED BY 'temp_password' PASSWORD EXPIRE INTERVAL 30 DAY;

Назначение привилегий​

-- Полные права на базу данных
GRANT ALL PRIVILEGES ON my_app.* TO 'webapp'@'localhost';

-- Права только на чтение
GRANT SELECT ON my_app.* TO 'readonly'@'localhost';

-- Специфичные права
GRANT SELECT, INSERT, UPDATE ON my_app.users TO 'limited_user'@'localhost';

-- Права на создание баз данных
GRANT CREATE ON *.* TO 'developer'@'localhost';

-- Применение изменений
FLUSH PRIVILEGES;

Просмотр и отзыв привилегий​

-- Просмотр привилегий пользователя
SHOW GRANTS FOR 'webapp'@'localhost';

-- Отзыв привилегий
REVOKE INSERT, UPDATE ON my_app.* FROM 'webapp'@'localhost';

-- Удаление пользователя
DROP USER 'webapp'@'localhost';

-- Просмотр всех пользователей
SELECT user, host FROM mysql.user;

Настройка репликации​

Настройка мастера​

-- В my.cnf мастера
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

-- Создание пользователя для репликации
CREATE USER 'replication'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

-- Получение информации о бинарном логе
SHOW MASTER STATUS;

Настройка слейва​

-- В my.cnf слейва
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1

-- Настройка репликации
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'replication',
    MASTER_PASSWORD = 'repl_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 12345;

-- Запуск репликации
START SLAVE;

-- Проверка статуса
SHOW SLAVE STATUS\G

Автоматизация задач​

Скрипт автоматического бэкапа​

#!/bin/bash
# mysql-auto-backup.sh

# Конфигурация
DB_USER="backup_user"
DB_PASS="backup_password"
BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)

# Создание директории
mkdir -p "$BACKUP_DIR"

# Получение списка баз данных
DATABASES=$(mysql -u"$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

for db in $DATABASES; do
    echo "Создание бэкапа: $db"

    mysqldump -u"$DB_USER" -p"$DB_PASS" \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        "$db" | gzip > "$BACKUP_DIR/${db}_${DATE}.sql.gz"

    if [ $? -eq 0 ]; then
        echo "✓ Бэкап $db создан"
    else
        echo "✗ Ошибка бэкапа $db"
    fi
done

# Удаление старых бэкапов
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Бэкап завершен"

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

#!/bin/bash
# mysql-monitor.sh

# Проверка работы MySQL
if ! systemctl is-active --quiet mysql; then
    echo "MySQL не запущен!"
    systemctl start mysql
fi

# Проверка подключения
if ! mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "SELECT 1;" > /dev/null 2>&1; then
    echo "Не удается подключиться к MySQL"
    exit 1
fi

# Проверка репликации (для слейвов)
SLAVE_STATUS=$(mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running" | awk '{print $2}')

if [ "$SLAVE_STATUS" != "Yes" ]; then
    echo "Репликация не работает!"
fi

echo "MySQL работает нормально"

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

Настройка SSL​

# Генерация сертификатов
mysql_ssl_rsa_setup --uid=mysql

# В my.cnf
[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

# Принудительное использование SSL для пользователя
ALTER USER 'secure_user'@'%' REQUIRE SSL;

Аудит безопасности​

-- Включение общего лога запросов
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- Проверка пользователей без паролей
SELECT user, host FROM mysql.user WHERE authentication_string = '';

-- Проверка привилегий SUPER
SELECT user, host FROM mysql.user WHERE Super_priv = 'Y';
Производительность

Регулярно анализируйте медленные запросы и оптимизируйте индексы для улучшения производительности.

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

Всегда используйте сильные пароли и ограничивайте привилегии пользователей по принципу наименьших прав.