phpMyAdmin Root Password

WAMPSERVERicon-200Under the category of been there and done that, I present everyone with a problem that needs fixing in phpMyAdmin, and how to get around it.  I use WampServer on Windows to do my local web development.  I've been using it for years and know most of its quirks, and the undocumented, how to set up Virtual Hosts to show in the WampServer start screen, see my article on: Multiple Virtual Hosts in WampServer .

One of the nice things about WampServer is it installs several applications with the installation of WampServer that work well together, that includes: SQLBuddy, webGrind, and phpMyAdmin.  WebGrind is a Xdebug web profiling tool, SQLBuddy is a database editing tool similar to phpMyAdmin.  When WampServer first included SQLBuddy, I for the life of me couldn't figure out why you needed two database editing tools with WampServer.  WampServer played it down by only including the tool on the web start page, not in the task bar sub-menu, which starts everything in WampServer.  I'm here to tell you, I'm glad WampServer included SQLBuddy, but first let's talk about the phpMyAdmin problem.

When you first start up phpMyAdmin through the WampServer menu, you're presented with a reddish background error message that tells you that your root password does not have a password, and that is a security hole.  Now if I'm on a server, using phpMyAdmin, then I agree this is a big no-no, which would let anyone gain access and modify your server database, but since WampServer is only running locally on my computer, this is not that big a deal, but phpMyAdmin will continually give you the error message, which gets annoying, and thus becomes a "nag screen."

What do you do with "nag screens?"  You make them go away.  In this case, you open a  database in phpMyAdmin, click on the "Privileges" tab, edit the root user, and put a password in, save it, and your good to go. WRONG!  Problem Alert! Instantly, you're not granted access to phpMyAdmin.  Well, you just put in a password, you think, since you know the password, you can just put in the root username and the password and your all set.  WRONG, there is no login screen in phpMyAdmin.  PhpMyAdmin folks, please fix this!

The first time I did this, it was early on in my WampServer use on that particular computer, I had not made a lot of configuration changes, so I just re-installed WampServer to fix the problem.  Quick, and relatively easy, but what happens when your cooking several websites, have web applications hooked to databases, and have configured several Apache, PHP, MySQL, and phpMyAdmin configuration files.

How do you get to your database visibility back, you're dead in the water with all your local sites,  you fix the problem.  There are several solutions, but none easy.

1. You could bring up a terminal window, enter the SQL command line, and change the root user password, back to no password.  Most folks don't want to spend the time to learn how to do this.

2. You could save all the configuration files you changed under the Wamp directory in another directory, re-install WampServer, and then copy all the config files back.  Of course, if you miss any application configuration files, you could be locked out of the applications database until you re-edit the configuration file.  This seems simple, but overwriting all your Wamp files makes me cringe a little when I am in the middle of a mess of web sites under development.

Enter SQLBuddy, go to your localhost start page, scroll to the bottom of the page and click on "sqlbuddy."  Low and behold, you're presented with a login screen.  Thank you WampServer for including SQLBuddy!  Put in the "root" username and the new password you just created for root, and wahlah, you're into your databases.

SQLBuddy is laid out differently then phpMyAdmin,  To get to where you change the passwords, along the left side menus, at the top, you'll see "Home" and then "Users."  Click on "Users" and your presented with the password change page, check the checkbox for localhost root and click "Edit" at the top menu bar.  You don't want a password, leave it blank, make sure you maintain all you database privileges and click "Submit."  You've just changed your password back to no password, and phpMyAdmin will work, but your back to the red background nag screens.  What to do?

SQLBuddy Users Screen

SQLBuddy Users Screen


Believe it or not, you can have your cake and eat it too.

If you haven't already, I recommend you create another admin user with all the privileges.  This username and password should be in all your web applications as an admin, which means it will be a user name in all your web databases.  I will assume you do this anyway for ease in development.  Now, go to any database in phpMyAdmin, click on the Privileges top tap in the window, and then half way down click on "Add user."  Put in your pet name and password, scroll down, check the Grant all privileges radio button, and "check all" for "Global Privileges,"  leave the Resource Limits at "0" which means unlimited, click "Add User".  This gives you admin rights across all databases and this is how we'll log in to your databases as an admin.

Now, change your root password again, which generates the no access error.  You can make these configuration changes without changing the "root" password in phpMyAdmin, and causing the shutdown error, but I did it this way so I know I got the below configuration correct, and everything is working properly.  Especially, now that you know that you can get back to your databases with SQLBuddy.

What you need to do is change the phpMyAdmin configuration file to the new user and password.  Let's do that.

Go to Wamp/apps/phpmyadmin3.5.1, or your version, and bring up in your editor.  Before you do anything else, make a copy of the file before you edit anything, this is always a good practice.  You don't know how many times this practice has saved me a lot of work in trying to get back to an original setting.

Here's part of the file contents:

$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['AllowNoPassword'] = true;
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['user'] = 'your new user name here';
$cfg['Servers'][$i]['password'] = 'your new password here';

Change the user and password to the new user you just set up, and save the file.  Restart WampServer to load the new configuration settings.  Also, clear your browser cache, so you don't get the old error page coming up at that URL.  PhpMyAdmin should start up just like it did before we messed with the root password, and you no longer have your error "nag screen."  A word, I tried to do the above edit with the "root" user and no password, and with the "root" user and a password, and that didn't work.  A new user name and password seems to do the trick.

Comments are closed.