Гайд: простой сброс пароля пользователя root MySQL или MariaDB на Ubuntu 20.04

В этой статье мы подробно разберем, как восстановить доступ к MySQL или MariaDB в случае утери пароля root. Если такое случилось, паниковать не стоит — при наличии доступа к серверу и учетной записи с правами root в операционной системе, пароль можно сбросить.

Важный момент

Стандартная конфигурация MySQL и MariaDB в Ubuntu 20.04 допускает вход в БД под рутом без дополнительного ввода пароля. В таком случае сброс пароля не требуется. Чтобы проверить, актуальна ли эта настройка, введите в терминале:

sudo mysql

Если получите ошибку "Access denied", значит, настройки были изменены, и вам потребуется выполнить действия из этой инструкции.

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

Шаг 1: Определяем версию БД и останавливаем ее работу

Метод сброса пароля зависит от того, какая СУБД установлена в системе. Чтобы узнать текущую версию, введите:

mysql --version

В итоге консоль должна выдать один из следующих вариантов: 

MariaDB:

mysql Ver 15.1 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

MySQL: 

mysql Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

Теперь необходимо остановить БД:

MariaDB:

sudo systemctl stop mariadb

MySQL:

sudo systemctl stop mysql

После остановки переходим к перезапуску в особом режиме.

Шаг 2: Запускаем БД 

Для сброса пароля необходимо запустить сервер без проверки таблицы прав (режим --skip-grant-tables). Этот режим небезопасен, поэтому сервер будет работать в однопользовательском режиме без сетевых подключений.

Для MariaDB:

1. Настроим переменные окружения:

sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"

2. Запускаемся:

sudo systemctl start mariadb

3. Проверяем работу:

sudo systemctl status mariadb

4. Теперь подключаемся:

sudo mysql -u root

Для MySQL:

1. Редактируем конфигурацию systemd:

sudo systemctl edit mysql

2. В открывшемся файле добавляем:

[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --skip-grant-tables --skip-networking

3. Сохраняем файл (CTRL+X, затем Y) и применяем правки:

sudo systemctl daemon-reload

4. Запускаем MySQL:

sudo systemctl start mysql

5. Подключаемся к БД:

sudo mysql -u root

После успешного входа переходим к следующему шагу.

Шаг 3: Сбрасываем старый пароль

Перед сменой пароля нужно обновить таблицы привилегий:

FLUSH PRIVILEGES

Теперь выполняем смену пароля.

Для MariaDB:

Выполните команду

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'

А если потребуется сбросить механизм аутентификации добавьте:

UPDATE mysql.user SET authentication_string = '' WHERE user = 'root';
UPDATE mysql.user SET plugin = '' WHERE user = 'root'

Для MySQL:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new_password'

После успешного выполнения команд можно выходить из консоли и переходить к нормальному режиму работы сервера.

Шаг 4: Перезапускаем и возвращаем БД в нормальный режим работы

MariaDB:
1. Удаляем переменные окружения:

sudo systemctl unset-environment MYSQLD_OPTS

2. Перезапускаем сервер:

sudo systemctl restart mariadb

MySQL:
1. Сбрасываем изменения конфигурации:

sudo systemctl revert mysql

Вывод может быть таким:

Removed /etc/systemd/system/mysql.service.d/override.conf.
Removed /etc/systemd/system/mysql.service.d.

2. Перезагружаем systemd и MySQL:

sudo systemctl daemon-reload
sudo systemctl restart mysql

Теперь можно протестировать вход с новым паролем:

mysql -u root -p

Готово! Теперь доступ к базе данных восстановлен. Советуем надежно хранить пароль, чтобы избежать подобных ситуаций в будущем.

Бонус №1: с какими ошибками во время сброса вы можете столкнуться

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

Ошибка Access denied for user 'root'@'localhost' даже после сброса

Причина: Это может быть связано с тем, что изменения пароля не вступили в силу, или пользователь root аутентифицируется через другой механизм (например, auth_socket в Ubuntu).

Решение:

→ После изменения пароля выполните команду FLUSH PRIVILEGES, чтобы обновить таблицы привилегий.

→ Проверьте текущий метод аутентификации пользователя root с помощью:

SELECT user, host, plugin FROM mysql.user

→ Если в колонке plugin указано auth_socket, смените плагин аутентификации на mysql_native_password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password'

После этого снова выполните

FLUSH PRIVILEGES

Сбой запуска MySQL/MariaDB в режиме --skip-grant-tables

Причина: Возможные ошибки в конфигурационных файлах или несовместимость с systemd.

Решение:

→ Проверить журналы ошибок сервера командой:

journalctl -xe | grep mysql

→ Убедиться, что MySQL/MariaDB не запущен перед стартом в безопасном режиме:

sudo systemctl stop mysql
sudo systemctl stop mariadb

→ Запуск сервера с флагом --skip-networking, чтобы предотвратить удалённые подключения и повысить безопасность:

sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"
sudo systemctl start mysql

Проблемы с systemd и возможные способы их устранения

Причина: systemd может не применять изменения конфигурации или использовать закэшированные параметры запуска.

Решение:

→ Принудительно перечитать конфигурацию systemd:

sudo systemctl daemon-reexec

→ Если внесены изменения через systemctl edit mysql, убедитесь, что они сохранены и применены:

sudo systemctl daemon-reload
sudo systemctl restart mysql

→ Если MySQL или MariaDB не запускается, попробуйте проверить наличие override-файлов:
systemctl cat mysqlЕсли override-файлы присутствуют и мешают нормальному запуску, сбросьте их:

sudo systemctl revert mysql

Бонус №2: Несколько советов по оптимальному администрированию БД

Эффективное администрирование MySQL/MariaDB позволяет минимизировать риски утраты доступа, повысить безопасность системы и обеспечить ее стабильную работу. Ниже приведены ключевые рекомендации:

1. Регулярные обновления системы и базы данных
→ Следите за выходом новых версий MySQL/MariaDB и обновляйте их своевременно.
→ Перед обновлением создавайте резервные копии данных, чтобы избежать потери информации.
→ Проверяйте совместимость обновлений с используемыми конфигурациями.

2. Настройка резервного копирования
→ Используйте инструменты для автоматического создания бэкапов, например mysqldump, Percona XtraBackup или Mariabackup.
→ Храните резервные копии в безопасном месте, желательно в нескольких географически разнесенных хранилищах.
→ Настройте ротацию резервных копий, чтобы не переполнять дисковое пространство.

3. Ограничение сетевого доступа к базе данных
→ Разрешите доступ только с доверенных IP-адресов.
→ Используйте файрволл (например, ufw или iptables) для блокировки нежелательных подключений.
→ Отключите удаленный доступ для пользователя root, если он не требуется:

UPDATE mysql.user SET Host='localhost' WHERE User='root'

4. Мониторинг работы сервера
→ Включите ведение логов ошибок (error log) и запросов (slow query log).
→ Используйте инструменты мониторинга, такие как Prometheus, Grafana или MySQL Enterprise Monitor.
→ Регулярно проверяйте производительность запросов и оптимизируйте их при необходимости.

5. Настройка пользователей и их привилегий
→ Создавайте отдельных пользователей для каждого приложения с минимально необходимыми правами.
→ Избегайте использования root-аккаунта для выполнения повседневных задач.
→ Регулярно проверяйте список пользователей и удаляйте неиспользуемые учетные записи.

6. Безопасность паролей и аутентификации
→ Используйте сложные пароли и храните их в менеджере паролей.
→ Включите двухфакторную аутентификацию (если поддерживается).
→ Настройте политику смены паролей и ограничьте срок их действия.

7. Настройка ротации логов
→ Настройте автоматическое удаление старых логов, чтобы избежать переполнения диска.
→ Используйте logrotate или встроенные механизмы управления логами в MySQL/MariaDB.

Следуя этим рекомендациям, вы сможете поддерживать стабильную и безопасную работу базы данных, минимизировать риски потери доступа и повысить производительность системы.
→ Регулярное обновление системы и проверка конфигурации БД
→ Настройка ротации логов, чтобы видеть попытки входа
→ Ограничение сетевого доступа к MySQL/MariaDB для повышения безопасности