MySQL 8.0 비밀번호 변경하기! (MySQL 5.7버전 이상)

mysql 비밀번호 변경하다가 병들겠네. 5번은 지웠다가 설치한듯.ㅋㅋㅋ 미친듯이 찾다가 중국분이 올리신 글 보고 어느정도 해결된건 안비밀.(중국어 모름) 아니 세상에 내가 중문글을 읽어볼거라곤 꿈에도 몰랐잖아^ㅆ^ 에효 mysql은 설치때부터 애먹이더니, 여기와서도 이렇게 나를 힘들게 하네ㅠ 그래도 이렇게 정리하고 나면 다음번엔 안그러겠지ㅠㅠ 흑흑..  안그럴거야... 그렇지...? 이제 버전따라 안바뀔꺼지? 응?

1. mysql 로그인

myslq -uroot -p

처음에는 Enter password에 아무것도 치지 않아도 들어갈 것이다.

그냥 엔터를 치자!^^

2. mysql 데이터베이스에 접근

use mysql;

꼭 myql 데이터베이스에 접근해서 비밀번호를 변경해야 된다. 그렇지 않으면 syntax 에러가 날 것이다.

그럼 이제 mysql의 테이블들을 보자. 대따 많다. 필요한건 user 이다.

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| default_roles             |
| ....                      |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
33 rows in set (0.00 sec)

이처럼 user 테이블이 있는 것을 볼 수 있다. user 테이블을 살펴보기 전에 왜 이전의 비밀번호 변경이 안먹혔는 지 알아보자.

update user set password=password('변경할비밀번호') where user='root'; 을 이용해서 비밀번호 변경을 해봤을 것이다.

이건 mysql 5.7이전 버전에서 비밀번호를 변경하는 법이다. mysql 5.7이후 버전에서는 사용할 수 없다.

그 이유는 user 테이블을 살펴보자. (밑에는 mysql 5.7이후 버전이다.)

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | caching_sha2_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history | smallint(5) unsigned              | YES  |     | NULL                  |       |
| Password_reuse_time    | smallint(5) unsigned              | YES  |     | NULL                  |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
49 rows in set (0.00 sec)

보다시피 mysql 5.7이후부터는 password 필드가 없다! 그 대신 authentication_string 필드가 password 필드를 대체한다.

update user set authentication_string=password('변경할비밀번호') where user='root'; 를 사용하자!

근데 에러가 나는 건 마찬가지 일 것이다. 메롱

그럼 뭐 어쩌라는거냐!!!!! 너이자식!!!! ٩(๑`^´๑)۶

그래서 열심히 구글링을 하고 또 하고, 했는데 해결이 되지 않았고 (ㅠㅠ) 중국인분 글에서 힌트를 얻어 mysql  메뉴얼 문서를 보게 되었다.

여기에 비밀번호 변경법이 나와있었다.^^ 등잔 밑이 어둡다더니... mysql 사이트부터 찾아볼 생각 안하고 구글링을 하고 앉았다니.. 절레절레..

ALTER USER Authentication Options

An account name may be followed by an auth_option authentication option that specifies the account authentication plugin, credentials, or both. It may also include a REPLACE clause that specifies the current account password to be replaced.

• auth_plugin names an authentication plugin. The plugin name can be a quoted string literal or an unquoted name. Plugin names are stored in the plugin column of the mysql.user system table.

  For auth_option syntaxes that do not specify an authentication plugin, the default plugin is indicated by the value of the default_authentication_plugin system variable. For descriptions of each plugin, see Section 6.5.1, “Authentication Plugins”.

• Credentials are stored in the authentication_string column of the mysql.user system table. An 'auth_string' or 'hash_string' value specifies account credentials, either as a cleartext (unencrypted) string or hashed in the format expected by the authentication plugin associated with the account, respectively:

  • For syntaxes that use 'auth_string', the string is cleartext and is passed to the authentication plugin for possible hashing. The result returned by the plugin is stored in the authentication_string column. A plugin may use the value as specified, in which case no hashing occurs.

  • For syntaxes that use 'hash_string', the string is assumed to be already hashed in the format required by the authentication plugin. If the hash format is inappropriate for the plugin, it will not be usable and correct authentication of client connections will not occur.

• A REPLACE 'current_auth_string' clause (available as of MySQL 8.0.13) specifies the current account password to be replaced, as a cleartext (unencrypted) string:

  • The clause must be given if password changes for the account are required to specify the current password (to verify that the user attempting to make the change actually knows the current password).

  • The clause is optional if password changes for the account may but need not specify the current password.

  • The statement fails if the clause is given but does not match the current password, even if the clause is optional.

  • REPLACE can be specified only when changing the account password for the current user.

  For more information about password verification by specifying the current password, see Section 6.3.8, “Password Management”.

ALTER USER permits these auth_option syntaxes:

• IDENTIFIED BY 'auth_string' [REPLACE 'current_auth_string']

  Sets the account authentication plugin to the default plugin, passes the cleartext 'auth_string' value to the plugin for hashing, and stores the result in the mysql.user account row.

  The REPLACE clause, if given, specifies the current account password, as described previously in this section.

• IDENTIFIED WITH auth_plugin

  Sets the account authentication plugin to auth_plugin, clears the credentials to the empty string (the credentials are associated with the old authentication plugin, not the new one), and stores the result in the mysql.user account row.

  In addition, the password is marked expired. The user must choose a new one when next connecting.

• IDENTIFIED WITH auth_plugin BY 'auth_string' [REPLACE 'current_auth_string']

  Sets the account authentication plugin to auth_plugin, passes the cleartext 'auth_string' value to the plugin for hashing, and stores the result in the mysql.useraccount row.

  The REPLACE clause, if given, specifies the current account password, as described previously in this section.

• IDENTIFIED WITH auth_plugin AS 'hash_string'

  Sets the account authentication plugin to auth_plugin and stores the hashed 'hash_string' value as is in the mysql.user account row. The string is assumed to be already hashed in the format required by the plugin.

(출처:https://dev.mysql.com/doc/refman/8.0/en/alter-user.html)

문서를 보면 ALTER로 user 계정 암호를 변경한다. UPDATE가 아니였다. 두둥.

위의 4개의 예시 중에서 IDENTIFIED WITH auth_plugin BY 'auth_string' 을 이용할 것이다. 

auth_plugin은 인증 플러인으로써, plugin 필드열에 저장된다. mysql의 기본 인증플러그인은 mysql_native_password이다.(변경 가능함)

auth_string은 (암호화되지않은)일반텍스트를 인증플러그인으로 반환하여 authentication_string 필드열에 저장된다.

먼저 user 테이블의 host, user, plugin, authentication_string을 확인해보자.

mysql> select host, user, plugin, authentication_string, password_last_changed from user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | plugin                | authentication_string                                                  | password_last_changed |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | root             | caching_sha2_password |                                                                        | 2018-09-11 18:20:19   |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

위와 같이 비밀번호 설정 전에는 authentication_string이 빈칸일 것이다. 저기에 암호화된 비밀번호를 넣을 것이다!

(password_last_changed는 바뀐거 확인하려구 넣어본것..ㅎ)

3. 비밀번호 변경

ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '변경할비밀번호';

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'pw123';
Query OK, 0 rows affected (0.07 sec)

이렇게 적어주면 비밀번호가 변경된다. 아래처럼 암호화된 비밀번호로 변경된것을 볼 수 있다.

mysql> select host, user, plugin, authentication_string, password_last_changed from user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | plugin                | authentication_string                                                  | password_last_changed |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | root             | mysql_native_password | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA                              | 2018-09-12 10:28:23   |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

이게 끝이 아니다!!! 가장 중요한 게 남았다. 변경하면 끝이 아니라, 저장을 해줘야 한다!!!!!!!!!!

4. 변경 사항 저장

flush privileges;

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

비밀번호가 성공적으로 바뀌었을 것이다! 오예!

quit로 나갔다가 다시 로그인하여 변경한 비밀번호로 로그인하자!

mysql> quit;
Bye
 
C:\Windows\system32>mysql -uroot -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.12 MySQL Community Server - GPL
 
Copyright (c) 2000, 2018, 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>

행복하다! 진짜! 하! ٩(*´◒`*)۶♡

오늘의 교훈 : 알고 싶은 게 있으면 그 사이트의 문서부터 찾아보자^^7