Anyone who’s involved in the technical side of computing has experienced those days where you chase your tail attempting to solve a very simple problem. Welcome to my recent version of this exact day. Our primary actors here are Plesk Parallels 10, MySQL, Linux and a hapless developer (that would be me).
The Problem
The simple problem: Login to a MySQL server as admin, from a PHP script. I used this short bit of PHP code in a test page, put it on my website and ran it:
<!--?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) die('Could not connect: '. mysql_error());
echo 'Connected successfully';
mysql_close($link);
?-->
Result: Could not connect: ERROR 1045 (28000): Access denied for user ‘admin’@’localhost’ (using password: YES)
The Resolution Path
I tried all the obvious things like checking the password was correct but I still couldn’t connect. So, I opened an SSH terminal and tried using the command line to login. Again, very easy stuff:
mysql -u admin -p
and enter the password when prompted. Again, no joy. Well, the good news is that there’s no need for me to waste any more time with the PHP script. The problem is definitely with the username / password combination. I know that I have the password right because it’s set by Plesk to be the same as the password for Plesk.
Next, I try to access the database via phpMyAdmin via Plesk. It works fine. So I change the password in Plesk to something really simple, test phpMyAdmin again (it works fine) and then try to login via the command line again. It fails. Same error.
All the time, I am watching the content of the mysql.user table via phpMyAdmin and note that the password for the admin user does indeed change. Of course, the password in the db is hash encoded so I have no idea what the clear text value is. I only know that it is changing when I change the Plesk password.
So at this point, Plesk obviously knows something I don’t know so I search on the forums and find a knowledge base article telling me how to reset the password for Plesk and MySQL. Excellent! I follow the steps precisely, attempt to login to MySQL from the command line and… it fails again. I repeat the steps two more times. It still fails.
But then I noticed something really odd in the knowledge base article. One of the lines of code looks strange. Check out the line below and see if you can spot it:
mysql -uadmin -p`cat /etc/psa/.psa.shadow` -D psa
The answer to the question ultimately led me to the fix. What I worked out was that the kb instruction was pulling in a value stored in an external file (/etc/psa/.psa.shadow) for the password. I went to check this file and sure enough, all it contained was an encoded string. I copied this string and tried it verbatim as my password as I tried to login via the command line. Success! I then tried it via my PHP test script and again, success!
Summary
The password that you use for Plesk Parallels 10 is also used as the password for the admin user on the MySQL server.
However, the password is encoded by Plesk before being set as the the MySQL admin password, where MySQL then encodes it further before storing it in the database. The Plesk encoded version is written out, as the only entry, to a file. On Linux distributions, the file is /etc/psa/.psa.shadow file.
You can copy the value stored in that file to be used as the password when logging in to MySQL via the command line or via a PHP script.
