MySQL root 패스워드 분실했을 때

① 실행중인 msyql 종료

# ps -ef | grep mysqld
root 9567 1 0 Mar16 ? 00:00:00 sh ./mysqld_safe
root 9576 9567 0 Mar16 ? 00:00:00 /usr/local/mysql/libexec/mysqld 
root 9578 9576 0 Mar16 ? 00:00:00 /usr/local/mysql/libexec/mysqld 
root 9579 9578 0 Mar16 ? 00:00:00 /usr/local/mysql/libexec/mysqld 

# killall mysqld

② grant-table 미사용모드로 mysql시작(권한 테이블을 사용하지 않는 옵션으로 데몬 실행)

# ./safe_mysqld --skip-grant-tables &
[1] 12084
# Starting mysqld daemon with databases from /usr/local/mysql/data
# 

# ./mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.22.24
Type 'help' for help.
mysql> 

※ mysqld_safe 명령어는 mysql 데몬을 실행시킨다.

③ update문으로 root사용자 패스워드 갱신

mysql> update user set password=password('123') where user = 'root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> \q 
Bye

④ 실행중인 mysql 다시 종료(권한 테이블을 사용하지 않는 데몬을 종료)

# ps -ef | grep mysqld
root 12084 11558 0 20:10 pts/2 00:00:00 sh ./mysqld_safe --skip-grant-ta
root 12090 12084 0 20:10 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld 
root 12092 12090 0 20:10 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld 
root 12093 12092 0 20:10 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld 
# 
# killall mysqld
mysqld daemon ended
[1]+ Done ./mysqld_safe --skip-grant-tables
# 

⑤ Mysql 데몬 재 실행 후 갱신된 패스워드로 로그인

# ./safe_mysqld &
[1] 12102
# Starting mysqld daemon with databases from /usr/local/mysql/data
# 

# ps -ef | grep mysql
root 12102 11558 0 20:13 pts/2 00:00:00 sh ./mysqld_safe
root 12108 12102 0 20:13 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld 
root 12110 12108 0 20:13 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld 
root 12111 12110 0 20:13 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld 

# mysql -u root -p
Enter Password: ***************ero