МДК.11.01 - 25 - Основы администрирования БД

Введение

Администрирование баз данных — это ключевой аспект работы с системами хранения данных. Администратор баз данных (DBA) отвечает за настройку серверной среды, контроль нагрузки, диагностику проблем и обеспечение безопасности данных. В этой лекции мы рассмотрим основные задачи администратора MySQL, ключевые параметры конфигурации и их практическое применение.

Основные задачи администратора баз данных (DBA)

Администратор баз данных выполняет следующие задачи:

Настройка серверной среды:
- Конфигурирование сервера для обеспечения стабильной работы.
- Оптимизация производительности базы данных.
Контроль нагрузки:
- Мониторинг использования ресурсов (CPU, память, дисковое пространство).
- Анализ медленных запросов и их оптимизация.
Диагностика и устранение проблем:
- Поиск и исправление ошибок в работе базы данных.
- Восстановление данных после сбоев.
Обеспечение безопасности:
- Настройка прав доступа пользователей.
- Защита данных от несанкционированного доступа.

Эти задачи требуют глубокого понимания конфигурационных параметров MySQL и способов их проверки.

Файлы конфигурации MySQL

Файлы конфигурации MySQL содержат параметры, которые влияют на работу сервера. Их расположение зависит от операционной системы:

Windows: Файл my.ini находится в папке установки MySQL или в C:\ProgramData\MySQL\MySQL Server 8.0.
Linux: Файл my.cnf обычно расположен в /etc/mysql/ или /etc/.

После изменения конфигурационного файла необходимо перезапустить службу MySQL:

Windows:
```
net stop mysql && net start mysql
```
Linux:
```
sudo systemctl restart mysql
```

Ключевые параметры MySQL: описание и примеры

Перед тем как приступить к практической части, давайте рассмотрим несколько важных параметров конфигурации MySQL, которые могут быть настроены для оптимизации работы сервера. Эти параметры влияют на производительность, безопасность и удобство администрирования.

1. `max_connections`

Описание: Определяет максимальное количество одновременных подключений к серверу. По умолчанию значение равно 151.
Пример использования: Если ваш сервер обслуживает много пользователей, увеличьте значение этого параметра. Однако учтите, что слишком высокое значение может привести к перегрузке сервера.
Пример настройки:
```
max_connections = 200
```
Рекомендации:
- Для небольших проектов достаточно значений от 100 до 200.
- Для крупных проектов можно увеличить до 500-1000, но важно мониторить использование памяти.

2. `wait_timeout`

Описание: Задаёт время ожидания неактивного соединения перед его закрытием. По умолчанию значение равно 28800 секунд (8 часов).
Пример использования: Уменьшение этого значения может помочь освободить ресурсы от неактивных подключений.
Пример настройки:
```
wait_timeout = 60
```
Рекомендации:
- Установите значение в зависимости от характера нагрузки. Например, для веб-приложений можно использовать 60-120 секунд.
- Слишком маленькое значение может привести к частым разрывам соединений.

3. `innodb_buffer_pool_size`

Описание: Размер буферного пула InnoDB, который используется для кэширования данных и индексов. Это один из самых важных параметров для производительности.
Пример использования: Для больших баз данных рекомендуется увеличить этот параметр. Обычно выделяют 70-80% оперативной памяти сервера.
Пример настройки:
```
innodb_buffer_pool_size = 4G
```
Рекомендации:
- Для серверов с 16 ГБ RAM можно установить значение 12G.
- Используйте команду SHOW ENGINE INNODB STATUS; для анализа использования буфера.

4. `innodb_flush_log_at_trx_commit`

Описание: Определяет, как часто журнал транзакций записывается на диск. Значение 1 обеспечивает максимальную надёжность, но может снижать производительность. Значение 2 или 0 может быть использовано для повышения производительности за счёт меньшей надёжности.
Пример настройки:
```
innodb_flush_log_at_trx_commit = 2
```
Рекомендации:
- Используйте значение 1 для критически важных данных.
- Значение 2 подходит для тестовых сред или проектов с менее строгими требованиями.

5. `slow_query_log`

Описание: Включает логирование медленных запросов. Медленным считается запрос, выполнение которого занимает больше времени, чем указано в параметре long_query_time.

Пример настройки:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Далее в рамках лекции мы рассмотрим медленные запросы подробнее.

6. `query_cache_size`

Описание: Размер кэша запросов, который используется для хранения результатов часто выполняемых запросов.
Пример использования: Может быть полезен для часто повторяющихся запросов.
Пример настройки:
```
query_cache_size = 64M
```

7. `tmp_table_size` и `max_heap_table_size`

Описание: Определяют максимальный размер временных таблиц в памяти.
Пример использования: Увеличение этих значений может улучшить производительность сложных запросов.

Пример настройки:

tmp_table_size = 64M
max_heap_table_size = 64M

8. `log_queries_not_using_indexes`

Описание: Логирует запросы, которые не используют индексы.
Пример использования: Помогает выявить запросы, которые могут быть оптимизированы.
Пример настройки:
```
log_queries_not_using_indexes = 1
```

9. `sql_mode`

Описание: Определяет режим работы SQL, влияющий на строгость проверок и поведение сервера.
Пример использования: Режим STRICT_ALL_TABLES делает сервер более строгим в отношении ошибок.
Пример настройки:
```
sql_mode = STRICT_ALL_TABLES
```

10. `bind-address`

Описание: Определяет IP-адрес, на котором будет работать сервер MySQL.
Пример использования: По умолчанию MySQL привязан к 127.0.0.1, что ограничивает доступ только локальным пользователям.
Пример настройки:
```
bind-address = 0.0.0.0
```

Инфо

Это лишь часть параметров MySQL. В реальности существует гораздо больше настроек, которые можно настраивать в зависимости от требований вашего проекта.

Медленные запросы: причины и оптимизация

Что такое медленные запросы?

Медленные запросы — это SQL-запросы, которые выполняются дольше заданного времени (long_query_time). Они могут значительно замедлять работу базы данных.

Почему запросы становятся медленными?

Отсутствие индексов: Запросы, которые сканируют всю таблицу, вместо использования индексов.
Неправильные JOIN-операции: Некорректные связи между таблицами.
Сложные подзапросы: Подзапросы, которые выполняются многократно.
Большие объемы данных: Работа с большими таблицами без оптимизации.

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

SELECT * FROM users WHERE username LIKE '%admin%';

Этот запрос медленный, потому что:

Он использует оператор LIKE с начальным %, что делает невозможным использование индексов.
Сканирует всю таблицу.

Как найти медленные запросы?

Используйте команду EXPLAIN для анализа запроса:

EXPLAIN SELECT * FROM users WHERE username LIKE '%admin%';

Вывод покажет, что запрос выполняет полное сканирование таблицы (type: ALL).

Как исправить медленные запросы?

Добавьте индексы: Создайте индекс для столбца username:
```
CREATE INDEX idx_username ON users(username);
```
Оптимизируйте запрос: Избегайте использования LIKE с начальным %.

Совет

Используйте инструменты мониторинга, такие как Performance Schema или сторонние решения (например, Percona Monitoring and Management), чтобы находить и анализировать медленные запросы.

Практическая часть: Настройка и тестирование параметров

1. Настройка `max_connections`

Описание: Этот параметр определяет максимальное количество одновременных подключений к серверу MySQL.

Шаги тестирования:

Откройте файл конфигурации MySQL:
- Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
- Linux: /etc/mysql/my.cnf или /etc/my.cnf
Добавьте или измените следующий параметр:
```
max_connections = 1
```

Перезапустите сервер MySQL:

Windows:
```
net stop mysql && net start mysql
```
Linux:
```
sudo systemctl restart mysql
```

Проверьте текущее значение параметра:
```
SHOW VARIABLES LIKE 'max_connections';
```
Откройте два терминала (или два окна командной строки) и попробуйте подключиться к серверу MySQL:
```
mysql -u root -p
```
Первое подключение должно быть успешным, а второе — отклонено с ошибкой:
```
ERROR 1040 (HY000): Too many connections
```
Увеличьте значение до 2 и повторите шаги 3-5. Теперь должны быть разрешены два одновременных подключения.

2. Тестирование `wait_timeout`

Описание: Этот параметр задаёт время ожидания неактивного соединения перед его закрытием.

Шаги тестирования:

Откройте файл конфигурации MySQL и установите значение:
```
wait_timeout = 30
```

Перезапустите сервер MySQL:

Windows:
```
net stop mysql && net start mysql
```
Linux:
```
sudo systemctl restart mysql
```

Подключитесь к серверу MySQL:
```
mysql -u root -p
```
Выполните простой запрос:
```
SELECT NOW();
```
Подождите 30 секунд (не выполняйте никаких действий).
Попробуйте выполнить ещё один запрос:
```
SELECT NOW();
```
Если таймаут истёк, вы получите ошибку:
```
ERROR 2006 (HY000): MySQL server has gone away
```

Заметка

Если вы хотите проверить текущее значение wait_timeout, выполните:

SHOW VARIABLES LIKE 'wait_timeout';

3. Логирование медленных запросов

Описание: Этот параметр позволяет логировать запросы, которые выполняются дольше указанного времени (long_query_time).

Шаги тестирования:

Откройте файл конфигурации MySQL и добавьте следующие параметры:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log  # Для Linux
slow_query_log_file = C:/path/to/slow.log      # Для Windows
long_query_time = 2

Перезапустите сервер MySQL:

Windows:
```
net stop mysql && net start mysql
```
Linux:
```
sudo systemctl restart mysql
```

Подключитесь к серверу MySQL и выполните запрос, который занимает больше 2 секунд:
```
SELECT SLEEP(3);
```
Проверьте файл лога:
- Linux:
```
cat /var/log/mysql/slow.log
```
- Windows:
  Откройте файл C:/path/to/slow.log в текстовом редакторе.

Вы должны увидеть запись о медленном запросе:

# Time: ...
# User@Host: ...
# Query_time: 3.000123  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SELECT SLEEP(3);

Совет

Для анализа медленных запросов используйте команду EXPLAIN. Например:

EXPLAIN SELECT * FROM users WHERE username LIKE '%admin%';

Заключение

В этой лекции мы изучили ключевые параметры конфигурации MySQL и их практическое применение. Эти навыки позволяют администраторам обеспечить стабильность и производительность базы данных.
Теперь вы знаете, как настраивать и тестировать ключевые параметры MySQL, такие как max_connections, wait_timeout и логирование медленных запросов. Эти навыки помогут вам обеспечить стабильность и производительность базы данных в различных операционных системах.
Следующая лекция будет посвящена безопасности баз данных.