Page 1 of 1

[Q&A] Setting MySQL root password

PostPosted: 13. August 2015 22:32
by 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)
    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

Re: [Q&A] Setting MySQL root password

PostPosted: 09. March 2016 03:48
by RastamanD
Thanks man info was bang on, help me out just in time, keep up the good works

Ras D

Re: [Q&A] Setting MySQL root password

PostPosted: 09. March 2016 03:56
by 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!

Re: [Q&A] Setting MySQL root password

PostPosted: 09. March 2016 19:04
by 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

Re: [Q&A] Setting MySQL root password

PostPosted: 09. March 2016 20:19
by 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

Re: [Q&A] Setting MySQL root password

PostPosted: 07. October 2016 21:02
by 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!

Re: [Q&A] Setting MySQL root password

PostPosted: 09. October 2016 08:12
by Altrea
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.

Re: [Q&A] Setting MySQL root password

PostPosted: 10. October 2016 01:32
by 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!!

Re: [Q&A] Setting MySQL root password

PostPosted: 18. October 2016 20:33
by waqasramzan41
[img]00[/img]im face same problem i cant access phpadmin after set the password.. xampp version is v.3.2.2

Re: [Q&A] Setting MySQL root password

PostPosted: 19. October 2016 07:01
by 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

Re: [Q&A] Setting MySQL root password

PostPosted: 26. October 2016 18:39
by 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?

Image

Re: [Q&A] Setting MySQL root password

PostPosted: 26. October 2016 20:10
by Altrea
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.

Re: [Q&A] Setting MySQL root password

PostPosted: 26. October 2016 20:30
by 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.

Re: [Q&A] Setting MySQL root password

PostPosted: 26. October 2016 20:37
by Altrea
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.

Re: [Q&A] Setting MySQL root password

PostPosted: 02. January 2017 21:43
by longintooth
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 :)