Setting, Changing And Resetting MySQL Root Passwords

I have faced this problemmyself when I was transitioned an applicationwhich was built years back and nobody actually knew the passwords for MySQL root account / other serviceaccounts for the Application. This inspired me create a short tutorial on how to changea MySQL password when you know the password and in case you don't know the existing password.

This blogexplains 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 fora password when connectingas root. The mysqladmin command can be used to change the password as follows:

bash $ mysqladmin -u root password newpass

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

bash $ mysqladmin -u root -p oldpassword newpass Enter password:

If you get... bash mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)'

then readthe instructions in section below "recover your MySQL password".

Change MySQL password for other users

To change a normal user password you need to type:

bash $ mysqladmin -u user-name -p oldpassword newpass

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:

bash $ mysql -u root -p

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

sql mysql> use mysql;

3) Change password for a user:

sql mysql> update user set password=PASSWORD("yourpassword") > where User='yourusername';

4) Reload privileges:

sql mysql> flush privileges; mysql> quit

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:

bash $ sudo servicemysqld stop Stopping MySQL database server: mysqld.

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

bash $ sudo mysqld\_safe --skip-grant-tables & \[1\] 5988 Starting mysqld daemon with databases from /var/lib/mysql mysqld\_safe\[7381\]: started

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

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> Step # 4: Set a new MySQL root user password:

sql mysql> use mysql; mysql> update user set password=PASSWORD("yournewpassword") > where User='root'; mysql> flush privileges; mysql> quit

Step # 5: Stop the MySQL server: bash $ sudo servicemysqld stop Stopping MySQL database server: mysqld STOPPING server from pid file /var/run/mysqld/ mysqld\_safe\[7381\]: ended \[1\]+ Done mysqld\_safe --skip-grant-tables

Start the MySQL server and test it: bash $ sudo servicemysqld start $ mysql -u root -p

Happy Reading !