MySQL root 패스워드 분실

아래와 같이 가끔 MySQL의 root 패스워드를 잃어버릴때가 있죠..

root 패스워드를 초기화 해보겠습니다.

아래와 같이 진행해줍니다.

[root@localhost bin]$
[root@localhost bin]# systemctl stop mysqld
[root@localhost bin]#
[root@localhost bin]# pwd
/usr/bin
[root@localhost bin]# mysqld_safe --skip-grant &
[1] 6760
[root@localhost bin]# 161002 00:30:00 mysqld_safe Logging to '/var/lib/mysql/localhost.localdomain.err'.
161002 00:30:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@localhost bin]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

1) mysql을 종료합니다

2) mysql 실행파일이 있는 폴더에가서 mysql_safe를 실행해줍니다(백그라운드로)

3) 패스워드 없이 mysql에 접속됩니다. (패스워드 입력 안하고 걍 엔터)

이제 root 패스워드 바꿔줘야죠

UPDATE USER SET PASSWORD=PASSWORD('패스워드') WHERE USER='root';

위와 같이 바꿔주면 됩니다.

이제 mysql_safe는 종료하고, mysql 재시작 후 다시 접속해보겠습니다.

# 패스워드 없이 접속
[root@localhost bin]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> 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> update user set password=password('mysql123') where user='root';
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> quit
Bye

#mysql_safe 종료 & mysql 재시작
[root@localhost bin]# jobs
[1]+  Running                 mysqld_safe --skip-grant &
[root@localhost bin]# kill %1
[root@localhost bin]# se161002 00:46:05 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended

[1]+  Done                    mysqld_safe --skip-grant
[root@localhost bin]# systemctl restart mysqld

# 변경된 패스워드로 접속 확인
[root@localhost bin]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

도움이 되시기를 ^^