CentOS 7.7 の MySQL 8.0 で rootパスワードを初期化する

# はじめに

MySQL 8.0 を使って検証しようと思いサーバーを立てていたのですが、イロイロあって年を越した結果・・・

```
[root@documize ~]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
```

やらかしましたw
rootパスワードを忘れたのですよね🤣

と言うことで MySQL のrootパスワードを初期化する手順をメモとして残します。

# とりあえずセーフモードの起動

```
[root@documize ~]# systemctl stop mysqld
[root@documize ~]# mysqld_safe --skip-grant-tables
-bash: mysqld_safe: コマンドが見つかりません
```

・・・・・・あれ？

[Migrating from mysqld_safe to systemd](https://dev.mysql.com/doc/refman/8.0/en/using-systemd.html#mysqld-safe-to-systemd-migration)
を見ると `mysqld_safe` は systemd を使うプラットフォームではインストールされないようです。

ではどうしたら良いかというと、それに関しても書いてありました。

[Configuring systemd for MySQL](https://dev.mysql.com/doc/refman/8.0/en/using-systemd.html#systemd-mysql-configuration)

`systemctl set-environment MYSQLD_OPTS="XXXXX"`で環境変数を設定すれば良さそうです。
やってみましょう。

```
[root@documize ~]# systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
[root@documize ~]# systemctl start mysqld
[root@documize ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

```

お！無事にログインできました！
あとはパスワードの再設定をするだけです！

まずパスワードを消去します。

```
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT user, host, authentication_string FROM user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | $A$005$%jSUF\ C}"2}kIt B4mU1GgNJDg6bmijtCrPCwx8a5X4cf2HPvqCdmQ7DEY/C |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> UPDATE user SET authentication_string=null WHERE User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> exit
Bye
[root@documize ~]# 
```

MySQLを起動するのですが、このままだと環境変数に `--skip-grant-tables` が設定されたままなので環境変数を削除します。

```
[root@documize ~]# systemctl show-environment
LANG=en_US.UTF-8
MYSQLD_OPTS=--skip-grant-tables
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
[root@documize ~]# systemctl unset-environment MYSQLD_OPTS
[root@documize ~]# 
[root@documize ~]# systemctl show-environment
LANG=en_US.UTF-8
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
[root@documize ~]# 
```

MySQLを起動してアクセスします。

```
[root@documize ~]# systemctl start mysqld
[root@documize ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
```

以下のクエリでパスワードの再設定を行います。
`ALTER USER 'root'@'localhost' identified BY '新しいパスワード';`

```
mysql> ALTER USER 'root'@'localhost' identified BY 'Xxxx@1234';
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
[root@documize ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@documize ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
```

無事に再設定ができましたー！