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>
No comments:
Post a Comment