Question How to reset mysql admin password ?

[AdrianC]

On Plesk 17.5.3 CentOS 7 1705170317.16

I imported some external mysql dump without realizing it also contained queries that altered mysql configurations (users, settings, etc).

When I login to plesk web interface I get this:

ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [1045] Access denied for user 'admin'@'localhost' (using password: YES)

Additionally, an exception has occurred while trying to report this error: Zend_Exception
No entry is registered for key 'translate' (Abstract.php:144)

Search for related Knowledge Base articles

Mysql log says

170803 21:24:05 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE' 
[...]
[ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
170803 21:24:05 [Note] /usr/libexec/mysqld: ready for connections.

I assume the queries did some damage and I would like at least to reset mysql password so that I can login and export my databases to move to a fresh plesk instalation. I have around 20 databases on this server.

How can I reset the admin password for mysql ? I tried many commands from plesk articles but most of them somehow assume I am already logged in as admin in mysql, e.g

plesk bin database --update-dbuser
SQLSTATE[HY000] [1045] Access denied for user 'admin'@'localhost' (using password: YES)
exit status 3

 

[UFHH01]

Hi AdrianC,

as you don't provide informations, WHAT you did and WHICH article you read, I will point you to the actual and valid KB - article for your question:

How can I reset the admin password for mysql ?

Pls. visit: => How to get or reset password for admin account in Plesk

If you experience any issues/errors/problems, when following the suggestions/resolutions, pls. do not hesitate to tell us WHICH issues/errors/problems appear after you performed WHICH commands, so that people willing to help you can guide you to your next steps after they have been able to investigate your issue/error/problem. ​

 

[AdrianC]

Hi UFHH01
I was able to set parameter skip-grant-tables in /etc/my.cnf, then restarted mysql full server with reboot command and now I can run mysql commands without getting 'Access denied for user'
Plesk interface loads too.

Now I assume I need a command to set a certain password for mysql user "admin" AND another command to tell plesk to use this same password to connect to mysql database, correct ? What command is that ?

I looked in above article too but I am not sure that is what I need.
From another article I tried this:

plesk bin database --update-dbuser
Unrecognized option: '--update-dbuser'

 

[AdrianC]

UFHH01 , I hope you can still guide me, I think I made some progress, I got to running:

UPDATE mysql.user SET Password=PASSWORD('AES-128-****==') WHERE User='admin';

(using password from /etc/psa/.psa.shadow) but the query said that were 0 rows affected, and I see no "admin" if I list mysql users, should I create it (by what cmd ?) or does plesk use 'root' ?

 

[UFHH01]

Hi AdrianC,

for Plesk Onyx, after you ( successfully ) logged in as user "root" over SSH, pls. use the command:

cat /etc/psa/.psa.shadow

The encrypted, stored password is now displayed on your command line! ( something like: "$AES-128-XXXXXXXXXXXXXXXXXX" )

Pls. connect to your "mysql" - database, using the command:

mysql -uadmin mysql

You should now being logged in at your database - server and are using the "mysql" - database.​

Now pls. change your stored "admin" - password with the command:

UPDATE mysql.user SET Password=PASSWORD('YOUR_VERY_OWN_ENCRYPTED_PASSWORD_FOR_ADMIN-USER_ON_YOUR_SYSTEM') WHERE User='admin';

Afterwards, pls. use the commands:

GRANT GRANT OPTION ON *.* TO 'admin'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES;

exit

... and re-change the "skip-grant-tables" option in your "my.cnf".​

Now restart your MySQL - server. pls. !​

Afterwards, pls. use the command:

export PSA_PASSWORD=YOUR_NEW_PASSWORD
/usr/local/psa/admin/bin/ch_admin_passwd

... which changes the password to "YOUR_NEW_PASSWORD".

OR ( !!! )

use the command:

/usr/local/psa/bin/admin –get-login-link

... to get a temporary link for your Plesk Control Panel, to change the password for your user "admin" over your Plesk Control Panel.​

Additional note:

I imported some external mysql dump without realizing it also contained queries that altered mysql configurations (users, settings, etc).

Pls. consider as well to import a previous DAILY dump for your "mysql" - database ( pls. see: => "/var/lib/psa/dumps" ), if you experience further issues/errors/problems.

 

[AdrianC]

ufhh01 I appreciate the detailed answers but my main problem now was that I have no "admin" user in mysql users table, and any "update admin user password" queries (as you included above) would fail.
I also looked at the mysql daily dumps you mentioned but I can't find the admin user record in them.

I am able to login to plesk and even phpmyadmin if I start with skip-grant-tables in /etc/my.cnf
So I tried to create it but it fails while --skip-grant-tables

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'my_pass';
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

So I am left with exporting all ~30 sites and db now and wipe the server because I cannot properly identify the problem and solution.