MySQL 8 비밀번호 초기화

MySQL 8 버전 기준으로 정리함.

 

1. MySQL 서비스 종료

2. 인증권한 skip 하고 서비스 구동

3. root 계정으로 접속

4. 비밀번호 변경

5. MySQL 서비스 재구동

 

1. MySQL 서비스 종료

서비스를 종료하는 방법은 두가지가 있음. 하나는 windows 작업관리자를 통해 MySQL 서비스를 중지 시키는 방법이고

두번째는 command 상에서 서비스를 중지 시키는 방법이다. 단, command 는 관리자 권한으로 실행 을 해주어야 한다.

 

C:\mysql-8\bin>net stop MySQL
MySQL 서비스를 멈춥니다...
MySQL 서비스를 잘 멈추었습니다.

 

2. 인증 권한을 skip 하도록 지정하고 MySQL 을 시작.

 

C:\mysql-8\bin>mysqld --console --skip-grant-tables --shared-memory
2021-04-05T04:35:41.126363Z 0 [System] [MY-010116] [Server] C:\mysql-8\bin\mysqld.exe (mysqld 8.0.23) starting as process 5892
2021-04-05T04:35:41.145968Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-04-05T04:35:41.806902Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-04-05T04:35:41.965375Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'
2021-04-05T04:35:41.995578Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2021-04-05T04:35:42.006228Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-04-05T04:35:42.104771Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-04-05T04:35:42.109320Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-04-05T04:35:42.133494Z 0 [System] [MY-010931] [Server] C:\mysql-8\bin\mysqld.exe: ready for connections. Version: '8.0.23'  socket: ''  port: 0  MySQL Community Server - GPL.

 

새로운 command 화면을 띄운다. (관리자 권한으로 실행)

3. MySQL 서버에 로그인 수행

 

C:\mysql-8\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>

 

변경하려는 대상 계정의 비밀번호를 Null로 초기화 후 종료.

 

mysql> use mysql;
Database changed
mysql> update user set authentication_string=null where user='root';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'privilieges' at line 1
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

 

4. root 계정으로 다시 로그인 후 비밀번호 변경.

 

C:\mysql-8\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> use mysql
Database changed
mysql> alter user 'root'@'localhost' identified with caching_sha2_password by '12345';
Query OK, 0 rows affected (0.01 sec)

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

 

5. 비밀번호 변경이 완료 되었으므로 MySQL 서비스 구동.