How To Change MySQL Root Password

by Ramesh Natarajan on July 17, 2009

Question: How do I change MySQL root Password?

Answer: You can change MySQL root password using one of the following 3 methods.

These methods can be used on both Windows and Unix Environment including Ubuntu, Debian, CentOS, Fedora, RedHat, Arch Linux, SUSE etc.,

Method 1. How to Change MySQL Root Password Using mysqladmin Command?

You can change the mySQL root password using mysqladmin command as shown below. Please note that there is no space between -p and currentpassword.

# mysqladmin -u root -pcurrentpassword password 'newpassword'

Once you’ve changed it make sure you can login with your new password successfully as shown below.

# mysql -u root -pnewpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
mysql>

Method 2. How to Change MySQL Root Password From MySQL Prompt Using UPDATE SQL Command?

You can also use the standard update SQL command combined with the MySQL password function to change the password as shown below.

Login to MySQL root account using old password

# mysql -u root -poldpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
mysql>

Use the UPDATE Command to change root password.

mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verify the new MySQL root password

Once you’ve changed it make sure you can login with your new password successfully as shown below.

# mysql -u root -pnewpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
mysql>

Method 3. How to Set MySQL Root Password Using mysqladmin Command?

This method works only if there is no password currently assigned for the root account. If you don’t have any password assigned to the root account, set the password without giving current password as shown below.

# mysqladmin -u root password 'newpassword'
[Note: There is no currentpassword for root in this example]

How to Change MySQL Regular User (non-root) Password Using mysqladmin Command?

You can use the same mysqladmin command to change password for a mySQL end-user account as shown below.

# mysqladmin -u jsmith -pcurrentpassword password 'newpassword'

How to Change MySQL Regular User (non-root) Password From MySQL Prompt Using UPDATE SQL Command?

You can also use the standard update SQL command combined with the MySQL password function to change the password of a non-root user as shown below.

mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='ramesh';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 11 comments… read them below or add one }

1 Andrey July 17, 2009 at 8:04 am

What if you do not know the current password? Create a fresh MySql installation. Create some database. Copy everything from the mysql directory of the problem database to the directory of the new database. Change everything as needed and copy back to the mysql directory. And do not forget to stop the servers before copying files.

2 Ramesh Natarajan July 20, 2009 at 12:31 am

@Andrey,

Thanks for the suggestion on what to do if someone do not know the current password. There is a better way to rest the forgotten root password on MySQL, which will be posted in the next article this Wed.

3 existing May 24, 2011 at 5:47 pm

When I run this query:
“mysql> UPDATE user SET password=PASSWORD(‘newpassword’) WHERE user=’root’;”

I get this result:

“Query OK, 0 row affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0″

It recognizes and updates if I target a different user, but not with “root”. Any ideas what is going on?

4 muhammed sekertekin October 7, 2011 at 9:30 pm

Wow your the best thank very nice and informative article this made my day…. muhammed sekertekin

5 prasad January 3, 2012 at 4:24 am

HI ,
I FORGOT OLD PASSWORD AND NEED TO SET NEW ONE PLS. HELP

6 Lionel April 7, 2012 at 10:19 am

Your post helped me to set up phpmyadmin on localhost which was starting to confuse me because I could not login to my phpmyadmin when my root password is empty.

7 dhanesh mane October 16, 2012 at 12:21 am

I used this, works well. thx bro.

UPDATE user SET password=PASSWORD(‘newpassword’) WHERE user=’root’;

8 Jnr December 11, 2012 at 2:24 am

Hi, when I use the update command line
UPDATE user SET password=PASSWORD(‘newpassword’) WHERE user=’root’;
I get the ERROR 1046 No database selected
I am using mysql 5.5 command line client

9 vijay April 6, 2013 at 8:19 pm

I am also getting “No Database selected Error”

10 Frolic July 9, 2013 at 3:43 am

When you modify the “user” table directly with “UPDATE”, don’t forget to “FLUSH PRIVILEGES;” as the last step. Otherwise the changes may not come into effect.

11 Sasha April 3, 2014 at 7:00 pm

As usual, the Geek Stuff pulls through. Thanks!

Leave a Comment

Previous post:

Next post: