BJcomm

mysql old password 에서 new password 체계로 변경하기

old password 체계를 사용하는 mysql 서버로 접속할 때 아래롸 같은 에러가 날때 조치방법

PHP Warning: mysqli_connect(): (HY000/2000): mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file ...

1. OLD PASSWORD 환경인지 확인

mysql> SHOW VARIABLES LIKE 'old_passwords';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| old_passwords | ON |

+---------------+-------+

1 row in set (0.00 sec)

mysql> SELECT LENGTH(PASSWORD) FROM mysql.user WHERE USER='유저명'

+------------------+

| LENGTH(PASSWORD) |

+------------------+

| 16 |

+------------------+

mysql>

* 비밀번호 길이

old_password: 16

new_password: 41

2. 새 비밀번호 체계로 변경하기

mysql> SET old_passwords=0;

mysql> use mysql;

mysql> UPDATE user SET PASSWORD=PASSWORD('비밀번호') WHERE USER = '유저명';

mysql> SET old_passwords=1;

mysql> FLUSH PRIVILEGES;

mysql> SELECT LENGTH(PASSWORD) FROM mysql.user WHERE USER='유저명'

+------------------+

| LENGTH(PASSWORD) |

+------------------+

| 41 |

+------------------+

mysql>