MySQL для администратора: команды, права пользователей, резервное копирование и восстановление

В MySQL опаснее всего команда, которая успешно выполнилась не на том сервере. Поэтому рабочая шпаргалка должна отвечать на четыре вопроса: куда мы подключились, какие права используем, что изменится и как проверить результат. Ниже — последовательность для MySQL 8.4 LTS: от диагностики до восстановления копии на отдельном тестовом экземпляре.

Примеры shell рассчитаны на Bash в Linux, SQL — на клиент mysql. Имена appdb, app_user и адрес 192.0.2.20 демонстрационные. MariaDB имеет собственные версии и инструменты: не считайте её полностью взаимозаменяемой с MySQL. Изменяющие команды выполняйте после проверки объекта и плана возврата.

1. Как подключиться и определить сервер

Проверьте установленный клиент, затем подключитесь с запросом пароля, без его передачи в аргументах:

mysql --version
mysql --host=127.0.0.1 --port=3306 --user=admin_ops --password

127.0.0.1 задаёт TCP-подключение. localhost в Unix обычно означает локальный сокет; это другой путь и потенциально другая учётная запись. На удалённом сервере настройте TLS с проверкой имени и доверенным CA, например --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/mysql/company-ca.pem. Имя в --host должно соответствовать сертификату.

В клиенте выполните:

SELECT VERSION(), @@hostname, @@port, DATABASE(), USER(), CURRENT_USER();
SHOW VARIABLES LIKE 'read_only';
SHOW VARIABLES LIKE 'super_read_only';

USER() показывает предъявленное имя и источник, CURRENT_USER() — аккаунт, выбранный сервером. read_only не является гарантией запрета записи для всех привилегированных пользователей. Сопоставьте hostname и роль экземпляра с реестром: primary, replica или тестовый сервер.

2. Как проверить базу, таблицы и движки

SHOW DATABASES;
USE appdb;
SHOW TABLES;
SHOW CREATE TABLE appdb.orders;
SELECT ENGINE, COUNT(*) AS tables_count
FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_type = 'BASE TABLE'
GROUP BY ENGINE;

Список баз зависит от прав пользователя. SHOW CREATE TABLE помогает проверить индексы, внешние ключи и кодировку перед изменением. Таблица orders приведена как пример; её наличие не предполагается.

Для оценки размера без полного COUNT(*) каждой таблицы:

SELECT table_name, engine, table_rows,
       ROUND((data_length + index_length)/1024/1024, 1) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'appdb'
ORDER BY data_length + index_length DESC;

table_rows для InnoDB — оценка, не контроль точного количества записей. Размер в этой выборке также не равен всему занятому месту на диске: отдельно учитывайте tablespaces, журналы и свободное пространство.

3. Как создать базу и отдельного пользователя приложения

Для нового учебного приложения пример выглядит так:

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'app_user'@'192.0.2.20'
  IDENTIFIED BY 'REPLACE_WITH_RANDOM_PASSWORD' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_user'@'192.0.2.20';
SHOW GRANTS FOR 'app_user'@'192.0.2.20';

Замените обозначение пароля настоящим секретом из защищённого хранилища; не копируйте заглушку в рабочую сеть. Кодировка и collation выбираются под приложение. utf8mb4_0900_ai_ci — пример MySQL 8, а не универсальная настройка для MariaDB или старого приложения. Существующую базу эта команда не преобразует.

Разделение account и выдачи прав описано в CREATE USER и GRANT. Host — источник соединения, который видит MySQL; NAT может изменить его. REQUIRE SSL требует защищённый транспорт, но проверку личности сервера выполняет клиент.

Права CREATE/ALTER/DROP выдавайте отдельно роли миграции, если это допускает приложение. Не давайте runtime-аккаунту ALL ON *.*. После штатных CREATE USER/GRANT не требуется FLUSH PRIVILEGES. Не исправляйте системную таблицу mysql.user вручную.

4. Как проверить доступ и сменить пароль

Откройте отдельное соединение с узла приложения и убедитесь, что работает нужная база и выбран правильный аккаунт. Для смены секрета:

ALTER USER 'app_user'@'192.0.2.20'
  IDENTIFIED BY 'REPLACE_WITH_NEW_RANDOM_PASSWORD';
SHOW GRANTS FOR 'app_user'@'192.0.2.20';

Смена пароля требует согласованного обновления секрета приложения, проверки пула соединений и новых подключений. Не предполагайте, что уже открытая сессия немедленно завершится. Не публикуйте выводы с чувствительными данными.

Если старый клиент не поддерживает аутентификацию, сначала обновите драйвер. В MySQL 8.4 mysql_native_password отключён по умолчанию; перенос старых инструкций с принудительным включением плагина требует отдельного решения. Статус плагина — в документации MySQL.

5. Как посмотреть запросы и параметры без «оптимизации всего»

SHOW FULL PROCESSLIST;
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'slow_query_log';

Часть сведений требует дополнительных привилегий. PROCESSLIST может содержать тексты запросов и пользовательские данные; храните вывод ограниченно. Справочник PROCESSLIST описывает доступность сведений для разных прав.

Счётчики смотрите в динамике: накопленное число медленных запросов без времени наблюдения мало что объясняет. До изменения buffer pool или max_connections оцените RAM, конкуренцию с PHP/другими сервисами, пики и план возврата. Системные переменные имеют разные области и способы сохранения.

Не завершайте все длинные запросы: это может быть резервное копирование или ожидаемая транзакция. Сначала определите владельца, блокировки и назначение операции.

6. Как изменить данные с проверкой и возвратом

Для InnoDB на учебной таблице сначала выберите точно нужные строки, затем откройте транзакцию:

SELECT id, status FROM appdb.orders WHERE id = 123;
START TRANSACTION;
UPDATE appdb.orders SET status = 'archived' WHERE id = 123;
SELECT ROW_COUNT();
SELECT id, status FROM appdb.orders WHERE id = 123;
ROLLBACK;

Здесь намеренно выполняется ROLLBACK: пример показывает проверку без сохранения. В согласованной операции COMMIT применяют только после ожидаемого результата. Если строки меняют другие процессы, нужен отдельный план конкурентного доступа; SELECT до транзакции их не блокирует.

Транзакция не заменяет копию. DDL и нетранзакционные движки имеют другое поведение; долго открытая транзакция удерживает ресурсы. Ограничения — в START TRANSACTION и ROLLBACK. UPDATE без WHERE и глобальный REPLACE в неизвестных данных не входят в безопасную шпаргалку.

7. Как сохранить логическую копию через mysqldump

Для небольшой базы InnoDB без DDL во время копирования, без задачи переноса GTID/репликации:

umask 077
backup_dir="$HOME/mysql-backups"
mkdir -p "$backup_dir"
dump_file="$backup_dir/appdb-$(date +%Y%m%d-%H%M%S).sql"
mysqldump --host=127.0.0.1 --user=backup_ops --password \
  --single-transaction --quick --routines --events --triggers \
  --no-tablespaces --set-gtid-purged=OFF \
  --result-file="$dump_file" appdb
backup_rc=$?
printf 'mysqldump exit=%s\n' "$backup_rc"
if [ "$backup_rc" -eq 0 ] && [ -s "$dump_file" ]; then
  sha256sum "$dump_file"
else
  printf 'Копия не принята: проверьте ошибку и файл\n' >&2
fi

backup_ops должен иметь права, требуемые выбранными объектами; routines/events могут потребовать дополнительные полномочия. Здесь файл сохраняется в профиль оператора и не считается внешней защищённой копией. Команда не выгружает аккаунты и всю конфигурацию сервера.

--single-transaction не обеспечивает согласованность MyISAM и не защищает от конкурентного ALTER/DROP. --set-gtid-purged=OFF выбран для отдельного восстановления, не для подготовки replica. Windows PowerShell-перенаправление может менять кодировку: используйте --result-file. Возможности и требования — в mysqldump.

8. Как восстановить копию на отдельном тестовом сервере

Не импортируйте первый проверочный дамп обратно в production. Подготовьте отдельный MySQL той же совместимой версии, без доступа к рабочей почте, платежам и интеграциям. Сначала убедитесь, что в дампе нет USE/CREATE DATABASE с рабочим именем; пример выше выгружает одну базу без --databases.

mysql --host=127.0.0.1 --port=3307 --user=restore_ops --password \
  -e "CREATE DATABASE restore_appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;"
mysql --host=127.0.0.1 --port=3307 --user=restore_ops --password \
  restore_appdb < "$dump_file"
restore_rc=$?
printf 'restore exit=%s\n' "$restore_rc"

Порт 3307 предполагает уже подготовленный тестовый экземпляр, а не автоматически создаёт его. Перед импортом повторите SELECT @@hostname, @@port. DEFINER у views/routines и events требуют отдельной проверки; не включайте фоновые задания тестовой базы, пока не изолированы внешние действия.

Проверьте таблицы, контрольные записи, чтение/запись приложения и длительность восстановления. Файл ненулевого размера и хеш подтверждают наличие и идентичность файла, а не пригодность базы. Для больших объёмов оцените физические копии и подходящий инструмент; для RPO меньше интервала копирования — binlog/PITR.

9. Почему нельзя запускать ежедневный repair/optimize всех баз

REPAIR TABLE не является ремонтом InnoDB. Автоматическое mysqlcheck с repair и optimize всех баз из старых шпаргалок может вызвать нагрузку, блокировки и перестроение объектов без установленной причины. Различия движков — в Rebuilding or Repairing Tables.

При признаках повреждения сохраните журналы и исходное состояние, проверьте диски и наличие пригодной копии. Не удаляйте redo/ibd-файлы и не включайте recovery-параметры по случайной инструкции. Возврат из подтверждённой копии и диагностика причины — отдельный план DBA.

10. Как организовать повторяемое обслуживание

У задания должны быть отдельный аккаунт, журнал, уведомление о ненулевом exit code, контроль срока последней успешной копии и регулярное тестовое восстановление. Для автоматизации mysql_config_editor позволяет не передавать пароль в аргументах, но файл login-path лишь затрудняет чтение секрета и не заменяет его защищённое хранение.

После изменения примите три результата: нужное приложение работает, лишние права не появились, возврат проверен. Для совместимой настройки MySQL, резервного копирования и диагностики обратитесь в АВАНГАРД ИТ.