mysql root userの権限を全て戻そうとしたらroot@localhostにすらログインできなくなる。。。（解決済み）

MySQL version: 5.5.46

経緯

WordPress on Amazon EC2でWebサイトを公開する Part 2/2の「5. MySQLのインストール」まで終えデータベース作成に入る。

続いて、rootでデータベース “wordpress” を作成(#1)。
mysql > CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 >COLLATE utf8_general_ci;

“wordpress” ユーザーを作成(#2)し、all権限を付与(#3)。

mysql > GRANT ALL ON wordpress.* TO wordpress@localhost IDENTIFIED BY 'password';
mysql > FLUSH PRIVILEGES;(#4)
mysql > exit

問題

#1は行えたのだが、#2ができない。rootユーザーに（恐らく何も）権限が渡されていないためにサイト上の指示に従えない。wordpressユーザーを作成できない。

mysql上のユーザー一覧を表示させようとすると、
mysql> select * from mysql.user;

ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user'と返ってきてしまう。

ユーザー作成も
mysql> create user 'wordpress'@'localhost' identified by 'wordpress';

ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operationとエラーが返ってきてしまう。

Attempt#1

How can I restore the MySQL root user’s full privileges?という投稿を見つけたのでそれを参考にroot userの権限を戻してみる。

1. mysqldを止めて--skip-grant-tablesを使ってリスタートさせる

• service mysqld stop
• vim /etc/my.cnfでmysqldの項目にskip-grant-tablesを追加。
• service mysqld start

1. mysqldサーバーにmysqlで接続。(-p, username無し)
2. UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';&FLUSH PRIVILEGES;

すると以下の様なメッセージ：

Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

（0 rows affectedとなっているが果たして反映されているのだろうか…）

そしてFLUSH PRIVILEGES;

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

（これは0 rows affectedでも大丈夫だっとstackoverflowで誰かが言っていた）

結果

やっと次のステップであるCREATE USERできるはず。ということで

CREATE USER 'wordpress'@"localhost" IDENTIFIED BY "wordpress";

すると

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

なるほど。ということはvimにもどって消してあげればいいのかと思い、一度exitをしてvimを書き換えてmysql -u root -pで戻ろうとすると、

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

と表示される。

それどころか試行錯誤しているうちに新たな問題が浮上

1. vim上でskip-grant-tablesを外してmysql -u root -pでログインできなくなってしまう。エラーの表示内容が以下に変わっている。。

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

1. mysql> select host, user from user;で表示されるuserのうち、間違えてroot user (host: localhost)を消してしまう。

+-----------+------+
| host      | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1       | root |
+-----------+------+
2 rows in set (0.00 sec)

• /etc/my.cnfの中身:

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
character_set_server=utf8


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

追記: root@localhostを作成し直す

Restoring deleted 'root' user and password for MySQLというサイトを発見したので早速サイトに従って以下の様なコマンドを打ち込む。

DELETE FROM mysql.user 
WHERE  user = 'root' 
       AND host = 'localhost'; 

INSERT INTO mysql.user 
SET user = 'root', 
    host = 'localhost', 
    password = Password('whatevernewpassword'), 
    Select_priv = 'y',
    Insert_priv = 'y',
    Update_priv = 'y',
    Delete_priv = 'y',
    Create_priv = 'y',
    Drop_priv = 'y',
    Reload_priv = 'y',
    Shutdown_priv = 'y',
    Process_priv = 'y',
    File_priv = 'y',
    Grant_priv = 'y',
    References_priv = 'y',
    Index_priv = 'y',
    Alter_priv = 'y',
    Show_db_priv = 'y',
    Super_priv = 'y',
    Create_tmp_table_priv = 'y',
    Lock_tables_priv = 'y',
    Execute_priv = 'y',
    Repl_slave_priv = 'y',
    Repl_client_priv = 'y',
    Create_view_priv = 'y',
    Show_view_priv = 'y',
    Create_routine_priv = 'y',
    Alter_routine_priv = 'y',
    Create_user_priv = 'y',
    Event_priv = 'y',
    Trigger_priv = 'y',
    Create_tablespace_priv = 'y';

そして作成したユーザーでmysql -u root -pでログインをし

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

で全ての権限を与える。

もう一度CREATE USERに挑戦:

CREATE USER 'wordpress'@'localhost' IDENTIFIED BY 'wordpress';

それでもまだエラーが返ってくる。。。

ERROR 1396 (HY000): Operation CREATE USER failed for 'wordpress'@'localhost'

Attempt#2

以上の追記を参考に以下の様なコマンドを打てば無理矢理ユーザーを作れるのではないかと思い、やってみることに。

INSERT INTO mysql.user 
SET user = 'wordpress', 
    host = 'localhost', 
    password = Password('wordpress'), 
    Select_priv = 'y',
    Insert_priv = 'y',
    Update_priv = 'y',
    Delete_priv = 'y',
    Create_priv = 'y',
    Drop_priv = 'y',
    Reload_priv = 'y',
    Shutdown_priv = 'y',
    Process_priv = 'y',
    File_priv = 'y',
    Grant_priv = 'y',
    References_priv = 'y',
    Index_priv = 'y',
    Alter_priv = 'y',
    Show_db_priv = 'y',
    Super_priv = 'y',
    Create_tmp_table_priv = 'y',
    Lock_tables_priv = 'y',
    Execute_priv = 'y',
    Repl_slave_priv = 'y',
    Repl_client_priv = 'y',
    Create_view_priv = 'y',
    Show_view_priv = 'y',
    Create_routine_priv = 'y',
    Alter_routine_priv = 'y',
    Create_user_priv = 'y',
    Event_priv = 'y',
    Trigger_priv = 'y',
    Create_tablespace_priv = 'y';

使うデータベースをwordpressに変更

mysql> use wordpress;
Database changed


参考にしたサイト

• How can I restore the MySQL root user’s full privileges?
• Restoring deleted 'root' user and password for MySQL
• MySQLのアクセス権限システムの基礎を知る
• MySQLのユーザー作成でハマったこと解決策メモ。
• [メモ][MySQL] rootユーザーの全権限を消してしまったときの対処方法