[Q&A] Setting MySQL root password

Problems with the Windows version of XAMPP, questions, comments, and anything related.

[Q&A] Setting MySQL root password

Postby Altrea » 13. August 2015 22:32

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)
    Image

  2. login to MySQL DBMS with the following command
    Code: Select all
    mysql --user=root --password=

    Image
    (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');

    Image

  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
    Image

  3. click on the Edit Privileges link of the user root with host localhost
    Image

  4. click on the Change password link
    Image

  5. Activate the choice Password, type in and re-type the new password and click on Go
    Image

  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';

    Image

  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
Image
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: [Q&A] Setting MySQL root password

Postby RastamanD » 09. March 2016 03:48

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

Ras D
RastamanD
 
Posts: 2
Joined: 08. March 2016 22:11
Operating System: Windows 7

Re: [Q&A] Setting MySQL root password

Postby jan24 » 09. March 2016 03:56

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
 
Posts: 2
Joined: 09. March 2016 03:33
Operating System: Windows 7

Re: [Q&A] Setting MySQL root password

Postby jan24 » 09. March 2016 19:04

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
jan24
 
Posts: 2
Joined: 09. March 2016 03:33
Operating System: Windows 7

Re: [Q&A] Setting MySQL root password

Postby Altrea » 09. March 2016 20:19

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
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: [Q&A] Setting MySQL root password

Postby jaj_781 » 07. October 2016 21:02

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!
jaj_781
 
Posts: 2
Joined: 07. October 2016 20:31
XAMPP version: 5.6.24
Operating System: windows10

Re: [Q&A] Setting MySQL root password

Postby Altrea » 09. October 2016 08:12

jaj_781 wrote: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

jaj_781 wrote: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.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: [Q&A] Setting MySQL root password

Postby jaj_781 » 10. October 2016 01:32

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!!
jaj_781
 
Posts: 2
Joined: 07. October 2016 20:31
XAMPP version: 5.6.24
Operating System: windows10

Re: [Q&A] Setting MySQL root password

Postby waqasramzan41 » 18. October 2016 20:33

[img]00[/img]im face same problem i cant access phpadmin after set the password.. xampp version is v.3.2.2
waqasramzan41
 
Posts: 1
Joined: 18. October 2016 20:01
XAMPP version: v3.2.2
Operating System: windows 10

Re: [Q&A] Setting MySQL root password

Postby MicahBerhe » 19. October 2016 07:01

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
MicahBerhe
 
Posts: 1
Joined: 19. October 2016 06:55
XAMPP version: 5.6.23
Operating System: Windows 8

Re: [Q&A] Setting MySQL root password

Postby costarica » 26. October 2016 18:39

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?

Image
costarica
 
Posts: 2
Joined: 26. October 2016 18:33
XAMPP version: 3.2.2
Operating System: Windows vista

Re: [Q&A] Setting MySQL root password

Postby Altrea » 26. October 2016 20:10

costarica wrote: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?

Image

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.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: [Q&A] Setting MySQL root password

Postby costarica » 26. October 2016 20:30

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.
costarica
 
Posts: 2
Joined: 26. October 2016 18:33
XAMPP version: 3.2.2
Operating System: Windows vista

Re: [Q&A] Setting MySQL root password

Postby Altrea » 26. October 2016 20:37

costarica wrote: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

costarica wrote: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.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: [Q&A] Setting MySQL root password

Postby longintooth » 02. January 2017 21:43

Altrea wrote: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 :)
longintooth
 
Posts: 1
Joined: 02. January 2017 20:27
XAMPP version: 5.6.28
Operating System: Windows 7 Home Premium

Next

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 129 guests