Monday, April 21, 2014

Reset MySQL root's password

If you just forgot your MySQL root's password like me yesterday, you can reset it. Don't panic.

Notes: For this method to work, you need to have root access to your server.

1. Stop the MySQL server:

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

2. Start MySQL server without requiring any privileges (aka. password) with mysqld_safe:

# mysqld_safe --skip-grant-tables &

3. Connect to MySQL server:

# mysql -u root

4. Reset root password:

mysql > use mysql;
mysql > update user set password=PASSWORD("MY-NEW-ROOT-PASSWORD") where user='root';
mysql > flush privileges;
mysql > quit

5. Stop and start MySQL server:

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

6. Try to connect to MySQL server with the new root password:

# mysql -u root -p'MY-NEW-ROOT-PASSWORD'