본문 바로가기

Computer Engineering/DB

mysql root 패스워드 분실시 재설정 방법


1) 일단 #ps -ef 로 프로세스를 확인하고 safe_mysqld, mysqld 가떠있으면 날려준다...
    #kill -9 pid번호입력      ========> safe_mysqld 의 pid
    #killall mysqld            =========>남은 찌꺼기 프로세스 제거
 
    #ps -ef     다시 mysqld 떠있나 확인한다.
 
2) mysql 초기화 모드 실행
    #safe_mysqld --skip-grant &               =====>#mysqld_safe --skip-grant &    
              mysql 3버젼                                                 mysql 4버젼
    #mysql                       =============> mysql 실행
 
3) 접속해서 암호 설정
----------------------------------------------------------------------------------------
mysql>use mysql;

mysql>update user set password=password('새암호') where user='root';  <-- 새암호로 변경

mysql> FLUSH PRIVILEGES; <-- 시스템에 반영합니다

mysql> exit 
-----------------------------------------------------------------------------------------
 
4) mysqld 관련 프로세스 모두 종료 시킨다.
 
5) safe_mysqld &       ============> 정상실행 mysql
    4버젼에서는  mysqld_safe &     





Forgot your MySQL root user password? Don’t worry. We are here for rescue.

When you tried to login to root without entering a password, you may get ‘Access Denied’ message, as MySQL is expecting a password.

This article explains how to recover mysql root password by setting a new MySQL password when you don’t remember your old one.

When you don’t remember root password and enter a wrong password, you will get the following MySQL error message.

# mysql -u root mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

1.  Stop MySQL Server

As a first step, stop the mysql server using one of the following method.

# service mysql stop (or) # /etc/rc.d/init.d/mysql stop

2. Add –skip-grant-tables to mysqld_safe Startup Command

Open the mysql startup script and add -skip-grant-tables as shown below.


# vi /etc/rc.d/init.d/mysql

Old Line: $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

New Line: $bindir/mysqld_safe --skip-grant-tables --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

3. Start MySQL Server With –skip-grant-tables

Start the mysql server with skip-grant-tables option, which will allow anybody to login to mysql without entering a password.

# service mysql start Starting MySQL.                                            [  OK  ] [Note: This is using the updated /etc/rc.d/init.d/mysql script]

4. Login Using MySQL Root User Without Entering Password

Since you’ve skipped the grant table, this time when you try to login to mysql, it will not ask for password.

# 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 Server version: 5.1.25-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> [Note: MySQL did not ask for any password]

5. Set MySQL Root Password to a New Password Using UPDATE Command

Follow the strong password rules while setting new password for the mysql root account.

mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='root'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>

6. Stop the MySQL Server

Stop the mysql server using one of the following method.

# service mysql stop

(or)

# /etc/rc.d/init.d/mysql stop

7. Update /etc/rc.d/init.d/mysql Startup Script and Remove –skip-grant-table

Open the mysql startup script and remove –skip-grant-tables as shown below.

# vi /etc/rc.d/init.d/mysql Old Line: $bindir/mysqld_safe --skip-grant-tables --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 & New Line: $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

8. Start MySQL Server

Start the mysql server without the skip-grant-tables option. This time mysql will ask for password when someone tries to login.

# service mysql start Starting MySQL.                                            [  OK  ]

9. Login to MySQL With the New Password

Login to mysql root account with the new password.

# mysql -u root -pnewpassword Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.25-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>