MySQL root 비밀번호를 잃었을 때

장난치다가 root 비밀번호를 잃을 수 있다!
하지만 복구할 수 있다 :)
MySQL 재설치 불 필요
본 글은 MacOS 기준으로 작성되었습니다.

원인

다음과 같이 비밀번호를 바꾸지 마세요 ^^

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

원인을 해결중에 비밀번호가 암호화된 문자열이 아닌 plain-text 인 'root' 로 설정된 것을 확인할 수 있습니다.
비밀번호 입력창에서 입력한 번호가 암호화되어 root 와 같아질 수 없습니다.

mysql> SELECT User, Host, Authentication_string from mysql.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 | root                                                                   |
+------------------+-----------+------------------------------------------------------------------------+

Error 화면

mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Mysql Password Rest 방법

1. 프로세스에 실행중이던 mysql 를 중지시킨다.

mysql.server stop 

brew 를 이용하는 방법도 있습니다.

brew services stop mysql

프로세스를 찾아서 kill해도 됩니다.

ps -ax | grep mysql
kill -9 <PID>

2. mysql 을 다시 시작할 때는 권한을 확인하지 않는 옵션을 추가합니다.

mysql.server start --skip-grant-tables

3. mysql 접속

mysql -u root

4. --skip-grant-tables 옵션 때문에 비밀번호를 바꾸는 명령어는 실패할 것입니다.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

5. flush privileges 명령어를 통해 grant 테이블을 reload 하면 다시 권한 오류가 발생합니다.

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

6. 해결책은 root 유저를 삭제하고 다시 생성하는 것입니다!

mysql> drop user root@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT User, Host, Authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| User             | Host      | Authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+------------------+-----------+------------------------------------------------------------------------+
3 rows in set (0.00 sec)

root 계정이 사라졌네요?

mysql> flush privileges;
mysql> create user root@localhost identified by 'root'
mysql> SELECT User, Host, Authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| User             | Host      | Authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
9yR{Z-7SyE3FXxISTabrSKTwy1HvnAhkYQEyJBYQh.1LcYu61 |
+------------------+-----------+------------------------------------------------------------------------+

이번엔 identified by 명령어를 통해 root 비밀번호를 암호화하여 설정했습니다! 휴..

7. 새로 설정한 비밀번호로 접속하는 데에 성공했습니다.

mysql> quit 
Bye
jinwoo@anjin-uui-MacBookPro eth-middleware % mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8