Setting, Changing And Resetting MySQL Root Passwords

Want to support HowtoForge? Become a subscriber!
 
Submitted by SamTzu (Contact Author) (Forums) on Fri, 2009-01-16 18:16. :: MySQL

Setting, Changing And Resetting MySQL Root Passwords

This tutorial explains how you can set, change and reset (if you've forgotten the password) MySQL root passwords. Time and again I see problems like mysqladmin:  connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)'. So I thought it's time to remind you how to solve MySQL related password problems. If you are just looking for a quick fix how to reset a MySQL root password you can find that at the bottom of this tutorial.

 

mysqladmin Command To Change Root Password

Method 1 - Set up root password for the first time

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To set up a root password for the first time, use the mysqladmin command at the shell prompt as follows:

$ mysqladmin -u root password newpass

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

$ mysqladmin -u root -p oldpassword newpass

Enter password:

If you get...

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

then follow the instructions below on how to recover your MySQL password.

 

Change MySQL password for other users

To change a normal user password you need to type:

$ 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:

$ mysql -u root -p

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

mysql> use mysql;

3) Change password for a user:

mysql> update user set password=PASSWORD("newpass") where User='ENTER-USER-NAME-HERE';

4) Reload privileges:

mysql> flush privileges;
mysql> quit

This method you need to use while using PHP or Perl scripting.

 

Recover MySQL root password

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

Step # 1: Stop the MySQL server process.

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

Step # 3: Connect to the MySQL server as the root user.

Step # 4: Set a new root password.

Step # 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:

# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld.

Step # 2: 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

Step # 3: Connect to the MySQL server 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>

Step # 4: Set a new MySQL root user password:

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

Step # 5: 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


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Recover MySQL r... (not registered) on Sat, 2013-04-27 13:24.
Just to know: in debian you can use the debian-sys-maint user to recover the password.
Submitted by Anonymous (not registered) on Thu, 2011-06-16 11:14.
Really good!! Thanks
Submitted by Naeem Rehman (not registered) on Thu, 2011-03-17 08:25.

Thanks for this article, its really works for me.

 Thanks again.

Submitted by Anonymous (not registered) on Thu, 2010-01-14 03:15.

Thank you very much for this!

 One thing to change if you do not have mysql setup to run as root is the "# mysqld_safe --skip-grant-tables &"  should have a -user=mysql in it.

 

 For me, my mistake was changing the security rights of the root user away from localhost.  So I did this trick listed to get mysqld running, then used phpMyAdmin to fix it.

Submitted by SamTzu (not registered) on Wed, 2009-01-21 16:12.

Thx 4 the feedback. I forgot to mention that the most common reason for this error is an empty password. You have to specify the root password while connecting to the Mysql database. mysqladmin -u root -pROOT-PASSWORD

Sam

Submitted by Shlomi Noach (not registered) on Fri, 2009-01-16 20:10.

Hi,

Sorry for double posting.

With regard to recovering root password, please see my post: Dangers of skip-grant-tables

 The post purposes that using an init-file is faster (requires but one restart instead of two) and safer.

Shlomi

Submitted by Tom Krouper (not registered) on Fri, 2009-01-16 20:07.

A safer way to do this would be to add the "init-file=/tmp/grant.sql" to the [mysqld] section of your my.cnf file. In /tmp/grant.sql you would have...

 SET PASSWORD FOR root@localhost = PASSWORD('xxxx');

or whatever SQL that would fix the root user.

Restart MySQL and the password is reset. You can then remove /tmp/grant.sql and remove the init-file line from your configuration.

 Using the skip-grant-tables exposed your entire database to anyone or any service that has access to your mysql instance. If you make sure that the grant.sql file only is readable by the mysql daemon user, you will have less exposure.

 One more thing... instead of going to the mysql db to change the password for a user, you can just type the following at the mysql prompt:

SET PASSWORD FOR name@host = PASSWORD('xxxx');

I believe FLUSH PRIVILEGES is optional here, but I always run it out of habit.

Submitted by Shlomi Noach (not registered) on Fri, 2009-01-16 19:45.

Hi,

Nice post,

 The proper way to change passwords is using the "SET PASSWORD" syntax, like:

SET PASSWORD FOR 'someuser'@'somehost' = PASSWORD('newpassword');

 Directly manipulating the mysql.users table is less preferred, since you may accidently inject typos.

See: http://dev.mysql.com/doc/refman/5.0/en/set-password.html

Regards,

Shlomi

Submitted by Giuseppe Maxia (not registered) on Fri, 2009-01-16 19:11.

Hi.

Your method for updating passwords is not the easiest nor the recommended one.

To change the password for the current user, simply use

SET PASSWORD=password('new_password');  # no FLUSH PRIVILEGES needed

To set the password for another user (as root), use

SET PASSWORD for user_name = password('new_password')

Best regards

Giuseppe