MySQL Change root Password

by Vivek Gite · 50 comments

How do I change MySQL root password under Linux, FreeBSD, OpenBSD and UNIX like operating system over ssh / telnet session?

Setting up mysql password is one of the essential tasks. By default root user is MySQL admin account. Please note that the Linux / UNIX login root account for your operating system and MySQL root are different. They are separate and nothing to do with each other (indeed some admin removes root account and setup admin as mysql super user).

mysqladmin command to change root password

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

$ mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use following command

$ mysqladmin -u root -p'oldpassword' password newpass

For example, If old password is abc, and set new password to 123456, enter:

$ mysqladmin -u root -p'abc' password '123456'

Change MySQL password for other user

To change a normal user password you need to type (let us assume you would like to change password for vivek):

$ mysqladmin -u vivek -p oldpassword password newpass

Changing MySQL root user password using MySQL sql command

This is another method. MySQL stores username and passwords in user table inside MySQL database. You can directly update password using the following method to update or change password for user vivek:

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

$ mysql -u root -p

2) Use mysql database (type command at mysql> prompt):

mysql> use mysql;

3) Change password for user vivek:

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='vivek';

4) Reload privileges:

mysql> flush privileges;
mysql> quit

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

1 Tabitha 06.05.07 at 3:34 pm: I got an error trying to use your method to change the root password using mysqladmin. I did some checking and found that the following method worked.

mysql> mysqladmin -u molly -p password NEWPASSWORD
(replacing NEWPASSWORD with the desired password, you will be prompted to enter the current password)
2 Samuel 06.06.07 at 10:44 am: I am using the latest MySQL, the codes above didn;t work for me, please if you have the correct code to use. I want to change the root password to null. Just like it came.

Thanks
3 Edd 06.18.07 at 11:18 am: computers can be sucha pain..!!

THANKS!
4 yesh 12.20.07 at 4:57 pm: Yessssss… same for me…
It will not work.
I use WAMPSERVER 2.0
I get following error in Mysql:

ERROR 1064 (42000): You have an error in you SQL syntax; check the manual that corresponds to your MySql server version for the right syntax to use near ‘$ mysql -u root -p
mysql> use mysql
mysql> update user set password=PASSWORD(“NE’ at line 1

Very annoying
5 K.Ramanathan 02.06.08 at 6:37 am: The information on your website has helped me. Thanks.

Best Wishes,
K.Ramanathan
6 Alexander Elo 03.17.08 at 11:49 pm: Thank you very much, this information was usefull!

- Alexander Elo
7 hardyboyz 04.25.08 at 5:35 pm: If I forgot my mysql password? how is it going?, because there is a step that need an old mysql password.
8 vivek 04.25.08 at 7:02 pm: Go here to recover mysql password.
9 ryan 05.14.08 at 2:33 am: Worked like a charm, thnx.
10 jessica 07.17.08 at 2:06 pm: thank you so much. i really needed this. ^^
11 Eutycus 08.12.08 at 6:40 am: mysqladmin -u root -p password “newPassword” {enter}

{prompt}
Enter password:”oldPassword”
12 Hendry Lee 09.08.08 at 1:30 am: To update mysql password for root, the command that works for me is:

mysqladmin -u root -poldpass password newpass
13 Daniel 09.10.08 at 2:41 pm: mysqladmin -u root -p password ”

this will remove your password
14 Bob 09.23.08 at 3:06 am: I found this quite frustrating. Not an ideal post about how to change the root pwd. Hate it.
15 vivek 11.14.08 at 5:37 am: brendon,

Whats wrong?
16 scott 11.26.08 at 7:56 pm: Have been coming here for years to cut and paste that syntax

mysqladmin -u root password NEWPASSWORD

works great, guys the “NEWPASSWORD” is where you put the new password

it is lazier for me to come here to grab it than learn it myself, i
17 Spam Script 11.26.08 at 8:55 pm: There are people out there that should not be using computers, as yesh, bob & brendon.
18 Bhong 12.02.08 at 11:45 pm: Thanks for sharing this very useful info.
19 MIke nutt 12.12.08 at 1:46 pm: HI! It worked for me changing password this way, but when I trying to flush privileges it says : ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t exist.
Whats wrong???
20 Vinod Rawat 01.04.09 at 1:00 pm: I was playing with WAMP tool. By default it had root password as empty. I wanted to provide a new password and not just leave it empty.
By this I will be giving simple steps which I learnt, where in one can make necessary changes for both mysql server and mysql client. Hopefully this will work perfect with any standalone mysql server and mysql client application. I hope this may save someones precious time.

WAMP Settings for MySQL
———————————-
By default WAMP does not set the mysql root password.
So phpmyadmin connects to internal mysqlServer with default username : root and empty[NO] password

If we want to have some form of secure database then root password should not be left empty.
How to provide the password so that mysql client connects to the server with some password?

The following tries to simplify the approach in WAMP Mysql setup.
Mysql Server End Changes for new Password
————————————————————–
Change the mysql server password as follows :
To setup root password for first time:
mysqladmin -u root password “NEWPASSWORD”
C:\wamp\bin\mysql\mysql5.1.30\bin> mysqladmin -u root password "root"

If password already exist and you are aware of it and want to change it then
1. Thru mysqladmin technique :
mysqladmin -u root -p password “MYNEWPASSWORD”
Enter password: OLDPASSWORD

C:\wamp\bin\mysql\mysql5.1.30\bin>mysqladmin -u root -p password “vinod”
Enter password: root

2. Alternatively, [Optional Step]
C:\wamp\bin\mysql\mysql5.1.30\bin>mysql -u root -p Enter password: root [OLD EXISTING ROOT PASSWORD]
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.30-community-log MySQL Community Server (GPL)

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

mysql> use mysql
Database changed

mysql> update user set password=PASSWORD("vinod") where User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> exit
Bye

This changes the root password.

Verify :

C:\wamp\bin\mysql\mysql5.1.30\bin>mysql -u root -p
Enter password: vinod
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.30-community-log MySQL Community Server (GPL)

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

mysql>exit
C:\wamp\bin\mysql\mysql5.1.30\bin>

————————————————————————————

Mysql Client End Changes for new Password
———————————————————
Change the mysql client password as follows :
Here phpmyadmin is a mysql client application which is connecting to mysql server.
Let us see what it requires to make changes to successfully connect with new password

1. Edit config.inc.php under C:\wamp\apps\phpmyadmin3.1.1\

Change
$cfg['Servers'][$i]['password'] = '';
to
$cfg['Servers'][$i]['password'] = 'vinod';

Thats it restart mysql service and http://localhost/phpmyadmin/ will let u login
21 Vivek Gite 01.04.09 at 1:04 pm: Thanks for sharing Windows specific information.
22 Jnanesh 01.06.09 at 11:03 am: Thanks Vivek.
It worked for me.
23 josh from america 01.13.09 at 11:06 pm: Windows is the festering bit of useless tripe that you hope does not appear on your porch, but if it does you kick it off and wash your shoe.

Keep it in joke computing world and off of serious informative boards please.
24 Unbiased 01.21.09 at 10:06 pm: Operating Systems are SRS BSNS.

But seriously, get over yourself, “Josh from America”, and stop posting such self-righteous fodder. No one cares what you think. This is supposed to be an area for assistance.
25 morg 01.29.09 at 9:40 am: when setting the root password for the first time, start a DOS terminal and run
C:\wamp\bin\mysql\mysql5.1.30\bin> mysqladmin -u root password “root”
like in vinod’s post. here the ‘mysqladmin’ is not a command, but an exe file!

yesh: do NOT use mysql console for this, because it does not recognise the command, and you keep getting new prompts and error messages.
first I made the same mistake. Thanks Vinod!
26 HappySpaceInvader 02.10.09 at 10:47 am: The problem with your examples, is that it is not clear which parts are literal and which parts are to be substituted.

I have been trying to get your example to work to change the existing root password, but in your example of: “mysqladmin -u root -p oldpassword newpass” are both of the last two arguments meant to be substituted or just the first one. And then, at the prompt, do I enter the old password, or the new one I want to create? It’s terribly unclear.
27 HappySpaceInvader 02.10.09 at 10:58 am: Here is a foolproof way of changing your root password without mucking around with prompts and error messages:

mysqladmin –user=root –password=’my_old_password’ password ‘my_new_password’
28 charu 02.16.09 at 11:23 am: My user name and password are not working while I am opening my ID. It says this is not a valid ID but i gave correct ID and password. I want my previous ID and password because this is my favourite ID
29 kathirvel 03.18.09 at 5:12 am: nice one helped me in time
30 Kenneth Gustine 03.27.09 at 6:45 am: To clear the root password I got a “too few arguments” with

mysqladmin -r root password

instead, mysqladmin -r root password “” ( MUST be double quotes )

I then entered the root password and this cleared it.
31 ThunderGod 03.30.09 at 10:12 pm: tnx it worked but i changed it a little i used this
mysqladmin -u root -poldpass password Newpass
32 Andy Towler 04.09.09 at 8:19 am: Thanks go to Vinod Rawat – a useful and easy to follow comment, resolved my issue in 2 minutes flat.
33 gorg 04.16.09 at 1:30 pm: Thank you very much for this information. I have been searching for hours on a correct technique for something as simple as changing the passwords of mysql users and most pages are just plain wrong with continuous comments complaining about failure. I hated being apart of that failure. I found your site and the solution was simple and correct. This has made me much more productive with my sites by not having to continuously change the username and password in the site code. Thank You!
34 Dan 05.13.09 at 7:28 pm: Hey thanks!, quite frankly, the windows tip got it done in a snap! o.O… who says open source is free… it’s only free if your time is worthless :o!
35 rahul 05.29.09 at 12:22 am: I login via SSH to my server, and then, into of my vps, write “mysql” and I can access (not need put root psswd mysql).

Can you help me?.
36 Ankur Kaushik 06.12.09 at 10:15 am: Excellent……………..:)
37 Sureshkumar 06.22.09 at 9:12 am: Superb !!!!!!!!!!!!!!!
38 john black 06.29.09 at 10:36 am: Thank you for your help.
mysqladmin -u root -p’oldpassword’ password newpass
Before successfully changing my password using your script I got single quotes around my new password (and a warning.) So I did like this:-
mysqladmin -u root -p”‘oldpassword’” password oldpassword
It worked! What a performance!
Linux seems easier than Windows XP.
39 khan 07.04.09 at 9:56 pm: The above command did not work for me, perhaps i am using latest version of mysql but the following did

C:\xampp\mysql\bin>mysqladmin -u root -p”1234″ password “”

1234 is old password. New password is blank. I tried different variation but it never worked. It did not ask for password confirmation this time and i was able to login using a blank password. Note that i used double quotes to wrap both old and new password. Hope it helped. I am using MySQL 5.1.29 which i believe is latest.
40 john black 07.05.09 at 11:58 am: I suggest that it is different for different versions and between Windows and Linux.
I suggest that you, Khan, have changed your password to “” which will be treated as no password.
What you did that worked was:
C:\xampp\mysql\bin>mysqladmin -u root -p”1234″ password “”
I suggest that it might have worked without the double quotes.
Now that I have got it working I really don’t want to try it and mess up my system.

Version 5 is the first version of Mysql that I have managed to make work.
I have installed many earlier versions and have always come unstuck about the password.
It really is a nightmare.
I am not a giant corporation with hackers trying to steal my credit card info using code injection.
I am just a home user who wants to learn Mysql.
41 sandeep 07.11.09 at 3:49 pm: I get an error for the flush privileges command – error 1146 (42s02): Table ‘mysql.servers’ doesn’t exist

Is reloading the privileges very important – or will this not affect at all?
42 yudymardianto 07.28.09 at 4:09 am: very Useful this article.
thanks.
43 jason 08.19.09 at 6:20 pm: 5.1.37
mysqladmin -u root -p”oldpass” password”newpass”

replace oldpass with your old password. replace newpass with your new password. leave the quotation marks “”.

thanks again for this post , esp thundergod.
44 Ste ve 08.21.09 at 4:20 pm: This just doesn’t work. I have version 5.0.84 on a windows server.
It doesn’t work without quotes, with quotes, double quotes or brackets…

I’ve never set a pasword, so I just try
$ mysqladmin -u root password NEWPASSWORD

And it doesn’t work. Please help.
45 edi 09.05.09 at 10:10 am: hello i am just use wamp but when i open phpmyadmin , i found this alert ,
“Your PHP MySQL library version 5.0.51a differs from your MySQL server version 5.1.32. This may cause unpredictable behavior.”

can anybody help me to fix this ?
46 john black 09.06.09 at 12:17 am: Hello edi,
I see that you are having problems too.
Welcome to the club.
“Your PHP MySQL library version 5.0.51a differs from your MySQL server version 5.1.32. This may cause unpredictable behavior.”
I have never used wamp precisely because of this sort of problem.
If all goes well wamp is a very easy way to install Apache, PHP and MySql.
But when something goes wrong as in your case it is very difficult to fix.
I think that your error message means exactly that.
Had you installed the three packages separately it would have been less trouble.
Before you uninstall wamp and install Apache, PHP and MySql separately,
I would try uninstalling MySql, uninstalling PHP and then installing PHP and then MySql.
You do need to download PHP and MySql separately.
But having said that you might be better off starting again
with a fresh install of Apache, PHP and MySql.
Each is dependent on the previous installation thus Apache can run on its own.
PHP depends on Apache. MySql depends on PHP.
You must work up to the full installation, uninstalling a part if it does not work.
Just to add a layer of complication, for some reason PHP no longer supports MySql
without a few extras. Some dispute between Sun Microsystems who support MySql
and PHP which is open source.
Your C:\PHP\ext folder needs to include a few extra dll files
which need to be enabled in your php.ini.
I could say more but it is well documented on the internet.
Yes it is a challenge. Good luck. It will work eventually.
47 Srikanth 09.15.09 at 6:57 am: Thanks a Lot :)
48 nicoliniyo 11.03.09 at 2:09 pm: This is really usefull!