After months of testing out Hestia, I am ready to bring over our production Vesta servers to Hestia. Unfortunately, I ran a test today, and we were not successful with the migration
I made backups of the user accounts in VestaCP and rsycned them over to the Hestia server. I placed them in /backup and ran the v-restore-user [username] [backup file].
The restore showed everything was successful, and so does the control panel. But when I load up the websites, they get the âError establishing a database connectionâ error (these are WordPress sites specifically but there are other frameworks such as Drupal and Joomla as well).
I did some testing, and it does appear that the databases are there, but the passwords must not have come over, or maybe they are reset to something new?
If I reset the database passwords manually in MySQL to match the original database user password from the Vesta server the sites do work.
So I guess all of that being said, how do I get the databases to come over with the proper passwords when restoring the backups? I am moving about 40ish user accounts, and many have multiple websites so it would be a very long drawn-out task to go into each one and find the password to change it manually.
For anyone willing to help me troubleshoot this, I have more information to add:
I found that one of the issues seems to be that the DB users on the Vesta server are using âmysq_native_passwordâ and Hestia is running the newer âcaching_sha2_passwordâ
For some reason, the import creates the DB users, but they are not able to log in. That being said I have tried using MySQL alter statements to update the user accounts to the new âcaching_sha2_passwordâ plugin but it fails like so:
ALTER USER '[username]'@'localhost' IDENTIFIED WITH caching_sha2_password BY '[password]';
ERROR 1396 (HY000): Operation ALTER USER failed for '[username]'@'localhost'
I have tried switching the MySQL server over to use the âmysql_native_passwordâ plugin and also tried a similar SQL query to alter the user to use that plugin. So far, none have been successful.
The only way I have been able to be successful is to drop the database user entirely and then re-add them.
I can write a script to do this, but the question becomes what permissions do I need to assign to these users so that they do not have access to databases they shouldnât have? I donât want to re-create all the database users just to find out that clients now have access to each otherâs databases.
Thank you! I should be able to write a script to pull this information out of Vesta and recreate it in Hestia. Any idea why it isnât working to begin with?
I also encountered an error when transferring some projects from Vesta:
AH01071: Got error 'PHP message: PHP Warning: mysqli::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]
While access through the phpMyAdmin interface works. This setting mysql does not help:
