Resolved Issues with Plesk accessing MySQL admin account

[ScottGoddard]

Under certain circumstance I am having issues with the MySQL admin account and the problem is becoming more prevalent.

The easiest way to produce the problem is by running plesk db at the command line – which gives the following ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES) exit status 1

However, running mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa works fine – as does manually pasting the contents of /etc/psa/.psa.shadow.

Obviously, this problem is not present when use ‘skip-grant-tables’

Now, please believe me, I *KNOW* this is a common problem but I have tried every fix I can think of but still no resolution.

Things I have tried to resolve this:

• Unable to access Plesk on Linux after manually changing the MySQL "admin" password: Access denied for user 'admin'@'localhost' (using password: YES) (Automatic and manual solution)
• ERROR PleskMainDBException Access denied for user 'admin'@'localhost' (using password: YES) (even though this is for Windows, I have tried everything relevant)
• Resolved - Changing root/MySQL passwords (everything in this thread)
• Plus many other things as I thought of them or found them online

Current issues this causes:

Plesk currently runs fine for most things. Initially I noticed the error a couple of months ago when using the migration tool from another server to transfer a domain that was on this affected server. Everything else transferred OK but the database transfer failed with this message:

Failed to copy content of database 'xxxxxx'
Migration tools tried to perform operation in 3 attempts: Command execution failed on the source server 'source' (xxx.xxx.xxx.84) with non-zero exit code.
command: MYSQL_PWD="$(cat /etc/psa/.psa.shadow)" mysqldump --no-defaults -h localhost -P 3306 -uadmin --quick --quote-names --add-drop-table --default-character-set=utf8 --set-charset --routines --events xxxxxx> /root/plesk_migrator/plesk_migrator-xxxxxxxxxxxxxxxxxx/db-dumps/xxxxxx.sql
exit code: 2
stdout:
stderr: mysqldump: Got error: 1045: Access denied for user 'admin'@'localhost' (using password: YES) when trying to connect

This was the only problem I saw for a couple of months, but around 2 weeks ago I started to get the following warnings on every DB in an FTP backup:

​

Unable to make database dump. Error: Failed to exec mysqldump: mysqldump: Got error: 1045: Access denied for user 'admin'@'localhost' (using password: YES) when trying to connect

Server details:

OS: ‪CentOS 6.10 (Final)
Product: Plesk Obsidian
Version: 18.0.20 Update #1, last updated on Oct 31, 2019 03:38 AM
DB Server Version: 5.7.29-log - MySQL Community Server (GPL)

 

[Bitpalast]

Instructions in the first article mentioned are o.k. and must work. And obviously, Plesk can access the database, else the GUI would not be accessible. So the problem might be elsewhere. Maybe you are using some security software that blocks access on the console level for the "admin" user?

 

[ScottGoddard]

Instructions in the first article mentioned are o.k. and must work. And obviously, Plesk can access the database, else the GUI would not be accessible. So the problem might be elsewhere. Maybe you are using some security software that blocks access on the console level for the "admin" user?

Thanks for your help Peter. I am not running any security software that is not part of Plesk or the OS.

It's like Plesk is using 2 slightly different methods of storing, recalling or outputting the password.
Strangely, this method works fine:

mysql -uadmin -p`cat /etc/psa/.psa.shadow`

Whereas, this method does not:

MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin

The second one results in the same ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES) error, but both work fine on my other servers?

I feel this could be relevant but not sure how?

Note: This is quite an old server now, though it has been upgraded regularlyto keep it current

 

[ScottGoddard]

After some more digging and testing, as far as I can tell, the issue ONLY occurs when using the environmental variable MYSQL_PWD.

Any scripts or direct console commands using MYSQL_PWD e.g. MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin does NOT work!

ONLY those that set directly set -p`cat /etc/psa/.psa.shadow` are working and adding this to the above works fine.

Editing any scripts to set the password using -p instead of MYSQL_PWD fixes the issue – what I don’t know is why MYSQL_PWD is not working? If I set it and recall it using MYSQL_PWD=`cat /etc/psa/.psa.shadow` printenv MYSQL_PWD then it echoes the password out correctly. So, the variable itself is working and it seems that MySQL is ignoring it for some reason?

Any ideas? Anyone? Please…?

 

[IgorG]

BTW, The MySQL docs expressively say that using the MYSQL_PWD env variable is discouraged.

 

[ScottGoddard]

BTW, The MySQL docs expressively say that using the MYSQL_PWD env variable is discouraged.

I realise that but you may want to make sure the Plesk devs do!

MYSQL_PWD is used in Plesk Migrator (/usr/local/psa/admin/plib/modules/panel-migrator/backend/lib/python/parallels/core/utils/database_utils.py) causing it to fail unless I edit it to use -p`cat /etc/psa/.psa.shadow`

I am pretty sure it is also used as part of the Plesk FTP backup, as this fails in exactly the same way, but I have not managed to find this script yet. It is probably used in many other Plesk scripts too.

I am not too concerned about the fact that Plesk scripts are using MYSQL_PWD and certainly don't want to wait for them to be changed.

However, I do need to find out why this particular server will not allow the *PLESK* scripts to set the password using MYSQL_PWD, so I can get them working again without having to find and edit them.

 

[ScottGoddard]

Not that anyone seems interested, but here is an update.

It seems the reason MYSQL_PWD isn't working is due to the password already set at startup and therefore MYSQL_PWD cannot overwrite it. It is also setting the user to the non-existent root user.

# mysql --print-defaults
mysql would have been started with the following arguments:
--default-character-set=utf8 --user=root --password=***** --host=localhost

default-character-set is set in my.cnf but the user, password and host are NOT - nor can I find any other config file, anywhere else that sets these.

How and where are these being set?

I am probably missing something obvious but a little help would be greatly appreciated.

 

[ScottGoddard]

I finally figured out the problem and will leave the resolution here in case anyone has a similar issue.

Using the command strace mysql ";" 2>&1 | grep cnf to find which config files MySQL was loading, showed the following:

stat("/etc/my.cnf", {st_mode=S_IFREG|0644, st_size=670, ...}) = 0
open("/etc/my.cnf", O_RDONLY)           = 3
stat("/etc/mysql/my.cnf", 0x7ffc673fbc00) = -1 ENOENT (No such file or directory)
stat("/usr/etc/my.cnf", 0x7ffc673fbc00) = -1 ENOENT (No such file or directory)
stat("/root/.my.cnf", 0x7ffc673fbc00)   = -1 ENOENT (No such file or directory)
stat("/root/.mylogin.cnf", {st_mode=S_IFREG|0600, st_size=136, ...}) = 0
open("/root/.mylogin.cnf", O_RDONLY)    = 3
open("/usr/local/mysql/ssl/openssl.cnf", O_RDONLY) = -1 ENOENT (No such file or directory)

I knew there was noting relevant in /etc/my.cnf but I had not checked (because I had no idea what it was!) the contents of .mylogin.cnf. It is actually a binary file rather than a standard text config file and, after a little research, I found out that this is created by mysql_config_editor which can be used to set a user, password and host.

The contents if the file can be verified using mysql_config_editor print and once I had cleared it using mysql_config_editor reset all the problems were rectified.

A genuine thanks to everyone who took the time to look but were unable to help - and a less than sincere "thanks a lot" to the paid contributors/Plesk employees that could have helped but chose not to bother...