はじめに
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
を見ると mysqld_safe は systemd を使うプラットフォームではインストールされないようです。
ではどうしたら良いかというと、それに関しても書いてありました。
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>
無事に再設定ができましたー！