Resetting MySQL ROOT password

Setting, Changing And Resetting MySQL Root Passwords

I have faced this problem myself when I was transitioned an application which was built years back and nobody actually knew the passwords for MySQL root account / other service accounts for the Application. This inspired me create a short tutorial on how to change a MySQL password when you know the password and in case you don’t know the existing password.

This blog explains how you can set, change and reset (if you’ve forgotten the password) MySQL root passwords. If you are just looking for how to reset a MySQL root password you can jump to the bottom l.

mysqladmin way of Changing Root Password

If you haven’t set a password for MySQL root account, the MYSQL Server doesn’t ask for a password when connecting as root. The mysqladmin command can be used to change the password as follows:

[code language=”bash”]
$ mysqladmin -u root password newpass
[/code]

If you want to change (or update) a root password, then you need to use the following command:

[code language=”bash”]
$ mysqladmin -u root -p oldpassword newpass
Enter password:
[/code]

If you get…
[code language=”bash”]
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’ (using password: YES)’
[/code]

then read the instructions in section below “recover your MySQL password”.

 

Change MySQL password for other users

To change a normal user password you need to type:

[code language=”bash”]
$ mysqladmin -u user-name -p oldpassword newpass
[/code]

Method 2 – Update or change password

MySQL stores usernames and passwords in the user table inside the ‘mysql’ database. You can directly update a password using the following method to update or change passwords:

1) Login to the MySQL server, type the following command at the shell prompt:

[code language=”bash”]
$ mysql -u root -p
[/code]

2) Use the mysql database (type commands at the mysql> prompt):

[code language=”sql”]
mysql> use mysql;
[/code]

3) Change password for a user:

[code language=”sql”]
mysql> update user set password=PASSWORD("yourpassword")
> where User=’yourusername’;
[/code]

4) Reload privileges:

[code language=”sql”]
mysql> flush privileges;
mysql> quit
[/code]

Recover MySQL root password

You can recover a MySQL database server password with the following five easy steps:

  1. Stop the MySQL server process.
  2. Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for a password.
  3. Connect to the MySQL server as the root user.
  4. Set a new root password.
  5. Exit and restart the MySQL server.

Here are the commands you need to type for each step (log in as the root user):

Step # 1 : Stop the MySQL service:

[code language=”bash”]
$ sudo service mysqld stop
Stopping MySQL database server: mysqld.
[/code]

Step # 2: Start the MySQL server w/o password:

[code language=”bash”]
$ sudo mysqld_safe –skip-grant-tables &
[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[7381]: started
[/code]

Step # 3: Connect to the MySQL server using the MySQL client:

[code language=”bash”]
$ sudo mysql -u root
  Welcome to the MySQL monitor. Commands end with ; or \g.
  Your MySQL connection id is X to server version: 5.0.95
  mysql>
[/code]
Step # 4: Set a new MySQL root user password:

[code language=”sql”]
mysql> use mysql;
mysql> update user set password=PASSWORD("yournewpassword")
> where User=’root’;
mysql> flush privileges;
mysql> quit
[/code]

Step # 5: Stop the MySQL server:
[code language=”bash”]
$ sudo service mysqld stop
  Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld5.pid
  mysqld_safe[7381]: ended
  [1]+ Done mysqld_safe –skip-grant-tables
[/code]

Start the MySQL server and test it:
[code language=”bash”]
$ sudo service mysqld start
$ mysql -u root -p
[/code]

Happy Reading !