Tuesday, October 25, 2016

How do I recover MySQL root password?

Recover MySQL root password in 5 Steps

In the rush of things, we sometimes fail to remember some important information such as wife's birthday or your MySQL password. 

Here are the steps that you may use to reset you MySQL password. For the problem of remembering wife's birthday, you will have to find your own solution. There's a solution to improve your memory, but that post will come later.

This assumes that you already have access to the computer but have misplaced your MySQL admin password.

These are the steps I followed to to reset the MySQL database server password.
Login as superuser, as root.

1. Stop the MySQL server process.
# /etc/init.d/mysql stop

Output:
Stopping MySQL database server: mysqld.

2. Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for a password.

Start the MySQL server w/o password:
# mysqld_safe --skip-grant-tables &

Output:
[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

3. Connect to the MySQL server as the root user, using the MySQL client.

# mysql -u root

Output:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

4. Set a new root password.

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

Note: It is important that you give password("Your new root password") and not just the "NEW-PASSWORD", which is a common mistake that happens.

5. Exit and restart the MySQL server.

Stop the MySQL server:
# /etc/init.d/mysql stop

Output:
Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

Start the MySQL server and test it:
# /etc/init.d/mysql start
# mysql -u root -p

No comments:

Knee Jerk Governance ?!

A Country of RULES I do not know how many of you have noticed.  We are a country of RULES, RULES & more RULES. Probably the only...

Copyrighted.com Registered & Protected DWYE-NHTO-NBNH-7FFM