[Q&A] Setting MySQL root password

[Altrea]

What can you expect from this thread?

The old XAMPP security functions to set the mysql root password are no longer maintained by the XAMPP developers. This code was old and buggy and has screwed up several dozen xampp installations.
This thread explains in detail how to set the root password with help of the very well maintained MySQL CLI and phpMyAdmin.


How to set a MySQL root password successfully

0. Requirements

To change any password you need to start MySQL first.
If you want to change the password with phpMyAdmin Apache needs to be started too.


1a. Setting root password with MySQL CLI

1. Open the XAMPP control panel and click the Shell button to start the XAMPP shell.
   (You may get asked if XAMPP should create the shell file because it is not existent. Accect this)
   
2. login to MySQL DBMS with the following command

   Code: Select all

   mysql --user=root --password=

   
   (the string after password= is empty because by default user root@localhost does not have any password)
3. Set the new password with the following command

   Code: Select all

   SET PASSWORD = PASSWORD('put your new password here');

   
4. If you have done everything correct you will get this output

   Code: Select all

   Query OK, 0 rows affected (0.00 sec)

1b. Setting root password with phpMyAdmin

1. Open your browser and request http://localhost/phpmyadmin/
   (You will get logged in automatically)
2. click on the Users tab
   
3. click on the Edit Privileges link of the user root with host localhost
   
4. click on the Change password link
   
5. Activate the choice Password, type in and re-type the new password and click on Go
   
6. If the password could be set successful you will see a message like this

   Code: Select all

   The password for 'root'@'localhost' was changed successfully.

2. Let phpMyAdmin know the changes

1. Open (in plain text editor of your choice) the file \xampp\phpMyAdmin\config.inc.php
2. search for the line

   Code: Select all

   $cfg['Servers'][$i]['auth_type'] = 'config';

3. Change this line to

   Code: Select all

   $cfg['Servers'][$i]['auth_type'] = 'cookie';

   
4. save the file

Login with phpMyAdmin

If you now access phpmyadmin you will get a login form where you can use the new credentials

[RastamanD]

Thanks man info was bang on, help me out just in time, keep up the good works

Ras D

[jan24]

Well, thank god I have found this post. I have just spent the past 36 hours downloading, activating and removing XAMPP from my computer - every time I put a password into the user accounts I got a message "Access Denied", and could find no way to get back in. So, in total I have set it up and discarded it 4 times already.
Everything I found online is out of date for the version I am using. Please, could you guys put something into the user documentation online, where the downloads are available (including apache friends page), so that we new users get a break here? I still haven't tried this out yet, but it looks like it may be an updated solution, so I'll give it a try tomorrow. Stay tuned..... Thanks!

[jan24]

Good morning, Apache friends. Further to my post last evening, I went through the steps recommended for setting the MySQL password, got to the point where the DBMS said, Query ok, but when I tried to access local host, to update password in myphpadmin, again, it said, Access Denied!
Can someone please help me here?! I am using version 3.2.2.
Thank you, I will await your reply.
Jan

[Altrea]

Did you follow the steps 2.I - 2.IV?
You don't need to change your password twice. Just follow either 1a or 1b and then follow the steps of 2

[jaj_781]

I made two attempts to login into myphpadmin...to no avail:

In Admin box wrote:
mysql --user =root --password=

got "error 1045: Access denied"

And also in Admin box wrote:
UPDATE mysql.user SET Password=PASSWORD ('password') WHERE User='root'; FLUSH PRIVILEGES

got "'UPDATE' is not recognized

Any suggestions? Thanks so much!

[Altrea]

I made two attempts to login into myphpadmin...to no avail:

In Admin box wrote:
mysql --user =root --password=

got "error 1045: Access denied"

There is no whitespace allowed in --user =root

And also in Admin box wrote:
UPDATE mysql.user SET Password=PASSWORD ('password') WHERE User='root'; FLUSH PRIVILEGES

got "'UPDATE' is not recognized

If you are not logged in you are not in the right scope to place any SQL Statements.

[jaj_781]

Altrea,
I am able to get a prompt back asking for my password. I have 'xxxx' in my config.inc file inside phpmyadmn, but that did not work. I don't know what else to enter to get into mysql to change or update my password. I am running into the same problem trying to log into phmyadmn page. I enter root for user but am clueless what to write for password. Please help. Thanks!!

[waqasramzan41]

[img]00[/img]im face same problem i cant access phpadmin after set the password.. xampp version is v.3.2.2

[MicahBerhe]

In 2012, I faced a same problem and this post solved the issue.

http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

[costarica]

very good info moderator, if not that forum, i would not know what to do, how to access security page to set up passwords.

Can you tell me from this screenshot from the old version, what of these 3 points does this your post manual protects?

[Altrea]

very good info moderator, if not that forum, i would not know what to do, how to access security page to set up passwords.

Can you tell me from this screenshot from the old version, what of these 3 points does this your post manual protects?

Very good question.
security issue 1 is not present in current XAMPP versions because there are no setting pages which can be made accessible. And phpmyadmin is already permitted for localhost requests only.

Security issue 2 would be solved if you do the first step of this guide.
Security issue 3 would be solved if you do the second step of this guide.


phpmyadmin is only accessible from localhost, root can only login from localhost. So this database user can only get used from scripts on your very own machine and phpmyadmin can only get used from your very own machine. This is already very secure. How much security is really needed on a local test and development environment?
IMHO there is no need for setting a root password at all.

[costarica]

ok, good. no i do not need security at all. SO if my locacl lan network has only my pc, i do not need this security, cos worldwide no can access nor mysqli, nor phpmyadmin login panel, correct?

Security issue 2 - so even i do no need to set up password for this step , i.e. mysql admin root password with all these privileges ticketed on. correct?

I also want to notice to everyone, till i did not change $cfg['Servers'][$i]['auth_type'] = 'config'; config for cookies i could not even start ywebsite/phpmyadmin page.

moderator, just reply for 2 questions yes or no. thanks.

[Altrea]

ok, good. no i do not need security at all. SO if my locacl lan network has only my pc, i do not need this security, cos worldwide no can access nor mysqli, nor phpmyadmin login panel, correct?

yes

Security issue 2 - so even i do no need to set up password for this step , i.e. mysql admin root password with all these privileges ticketed on. correct?

yes

As long you use XAMPP in the way it is designed for (as local test and development environment) without making it public accessible everything is fine already by the default configuration of XAMPP.

[longintooth]

IMHO there is no need for setting a root password at all.

OK, that's very important information for newcomers, because phpMyAdmin puts up a big red warning message if you leave the root password undefined. So I went ahead and changed it (through the Admin button in XAMPP Control Panel) and locked myself out of phpMyAdmin! It's lucky I found this post, with your instructions about config.inc.php, or I would be tearing XAMPP down and reinstalling it.

So, thanks