FAQ по восстановлению забытого пароля администратора в MySQL

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

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

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

1. Перезапустить сервер в режиме --skip-grant-tables
2. Установить новый пароль администратора
3. Перезапустить сервер в обычном режиме

Теперь подробнее о каждом пункте. В режиме skip-grant-tables отключена проверка прав доступа и привилегий, иными словами, вы можете подключиться с пустыми логин/пароль и будете обладать при этом всеми возможными привилегиями.

Примечание. Обратите внимание, что другие пользователи, подключившие до выполнения вами команды flush privileges; или перезагрузки сервера в обычном режиме, также будут обладать администраторскими правами.

MySQL сервер хранит информацию о привилегиях в таблицах привилегий служебной базы mysql. При старте сервера, содержимое таблиц привилегий загружается в память и в дальнейшей работе используется копия, находящаяся в памяти. Команда flush privileges; обновляет данные о привилегиях, загруженные в память. Таким образом, данная команда отменяет режим skip-grant-table и включает проверку прав доступа и привилегий.

Примечание. Обратите внимание, что если вы напрямую редактируете содержимое таблиц привилегий базы mysql, с помощью команд insert, update, delete, то изменения сразу не вступят в силу, так как информация о пользовательских привелегиях, загруженная в память, останется неизменной.
Если использовать рекомендованные команды типа grant или set password, то изменения вступят в силу незамедлительно.

Примечание. Обратите внимание, что изменение прав пользователей не распространяется на уже установленные соединения пользователей. Если нужно отнять у кого-то права, то после следует убедиться, что он не подключен к серверу (с помощью SHOW FULL PROCESSLIST), а если подключен, то убить его поток (оператором KILL).

Для запуска сервера в режиме skip-grant-tables проще всего временно добавить строчку skip-grant-tables в my.ini (для ОС Linux файл будет называться my.cnf) в секции [mysqld]

Затем перезапустить сервер.

Дальнейшие действия будут зависеть от используемого вами клиента:

1. Если ваш клиент не разрывает соединение после выполнения каждой команды как, например, родной клиент mysql, то первым делом выполняем команду flush privileges;, которая загружает в память таблицы грантов. Затем с помощью команд grant или set password назначаем пароль администратору:
   set password for root@localhost=password('mypassword');
   Данный вариант действий является предпочтительным.
2. Если ваш клиент разрывает соединение после выполнения каждой команды, например, Query Browser, то после выполнения flush privileges; он будет требовать указать пароль, который мы ещё не успели назначить. Назначить сначала пароль с помощью команд grant или set password не получится, так как в режиме skip-grant-tables их нельзя использовать. (Выше уже указывалось, что flush privileges; отменяет данный режим, поэтому в предыдущем пункте данные команды работают.) Остается единственно возможный способ это напрямую изменять данные в таблице mysql.user
   UPDATE mysql.user SET password=PASSWORD('mypassword') WHERE user='root' AND host='localhost';

Как восстановить root-пользователя?

Если root-пользователь (пользователь, обладающий всеми возможными прривилегиями, как правило имеет имя root) был по неосторожности удален, то последовательность действий аналогична предыдущему разделу за исключением того, что вместо назначения пароля необходимо будет создать root-пользователя. Т.е. в режиме skip-grant-tables в зависимости от используемого вами клиента действуем одним из нижеследующих способов:

1. flush privileges;
   Затем добавляем root-пользователя с помощью команды grant
   grant all ON *.* TO `root`@`localhost` identified by 'mypassword' with grant option;
2. Создаем root-пользователя путем прямого добавления записи в таблицу mysql.user. Обратите внимание, что структура таблицы mysql.user в разных версиях различна. Перед добавлением изучите её с помощью команд SHOW CREATE TABLE или DESCRIBE. Например, для версии 5.1.21-beta-community добавление root-пользователя производится следующей командой:
   INSERT INTO mysql.user
   (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,
   Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,
   Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_priv,Lock_tables_priv,Execute_priv,
   Repl_slave_priv,Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,
   Alter_routine_priv,Create_user_priv,Event_priv,Trigger_priv,ssl_type,ssl_cipher,x509_issuer,
   x509_subject,max_questions,max_updates,max_connections,max_user_connections)
   VALUES('localhost','root',password('mypassword'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
   'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);

Распространенные случаи.

Ошибка (1045  Access denied for user 'root'@'localhost' (using password: No)) означает, что подключение производится с пустым паролем.
Ошибка (1045  Access denied for user 'root'@'localhost' (using password: Yes)) указывает на неверный пароль.
Обе данные ошибки могут возникнуть вследствии недостатока прав доступа для выполнения требуемой операции, т.е. пользователя зовут root, но права у него не рутовые. На практике такая ситуация маловероятна.

Удалённый доступ root-пользователя.

В MySQL пользователь характеризуется двумя параметрами: именем и хостом, с которого он может обращаться, т.е. user@x и user@y - это разные пользователи. Для удобства записи в MySQL используются квантификаторы, например, % на месте хоста означает любую машину кроме локальной.

Если при доступе под рутом с удаленной машины вы получаете ошибку (1045 Acces denied for user 'root'@'%'), то это может оказаться следствием того, что в настройках по умолчанию удаленный root-пользователь оказался не совсем "рутом", т.е. не обладает всеми привилегиями. Для проверки нужно (под настоящим "рутом") сравнить результат выполнения команд:

И при необходимости добавить недостающие привилегии пользователю root@'%' с помощью команды GRANT. Если необходимо владеть привилегиями администратора при соединении с удаленной машины, то рекомендуем, в целях безопасности, называть такого пользователя другим именем (не root).

Ссылки

В официальной документации описан оригинальный способ восстановления администраторского пароля. Подробнее см. How to Reset the Root Password